| Bug #45774 | Selecting the data, ordered by the field, containing the same value in all rows | ||
|---|---|---|---|
| Submitted: | 26 Jun 2009 6:38 | Modified: | 2 Jul 2009 9:46 |
| Reporter: | Sergei Fundaev | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S3 (Non-critical) |
| Version: | 5.0.75, 4.1.22 | OS: | FreeBSD |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | limit, order by, SELECT | ||
[26 Jun 2009 6:54]
Valeriy Kravchuk
Sorry, but why do you expect any specific order of rows when you have the same value in all of them? The result will depend on storage engine (and optimizer plan!) used. I am sure your table is MyISAM. Check with InnoDB. Check EXPLAIN results for your selects. To summarize: this is NOT a bug.
[2 Jul 2009 9:46]
Sergei Fundaev
> Sorry, but why do you expect any specific order of rows when you have the same > value in all of them? In few, it is according to the architecture of the web-software. I.e. the situation, when the 'order_by' field contains the same value, is possible in our web-software, and in this case the problem, described above, occurs. > The result will depend on storage engine (and optimizer plan!) used. I am sure > your table is MyISAM. Check with InnoDB. Check EXPLAIN results for your > selects. Yes, when InnoDB is used, the problem doesn't exist. The 'EXPLAIN' results are presented here: MyISAM ---------- mysql> EXPLAIN SELECT field_id, order_by FROM test ORDER BY order_by LIMIT 130,8; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 138 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) InnoDB ---------- mysql> EXPLAIN SELECT field_id, order_by FROM test ORDER BY order_by LIMIT 130,8; +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | index | NULL | order_by | 4 | NULL | 138 | Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set (0.02 sec) On the one hand I understand, that it is strange and may be incorrect when the field, used to order the selection result, contains the same value for all rows. But on the other hand, why does MySQL return the duplicated result in this case? I think it would be more logically, if in the described case MySQL returns the same result as if the 'ORDER BY' statement is not used at all. Do you agree?
Description: MySQL server may return the invalid result if the selection is ordered by the field, containing the same value for all rows. I reproduced it with MySQL-server v5.0.75 and v4.1.22 (MySQL-client for both of them has version 4.1.22) with FreeBSD, and also with Darwin OS, MySQL v4.1.12. How to repeat: 1. To create the table in the database: ----------------------------- CREATE TABLE test ( field_id int(11) NOT NULL auto_increment, order_by int(11) NOT NULL DEFAULT 0, PRIMARY KEY(field_id), KEY (order_by) ) TYPE=MyISAM; ----------------------------- 2. To insert the data: ----------------------------- INSERT INTO test (field_id,order_by) VALUES(1,0); INSERT INTO test (field_id,order_by) VALUES(2,0); INSERT INTO test (field_id,order_by) VALUES(3,0); ... INSERT INTO test (field_id,order_by) VALUES(136,0); INSERT INTO test (field_id,order_by) VALUES(137,0); INSERT INTO test (field_id,order_by) VALUES(138,0); ----------------------------- 3. Try to select the data with this query: ----------------------------- SELECT field_id, order_by FROM test ORDER BY order_by LIMIT 130,10; ----------------------------- I got the following result: +----------+----------+ | field_id | order_by | +----------+----------+ | 45 | 0 | | 46 | 0 | | 47 | 0 | | 48 | 0 | | 49 | 0 | | 50 | 0 | | 51 | 0 | | 69 | 0 | +----------+----------+ Suggested fix: Unfortunately I don't now how to fix it, but I found out, that the problem doesn't occur if the following query is used: ----------------------------- SELECT field_id, order_by FROM test ORDER BY order_by LIMIT 130,7; ----------------------------- In this case the result is correct: +----------+----------+ | field_id | order_by | +----------+----------+ | 131 | 0 | | 132 | 0 | | 133 | 0 | | 134 | 0 | | 135 | 0 | | 136 | 0 | | 137 | 0 | +----------+----------+ But if I try to obtain also the latest row: ----------------------------- SELECT field_id, order_by FROM test ORDER BY order_by LIMIT 130,8; ----------------------------- I obtain the invalid result: +----------+----------+ | field_id | order_by | +----------+----------+ | 45 | 0 | | 46 | 0 | | 47 | 0 | | 48 | 0 | | 49 | 0 | | 50 | 0 | | 51 | 0 | | 69 | 0 | +----------+----------+