[MDEV-9513] Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index Created: 2016-02-03  Updated: 2020-10-06  Resolved: 2020-08-06

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.1.48, 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-10875 Assertion `join->group_list || !join-... Closed
relates to MDEV-9304 MariaDB crash with specific query Closed
relates to MDEV-23160 SIGSEGV in Explain_node::print_explai... Closed
Sprint: 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.



 Comments   
Comment by Varun Gupta (Inactive) [ 2020-08-04 ]

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.

Comment by Varun Gupta (Inactive) [ 2020-08-04 ]

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.

Comment by Varun Gupta (Inactive) [ 2020-08-04 ]

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)

Comment by Varun Gupta (Inactive) [ 2020-08-05 ]

Patch
http://lists.askmonty.org/pipermail/commits/2020-August/014302.html

Comment by Sergei Petrunia [ 2020-08-05 ]

Ok to push after phone input is addressed.

Generated at Thu Feb 08 07:35:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.