[MDEV-29362] Crash with query using constant subquery as left part of IN subquery Created: 2022-08-23  Updated: 2024-01-03  Resolved: 2024-01-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.10.0, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: crash

Attachments: HTML File 403_stack    
Issue Links:
Duplicate
is duplicated by MDEV-32314 Server crashes at Item_singlerow_subs... Closed
is duplicated by MDEV-32703 Assertion Failed at /mariadb-11.3.0/s... Closed

 Description   

mysqld: /home/wsh/database_fuzz/mysql_fuzz/Mariadb_10.3/sql/item_subselect.cc:1497: virtual longlong Item_singlerow_subselect::val_int(): Assertion `fixed()' failed.

poc:

CREATE TABLE v768 ( v769 BOOLEAN NOT NULL ) ;
 
 ( ( SELECT v769 FROM v768 ORDER BY v769 + v769 , v769 + ( v769 % ( SELECT v769 FROM v768 WHERE 16 = v769 ) <= v769 ) ) ) ;
 
 UPDATE v768 SET v769 = 99 WHERE v769 = -2147483648 ;
 
 INSERT INTO v768 ( v769 ) VALUES ( 15 ) , ( -1 ) ;
 
 SELECT v769 FROM v768 WHERE v769 IN ( v769 , 'x' NOT LIKE -1 ) GROUP BY v769 HAVING ( v769 IN ( ( ( SELECT ( SELECT v769 FROM v768 WHERE ( FALSE <= 127 BETWEEN 0 AND -2147483648 , v769 ) NOT IN ( SELECT ( v769 NOT IN ( v769 ) AND v769 NOT IN ( 63309275.000000 ^ v769 ) ) , v769 + v769 FROM v768 GROUP BY v769 HAVING ( v769 != 127 AND v769 = v769 AND ( NOT ( 'x' = 'x' AND FALSE = 90 ) ) AND v769 LIKE 'x' ) ) ) * -1 AS v770 FROM v768 WHERE NULL = v769 ) IN ( SELECT v769 FROM v768 ) ) < 'x' ) ) ;



 Comments   
Comment by Alice Sherepa [ 2022-08-23 ]

Thanks!
The bug is reproducible on 10.4+ with condition_pushdown_from_having=on, no crash on 10.3.

SET optimizer_switch='condition_pushdown_from_having=on';
 
 
 
CREATE TABLE t1 (i int);
 
INSERT INTO t1 VALUES (15),(1);
 
 
 
SELECT i FROM t1 GROUP BY i 
HAVING i IN ( (SELECT i FROM t1 where i=1) IN (SELECT i FROM t1)  );
 
 
 
DROP TABLE t1;

10.4 316847eab72022cd11351ea1

 
/10.4/src/sql/item_subselect.cc:1418: virtual longlong Item_singlerow_subselect::val_int(): Assertion `fixed == 1' failed.
 
220823 18:35:25 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.27-MariaDB-debug-log
 
 
 
sql/signal_handler.cc:232(handle_fatal_signal)[0x55879bbb69e7]
 
sql/item_subselect.cc:1419(Item_singlerow_subselect::val_int())[0x55879bdeb369]
 
sql/item.h:1549(Item::val_int_result())[0x55879b157414]
 
sql/item.cc:9980(Item_cache_int::cache_value())[0x55879bc5d43b]
 
sql/item_cmpfunc.cc:1574(Item_in_optimizer::val_int())[0x55879bc8ca33]
 
sql/sql_type.cc:8274(Type_handler_int_result::Item_eq_value(THD*, Type_cmp_attributes const*, Item*, Item*) const)[0x55879b991c3e]
 
sql/item_cmpfunc.cc:6667(Item_equal::add_const(THD*, Item*))[0x55879bcbda02]
 
sql/item_cmpfunc.cc:6793(Item_equal::merge_with_check(THD*, Item_equal*, bool))[0x55879bcbe0af]
 
sql/sql_select.cc:17294(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x55879b53ee10]
 
sql/opt_subselect.cc:6080(and_new_conditions_to_optimized_cond(THD*, Item*, COND_EQUAL**, List<Item>&, Item::cond_result*))[0x55879b904902]
 
sql/sql_select.cc:2108(JOIN::optimize_inner())[0x55879b4cb8a9]
 
