Bug #19323 Subquery with LIMIT 1,1 is not recognized as returning a single row

• Submitted: 25 Apr 2006 7:46
• Modified: 22 Aug 2006 16:16
• Reporter: Björn Steinbrink
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.21-BK, 5.0.20
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[25 Apr 2006 7:46] Björn Steinbrink

Description:
The MySQL server refuses to accept a correlated subquery as scalar expression if there is a LIMIT x,1 clause with x > 0.

How to repeat:
mysql> CREATE TABLE test (a INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test X WHERE a = (SELECT a FROM test WHERE a = X.a LIMIT 0,1);
+------+
| a    |
+------+
| 1    |
| 1    |
+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test X WHERE a = (SELECT a FROM test WHERE a = X.a LIMIT 1,1);
ERROR 1242 (21000): Subquery returns more than 1 row

Suggested fix:
Server should recognize that LIMIT x,1 will always return a single row.

[25 Apr 2006 12:03] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.21-BK.

[22 Aug 2006 16:16] Igor Babaev

This is a duplicate of bug #20519.