| Bug #1748 | SELECT with LIMIT _AND_ UNION works incorrectly | ||
|---|---|---|---|
| Submitted: | 4 Nov 2003 9:03 | Modified: | 4 Nov 2003 9:16 |
| Reporter: | Dirk Thomsen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.17b | OS: | Windows (Win) |
| Assigned to: | CPU Architecture: | Any | |
[4 Nov 2003 9:16]
Dean Ellis
The final LIMIT is being applied to the entire result set. If you want to limit the individual queries, surround them with parentheses. ie: ( SELECT * FROM t1 LIMIT 2 ) UNION ALL ( SELECT * FROM t2 LIMIT 2 );
Description: If I make a UNION SELECT and specify a limit the result is quit curious. mysql> select * from t1; +------+ | i | +------+ | 1 | | 2 | | 3 | | 3 | | 5 | +------+ -> ok mysql> select * from t2; +------+ | i | +------+ | 6 | | 7 | | 8 | | 9 | | 10 | +------+ -> ok mysql> select * from t1 limit 2 union all select * from t2 limit 2; +------+ | i | +------+ | 1 | | 2 | +------+ -> ??? limit 2 seems to work for the whole result allthough select_limit and offset_limit is part of select_lex mysql> select * from t1 limit 2 union all select * from t2 limit 5; +------+ | i | +------+ | 1 | | 2 | | 6 | | 7 | | 8 | +------+ -> ??? the select from t1 seems to be limited to 2 and the whole result to 5 ??? mysql> select * from t1 limit 5 union all select * from t2 limit 2; +------+ | i | +------+ | 1 | | 2 | +------+ -> ??? ??? How to repeat: see above