Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
5.5.50, 5.5.58, 5.5.59, 10.0.34
Description
SELECT 'foo' > ALL ( SELECT 'bar' UNION SELECT 'baz' ORDER BY 1 );
|
or
SELECT 'foo' IN ( SELECT 'bar' UNION SELECT 'baz' ORDER BY 1 );
|
mysqld: /src/5.5/sql/sql_select.cc:19816: int create_sort_index(THD*, JOIN*, ORDER*, ha_rows, ha_rows, bool): Assertion `join->group_list || !join->is_in_subquery()' failed.
|
160203 18:32:16 [ERROR] mysqld got signal 6 ;
|
|
Stack trace from 5.5 commit 9c9d10b441fe79b111509949ca40afe1a6284c0f |
#7 0x00007f290c4131d2 in __assert_fail () from /lib64/libc.so.6
|
#8 0x00000000006818f6 in create_sort_index (thd=0x7f290a9c38e0, join=0x7f290788c1f8, order=0x7f2907882520, filesort_limit=1, select_limit=1, is_order_by=false) at /src/5.5/sql/sql_select.cc:19816
|
#9 0x0000000000658a4c in JOIN::exec (this=0x7f290788c1f8) at /src/5.5/sql/sql_select.cc:2843
|
#10 0x0000000000659471 in mysql_select (thd=0x7f290a9c38e0, rref_pointer_array=0x7f29078822d8, tables=0x7f29078815c0, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x7f2907882520, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x7f29078826f0, unit=0x7f2907881570, select_lex=0x7f2907882068) at /src/5.5/sql/sql_select.cc:3094
|
#11 0x00000000006d3667 in st_select_lex_unit::exec (this=0x7f2907881570) at /src/5.5/sql/sql_union.cc:807
|
#12 0x000000000086e9eb in subselect_union_engine::exec (this=0x7f2907882710) at /src/5.5/sql/item_subselect.cc:3263
|
#13 0x0000000000867478 in Item_subselect::exec (this=0x7f2907882560) at /src/5.5/sql/item_subselect.cc:658
|
#14 0x00000000008679be in Item_in_subselect::exec (this=0x7f2907882560) at /src/5.5/sql/item_subselect.cc:831
|
#15 0x0000000000869e09 in Item_in_subselect::val_bool (this=0x7f2907882560) at /src/5.5/sql/item_subselect.cc:1657
|
#16 0x000000000058c815 in Item::val_bool_result (this=0x7f2907882560) at /src/5.5/sql/item.h:981
|
#17 0x0000000000807703 in Item_in_optimizer::val_int (this=0x7f290788b178) at /src/5.5/sql/item_cmpfunc.cc:1784
|
#18 0x00000000007f2223 in Item::send (this=0x7f290788b178, protocol=0x7f290a9c3ea8, buffer=0x7f290de62260) at /src/5.5/sql/item.cc:6574
|
#19 0x0000000000588b18 in Protocol::send_result_set_row (this=0x7f290a9c3ea8, row_items=0x7f290a9c7408) at /src/5.5/sql/protocol.cc:903
|
#20 0x00000000005ead76 in select_send::send_data (this=0x7f2907882898, items=...) at /src/5.5/sql/sql_class.cc:2374
|
#21 0x00000000006567c5 in JOIN::exec (this=0x7f29078828b8) at /src/5.5/sql/sql_select.cc:2281
|
#22 0x0000000000659471 in mysql_select (thd=0x7f290a9c38e0, rref_pointer_array=0x7f290a9c7560, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2907882898, unit=0x7f290a9c6c10, select_lex=0x7f290a9c72f0) at /src/5.5/sql/sql_select.cc:3094
|
#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
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Sprint | 5.5.50 [ 71 ] |
| Rank | Ranked higher |
| Link |
This issue relates to |
| Sprint | 5.5.50 [ 71 ] | 5.5.50, 5.5.58 [ 71, 197 ] |
| Sprint | 5.5.50, 5.5.58 [ 71, 197 ] | 5.5.50, 5.5.58, 5.5.59 [ 71, 197, 221 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Sprint | 5.5.50, 5.5.58, 5.5.59 [ 71, 197, 221 ] | 5.5.50, 5.5.58, 5.5.59, 10.0.34 [ 71, 197, 221, 224 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Varun Gupta [ varun ] |
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.
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)
|
|
| Assignee | Varun Gupta [ varun ] | Oleksandr Byelkin [ sanja ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1.46 [ 24308 ] | |
| Fix Version/s | 10.2.33 [ 24307 ] | |
| Fix Version/s | 10.3.24 [ 24306 ] | |
| Fix Version/s | 10.4.14 [ 24305 ] | |
| Fix Version/s | 10.5.5 [ 24423 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.1.47 [ 24510 ] | |
| Fix Version/s | 10.2.34 [ 24505 ] | |
| Fix Version/s | 10.3.25 [ 24506 ] | |
| Fix Version/s | 10.4.15 [ 24507 ] | |
| Fix Version/s | 10.5.6 [ 24508 ] | |
| Fix Version/s | 10.4.14 [ 24305 ] | |
| Fix Version/s | 10.3.24 [ 24306 ] | |
| Fix Version/s | 10.2.33 [ 24307 ] | |
| Fix Version/s | 10.1.46 [ 24308 ] | |
| Fix Version/s | 10.5.5 [ 24423 ] |
| Link |
This issue relates to |
| Fix Version/s | 10.5.7 [ 25019 ] |
| Fix Version/s | 10.5.6 [ 24508 ] |
| Fix Version/s | 10.4.16 [ 25020 ] |
| Fix Version/s | 10.4.15 [ 24507 ] |
| Fix Version/s | 10.3.26 [ 25021 ] |
| Fix Version/s | 10.3.25 [ 24506 ] |
| Fix Version/s | 10.2.35 [ 25022 ] |
| Fix Version/s | 10.2.34 [ 24505 ] |
| Fix Version/s | 10.1.48 [ 25108 ] |
| Fix Version/s | 10.1.47 [ 24510 ] |
| Workflow | MariaDB v3 [ 73869 ] | MariaDB v4 [ 150076 ] |
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.