[MDEV-28616] Crash when using derived table over union with order by clause Created: 2022-05-19  Updated: 2023-09-04  Resolved: 2023-07-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.35, 10.3
Fix Version/s: 10.3.39, 10.4.29, 10.5.20

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: fuzzer, not-10.4
Environment:

ubuntu 18.04

Attachments: HTML File 65_stack    
Issue Links:
Relates

 Description   

poc:

CREATE TABLE v1239 ( v1240 BOOLEAN NOT NULL , v1241 INT ) ;
 
 CREATE TABLE v1242 ( v1243 BOOLEAN NOT NULL , v1244 INT ) ;
 
 ( ( SELECT v1243 FROM ( SELECT v1243 FROM v1242 UNION SELECT v1243 NOT IN ( v1243 ) AND v1244 NOT IN ( NOT v1243 > v1243 % v1244 % CASE ( 83654606.000000 ^ 50823089.000000 AND 'x' = ( 71 * 41 = 36 AND ( v1243 = 81 OR v1243 = 11 ) NOT LIKE ( ( NOT ( 63411216.000000 AND v1244 = 0 ) ) = -2147483648 AND v1243 = 73 ) ) ) * 85 = 98 WHEN 127 THEN v1244 = 74314347.000000 OR - ( 21636966.000000 AND v1243 = 61 ) + ( NOT -128 ) WHEN 8 THEN 'x' ELSE -128 END != -1 ) AS v1245 FROM v1242 WHERE 28178329.000000 = -1 ORDER BY v1243 + v1243 , ( v1243 = -128 OR v1243 = 0 ) NOT LIKE ( SELECT v1244 FROM v1242 WHERE ( FALSE <= 'x' * 7 BETWEEN 97 AND 15 , v1244 ) NOT IN ( SELECT ( v1240 NOT IN ( v1240 ) AND v1241 NOT IN ( 0 ^ v1241 ) ) , v1241 + v1241 FROM v1239 GROUP BY v1240 HAVING ( v1240 IN ( CASE v1240 WHEN v1240 THEN 'x' ELSE TRUE END != ( ( ( v1241 OR NOT v1240 ) BETWEEN 50 AND -128 ) ) ) ) ) ) * ( 'x' = 'x' AND ( -1 ) IS NULL ) ) AS v1246 WHERE v1243 IN ( 'x' = v1243 ) ORDER BY v1243 + v1243 , v1243 + v1243 ) ) ;
 
 UPDATE v1242 SET v1244 = 52 WHERE ( v1244 > ( v1244 = 81 AND v1243 = 0 ) ) = 37 ;
 
 INSERT INTO v1242 ( v1244 ) VALUES ( 56 ) , ( -128 ) ;
 
 SELECT COUNT ( v1241 ) OVER v1247 , MAX ( v1240 ) OVER v1247 FROM v1239 WINDOW v1247 AS ( PARTITION BY v1241 ORDER BY v1241 DESC ) ;

output:
SUMMARY: AddressSanitizer: SEGV /sql/item.cc:8083 in Item_ref::fix_fields(THD*, Item**)

The full error log is in the attachment.

 Comments   
Comment by Daniel Black [ 2022-05-19 ]

Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 for the first select statement.

Comment by Alice Sherepa [ 2022-05-25 ]

reproducible on 10.3, not on 10.4+
This seems to be related to MDEV-28507.

SELECT 1 FROM (SELECT 1 UNION SELECT 1 
ORDER BY  (SELECT 1 FROM dual WHERE 'x' IN (SELECT 1 FROM dual HAVING XXX)) )dt   ;

10.3 7d3d3838c1b8af98a9704

 
220525 16:57:41 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.36-MariaDB-debug-log
 
 
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x564cc138f742]
 
sigaction.c:0(__restore_rt)[0x7fc851cb6420]
 
sql/item.cc:8083(Item_ref::fix_fields(THD*, Item**))[0x564cc1419078]
 
sql/item.h:829(Item::fix_fields_if_needed(THD*, Item**))[0x564cc09a175b]
 
sql/item_func.cc:352(Item_func::fix_fields(THD*, Item**))[0x564cc14d6f34]
 
sql/item.h:829(Item::fix_fields_if_needed(THD*, Item**))[0x564cc09a175b]
 
sql/item.h:833(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x564cc09a1795]
 
sql/item.h:838(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x564cc0ac7599]
 
sql/sql_select.cc:1215(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x564cc0cbffbd]
 
