[MDEV-16357] LIMIT and ORDER BY clause is ignored on a query with UNION when using brackets Created: 2018-05-31  Updated: 2018-11-07  Resolved: 2018-11-07

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.4
Fix Version/s: 10.4.0

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-11953 support of brackets (parentheses) in ... Closed
relates to MDEV-16317 Testing for brackets (parentheses) in... Stalled

 Description    

CREATE TABLE t1 (b int);
 
INSERT INTO t1 VALUES(1),(2),(3),(4);
 
 
 
CREATE TABLE t2 (b int);
 
INSERT INTO t2 VALUES (4),(5),(6),(7);
 
 
 
(select b from t1  UNION select b from t2) limit 1;
 
(select b from t1  UNION select b from t2) order by b desc;


MariaDB [test]> (select b from t1  UNION select b from t2) limit 1;
 
+------+
 
| b    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
|    4 |
 
|    5 |
 
|    6 |
 
|    7 |
 
+------+
 
7 rows in set (0.001 sec)
 
 
 
MariaDB [test]> (select b from t1  UNION select b from t2) order by b desc;
 
+------+
 
| b    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
|    4 |
 
|    5 |
 
|    6 |
 
|    7 |
 
+------+
 
7 rows in set (0.001 sec)
 
 
 
MariaDB [test]> (select b from t1  UNION select b from t2 limit 1);
 
+------+
 
| b    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
|    4 |
 
|    5 |
 
|    6 |
 
|    7 |
 
+------+
 
7 rows in set (0.002 sec)
 
expected :
 
MariaDB [test]> select b from t1  UNION select b from t2 limit 1;
 
+------+
 
| b    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Alice Sherepa [ 2018-10-25 ]

10.4 4272eec050400a1913362cbcd215133ff3eccdd5

MariaDB [test]> ((select b from t1  UNION select b from t2 ) order by b desc);
 
+------+
 
| b    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
|    4 |
 
|    5 |
 
|    6 |
 
|    7 |
 
+------+
 
7 rows in set (0.004 sec)
 
 
 
MariaDB [test]> explain extended ((select b from t1  UNION select b from t2 ) order by b desc);
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |       |
 
|    2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |       |
 
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL |       |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
 
3 rows in set, 1 warning (0.003 sec)
 
 
 
Note (Code 1003): (/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`

But this works:

MariaDB [test]> (((select b from t1  UNION select b from t2 ) order by b desc) order by b desc);
 
+------+
 
| b    |
 
+------+
 
|    7 |
 
|    6 |
 
|    5 |
 
|    4 |
 
|    3 |
 
|    2 |
 
|    1 |
 
+------+
 
7 rows in set (0.006 sec)
 
 
 
MariaDB [test]> explain extended ( ((select b from t1  UNION select b from t2 ) order by b desc) order by b desc);
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
|    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using filesort |
 
|    2 | DERIVED      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |                |
 
|    3 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |                |
 
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using filesort |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
4 rows in set, 1 warning (0.004 sec)
 
 
 
Note (Code 1003): /* select#1 */ select `__3`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `b` desc) `__3` order by `__3`.`b` desc

Comment by Oleksandr Byelkin [ 2018-11-06 ]

OK to push

Comment by Igor Babaev [ 2018-11-07 ]

A fix for this bug was pushed into 10.4

Generated at Thu Feb 08 08:28:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.