[MDEV-9514] Server crashes in subselect_union_engine::no_rows Created: 2016-02-03  Updated: 2020-11-25  Resolved: 2020-11-25

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 5.5.54, 10.0.29, 10.1.21, 10.2.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 2
Labels: upstream-fixed

Sprint: 5.5.58, 10.1.31

 Description    

CREATE TABLE t1 (f1 INT);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (f2 INT);
 
INSERT INTO t2 VALUES (3),(4);
 
 
 
CREATE TABLE t3 (f3 INT);
 
INSERT INTO t3 VALUES (5),(6);
 
 
 
SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON (f3 = f2) ) ON (f1 <> f2)
 
WHERE f2 NOT IN ( SELECT 10 UNION SELECT 20 ORDER BY 2 );

Stack trace from 5.5 commit 9c9d10b441fe79b111509949ca40afe1a6284c0f

 
#3  <signal handler called>
 
#4  0x000000000086dac2 in subselect_union_engine::no_rows (this=0x7f527b3c76c0) at /src/5.5/sql/item_subselect.cc:2950
 
#5  0x0000000000807664 in Item_in_optimizer::val_int (this=0x7f527b3c8990) at /src/5.5/sql/item_cmpfunc.cc:1770
 
#6  0x000000000058c789 in Item::val_int_result (this=0x7f527b3c8990) at /src/5.5/sql/item.h:977
 
#7  0x00000000007f8b5d in Item_cache_int::cache_value (this=0x7f527b3f4ef8) at /src/5.5/sql/item.cc:8932
 
#8  0x000000000080011a in Item_cache_wrapper::cache (this=0x7f527b3f4e38) at /src/5.5/sql/item.cc:7735
 
#9  0x00000000007f5eb9 in Item_cache_wrapper::val_bool (this=0x7f527b3f4e38) at /src/5.5/sql/item.cc:7899
 
#10 0x00000000008033b1 in Item_func_not::val_int (this=0x7f527b3c76f8) at /src/5.5/sql/item_cmpfunc.cc:335
 
#11 0x000000000068ec13 in Item_func_trig_cond::val_int (this=0x7f527b3ab038) at /src/5.5/sql/item_cmpfunc.h:486
 
#12 0x00000000007e0fff in Item::val_bool (this=0x7f527b3ab038) at /src/5.5/sql/item.cc:215
 
#13 0x0000000000811633 in Item_cond_and::val_int (this=0x7f527b3ab830) at /src/5.5/sql/item_cmpfunc.cc:4734
 
#14 0x00000000006daeee in SQL_SELECT::skip_record (this=0x7f527b3aae50, thd=0x7f527e3c4020) at /src/5.5/sql/opt_range.h:1028
 
#15 0x0000000000756104 in JOIN_CACHE::check_match (this=0x7f527b3abd30, rec_ptr=0x7f527b2a95a5 "\377\001\006\b") at /src/5.5/sql/sql_join_cache.cc:2476
 
#16 0x0000000000751d3f in JOIN_CACHE::generate_full_extensions (this=0x7f527b3abd30, rec_ptr=0x7f527b2a95a5 "\377\001\006\b") at /src/5.5/sql/sql_join_cache.cc:2389
 
#17 0x00000000007520d7 in JOIN_CACHE::join_null_complements (this=0x7f527b3abd30, skip_last=false) at /src/5.5/sql/sql_join_cache.cc:2544
 
#18 0x00000000007516ee in JOIN_CACHE::join_records (this=0x7f527b3abd30, skip_last=false) at /src/5.5/sql/sql_join_cache.cc:2120
 
#19 0x000000000075172b in JOIN_CACHE::join_records (this=0x7f527b3abb60, skip_last=false) at /src/5.5/sql/sql_join_cache.cc:2132
 
#20 0x000000000067a5ed in sub_select_cache (join=0x7f527b3c7890, join_tab=0x7f527b3aa398, end_of_records=true) at /src/5.5/sql/sql_select.cc:16719
 
#21 0x000000000067a7f8 in sub_select (join=0x7f527b3c7890, join_tab=0x7f527b3aa078, end_of_records=true) at /src/5.5/sql/sql_select.cc:16889
 
