MySQL Bugs: #18885: UNION ALL on the same table with SELECT ... LIMIT gives not enough rows

Bug #18885	UNION ALL on the same table with SELECT ... LIMIT gives not enough rows
Submitted:	7 Apr 2006 13:06	Modified:	9 May 2006 20:51
Reporter:	Oli Sennhauser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Linux (Linux 2.6.11.4-21.10-default)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
SELECT * FROM t LIMIT 0,4
UNION ALL
SELECT * FROM t LIMIT 4,4
;

gives only 4 rows instead of 8.

How to repeat:
create table t (id int auto_increment, primary key (id));

insert into t values (null);
insert into t select null from t;
insert into t select null from t;
insert into t select null from t;

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+

SELECT * FROM t LIMIT 0,4
UNION ALL
SELECT * FROM t LIMIT 4,4
;

-> This should give at least 8 rows

Workaround:
(SELECT * FROM t LIMIT 0,4)
UNION ALL
(SELECT * FROM t LIMIT 4,4)
;

This is documented on http://dev.mysql.com/doc/refman/5.0/en/union.html

   To apply ORDER BY or LIMIT to an individual SELECT, place the clause 
   inside the parentheses that enclose the SELECT:

  (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) 
  UNION
  (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

but i agree that this should not just silently produce unexpected results like this

The query is evaluated as

((SELECT * FROM t LIMIT 0,4) UNION ALL SELECT * FROM t) LIMIT 4,4

that is LIMIT is applied to the result of UNION ALL.
No more unexpected than 1+2*3 being equal to 7 and not 9.