[MDEV-16357] LIMIT and ORDER BY clause is ignored on a query with UNION when using brackets - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL)
Fix Version/s: 10.4.0
Component/s: Parser
Labels:
None

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)

Attachments

Issue Links

relates to

MDEV-11953 support of brackets (parentheses) in UNION/EXCEPT/INTERSECT operations

Closed

MDEV-16317 Testing for brackets (parentheses) in UNION/EXCEPT/INTERSECT operations

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-10-25 10:03

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

Alice Sherepa added a comment - 2018-10-25 10:03 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

Oleksandr Byelkin added a comment - 2018-11-06 19:48

OK to push

Oleksandr Byelkin added a comment - 2018-11-06 19:48 OK to push

Igor Babaev (Inactive) added a comment - 2018-11-07 21:28

A fix for this bug was pushed into 10.4

Igor Babaev (Inactive) added a comment - 2018-11-07 21:28 A fix for this bug was pushed into 10.4

MariaDB Server

LIMIT and ORDER BY clause is ignored on a query with UNION when using brackets

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration