Description:
When I use "limit" with "union", it doesn't show me the proper elements of the table. It looks like the problem happens when the limit is not filled by the first select in the UNION. In this case, it "skips" over the values returned by next select in the UNION even though it has already done the "skipping" part. There are many other possible combinations that I haven't tested - like what if the "ORDER BY" interleaves the elements of the two selects, what gets skipped then? Etc...basically, my understanding is that the LIMIT and ORDER BY should apply to the final UNION-ized result, not the individual selects within the query.
How to repeat:
mysql> create table foo (val char(5));
mysql> insert into foo values ('a'), ('b'), ('c'), ('d'), ('e');
mysql> select * from foo where val < 'c' union select * from foo where val > 'c';
+------+
| val |
+------+
| a |
| b | <--- Works perfectly
| d |
| e |
+------+
mysql> select * from foo where val < 'c' union select * from foo where val > 'c' order by val limit 2;
+------+
| val |
+------+
| a | <--- Works perfectly
| b |
+------+
mysql> select * from foo where val < 'c' union select * from foo where val > 'c' order by val limit 1,2;
+------+
| val |
+------+
| b | <---- ??? Should have been "b,d"
| e |
+------+
Suggested fix:
Don't use unions. Use temporary tables and a series of INSERT INTO ... SELECT statements, then a final SELECT/ORDER BY/SKIP at the end, like was necessary under MySQL 3.x.
Description: When I use "limit" with "union", it doesn't show me the proper elements of the table. It looks like the problem happens when the limit is not filled by the first select in the UNION. In this case, it "skips" over the values returned by next select in the UNION even though it has already done the "skipping" part. There are many other possible combinations that I haven't tested - like what if the "ORDER BY" interleaves the elements of the two selects, what gets skipped then? Etc...basically, my understanding is that the LIMIT and ORDER BY should apply to the final UNION-ized result, not the individual selects within the query. How to repeat: mysql> create table foo (val char(5)); mysql> insert into foo values ('a'), ('b'), ('c'), ('d'), ('e'); mysql> select * from foo where val < 'c' union select * from foo where val > 'c'; +------+ | val | +------+ | a | | b | <--- Works perfectly | d | | e | +------+ mysql> select * from foo where val < 'c' union select * from foo where val > 'c' order by val limit 2; +------+ | val | +------+ | a | <--- Works perfectly | b | +------+ mysql> select * from foo where val < 'c' union select * from foo where val > 'c' order by val limit 1,2; +------+ | val | +------+ | b | <---- ??? Should have been "b,d" | e | +------+ Suggested fix: Don't use unions. Use temporary tables and a series of INSERT INTO ... SELECT statements, then a final SELECT/ORDER BY/SKIP at the end, like was necessary under MySQL 3.x.