Bug #20519 "Subquery returns more that 1 row" error with "limit x, 1"
Submitted: 17 Jun 2006 15:00 Modified: 24 Jul 2006 17:47
Reporter: Balazs Odor Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:Linux (Debian Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[17 Jun 2006 15:00] Balazs Odor
Description:
LIMIT 1,1 in subquery => ERROR 1242 (21000): Subquery returns more than 1 row
LIMIT 3,1 in subquery => no error, but results bad!

How to repeat:
> SELECT *, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 0, 1) AS `date_last`, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 1, 1) AS `date_next_to_last` FROM `master`;
ERROR 1242 (21000): Subquery returns more than 1 row

This is funny:

> SELECT *, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 0, 1) AS `date_last`, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 3, 1) AS `date_next_to_last` FROM `master`;
+----+--------+------------+-------------------+
| id | name   | date_last  | date_next_to_last |
+----+--------+------------+-------------------+
|  1 | Balazs | 2006-05-01 | NULL              |
|  2 | Joe    | 2006-04-20 | 2006-04-06        |
|  3 | Frank  | 2006-04-13 | 2006-04-13        |
+----+--------+------------+-------------------+
3 rows in set (0.00 sec)

In this result: (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 3, 1) = '2006-04-06' where `master`.`id` = '2', SEE:

> SELECT `date` FROM `slave` WHERE `master_id` = '2' ORDER BY `date` DESC LIMIT 3, 1
Empty set (0.00 sec)
????
[17 Jun 2006 15:02] Balazs Odor
master table

Attachment: master.table.sql (text/plain), 327 bytes.

[17 Jun 2006 15:02] Balazs Odor
slave table

Attachment: slave.table.sql (text/plain), 580 bytes.

[17 Jun 2006 15:20] Balazs Odor
> SELECT COUNT(*) FROM `master` LIMIT 0, 1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

This ok!

> SELECT COUNT(*) FROM `master` LIMIT 1, 1;
Empty set (0.00 sec)

This ok! :)

> SELECT *, (SELECT COUNT(*) FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 1, 1) AS `date_count` FROM `master`;

+----+--------+------------+
| id | name   | date_count |
+----+--------+------------+
|  1 | Balazs |       NULL |
|  2 | Joe    |          2 |
|  3 | Frank  |          1 |
+----+--------+------------+
3 rows in set (0.00 sec)

And this is what? The subquery results SELECT COUNT(*) ... LIMIT x, 1 where x > 1 why is not NULL?
[17 Jun 2006 15:24] Balazs Odor
Repair my last question:
The subquery results SELECT COUNT(*) ... LIMIT x, 1 where x > 0 is not NULL? Why?
[19 Jun 2006 10:25] Tonci Grgin
Hi Balazs and thanks for your excellent bug report.
Verified as described by reporter on latest 5.0 and 5.1 bk. However, this works OK on latest 4.1 bk. All MySQL servers are on Suse 10 host.

Findings:
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.23-debug-log |
+------------------+
1 row in set (0.00 sec)

and

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.12-beta-debug-log |
+-----------------------+
1 row in set (0.05 sec)

mysql> SELECT *,(SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 0, 1) AS `date_last`,
    -> (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 1, 1) AS `date_next_to_last`
    -> FROM `master`;
ERROR 1242 (21000): Subquery returns more than 1 row

mysql> show errors;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1242 | Subquery returns more than 1 row |
| Error | 1105 | Unknown error                    |
+-------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT *, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER
    -> BY `date` DESC LIMIT 0, 1) AS `date_last`, (SELECT `date` FROM `slave` WHERE
    -> `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 3, 1) AS
    -> `date_next_to_last` FROM `master`;
+----+--------+------------+-------------------+
| id | name   | date_last  | date_next_to_last |
+----+--------+------------+-------------------+
|  1 | Balazs | 2006-05-01 | NULL              |
|  2 | Joe    | 2006-04-20 | 2006-04-06        |
|  3 | Frank  | 2006-04-13 | 2006-04-13        |
+----+--------+------------+-------------------+
3 rows in set (0.02 sec)

mysql> SELECT *, (SELECT COUNT(*) FROM `slave` WHERE `master_id` = `master`.`id`
    -> ORDER BY `date` DESC LIMIT 1, 1) AS `date_count` FROM `master`;
+----+--------+------------+
| id | name   | date_count |
+----+--------+------------+
|  1 | Balazs |       NULL |
|  2 | Joe    |          2 |
|  3 | Frank  |          1 |
+----+--------+------------+
3 rows in set (0.02 sec)

-----------------------
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.21-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT *,(SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 0, 1) AS `date_last`,
    -> (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 1, 1) AS `date_next_to_last`
    -> FROM `master`;
+----+--------+------------+-------------------+
| id | name   | date_last  | date_next_to_last |
+----+--------+------------+-------------------+
|  1 | Balazs | 2006-05-01 | 2006-03-30        |
|  2 | Joe    | 2006-04-20 | 2006-04-06        |
|  3 | Frank  | 2006-04-13 | NULL              |
+----+--------+------------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT *, (SELECT `date` FROM `slave` WHERE `master_id` = `master`.`id` ORDER
    -> BY `date` DESC LIMIT 0, 1) AS `date_last`, (SELECT `date` FROM `slave` WHERE
    -> `master_id` = `master`.`id` ORDER BY `date` DESC LIMIT 3, 1) AS
    -> `date_next_to_last` FROM `master`;
+----+--------+------------+-------------------+
| id | name   | date_last  | date_next_to_last |
+----+--------+------------+-------------------+
|  1 | Balazs | 2006-05-01 | NULL              |
|  2 | Joe    | 2006-04-20 | NULL              |
|  3 | Frank  | 2006-04-13 | NULL              |
+----+--------+------------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT *, (SELECT COUNT(*) FROM `slave` WHERE `master_id` = `master`.`id`
    -> ORDER BY `date` DESC LIMIT 1, 1) AS `date_count` FROM `master`;
+----+--------+------------+
| id | name   | date_count |
+----+--------+------------+
|  1 | Balazs |       NULL |
|  2 | Joe    |       NULL |
|  3 | Frank  |       NULL |
+----+--------+------------+
3 rows in set (0.02 sec)
[15 Jul 2006 2:27] 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/9201
[19 Jul 2006 0:03] Evgeny Potemkin
The bug was due to a loss happened during a refactoring made
on May 30 2005 that modified the function JOIN::reinit.
As a result of it for any subquery the value of offset_limit_cnt
was not restored for the following executions. Yet the first 
execution of the subquery made it equal to 0.

Fixed in 5.0.25
[24 Jul 2006 17:38] Paul DuBois
Noted in 5.0.25 changelog.