Bug #3646 "14.1.8.3 Subqueries with ANY, IN, and SOME" and "14.1.8.4 Subqueries with ALL"
Submitted: 4 May 2004 8:41 Modified: 14 May 2004 14:28
Reporter: Haruo Hayami Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[4 May 2004 8:41] Haruo Hayami
Description:
mysql> select * from DetalT;
+---------+------------------+--------+--------+
| OrderID | Goods            | Price  | Number |
+---------+------------------+--------+--------+
| 16001   | パソコン         | 100000 |      2 |
| 16001   | MOドライブ       |  50000 |      1 |
| 16001   | テーブルタップ   |   2000 |      4 |
| 16001   | ディスプレイ     |  45000 |      2 |
| 16002   | ディジタルカメラ |  30000 |      1 |
| 16002   | CFメモリ         |  10000 |      2 |
| 16003   | フィルター       |   6000 |      2 |
| 16003   | パソコン         |  90000 |      3 |
| 16004   | ノートパソコン   | 190000 |      1 |
| 16004   | キャリアー       |   5000 |      1 |
| 16004   | バッテリー       |   9000 |      1 |
| 16004   | ディスプレイ     |  40000 |      3 |
+---------+------------------+--------+--------+
12 rows in set (0.00 sec)

mysql> select * from DetalT where Price > any
    -> (select Price from DetalT where OrderID = '16003');
+---------+------------------+--------+--------+
| OrderID | Goods            | Price  | Number |
+---------+------------------+--------+--------+
| 16001   | パソコン         | 100000 |      2 |
| 16001   | MOドライブ       |  50000 |      1 |
| 16001   | テーブルタップ   |   2000 |      4 |
| 16001   | ディスプレイ     |  45000 |      2 |
| 16002   | ディジタルカメラ |  30000 |      1 |
| 16002   | CFメモリ         |  10000 |      2 |
| 16003   | フィルター       |   6000 |      2 |
| 16003   | パソコン         |  90000 |      3 |
| 16004   | ノートパソコン   | 190000 |      1 |
| 16004   | キャリアー       |   5000 |      1 |
| 16004   | バッテリー       |   9000 |      1 |
| 16004   | ディスプレイ     |  40000 |      3 |
+---------+------------------+--------+--------+
12 rows in set (0.00 sec)

mysql> select * from DetalT where Price > all
    -> (select Price from DetalT where OrderID = '16003');
+---------+----------------+--------+--------+
| OrderID | Goods          | Price  | Number |
+---------+----------------+--------+--------+
| 16001   | パソコン       | 100000 |      2 |
| 16001   | MOドライブ     |  50000 |      1 |
| 16001   | ディスプレイ   |  45000 |      2 |
| 16003   | パソコン       |  90000 |      3 |
| 16004   | ノートパソコン | 190000 |      1 |
+---------+----------------+--------+--------+
5 rows in set (0.00 sec)

How to repeat:
"14.1.8.3 Subqueries with ANY, IN, and SOME" and "14.1.8.4 Subqueries with ALL"
[5 May 2004 18:42] Dean Ellis
I cannot repeat this against the current 5.0.1 source tree; it has quite likely already been corrected.
[5 May 2004 20:43] Dean Ellis
The lack of complete test case resulted in not testing a crucial detail for reproducing this; please in the future submit complete SQL to create, populate and query a table, demonstrating the issue.

The bug occurs in 4.1 and is not Windows-specific, so I have changed those details.  Thank you for the report.
[7 May 2004 22:16] Oleksandr Byelkin
ChangeSet 
  1.1826 04/05/07 23:06:11 bell@sanja.is.com.ua +11 -0 
  keep old engine & JOIN if we changed subquery Item (Bug #3646)
[14 May 2004 14:28] Oleksandr Byelkin
Thank you for bugreport. Patch for this bug is pushed in our internal 
repository and will be present in next server release