MySQL Bugs: #4814: Creating index causes different (incorrect) results in subquery

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	CPU Architecture:	Any

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.

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.

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)

Sorry, I used wrong bug report for changing

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)

Thank you for bug report. Bug is fixed, patch is pushed in  our source repository and will 
be present in next server release