MySQL Bugs: #25126: Reference to non-existant column in UPDATE ... ORDER BY ... crashes server

Bug #25126	Reference to non-existant column in UPDATE ... ORDER BY ... crashes server
Submitted:	17 Dec 2006 22:17	Modified:	13 Mar 2007 19:04
Reporter:	Travers Carter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.26/4.1BK/5.0BK/5.1BK	OS:	Linux (Linux (CentOS v4.4))
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	crash

Description:
Referencing a non-existant column in both the SET clause and ORDER by clause of an ordered update causes the server to crash.

If the column is referenced only in the order by clause the expected error is recieved:
ERROR 1054 (42S22): Unknown column 'MissingCol' in 'order clause'

The problem occurrs with both MyISAM and InnoDB tables.

How to repeat:
mysql> use test;
mysql> CREATE TABLE orderedupdate (
  val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
);
mysql> UPDATE orderedupdate SET MissingCol = MissingCol ORDER BY MissingCol;
ERROR 2013 (HY000): Lost connection to MySQL server during query

# But if only referenced in the order by it works as expected
mysql> UPDATE orderedupdate SET MissingCol1 = MissingCol1 ORDER BY MissingCol;
ERROR 1054 (42S22): Unknown column 'MissingCol' in 'order clause'

Suggested fix:
Invalid/Non-existant columns in the ORDER BY clause should return an error message instead of crashing the server.

Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
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 2
Server version: 5.1.15-beta-debug Source distribution

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

mysql> CREATE TABLE orderedupdate (
    ->   val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE orderedupdate SET MissingCol = MissingCol ORDER BY MissingCol;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
--------------------------------------------------------------------------
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
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
Server version: 5.0.32-debug Source distribution

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

mysql> CREATE TABLE orderedupdate (
    -> val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE orderedupdate SET MissingCol = MissingCol ORDER BY MissingCol;
ERROR 2013 (HY000): Lost connection to MySQL server during query
--------------------------------------------------------------------------
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
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.1.23-debug

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

mysql> CREATE TABLE orderedupdate (
    -> val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE orderedupdate SET MissingCol = MissingCol ORDER BY MissingCol;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

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

ChangeSet@1.2558, 2007-01-02 10:13:23-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#25126: Reference to non-existant column in UPDATE...ORDER BY... crashes server
  
  "update existingtable set anything=nonexisting order by nonexisting" would crash
  the server.
  
  Though we would find the reference to a field, that doesn't mean we can then use
  it to set some values.  It could be a placeholder.  If the Field is NULL, don't 
  try to use it to set values in the Item_field.

1/2

Review on IRC.

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

ChangeSet@1.2597, 2007-02-09 11:05:36+01:00, cmiller@calliope.local.cmiller +3 -0
  Bug#25126: Reference to non-existant column in UPDATE...ORDER BY... crashes server
  
  "update existingtable set anycolumn=nonexisting order by nonexisting" would crash
  the server.
  
  Though we would find the reference to a field, that doesn't mean we can then use
  it to set some values.  It could be a reference to another field.  If it is NULL, 
  don't try to use it to set values in the Item_field and instead return an error.
  
  Over the previous patch, this signals an error at the location of the error, rather
  than letting the subsequent deref signal it.

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

ChangeSet@1.2428, 2007-03-03 00:13:07+03:00, evgen@moonbone.local +4 -0
  Bug#25126: Wrongly resolved field leads to a crash.
  
  When the ORDER BY clause gets fixed it's allowed to search in the current
  item_list in order to find aliased fields and expressions. This is ok for a
  SELECT but wrong for an UPDATE statement. If the ORDER BY clause will
  contain a non-existing field which is mentioned in the UPDATE set list
  then the server will crash due to using of non-existing (0x0) field.
  
  Now item list lookups while item fixing are allowed only for selects.

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

ChangeSet@1.2607, 2007-03-04 00:32:24+03:00, evgen@sunlight.local +4 -0
  Bug#25126: Wrongly resolved field leads to a crash.
  
  When the ORDER BY clause gets fixed it's allowed to search in the current
  item_list in order to find aliased fields and expressions. This is ok for a
  SELECT but wrong for an UPDATE statement. If the ORDER BY clause will
  contain a non-existing field which is mentioned in the UPDATE set list
  then the server will crash due to using of non-existing (0x0) field.
  
  Now item list lookups while item fixing are allowed only for selects.

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

ChangeSet@1.2607, 2007-03-04 00:47:42+03:00, evgen@sunlight.local +4 -0
  Bug#25126: Wrongly resolved field leads to a crash.
  
  When the ORDER BY clause gets fixed it's allowed to search in the current
  item_list in order to find aliased fields and expressions. This is ok for a
  SELECT but wrong for an UPDATE statement. If the ORDER BY clause will
  contain a non-existing field which is mentioned in the UPDATE set list
  then the server will crash due to using of non-existing (0x0) field.
  
  When an Item_field is getting fixed it's allowed to search item list for
  aliased expressions and fields only for selects.

pushed to 4.1.23, 5.0.38, 5.1.17

Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

A reference to a non-existent column in the ORDER BY clause of an
UPDATE ... ORDER BY statement could cause a server crash.