sql/sql_select.cc:1676(JOIN::optimize())[0x55879b4c6bca]
 
sql/sql_select.cc:4772(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*))[0x55879b4e7c87]
 
sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55879b4b8b10]
 
sql/sql_parse.cc:6450(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55879b426d5e]
 
sql/sql_parse.cc:3964(mysql_execute_command(THD*))[0x55879b414691]
 
sql/sql_parse.cc:7996(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55879b43022f]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55879b406a23]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55879b403517]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55879b802090]
 
sql/sql_connect.cc:1317(handle_one_connection)[0x55879b8017e9]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55879c48d83d]
 
nptl/pthread_create.c:478(start_thread)[0x7fcb78701609]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0)
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fcb782d2133]
 
 
 
Query (0x62b0000a1290): SELECT i FROM t1 GROUP BY i 
HAVING i IN ( (SELECT i FROM t1 where i=1) IN (SELECT i FROM t1)  )

on non-debug version- signal 11:

10.6.9

 
220823 18:39:28 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.6.9-MariaDB
 
 
 
sql/signal_handler.cc:236(handle_fatal_signal)[0x560ddb3f0917]
 
sigaction.c:0(__restore_rt)[0x7f1b2c0cf630]
 
sql/item_subselect.cc:4047(subselect_single_select_engine::exec())[0x560ddb4aaadc]
 
sql/item_subselect.cc:859(Item_subselect::exec())[0x560ddb4a917a]
 
sql/item_subselect.cc:1498(Item_singlerow_subselect::val_int())[0x560ddb4a9d1e]
 
sql/item.cc:10087(Item_cache_int::cache_value())[0x560ddb405407]
 
sql/item_cmpfunc.cc:1571(Item_in_optimizer::val_int())[0x560ddb433d8b]
 
sql/sql_type.cc:8706(Type_handler_int_result::Item_eq_value(THD*, Type_cmp_attributes const*, Item*, Item*) const)[0x560ddb330f9f]
 
sql/item_cmpfunc.cc:6760(Item_equal::add_const(THD*, Item*))[0x560ddb439ce4]
 
sql/item_cmpfunc.cc:6885(Item_equal::merge_with_check(THD*, Item_equal*, bool))[0x560ddb439fcd]
 
sql/sql_list.h:429(base_list_iterator::next())[0x560ddb200fe0]
 
sql/opt_subselect.cc:6128(and_new_conditions_to_optimized_cond(THD*, Item*, COND_EQUAL**, List<Item>&, Item::cond_result*))[0x560ddb308e8e]
 
sql/sql_select.cc:2256(JOIN::optimize_inner())[0x560ddb22a718]
 
sql/sql_select.cc:1838(JOIN::optimize())[0x560ddb22c375]
 
sql/sql_select.cc:5027(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560ddb22c448]
 
sql/sql_select.cc:566(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560ddb22cd54]
 
sql/sql_parse.cc:6257(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560ddb08fc6a]
 
sql/sql_parse.cc:3946(mysql_execute_command(THD*, bool))[0x560ddb1ced9c]
 
sql/sql_parse.cc:8031(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x560ddb1d112b]
 
sql/sql_parse.cc:1955(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x560ddb1d32e7]
 
sql/sql_parse.cc:1411(do_command(THD*, bool))[0x560ddb1d4983]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x560ddb2cac47]
 
sql/sql_connect.cc:1318(handle_one_connection)[0x560ddb2caee4]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x560ddb65fcdc]
 
pthread_create.c:0(start_thread)[0x7f1b2c0c7ea5]
 
 
 
Query (0x7f1ac80116f0): SELECT i FROM t1 GROUP BY i 
HAVING i IN ( (SELECT i FROM t1 where i=1) IN (SELECT i FROM t1)  )

Comment by Alice Sherepa [ 2023-10-04 ]

test from MDEV-32314

SELECT * FROM ( SELECT 1 x ) ss GROUP BY x HAVING x = ( ( ( SELECT 1 UNION SELECT 1 ) ) IN ( SELECT NULL WHERE FALSE ) ) ;


