#23 0x000000000064fd0d in handle_select (thd=0x7f290a9c38e0, lex=0x7f290a9c6b60, result=0x7f2907882898, setup_tables_done_option=0) at /src/5.5/sql/sql_select.cc:319
#24 0x00000000006291a0 in execute_sqlcom_select (thd=0x7f290a9c38e0, all_tables=0x0) at /src/5.5/sql/sql_parse.cc:4689
#25 0x00000000006224d3 in mysql_execute_command (thd=0x7f290a9c38e0) at /src/5.5/sql/sql_parse.cc:2234
#26 0x000000000062bc80 in mysql_parse (thd=0x7f290a9c38e0, rawbuf=0x7f2907881078 "SELECT 'foo' IN ( SELECT 'bar' UNION SELECT 'baz' ORDER BY 1 )", length=62, parser_state=0x7f290de63650) at /src/5.5/sql/sql_parse.cc:5914
#27 0x000000000061fa72 in dispatch_command (command=COM_QUERY, thd=0x7f290a9c38e0, packet=0x7f2907f54a61 "", packet_length=62) at /src/5.5/sql/sql_parse.cc:1079
#28 0x000000000061ec05 in do_command (thd=0x7f290a9c38e0) at /src/5.5/sql/sql_parse.cc:793
#29 0x00000000007214e1 in do_handle_one_connection (thd_arg=0x7f290a9c38e0) at /src/5.5/sql/sql_connect.cc:1269
#30 0x000000000072126e in handle_one_connection (arg=0x7f290a9c38e0) at /src/5.5/sql/sql_connect.cc:1185
#31 0x0000000000c8acbd in pfs_spawn_thread (arg=0x7f290a9f3140) at /src/5.5/storage/perfschema/pfs.cc:1015
#32 0x00007f290dadb0a4 in start_thread () from /lib64/libpthread.so.0
#33 0x00007f290c4ca04d in clone () from /lib64/libc.so.6
Attention: While working on this, please check both queries, because a tentative fix for MDEV-9304 makes them behave differently: the ALL still hits the assertion failure, while the IN one hangs.
Attachments
Issue Links
relates to
MDEV-10875Assertion `join->group_list || !join->is_in_subquery()' failed on subquery with window function
SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
DROP TABLE t1,t2;
The test does not crash on 10.2 and higher version because we don't have this assert from 10.2 onwards.
Varun Gupta (Inactive)
added a comment - Adding a test with tables
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (2),(3);
EXPLAIN EXTENDED
SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
DROP TABLE t1,t2;
The test does not crash on 10.2 and higher version because we don't have this assert from 10.2 onwards.
Well looks like the fix to change the assert is a trivial one but i don't thin we need to perform sorting for a UNION inside an IN/ALL/ANY subquery.
Varun Gupta (Inactive)
added a comment - Well looks like the fix to change the assert is a trivial one but i don't thin we need to perform sorting for a UNION inside an IN/ALL/ANY subquery.
Varun Gupta (Inactive)
added a comment - This is what the query plan looks like and it says that it is using filesort to sort the result of Union which in my opinion is not needed.
MariaDB [test]> EXPLAIN SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | A | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | DEPENDENT UNION | B | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------+
4 rows in set (0.00 sec)
Bug
Major
Adding a test with tables
CREATE TABLE t1 (a INT);INSERT INTO t1 VALUES (1),(2);CREATE TABLE t2 (a INT);INSERT INTO t2 VALUES (2),(3);EXPLAIN EXTENDEDSELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);DROP TABLE t1,t2;The test does not crash on 10.2 and higher version because we don't have this assert from 10.2 onwards.