| Bug #62615 | limit and order by do not work properly when used together | ||
|---|---|---|---|
| Submitted: | 4 Oct 2011 9:01 | Modified: | 26 Jan 2012 19:08 |
| Reporter: | Sim Cec | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.1.52 | OS: | Other (CenTOs 2.6.32-71.29.1.e.l6.i686) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | limit, order by | ||
[4 Oct 2011 11:53]
Sim Cec
modified the severity level from S3 to S4 because I read the definition of S3 "S3 (Non-critical): Represents a minor loss of service, inconvenient usage; but a workaround is available." and since I did not find a workaround I though it was more an S4 issue.
[4 Oct 2011 11:55]
Sim Cec
modified the severity level from S3 to S2 because I read the definition of S3 "S3 (Non-critical): Represents a minor loss of service, inconvenient usage; but a workaround is available." and since I did not find a workaround I though it was more an S2 issue.
[4 Oct 2011 14:25]
Valeriy Kravchuk
Please, check if the same problem happens with a newer version, 5.1.59 (recently released). For me everything looks OK: mysql> select host, password from mysql.user where host='localhost' order by hos t limit 0,7; +-----------+-------------------------------------------+ | host | password | +-----------+-------------------------------------------+ | localhost | | | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | localhost | | | localhost | *556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5 | | localhost | | | localhost | *87932843C6FE9A5D6BBACEEFD11AB9B69045B621 | +-----------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> select host, password from mysql.user where host='localhost' order by hos t limit 0,1; +-----------+----------+ | host | password | +-----------+----------+ | localhost | | +-----------+----------+ 1 row in set (0.00 sec) mysql> select host, password from mysql.user where host='localhost' order by hos t limit 1,1; +-----------+-------------------------------------------+ | host | password | +-----------+-------------------------------------------+ | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +-----------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> select host, password from mysql.user where host='localhost' order by hos t limit 2,1; +-----------+-------------------------------------------+ | host | password | +-----------+-------------------------------------------+ | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | +-----------+-------------------------------------------+ 1 row in set (0.02 sec) mysql> select version(); +----------------------+ | version() | +----------------------+ | 5.1.58-community-log | +----------------------+ 1 row in set (0.09 sec)
[5 Oct 2011 11:51]
Sim Cec
Hi again I haven't tried on the new version (i will do it soon though) but I have an update about the issue ( if you want to reproduce the issue you can find enclosed the SQL file to import): mysql> select * from user2 order by host limit 0,7 ; +-----------+-------------------------------------------+--------+ | host | password | user | +-----------+-------------------------------------------+--------+ | localhost | *87932843C6FE9A5D6BBACEEFD11AB9B69045B621 | root | | localhost | | dave | | localhost | *556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5 | john | | localhost | | rita | | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mary | | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | rosy | | localhost | | carmen | +-----------+-------------------------------------------+--------+ 7 rows in set (0.01 sec) **********COMMENT FOR THE RESULT BELOW ************** * Why the first is now carmen? it is the last! ******* ****************************************************** mysql> select * from user2 order by host limit 0,1 ; +-----------+----------+--------+ | host | password | user | +-----------+----------+--------+ | localhost | | carmen | +-----------+----------+--------+ 1 row in set (0.00 sec) **********COMMENT FOR THE RESULT BELOW ************************** * Why do you skip the user root? (also in the next example **** * where i change the position of root, it skips the user root **** ****************************************************************** mysql> select * from user2 order by host limit 1,1 ; +-----------+----------+------+ | host | password | user | +-----------+----------+------+ | localhost | | dave | +-----------+----------+------+ 1 row in set (0.01 sec) mysql> select * from user2 order by host limit 2,1 ; +-----------+-------------------------------------------+------+ | host | password | user | +-----------+-------------------------------------------+------+ | localhost | *556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5 | john | +-----------+-------------------------------------------+------+ 1 row in set (0.00 sec) mysql> select * from user2 order by host limit 3,1 ; +-----------+----------+------+ | host | password | user | +-----------+----------+------+ | localhost | | rita | +-----------+----------+------+ 1 row in set (0.00 sec) mysql> select * from user2 order by host limit 4,1 ; +-----------+-------------------------------------------+------+ | host | password | user | +-----------+-------------------------------------------+------+ | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mary | +-----------+-------------------------------------------+------+ 1 row in set (0.00 sec) mysql> select * from user2 order by host limit 5,1 ; +-----------+-------------------------------------------+------+ | host | password | user | +-----------+-------------------------------------------+------+ | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | rosy | +-----------+-------------------------------------------+------+ 1 row in set (0.00 sec) mysql> select * from user2 order by host limit 6,1 ; +-----------+----------+--------+ | host | password | user | +-----------+----------+--------+ | localhost | | carmen | +-----------+----------+--------+ 1 row in set (0.00 sec) mysql> select * from user2 order by host limit 7,1 ; Empty set (0.01 sec) **********COMMENT FOR HTHE RESULT BELOW ***** * UHMmmm this is not good ******* ********************************************* mysql> select * from user2 order by host limit 0,2 ; +-----------+----------+--------+ | host | password | user | +-----------+----------+--------+ | localhost | | carmen | | localhost | | dave | +-----------+----------+--------+ 2 rows in set (0.00 sec) mysql> select * from user2 order by host limit 0,3 ; +-----------+-------------------------------------------+------+ | host | password | user | +-----------+-------------------------------------------+------+ | localhost | *87932843C6FE9A5D6BBACEEFD11AB9B69045B621 | root | | localhost | | dave | | localhost | *556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5 | john | +-----------+-------------------------------------------+------+ 3 rows in set (0.00 sec) -------------------------------------------------------------------------- I then created a second table user3 where the first ordered element has password null mysql> select * from user3 order by host limit 0,7 ; +-----------+-------------------------------------------+--------+ | host | password | user | +-----------+-------------------------------------------+--------+ | localhost | | carmen | | localhost | *87932843C6FE9A5D6BBACEEFD11AB9B69045B621 | root | | localhost | | dave | | localhost | *556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5 | john | | localhost | | rita | | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mary | | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | rosy | +-----------+-------------------------------------------+--------+ 7 rows in set (0.01 sec) **********COMMENT FOR HTHE RESULT BELOW ********* * Ok, it take Carmen and not rosy *** ************************************************* mysql> select * from user3 order by host limit 0,1 ; +-----------+----------+--------+ | host | password | user | +-----------+----------+--------+ | localhost | | carmen | +-----------+----------+--------+ 1 row in set (0.00 sec) **********COMMENT FOR HTHE RESULT BELOW *************** * IT SKIPS THE ROOT ACCCOUT AGAIN!! ******* ******************************************************* mysql> select * from user3 order by host limit 0,2 ; +-----------+----------+--------+ | host | password | user | +-----------+----------+--------+ | localhost | | carmen | | localhost | | dave | +-----------+----------+--------+ 2 rows in set (0.00 sec) mysql> select * from user3 order by host limit 1,1 ; +-----------+----------+------+ | host | password | user | +-----------+----------+------+ | localhost | | dave | +-----------+----------+------+ 1 row in set (0.00 sec) **********COMMENT FOR HTHE RESULT BELOW ***** * This is ok ******* ********************************************* mysql> select * from user3 order by host limit 0,3 ; +-----------+-------------------------------------------+--------+ | host | password | user | +-----------+-------------------------------------------+--------+ | localhost | | carmen | | localhost | *87932843C6FE9A5D6BBACEEFD11AB9B69045B621 | root | | localhost | | dave | +-----------+-------------------------------------------+--------+ 3 rows in set (0.00 sec) Why does it skips the password for the user root in both cases?? Can you try to import in the new version the files that I've enclosed? (This time the database name has been "test" and not "mysql") thanks
[5 Oct 2011 13:04]
Valeriy Kravchuk
You order rows by host, and all rows have the same value, "localhost", in this column. Why do you expect any specific order for these rows? Check EXPLAIN results for all your queries. I expect that execution path may be different...
[5 Oct 2011 13:29]
Sim Cec
I order by host because was your example, in my first example i had different host names
Below you can find the results for the command "EXPLAIN"
As you can see when I use limit 0,1 and limit 0,2 -that is when I get the unexpected result - the value for "rows" is 1 and 2 respectively instead of 7 !!
mysql> explain select * from user2 order by host limit 0,1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user2
type: index
possible_keys: NULL
key: PRIMARY
key_len: 76
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> explain select * from user2 order by host limit 0,2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user2
type: index
possible_keys: NULL
key: PRIMARY
key_len: 76
ref: NULL
rows: 2
Extra:
1 row in set (0.01 sec)
mysql> explain select * from user2 order by host limit 0,3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using filesort
1 row in set (0.00 sec)
----------------------------------------------------
mysql> explain select * from user3 order by host limit 0,1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user3
type: index
possible_keys: NULL
key: PRIMARY
key_len: 76
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> explain select * from user3 order by host limit 0,2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user3
type: index
possible_keys: NULL
key: PRIMARY
key_len: 76
ref: NULL
rows: 2
Extra:
1 row in set (0.00 sec)
mysql> explain select * from user3 order by host limit 0,3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using filesort
1 row in set (0.01 sec)
mysql>
[5 Oct 2011 14:31]
Sim Cec
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ I Have just tried with the new version 5.1.59 and it does the same problem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[10 Oct 2011 9:17]
Sim Cec
Any update about this bug?
[26 Dec 2011 19:08]
Sveta Smirnova
Thank you for the feedback. > I order by host because was your example, in my first example i had different host names Please send us test case with different host names. If host names are same described behavior does not contradict with how ORDER BY supposed to work. Run CHECK TABLE and OPTIMIZE TABLE also.
[27 Jan 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: cannot retrieve a single line of a result when using order by and limit <num>,1. The problem occurs only when null cells are present in the result to order How to repeat: I have MySQL installed on 2 CentOS machines and for both I cannot solve this issue. See the issue surrounded by * to see the problem mysql> select host, password from mysql.user; +-----------------------+-------------------------------------------+ | host | password | +-----------------------+-------------------------------------------+ | localhost | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | | localhost.localdomain | | | 127.0.0.1 | | | localhost | | | localhost.localdomain | | +-----------------------+-------------------------------------------+ 5 rows in set (0.01 sec) mysql> select password from mysql.user order by host; +-------------------------------------------+ | password | +-------------------------------------------+ | | | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | | | | | | | +-------------------------------------------+ 5 rows in set (0.01 sec) mysql> select password from mysql.user order by host limit 0,5; +-------------------------------------------+ | password | +-------------------------------------------+ | | | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | | | | | | | +-------------------------------------------+ 5 rows in set (0.01 sec) mysql> select password from mysql.user order by host limit 0,1; +----------+ | password | +----------+ | | +----------+ 1 row in set (0.01 sec) ****************** HERE THE PROBLEM * ********************************************************************* * mysql> select password from mysql.user order by host limit 1,1; * +----------+ * | password | * +----------+ * | | * +----------+ * 1 row in set (0.01 sec) * * mysql> * * ********************************************************************* mysql> select password from mysql.user order by host limit 2,1; +----------+ | password | +----------+ | | +----------+ 1 row in set (0.01 sec) mysql> select password from mysql.user order by host limit 3,1; +----------+ | password | +----------+ | | +----------+ 1 row in set (0.00 sec) mysql> select password from mysql.user order by host limit 4,1; +----------+ | password | +----------+ | | +----------+ 1 row in set (0.00 sec) Suggested fix: Return the right value when the lines are singularly called