Bug #4814 Creating index causes different (incorrect) results in subquery
Submitted: 29 Jul 2004 20:38 Modified: 23 Aug 2004 22:38
Reporter: Keith Dreibelbis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.3-beta OS:Linux (Linux)
Assigned to: Oleksandr Byelkin

[29 Jul 2004 20:38] Keith Dreibelbis
Description:
An aggregate query involving a subquery gives incorrect results when an index is added.  Results seem to be shifted off by one row; I have seen the same off-by-one behavior in larger examples.  I verified this behavior in InnoDB, MyISAM and ISAM tables.

In the example below, "howmanyvalues" is supposed to indicate how many rows contain the given key.  e.g. 1 has one row, 2 has 2 rows etc.  Query 1 performs a COUNT/GROUP BY to verify that the table is set up as I expected, and it always gives the correct results.

Queries 2 and 3 do the same task, only in a more roundabout way, using a subquery.  Note that queries 2 and 3 are identical, but between them, an index is created.  Just to demonstrate that query 3 gives the wrong results, when the index is present.

How to repeat:
This is the SQL to pipe to mysql:

CREATE TABLE foo (howmanyvalues bigint, avalue int);

INSERT INTO foo VALUES (1, 1);

INSERT INTO foo VALUES (2, 1);
INSERT INTO foo VALUES (2, 2);

INSERT INTO foo VALUES (3, 1);
INSERT INTO foo VALUES (3, 2);
INSERT INTO foo VALUES (3, 3);

INSERT INTO foo VALUES (4, 1);
INSERT INTO foo VALUES (4, 2);
INSERT INTO foo VALUES (4, 3);
INSERT INTO foo VALUES (4, 4);

SELECT howmanyvalues, count(*) from foo group by howmanyvalues;

SELECT a.howmanyvalues, (SELECT count(*) from foo b where b.howmanyvalues = a.howmanyvalues) as mycount from foo a group by a.howmanyvalues;

CREATE INDEX foo_howmanyvalues_idx ON foo (howmanyvalues);

SELECT a.howmanyvalues, (SELECT count(*) from foo b where b.howmanyvalues = a.howmanyvalues) as mycount from foo a group by a.howmanyvalues;

Here are the results when I pipe this to mysql.  Note the incorrect results in Query 3:

howmanyvalues   count(*)
1       1
2       2
3       3
4       4
howmanyvalues   mycount
1       1
2       2
3       3
4       4
howmanyvalues   mycount
1       2
2       3
3       4
4       4

Suggested fix:
I don't know, I haven't worked on mysql source... but row n is getting the results of row (n+1).  Maybe the subquery is being computed after the index has already iterated, but before the result row has been produced.

The acceptance test for this bug is that queries 1, 2, and 3 should all return identical results, whatever the contents of the table are.
[29 Jul 2004 21:30] Matt Lord
This is a higher level problem as it occurs with myisam and innodb tables.  I verified this on linux
root@localhost:bugs~> system uname -a
Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 2003 i686 i686 i386 GNU/Linux
as well as win2k.
[12 Aug 2004 16:35] Oleksandr Byelkin
ChangeSet 
  1.1957 04/08/12 17:31:23 bell@sanja.is.com.ua +3 -0 
  in case of compound index fill all parts in optimized IN (BUG#4435)
[12 Aug 2004 16:35] Oleksandr Byelkin
Sorry, I used wrong bug report for changing
[13 Aug 2004 9:03] Oleksandr Byelkin
ChangeSet 
  1.1956 04/08/13 10:01:30 bell@sanja.is.com.ua +11 -0 
  skip resolving field in table list if table list is not accessable due to groupping 
(BUG#4814)
[23 Aug 2004 22:38] Oleksandr Byelkin
Thank you for bug report. Bug is fixed, patch is pushed in  our source repository and will 
be present in next server release