MySQL Bugs: #25427: Use of ambiguous column in an expression in ORDER BY clause crashes server

Bug #25427	Use of ambiguous column in an expression in ORDER BY clause crashes server
Submitted:	5 Jan 2007 1:47	Modified:	16 Jan 2007 6:46
Reporter:	Travers Carter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.15-BK, 5.0.34-BK, 4.1.23-BK, 5.0.27, 4.1.20	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	crash, regression

Description:
Referencing an ambiguous column alias in an expression in the ORDER BY clause of a query causes the server to crash.

Referencing the ambiguous column alias directly, not as part of an expression, triggers an error, which is the expected behaviour.

How to repeat:
mysql> DROP TABLE IF EXISTS orderbycrash;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE orderbycrash (Value tinyint(3) unsigned NOT NULL default '0');
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT t1.Value as Val, t2.Value AS Val FROM orderbycrash t1, orderbycrash t2 ORDER BY  Val > 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
Raise an error as happens when referencing the column directly:

mysql> SELECT t1.Value as Val, t2.Value AS Val FROM orderbycrash t1, orderbycrash t2 ORDER BY  Val;
ERROR 1052 (23000): Column 'Val' in order clause is ambiguous

Thank you for a bug report. Verified just as described with 5.0.24-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE orderbycrash (Value tinyint(3) unsigned NOT NULL default
    -> '0');
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT t1.Value as Val, t2.Value AS Val FROM orderbycrash t1,
    -> orderbycrash t2 ORDER BY  Val > 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
070105 09:46:47  mysqld restarted

Sorry, verified on 5.0.34-Bk, surely. Resolved stack trace:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 25427.st
ack
0x81d9470 handle_segfault + 412
0x814bdde _ZN10Item_field10fix_fieldsEP3THDPP4Item + 238
0x81664f9 _ZN9Item_func10fix_fieldsEP3THDPP4Item + 223
0x82478c9 _Z18find_order_in_listP3THDPP4ItemP13st_table_listP8st_orderR4ListIS1_
ESA_b + 795
0x82479b7 _Z11setup_orderP3THDPP4ItemP13st_table_listR4ListIS1_ES8_P8st_order +
55
0x824d582 _Z19setup_without_groupP3THDPP4ItemP13st_table_listS5_R4ListIS1_ES8_S3
_P8st_orderSA_Pb + 252
0x822ad93 _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st
_select_lexP18st_select_lex_unit + 665
0x822fd74 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 458
0x822aa2b _Z13handle_selectP3THDP6st_lexP13select_resultm + 339
0x81f18cb _Z21mysql_execute_commandP3THD + 1523
0x81f923b _Z11mysql_parseP3THDPcj + 475
0x81efb99 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951
0x81ef3ea _Z10do_commandP3THD + 526
0x81ee5d6 handle_one_connection + 982
0x40050aa7 _end + 932068135
0x40247c2e _end + 934128814

Latest 5.1.15-BK and 4.123-BK crash with this test case also.

$.0.28-BK works as expected:

openxs@suse:~/dbs/4.0> bin/mysql -uroot -proot --port=3340 --host=127.0.0.1 tes
t
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.28-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE orderbycrash (Value tinyint(3) unsigned NOT NULL default '0
');
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT t1.Value as Val, t2.Value AS Val FROM orderbycrash t1, orderbycra
sh t2 ORDER BY  Val > 1;
ERROR 1054: Unknown column 'Val' in 'order clause'

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/17831

ChangeSet@1.2593, 2007-01-10 00:27:11-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25427.
  In the method Item_field::fix_fields we try to resolve the name of
  the field against the names of the aliases that occur in the select
  list. This is done by a call of the function find_item_in_list.
  When this function finds several occurrences of the field name
  it sends an error message to the error queue and returns 0.
  Yet the code did not take into account that find_item_in_list
  could return 0 and tried to dereference the returned value.

Fixed in 4.1.23, 5.0.34, 5.1.15-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.23, 5.0.34, and 5.1.15 changelogs.