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

[28 Nov 2006 13:28] Martin Friebe
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:
-
[28 Nov 2006 16:35] MySQL Verification Team
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
[28 Nov 2006 16:43] MySQL Verification Team
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>
[26 Jan 2007 2:41] 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/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.
[3 Feb 2007 6:08] Igor Babaev
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.
[7 Feb 2007 19:18] Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs.

Resetting report to Patch Queued pending backport
to the 4.1 tree.
[9 Feb 2007 8:38] Sergei Glukhov
fixed in 4.1.23
[9 Feb 2007 20:21] Paul DuBois
Noted in 4.1.23 changelog.