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: | |
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
[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)