Bug #3667 ORDER BY (subquery) allows illegitimate subquery

• Submitted: 5 May 2004 20:36
• Modified: 5 May 2004 23:29
• Reporter: Peter Gulutzan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[5 May 2004 20:36] Peter Gulutzan

Description:
In a select list, if I use a subquery which would return more than one row, I get an error. 
But in an ORDER BY list, if I use a subquery which would return more than one row, I get 
no error. 
This strikes me as trivial  but inconsistent. 

How to repeat:
mysql> create table t10 (s1 int); 
Query OK, 0 rows affected (0.05 sec) 
 
mysql> insert into t10 values (1),(2); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
mysql> create table t20 (s1 int); 
Query OK, 0 rows affected (0.04 sec) 
 
mysql> insert into t20 values (1),(2); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
mysql> select * from t10 order by (select s1 from t20); 
+------+ 
| s1   | 
+------+ 
|    1 | 
|    2 | 
+------+ 
2 rows in set (0.01 sec) 
 
mysql> select (select s1 from t20) from t10; 
ERROR 1242 (21000): Subquery returns more than 1 row 
 

Suggested fix:
ORDER BY (subquery) and ORDER BY set-function (e.g. "ORDER BY AVG(s1)") are not 
required in standard SQL, so it would be okay to make them illegal.

[5 May 2004 23:29] Oleksandr Byelkin

Thank you for bug report. 
 
Subquery in example do not depents on outer query, i.e. it is constant 
(related to outer query). Mysql eliminate constant ORDER BY clause. I.e. 
subquery never will be executed. As far as that error can be fount only during 
execution it will not be found.