| Bug #4430 | undeterminated order of rows in resultset | ||
|---|---|---|---|
| Submitted: | 7 Jul 2004 9:13 | Modified: | 16 Aug 2004 20:28 |
| Reporter: | Victor Vagin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0 | OS: | Linux (Linux Suse 7.1) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[12 Jul 2004 14:00]
Hartmut Holzgraefe
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: Manual Section "3.3.4.4 Sorting Rows" starts with: "You may have noticed in the preceding examples that the result rows are displayed in no particular order. ..." This also implies that even on subsequent executions of the same query there is no guarantee that results are produced in the same order.
[12 Jul 2004 15:05]
Sergei Golubchik
Hartmut, it's not enough. We need to know *exactly* why this behaviour happens.
[7 Aug 2004 0:42]
Guilhem Bichot
I got it in 4.0 on Linux. For me the order of rows is different between ./mysql-test-run --local --skip-innodb t/sql_mode.test and ./mysql-test-run --local t/sql_mode.test --valgrind or --debug give same results as --local --skip-innodb. Valgrind shows no uninited memory.
[13 Aug 2004 0:20]
Sergey Petrunya
It turned out to have an easy explanation.
filesort sorts the data by {masks.p, heap_table_rowid}.
The rowid of a heap table is a pointer. On a little-endian machine least-significant bytes of pointer are stored first. (see ha_heap::position)
The sorting uses ptr_compare_0 function, which assumes that first bytes are most significant.
Therefore, the record pointers of a heap table are compared incorrectly and the order of result depens on where heap table was allocated.
[13 Aug 2004 23:25]
Sergey Petrunya
ChangeSet@1.1952, 2004-08-14 01:21:23+04:00, sergefp@mysql.com Fix and testcase for BUG#4430
[16 Aug 2004 20:28]
Sergey Petrunya
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: The changeset posted previously will not be applied. The initial behavior was correct.
Description: A query returns resultset in underterminated order (different order from time to time for the same table and the same query) It looks like - mysqld uses an uninitialized variable somewhere (then it should be fixed) - mysqld optymizer has such an effect (then it should be described in the manual) How to repeat: run this commands from mysql sources root: -------------------------------------------- cd mysql-test; echo " CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); INSERT t1 (a) VALUES ('\\'), ('\%'), ('\_'), ('\n'), ('\b'), ('\r'), ('\t'), ('%'); SELECT p, a, hex(a) FROM t1; select masks.p,'|', masks.a as mask,'|', hex(masks.a) as hex_mask,'|', examples.a as example,'|', hex(examples.a) as hex_example from t1 as masks left join t1 as examples on examples.a LIKE masks.a order by masks.p; DROP TABLE t1; " >> t/sql_mode.test mysql-test-run --do-test=sql_mode cp r/sql_mode.reject r/sql_mode.result mysql-test-run --do-test=sql_mode mysql-test-run --do-test=s -------------------------------------------- and get an error for last test (mysql-test-run --do-test=s) in sql_mode.. there will be new order in resultset of last select in sql_mode.test (if you change "order by masks.p;" to "order by masks.p, hex_example;" everything will be ok) Suggested fix: - find the unitialized variable in mysqld or - document that stuff