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

[19 May 2011 11:55] ttt ttt
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
[19 May 2011 12:30] Valeriy Kravchuk
Please, check if the same problem happens with recent version, 5.0.92.
[19 May 2011 12:33] ttt ttt
sorry, i hav't 5.0.92 at this time
[19 May 2011 12:45] Valeriy Kravchuk
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)