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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1 OS:Any (all)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[1 Jun 2006 13:45] [ name withheld ]
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)
[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.