Bug #20204 "order by" changes the results returned
| Submitted: | 1 Jun 2006 13:45 | Modified: | 4 Oct 2006 18:49 |
|---|---|---|---|
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 4.1 | OS: | Any (all) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[2 Jun 2006 2:09]
MySQL Verification Team
Thank you for the bug report.
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbz
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.23-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table a ( i varchar(45)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values ('123');
Query OK, 1 row affected (0.00 sec)
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i order by 1;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| | 123 |
+----------------+------+
1 row in set (0.01 sec)
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| 3 | 123 |
+----------------+------+
1 row in set (0.00 sec)
mysql> alter table a engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i order by 1;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| 3 | 123 |
+----------------+------+
1 row in set (0.01 sec)
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| 3 | 123 |
+----------------+------+
1 row in set (0.00 sec)
mysql>
-----------------------------------------------------------------------------
miguel@hegel:~/dbs/5.1> bin/mysql -uroot dbz
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table a ( i varchar(45)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into a values ('123');
Query OK, 1 row affected (0.01 sec)
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i order by 1;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| | 123 |
+----------------+------+
1 row in set (0.01 sec)
mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| 3 | 123 |
+----------------+------+
1 row in set (0.00 sec)
mysql>
-----------------------------------------------------------------------------
[27 Jul 2006 20:16]
Heikki Tuuri
Hi!
I am able to repeat this with a self-compiled 5.0.23.
I stepped this through inside gdb:
select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i order by 1;
+----------------+------+
| substr(Z.i,-1) | i |
+----------------+------+
| NULL | 123 |
+----------------+------+
1 row in set (3 min 24.19 sec)
MySQL does a table scan first using one handle on the table. InnoDB does return the correct row when MySQL asks for the first row:
#0 row_search_for_mysql (buf=0x8987f30 "", mode=1, prebuilt=0x4034bc68,
match_mode=0, direction=0) at row0sel.c:3060
#1 0x082207f9 in ha_innobase::index_read (this=0x8987d90, buf=0x0,
key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:3827
#2 0x08220d64 in ha_innobase::index_first (this=0x8987d90, buf=0x0)
at ha_innodb.cc:4074
#3 0x08220e8c in ha_innobase::rnd_next (this=0x8987d90, buf=0x8987f30 "")
at ha_innodb.cc:4168
#4 0x08210535 in rr_sequential (info=0x89a6054) at records.cc:295
#5 0x081be3a9 in join_init_read_record (tab=0x89a6018) at sql_select.cc:10294
#6 0x081bd070 in sub_select (join=0x89a1e48, join_tab=0x89a6018,
end_of_records=false) at sql_select.cc:9664
#7 0x081bcbc2 in do_select (join=0x89a1e48, fields=0x0, table=0x89a3480,
(gdb) x/20b 0x8987f30
0x8987f30: 0x00 0x03 0x31 0x32 0x33 0x20 0x20 0x20
0x8987f38: 0x20 0x20 0x20 0x20 0x20 0x20 0x20 0x20
0x8987f40: 0x20 0x20 0x20 0x20
After that, InnoDB correctly returns end of file:
Breakpoint 5, ha_innobase::rnd_next (this=0x8987d90, buf=0x8987f30 "")
at ha_innodb.cc:4178
4178 }
Current language: auto; currently c++
(gdb) next
rr_sequential (info=0x89a6054) at records.cc:297
297 if (info->thd->killed)
(gdb)
306 if (tmp != HA_ERR_RECORD_DELETED)
(gdb)
206 if (error == HA_ERR_END_OF_FILE)
(gdb)
205 {
(gdb)
206 if (error == HA_ERR_END_OF_FILE)
(gdb)
207 error= -1;
Then with another table handle:
#0 row_search_for_mysql (buf=0x8987890 "", mode=1, prebuilt=0x4034aa68,
match_mode=0, direction=0) at row0sel.c:3060
#1 0x082207f9 in ha_innobase::index_read (this=0x89876f0, buf=0x0,
key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:3827
#2 0x08220d64 in ha_innobase::index_first (this=0x89876f0, buf=0x0)
at ha_innodb.cc:4074
#3 0x08220e8c in ha_innobase::rnd_next (this=0x89876f0, buf=0x8987890 "")
at ha_innodb.cc:4168
#4 0x08210535 in rr_sequential (info=0x89a61b8) at records.cc:295
#5 0x081bd426 in flush_cached_records (join=0x89a1e48, join_tab=0x89a617c,
skip_last=false) at sql_select.cc:10292
#6 0x081bcee7 in sub_select_cache (join=0x89a1e48, join_tab=0x89a617c,
end_of_records=true) at sql_select.cc:9506
#7 0x081bd0a0 in sub_select (join=0x89a1e48, join_tab=0x89a6018,
end_of_records=false) at sql_select.cc:9629
#8 0x081bccf5 in do_select (join=0x89a1e48, fields=0x0, table=0x89a3480,
procedure=0x0) at sql_select.cc:9430
#9 0x081b19a3 in JOIN::exec (this=0x89a1e48) at sql_select.cc:1360
#10 0x081b2eca in mysql_select (thd=0x8986278, rref_pointer_array=0x8986664,
(gdb) x/20b 0x8987890
0x8987890: 0x00 0x03 0x31 0x32 0x33 0x20 0x20 0x20
0x8987898: 0x20 0x20 0x20 0x20 0x20 0x20 0x20 0x20
0x89878a0: 0x20 0x20 0x20 0x20
(gdb) c
And after that with that same handle:
Breakpoint 5, ha_innobase::rnd_next (this=0x89876f0, buf=0x8987890 "")
at ha_innodb.cc:4178
4178 }
Current language: auto; currently c++
(gdb) next
rr_sequential (info=0x89a61b8) at records.cc:297
297 if (info->thd->killed)
(gdb)
306 if (tmp != HA_ERR_RECORD_DELETED)
(gdb)
206 if (error == HA_ERR_END_OF_FILE)
(gdb)
205 {
(gdb)
206 if (error == HA_ERR_END_OF_FILE)
(gdb)
207 error= -1;
The bug is probably in MySQL code.
Regards,
Heikki
[8 Sep 2006 14:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11621 ChangeSet@1.2540, 2006-09-08 19:02:54+05:00, ramil@mysql.com +3 -0 Fix for bug #20204: "order by" changes the results returned Item_substr's results are improperly stored in a temporary table due to wrongly calculated max_length value for multi-byte charsets if two arguments specified.
[21 Sep 2006 11:07]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12331 ChangeSet@1.2571, 2006-09-21 16:05:01+05:00, ramil@mysql.com +3 -0 Fix for bug #20204: "order by" changes the results returned Item_substr's results are improperly stored in a temporary table due to wrongly calculated max_length value for multi-byte charsets if two arguments specified.
[3 Oct 2006 20:02]
Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:09]
Chad MILLER
Available in 5.1.12-beta.
[4 Oct 2006 2:04]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs. With InnoDB tables and character columns containing multi-byte character sets, adding ORDER BY to some queries changed the set of rows returned, and not just their order.
[4 Oct 2006 13:57]
Chad MILLER
Available in 4.1.22.
[4 Oct 2006 18:49]
Paul DuBois
Modifying the changelog entry: SUBSTR() results sometimes were stored improperly into a temporary table when multi-byte character sets were used. Also noted in 4.1.22 changelog.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: "order by" changes the rows returned, not just the order. The problem happens with InnoDB tables and charset UTF8 -- if I switch to MyISAM or switch to charset Latin1, MySQL works correctly. Unfortunately neither of these are good options in my case. How to repeat: mysql> create table a ( i varchar(45)) engine=innodb default charset utf8; Query OK, 0 rows affected (0.02 sec) mysql> insert into a values ('123'); Query OK, 1 row affected (0.02 sec) mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i order by 1; +----------------+------+ | substr(Z.i,-1) | i | +----------------+------+ | NULL | 123 | +----------------+------+ 1 row in set (0.00 sec) mysql> select substr(Z.i,-1),Z.i from a as Y join a as Z on Y.i=Z.i; +----------------+------+ | substr(Z.i,-1) | i | +----------------+------+ | 3 | 123 | +----------------+------+ 1 row in set (0.00 sec)