Version: '10.4.32-MariaDB-debug-log'  
mysqld: 10.4/src/sql/item_subselect.cc:1375: virtual longlong Item_singlerow_subselect::val_int(): Assertion `fixed == 1' failed.
 
231004  9:23:21 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fdd9efbffd6]
 
sql/item_subselect.cc:1376(Item_singlerow_subselect::val_int())[0x55b81754c205]
 
sql/item.h:1557(Item::val_int_result())[0x55b8168930be]
 
sql/item.cc:10013(Item_cache_int::cache_value())[0x55b8173b8209]
 
sql/item_cmpfunc.cc:1587(Item_in_optimizer::val_int())[0x55b8173ea263]
 
sql/sql_type.cc:8274(Type_handler_int_result::Item_eq_value(THD*, Type_cmp_attributes const*, Item*, Item*) const)[0x55b8170e8df4]
 
sql/item_cmpfunc.cc:6712(Item_equal::add_const(THD*, Item*))[0x55b81741b56a]
 
sql/item_cmpfunc.cc:6838(Item_equal::merge_with_check(THD*, Item_equal*, bool))[0x55b81741bc17]
 
sql/sql_select.cc:17493(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x55b816c82ac1]
 
sql/opt_subselect.cc:6081(and_new_conditions_to_optimized_cond(THD*, Item*, COND_EQUAL**, List<Item>&, Item::cond_result*))[0x55b817057578]
 
sql/sql_select.cc:2143(JOIN::optimize_inner())[0x55b816c0e90a]
 
sql/sql_select.cc:1711(JOIN::optimize())[0x55b816c09c2b]
 
sql/sql_select.cc:4812(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*))[0x55b816c2ac1d]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55b816bfb922]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55b816b6772c]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55b816b54ea3]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55b816b70c07]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55b816b4702d]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55b816b43b58]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55b816f517fd]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x55b816f510a1]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55b817bfb99a]
 
nptl/pthread_create.c:478(start_thread)[0x7fdd9f4da609]
 
 
 
Query (0x62b0000a1420): SELECT * FROM ( SELECT 1 x ) ss GROUP BY x HAVING x = ( ( ( SELECT 1 UNION SELECT 1 ) ) IN ( SELECT NULL WHERE FALSE ) )

Comment by Alice Sherepa [ 2023-12-04 ]

please check also the test from MDEV-32703:

CREATE TABLE x ( x INT ) ;
 
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
UPDATE x SET x = 1 WHERE x = 1 ;
 
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
 
SELECT x = 'x' AND x > 1 , 'x' , NULL , 'x' FROM x GROUP BY x HAVING ( x IN ( ( SELECT x FROM x AS x GROUP BY x IN ( 1 , 'x' , 1 , 1 , 1 ) HAVING ( ( SELECT 1 FROM x WHERE ( 'x' ^ 1.000000 = 1 AND x AND x ) NOT LIKE CASE WHEN ( SELECT x ) + x IS NOT NULL = 1 THEN 1 ELSE x + 1 END ) NOT IN ( SELECT * FROM x ) AND x = 1 ) ) IN ( SELECT x FROM x ) ) ) ;

Comment by Igor Babaev [ 2023-12-15 ]

The following test case causes the same kind of crash:

CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (15), (1), (2);
 
CREATE TABLE t2 (b int) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (15), (1);
 
CREATE TABLE t3 (c int) ENGINE=MyISAM;
 
INSERT INTO t3 VALUES (15), (1);
 
 
 
SET optimizer_switch='condition_pushdown_from_having=on';
 
SELECT a FROM t1 GROUP BY a
 
   HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1;

With the setting

SET optimizer_switch='condition_pushdown_from_having=off';

the query returns the expected result: 

MariaDB [test]> SELECT a FROM t1 GROUP BY a
 
    ->    HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1;
 
+------+
 
| a    |
 
+------+
 
|    2 |
 
+------+

Comment by Igor Babaev [ 2023-12-16 ]

Please review the patch. See also bb-10.4-mdev-29362.

Comment by Igor Babaev [ 2023-12-16 ]

All test cases reported reported in this MDEV and all test cases from the duplicate MDEVs were checked against the fix.

Comment by Oleksandr Byelkin [ 2024-01-02 ]

OK to push

Comment by Igor Babaev [ 2024-01-03 ]

A fix for this bug was pushed into 10.4. It should be merged upstream as it is.

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