#22 0x000000000067a2e2 in do_select (join=0x7f527b3c7890, fields=0x7f527e3c7b48, table=0x0, procedure=0x0) at /src/5.5/sql/sql_select.cc:16600
 
#23 0x0000000000658c66 in JOIN::exec (this=0x7f527b3c7890) at /src/5.5/sql/sql_select.cc:2873
 
#24 0x0000000000659471 in mysql_select (thd=0x7f527e3c4020, rref_pointer_array=0x7f527e3c7ca0, tables=0x7f527b281338, wild_num=1, fields=..., conds=0x7f527b3c76f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f527b3c7870, unit=0x7f527e3c7350, select_lex=0x7f527e3c7a30) at /src/5.5/sql/sql_select.cc:3094
 
#25 0x000000000064fd0d in handle_select (thd=0x7f527e3c4020, lex=0x7f527e3c72a0, result=0x7f527b3c7870, setup_tables_done_option=0) at /src/5.5/sql/sql_select.cc:319
 
#26 0x00000000006291a0 in execute_sqlcom_select (thd=0x7f527e3c4020, all_tables=0x7f527b281338) at /src/5.5/sql/sql_parse.cc:4689
 
#27 0x00000000006224d3 in mysql_execute_command (thd=0x7f527e3c4020) at /src/5.5/sql/sql_parse.cc:2234
 
#28 0x000000000062bc80 in mysql_parse (thd=0x7f527e3c4020, rawbuf=0x7f527b281078 "SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON (f3 = f2) ) ON (f1 <> f2)\nWHERE f2 NOT IN ( SELECT 10 UNION SELECT 20 ORDER BY 2 )", length=125, parser_state=0x7f52816a6650) at /src/5.5/sql/sql_parse.cc:5914
 
#29 0x000000000061fa72 in dispatch_command (command=COM_QUERY, thd=0x7f527e3c4020, packet=0x7f527b9540e1 "", packet_length=125) at /src/5.5/sql/sql_parse.cc:1079
 
#30 0x000000000061ec05 in do_command (thd=0x7f527e3c4020) at /src/5.5/sql/sql_parse.cc:793
 
#31 0x00000000007214e1 in do_handle_one_connection (thd_arg=0x7f527e3c4020) at /src/5.5/sql/sql_connect.cc:1269
 
#32 0x000000000072126e in handle_one_connection (arg=0x7f527e3c4020) at /src/5.5/sql/sql_connect.cc:1185
 
#33 0x0000000000c8acbd in pfs_spawn_thread (arg=0x7f527e3f3140) at /src/5.5/storage/perfschema/pfs.cc:1015
 
#34 0x00007f528131e0a4 in start_thread () from /lib64/libpthread.so.0
 
#35 0x00007f527fd0d04d in clone () from /lib64/libc.so.6

Notes:

  • UNION in subquery is expected to fail, because there is no 2nd column to order by; but it crashes instead.
  • The tentative patch for MDEV-9304 does not fix this problem.

 Comments   
Comment by Alice Sherepa [ 2018-06-15 ]

not reproducible on current 5.5-10.3

Comment by Elena Stepanova [ 2020-11-25 ]

The crash was fixed in 5.5.54+ by a merged upstream patch:

commit 97fad8518bdce19938fdf55cbb5858e31e9ac464
 
Author: Kailasnath Nagarkar <kailasnath.nagarkar@oracle.com>
 
Date:   Fri Aug 26 11:11:27 2016 +0530
 
 
 
    Bug #23303485 : HANDLE_FATAL_SIGNAL (SIG=11) IN
 
                    SUBSELECT_UNION_ENGINE::NO_ROWS

After the patch the test case from the description would start failing with ER_BAD_FIELD_ERROR due to the unknown column 2 in the ORDER BY clause.

And recently, starting from 10.1.48, ER_BAD_FIELD_ERROR was eliminated as ORDER BY clause got ignored:

Author: Varun Gupta
 
Date:   Tue Aug 4 14:36:01 2020 +0530
 
 
 
    MDEV-9513: Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index
 
    
    Removing the ORDER BY clause from the UNION when UNION is inside an IN/ALL/ANY/EXISTS subquery.
 
    The rewrites are done for subqueries but this rewrite is not done for the fake_select of
 
    the UNION.

So, on 10.1+ the test case doesn't case the error anymore, but passes instead.

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