MySQL Bugs: #24653: server crash, with multirow subquery in order by

Bug #24653	server crash, with multirow subquery in order by
Submitted:	28 Nov 2006 13:28	Modified:	9 Feb 2007 20:21
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.22, 5.0.22/4.1BK/5.0K/5.1BK	OS:	FreeBSD (freebsd/Suse Linux 10)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	crash. subquery, order by

Description:
mysql does not always check the amount of rows returned by a subquery in the order by clause (and sometimes in having).

In certain queries, this leads to a server crash.

How to repeat:
drop table if exists o1; drop table if exists o2;
create table o1 (a int(11)); insert into o1 values (1),(2),(3);
create table o2 (b int(11), c int(11)); insert into o2 values (null, 1), (null,2), (null,2);

# will execute, even subquery returns more than 1 row
select * from o1 order by (select c from o2);

# will crash
select * from o1 order by ifnull(  (select b from o2), (select c from o2 where c=a order by 1)  );

# will crash
select * from o1 group by 1 having ifnull(  (select b from o2), (select c from o2 where c=a order by 1)  );

Suggested fix:
-

Thank you for the bug report.

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
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> drop table if exists o1; drop table if exists o2;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table o1 (a int(11)); insert into o1 values (1),(2),(3);
Query OK, 0 rows affected (0.01 sec)

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table o2 (b int(11), c int(11)); insert into o2 values (null, 1),
Query OK, 0 rows affected (0.03 sec)

    -> (null,2), (null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> # will execute, even subquery returns more than 1 row
mysql> select * from o1 order by (select c from o2);
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

----------------------------------------------------------------------------------
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> drop table if exists o1; drop table if exists o2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table o1 (a int(11)); insert into o1 values (1),(2),(3);
Query OK, 0 rows affected (0.01 sec)

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table o2 (b int(11), c int(11)); insert into o2 values (null, 1),
Query OK, 0 rows affected (0.01 sec)

    -> (null,2), (null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> # will execute, even subquery returns more than 1 row
mysql> select * from o1 order by (select c from o2);
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> 
mysql> # will crash
mysql> select * from o1 order by ifnull(  (select b from o2), (select c from o2 where
    -> c=a order by 1)  );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

mysql> # will crash
mysql> select * from o1 order by ifnull(  (select b from o2), (select c from o2 where
    -> c=a order by 1)  );
ERROR 2013 (HY000): Lost connection to MySQL server during query

Sorry I didn't pasted the complete output for 4.1:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
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> drop table if exists o1; drop table if exists o2;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table o1 (a int(11)); insert into o1 values (1),(2),(3);
Query OK, 0 rows affected (0.01 sec)

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table o2 (b int(11), c int(11)); insert into o2 values (null, 1),
Query OK, 0 rows affected (0.03 sec)

    -> (null,2), (null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> # will execute, even subquery returns more than 1 row
mysql> select * from o1 order by (select c from o2);
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> 
mysql> # will crash
mysql> select * from o1 order by ifnull(  (select b from o2), (select c from o2 where
    -> c=a order by 1)  );
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/18822

ChangeSet@1.2599, 2007-01-25 18:44:35-08:00, igor@olga.mysql.com +10 -0
  Fixed bug #24653.
  The bug report has demonstrated the following two problems.
  1. If an ORDER/GROUP BY list includes a constant expression being 
  optimized away and, at the same time, containing single-row
  subselects that return more that one row, no error is reported.
  Strictly speaking the standard allows to ignore error in this case.
  Yet, now a corresponding fatal error is reported in this case.
  2. If a query requires sorting by expressions containing single-row
  subselects that, however, return more than one row, then the execution
  of the query may cause a server crash. 
  To fix this some code has been added that blocks execution of a subselect
  item in case of a fatal error in the method Item_subselect::exec.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees. 

Yet to be pushed in 4.1 main tree as well.

Noted in 5.0.36, 5.1.16 changelogs.

Resetting report to Patch Queued pending backport
to the 4.1 tree.

fixed in 4.1.23

Noted in 4.1.23 changelog.