MySQL Bugs: #61227: where->order

Bug #61227	where->order
Submitted:	19 May 2011 11:55	Modified:	19 May 2011 12:45
Reporter:	ttt ttt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51, 5.0.91, 5.1.57	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
use or not use index -same query, different result.
use index: ORDER->WHERE->RESULT
not use index: WHERE->ORDER->RESULT

different result = very-very bad

How to repeat:
CREATE TABLE `test11` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`a` INT NOT NULL ,`b` INT NOT NULL, INDEX ( `a` ) ) ENGINE = MYISAM ;

INSERT IGNORE INTO `test11` (`id`, `a`, `b`) VALUES (NULL, '1', '1'), (NULL, '1', '5'), (NULL, '1', '2'), (NULL, '1', '3'), (NULL, '1', '4'), (NULL, '1', '6'), (NULL, '1', '7'), (NULL, '1', '8'), (NULL, '1', '9'), (NULL, '2', '1'), (NULL, '2', '2'), (NULL, '2', '3'), (NULL, '2', '4'), (NULL, '2', '5'), (NULL, '2', '6'), (NULL, '2', '7'), (NULL, '2', '8'), (NULL, '2', '9'), (NULL, '2', '0'), (NULL, '1', '0');

set @count=0,@ownid=0,@count_limit=2;
SELECT `id`, `a`,`b` FROM `test11` IGNORE INDEX(a)  WHERE  if (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a) ) AND @count<@count_limit   ORDER BY  `a` 

Array ( [id] => 1 [a] => 1 [b] => 1 )
Array ( [id] => 2 [a] => 1 [b] => 5 )
Array ( [id] => 20 [a] => 1 [b] => 0 )
Array ( [id] => 10 [a] => 2 [b] => 1 )
Array ( [id] => 11 [a] => 2 [b] => 2 ) 

set @count=0,@ownid=0,@count_limit=2;
SELECT `id`, `a`,`b` FROM `test11` FORCE INDEX(a)  WHERE  if (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a)  ) AND @count<@count_limit   ORDER BY  `a` 

Array ( [id] => 1 [a] => 1 [b] => 1 )
Array ( [id] => 2 [a] => 1 [b] => 5 )
Array ( [id] => 10 [a] => 2 [b] => 1 )
Array ( [id] => 11 [a] => 2 [b] => 2 ) 

Suggested fix:
always ignore index for sort if where condition depend of previous value or always use sort before where

Please, check if the same problem happens with recent version, 5.0.92.

sorry, i hav't 5.0.92 at this time

Verified with 5.0.91 and 5.1.57 on Windows XP:

mysql> set @count=0,@ownid=0,@count_limit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `a`,`b` FROM `test11` IGNORE INDEX(a)  WHERE  if
    -> (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a) ) AND @count<@c
ount_limit
    -> ORDER BY  `a`;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 1 | 5 |
| 20 | 1 | 0 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
+----+---+---+
5 rows in set (0.05 sec)

mysql> set @count=0,@ownid=0,@count_limit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `a`,`b` FROM `test11` FORCE INDEX(a)  WHERE  if
    -> (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a)  ) AND @count<@
count_limit
    -> ORDER BY  `a`;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 1 | 5 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
+----+---+---+
4 rows in set (0.00 sec)

On 5.5.11 the results are different from the above, but same with FORCE INDEX() and without:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `test11` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`a`
INT NOT NULL
    -> ,`b` INT NOT NULL, INDEX ( `a` ) ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT IGNORE INTO `test11` (`id`, `a`, `b`) VALUES (NULL, '1', '1'), (NU
LL, '1', '5'),
    -> (NULL, '1', '2'), (NULL, '1', '3'), (NULL, '1', '4'), (NULL, '1', '6'), (
NULL, '1', '7'),
    -> (NULL, '1', '8'), (NULL, '1', '9'), (NULL, '2', '1'), (NULL, '2', '2'), (
NULL, '2', '3'),
    -> (NULL, '2', '4'), (NULL, '2', '5'), (NULL, '2', '6'), (NULL, '2', '7'), (
NULL, '2', '8'),
    -> (NULL, '2', '9'), (NULL, '2', '0'), (NULL, '1', '0');
Query OK, 20 rows affected (0.06 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> set @count=0,@ownid=0,@count_limit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `a`,`b` FROM `test11` IGNORE INDEX(a)  WHERE  if
    -> (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a) ) AND @count<@c
ount_limit
    -> ORDER BY  `a`
    -> ;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 1 | 5 |
+----+---+---+
2 rows in set (0.05 sec)

mysql> set @count=0,@ownid=0,@count_limit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `a`,`b` FROM `test11` FORCE INDEX(a)  WHERE  if
    -> (@ownid=a,(@count:=@count+1), (@count:=0)  OR (@ownid:=a)  ) AND @count<@
count_limit
    -> ORDER BY  `a`
    -> ;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
|  2 | 1 | 5 |
+----+---+---+
2 rows in set (0.00 sec)