sql/item_subselect.cc:3808(subselect_single_select_engine::prepare(THD*))[0x564cc15ca3d8]
 
sql/item_subselect.cc:282(Item_subselect::fix_fields(THD*, Item**))[0x564cc15a4203]
 
sql/item_subselect.cc:3471(Item_in_subselect::fix_fields(THD*, Item**))[0x564cc15c71ad]
 
sql/item.h:829(Item::fix_fields_if_needed(THD*, Item**))[0x564cc09a175b]
 
sql/item.h:833(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x564cc09a1795]
 
sql/item.h:838(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x564cc0ac7599]
 
sql/sql_base.cc:8274(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x564cc0abed6e]
 
sql/sql_select.cc:660(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x564cc0cb9344]
 
sql/sql_select.cc:1157(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x564cc0cbf394]
 
sql/item_subselect.cc:3808(subselect_single_select_engine::prepare(THD*))[0x564cc15ca3d8]
 
sql/item_subselect.cc:282(Item_subselect::fix_fields(THD*, Item**))[0x564cc15a4203]
 
sql/item.h:829(Item::fix_fields_if_needed(THD*, Item**))[0x564cc09a175b]
 
sql/item.h:833(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x564cc09a1795]
 
sql/item.h:842(Item::fix_fields_if_needed_for_order_by(THD*, Item**))[0x564cc0d929db]
 
sql/sql_select.cc:23711(find_order_in_list(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, st_order*, List<Item>&, List<Item>&, bool, bool, bool))[0x564cc0d6b973]
 
sql/sql_select.cc:23758(setup_order(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<Item>&, List<Item>&, st_order*, bool))[0x564cc0d6bf39]
 
sql/sql_select.cc:676(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x564cc0cb9798]
 
sql/sql_select.cc:1157(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x564cc0cbf394]
 
sql/sql_select.cc:4305(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564cc0ce0a2a]
 
sql/sql_union.cc:1673(st_select_lex_unit::exec())[0x564cc0eb93c7]
 
sql/sql_derived.cc:1169(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x564cc0b5670e]
 
sql/sql_derived.cc:193(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x564cc0b5028d]
 
sql/sql_select.cc:12913(st_join_table::preread_init())[0x564cc0d1eeb6]
 
sql/sql_select.cc:19853(sub_select(JOIN*, st_join_table*, bool))[0x564cc0d4e90f]
 
sql/sql_select.cc:19423(do_select(JOIN*, Procedure*))[0x564cc0d4ce88]
 
sql/sql_select.cc:4151(JOIN::exec_inner())[0x564cc0cdfa6d]
 
sql/sql_select.cc:3946(JOIN::exec())[0x564cc0cdd3ea]
 
sql/sql_select.cc:4356(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564cc0ce0ee8]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564cc0cb7449]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564cc0c28191]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x564cc0c161cc]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564cc0c31eee]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564cc0c08dcb]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x564cc0c0590e]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x564cc0fd8ef1]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x564cc0fd87ab]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564cc2608209]
 
nptl/pthread_create.c:478(start_thread)[0x7fc851caa609]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0)
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fc851bcd133]
 
 
 
Query (0x62b000000290): SELECT 1 FROM (SELECT 1 UNION SELECT 1 
ORDER BY  (SELECT 1 FROM dual WHERE 'x' IN (SELECT 1 FROM dual HAVING XXX)) )dt

Comment by Igor Babaev [ 2023-01-25 ]

A simpler test case that causes a crash of the same kind:

SELECT 1 FROM (SELECT 1 UNION SELECT 2 ORDER BY  (SELECT 1 FROM DUAL WHERE xxx = 0)) dt;

A more meaningful test case that causes the same problem

create table t1 (a int, b int);
 
insert into t1 values (3,8), (7,2), (1,4), (5,9);
 
 
 
create table t2 (a int, b int);
 
insert into t2 values (9,1), (7,3), (2,6);
 
 
 
create table t3 (c int, d int);
 
insert into t3 values (7,8), (1,2), (3,8);
 
 
 
select * from
 
(
 
  select a,b from t1 where t1.a > 3
 
  union
 
  select a,b from t2 where t2.b < 6
 
  order by (a - b / (select a + max(c) from t3  where d = x))
 
) dt;

Comment by Oleksandr Byelkin [ 2023-01-25 ]

OK to push

Generated at Thu Feb 08 10:02:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.