Hi everyone, I noticed that count(*) is giving 0 for dangling tuples in a query with a group by, when it should give 1. This is happening only in some situations, in particular after an alter table where I define a foreign key. Here is a sample code to show what I mean:
CREATE TABLE TABLE_1
( X INT NOT NULL PRIMARY KEY);
CREATE TABLE TABLE_2
( Y INT NOT NULL );
INSERT INTO TABLE_1
INSERT INTO TABLE_2
If I now do this query:
SELECT T1.X, COUNT (*) as c1
from TABLE_1 T1 left join TABLE_2 T2 on (T1.X = T2.Y)
group by T1.X;
I get the expected result with at least 1 for every row.
But the result set changes after this after table followed by a runstats:
ALTER TABLE TABLE_2
ADD CONSTRAINT fk FOREIGN KEY (Y) REFERENCES TABLE_1 ;
RUNSTATS ON TABLE TABLE_1;
Now the same query gives me 0 in c1 for the dangling tuples which it’s not correct.
I noticed that the same problem happens when I do the following: create the tables, insert some values, define the foreign key WITHOUT executing runstats, executing
REORG TABLE TABLE_1;
REORG TABLE TABLE_2;
And execute the query written above with the select count(*). This bring to the same WRONG result set with some zeros for dangling tuples.
BUT it’s even more weird that if I execute the query at least once before executing REORG TABLE, the problem doesn’t show up (neither in the query before REORG nor in the result of the query after REORG) and the result set is the correct one. It seems like the first query changes the behaviour of REORG TABLE.
An user on stackoverflow, where I posted this [question], suggested me to ask here to get some more information.
I would like to know if you guys also think that this is a bug or if I’m making a mistake somewhere.
NOTE: this is happening on a v9.7 server, using both v9.7 and v11.1 clients (clients running on windows 10 64bit, while the server is the university’s one, running on Linux 64bit).