MySQL Bugs: #83722: UNION with outer LIMIT can return wrong number of rows

Bug #83722	UNION with outer LIMIT can return wrong number of rows
Submitted:	7 Nov 2016 13:28	Modified:	7 Nov 2016 13:56
Reporter:	Vilnis Termanis (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7/8.0	OS:	Ubuntu (16.04)
Assigned to:		CPU Architecture:	Any
Tags:	union limit rows

Description:
It would appear that UNION does not return any rows if
- Has outer UNION ALL
- First inner SELECT returns no rows due to OFFSET+LIMIT
- Second inner SELECT returns at least one row

Please note the example queries use no tables but I have reproduced this with real tables where the query is a simple select from a single table (InnoDB).

How to repeat:
-- NO rows returned (expecting one row)
(SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1;

-- works
(SELECT 1) UNION ALL (SELECT 1) LIMIT 1;
-- distinct union also works
(SELECT 1 LIMIT 1 OFFSET 1) UNION (SELECT 1) LIMIT 1;
-- outer order by also works
(SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) ORDER BY 1 LIMIT 1;

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1;
Empty set (0.02 sec)

mysql 5.7 > exit
Bye

C:\dbs>57q

C:\dbs>c:\dbs\5.7\bin\mysqladmin -uroot --port=3570 -p shutdown
Enter password: ******

C:\dbs>50c

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 -p --prompt="mysql 5.0 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

mysql 5.6 > exit
Bye

C:\dbs>56q

C:\dbs>c:\dbs\5.6\bin\mysqladmin -uroot --port=3560 -p shutdown
Enter password:

C:\dbs>80c

C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.1-dmr Source distribution PULL: 2016-NOV-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1;
Empty set (0.04 sec)

Some interesting results:

mysql> (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 0,1;
Empty set (0.00 sec)

mysql> (SELECT 1 LIMIT 1 OFFSET 1) UNION ALL (SELECT 1) LIMIT 1,1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)