| 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.
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)