Hi All,

I've stumbled across a bug in 9.2.0 which appears to be fixed in 9.2.1 and in 10.0.0 but after reading the patch notes for both of those releases I'm unable to determine if this is fixed bug or was just fixed by accident in the higher releases.

What happens is this....
If a partitioned table (x) with sufficient data is queried with:
select count(distinct pid) from x;
I get the correct number (500000) for the data in the table.

However if I use essentially the same query to populate a second table with:
insert into y(pid) select distinct pid from x
Instead of the 500000 distinct pids I get 1496741 rows inserted. BTW. Tablex has 1500000 rows.

But if I create table y as select distinct pid from x....I get the correct number of rows!

The real problem here is that my chances of getting to upgrade the installation are remote at best, but I have a good chance of getting a patch install in place.

Martin Bowes

Can you reproduce it with smaller data volumes? Perhaps -assuming range
partitions - if you reduce the scope of the range in line with the
overall size of the table?



Are there stats involved? Secondary index?



With a testcase the process of figuring out which change fixed a bug
between two known points can be automated.



Cheers

Paul



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 28 April 2011 09:09
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Can you identify this bug?



Hi All,



I've stumbled across a bug in 9.2.0 which appears to be fixed in 9.2.1
and in 10.0.0 but after reading the patch notes for both of those
releases I'm unable to determine if this is fixed bug or was just fixed
by accident in the higher releases.



What happens is this....

If a partitioned table (x) with sufficient data is queried with:

select count(distinct pid) from x;

I get the correct number (500000) for the data in the table.



However if I use essentially the same query to populate a second table
with:

insert into y(pid) select distinct pid from x

Instead of the 500000 distinct pids I get 1496741 rows inserted. BTW.
Table x has 1500000 rows.



But if I create table y as select distinct pid from x....I get the
correct number of rows!



The real problem here is that my chances of getting to upgrade the
installation are remote at best, but I have a good chance of getting a
patch install in place.



Martin Bowes

Hi Paul,

No stats, no secondary index.

Test case is easy....see attached. It uses a perl script to generate the data.

FYI I have already raised this with the Corp as Issue 150398

Marty

From: Paul Mason [mailto:Paul.Mason (AT) ingres (DOT) com]
Sent: 28 April 2011 10:03
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Can you identify this bug?

Can you reproduce it with smaller data volumes? Perhaps -assuming range partitions - if you reduce the scope of the range in line with the overall size of the table?

Are there stats involved? Secondary index?

With a testcase the process of figuring out which change fixed a bug between two known points can be automated.

Cheers
Paul

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 28 April 2011 09:09
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Can you identify this bug?

Hi All,

I've stumbled across a bug in 9.2.0 which appears to be fixed in 9.2.1 and in 10.0.0 but after reading the patch notes for both of those releases I'm unable to determine if this is fixed bug or was just fixed by accident in the higher releases.

What happens is this....
If a partitioned table (x) with sufficient data is queried with:
select count(distinct pid) from x;
I get the correct number (500000) for the data in the table.

However if I use essentially the same query to populate a second table with:
insert into y(pid) select distinct pid from x
Instead of the 500000 distinct pids I get 1496741 rows inserted. BTW. Tablex has 1500000 rows.

But if I create table y as select distinct pid from x....I get the correct number of rows!

The real problem here is that my chances of getting to upgrade the installation are remote at best, but I have a good chance of getting a patch install in place.

Martin Bowes

On Apr 28, 2011, at 4:09 AM, Martin Bowes wrote:

I wonder if it has something to do with the defaulting of the other two "y"
columns. What you're really doing (in the testcase) is
insert into static_pids (pid,action,action_date) select distinct pid,'I',date('now') from x;

It could be that the bug that was fixed had something to do with getting the
sort list buggered up somehow. I don't know why it would only happen with
partitioned tables though.

I don't recall this symptom coming up, whatever the cause might have been.

Karl

I just realised my last couple of replies were direct to Marty not the
mailing list.

So in case anyone's interested:

It was the fix for bug 123442 which has to do with generating default
values. Karl's suggestion below does indeed give the correct number of
rows. Also the table didn't need to be partitioned to reproduce the
problem.

Cheers
Paul