[MDEV-29362] Crash with query using constant subquery as left part of IN subquery - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.10.0, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
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
Component/s: Optimizer
Labels:
- crash

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' ) ) ;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

403_stack
9 kB
2022-08-23 12:52

Issue Links

is duplicated by

MDEV-32314 Server crashes at Item_singlerow_subselect::store

Closed

MDEV-32703 Assertion Failed at /mariadb-11.3.0/sql/item_subselect.cc:1455

Closed

relates to

MDEV-32313 pushdown: and_new_conditions_to_optimized_cond: Server crashes at Item_singlerow_subselect::element_index

Closed

MDEV-32316 pushdown: and_new_conditions_to_optimized_cond: Server crashes at subselect_single_select_engine::exec

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2022-08-23 16:44

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) )

Alice Sherepa added a comment - 2022-08-23 16:44 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) )

Alice Sherepa added a comment - 2023-10-04 07:31

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 ) )

Alice Sherepa added a comment - 2023-10-04 07:31 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 ) )

Alice Sherepa added a comment - 2023-12-04 17:16

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 ) ) ) ;

Alice Sherepa added a comment - 2023-12-04 17:16 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 ) ) ) ;

Igor Babaev (Inactive) added a comment - 2023-12-15 04:42 - edited

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 |

+------+

Igor Babaev (Inactive) added a comment - 2023-12-15 04:42 - edited 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 | +------+

Igor Babaev (Inactive) added a comment - 2023-12-16 04:47

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

Igor Babaev (Inactive) added a comment - 2023-12-16 04:47 Please review the patch. See also bb-10.4-mdev-29362.

Igor Babaev (Inactive) added a comment - 2023-12-16 04:50

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

Igor Babaev (Inactive) added a comment - 2023-12-16 04:50 All test cases reported reported in this MDEV and all test cases from the duplicate MDEVs were checked against the fix.

Oleksandr Byelkin added a comment - 2024-01-02 07:51

OK to push

Oleksandr Byelkin added a comment - 2024-01-02 07:51 OK to push

Igor Babaev (Inactive) added a comment - 2024-01-03 07:27

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

Igor Babaev (Inactive) added a comment - 2024-01-03 07:27 A fix for this bug was pushed into 10.4. It should be merged upstream as it is.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Shihao Wen

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2022-08-23 12:52

Updated:: 2024-06-27 12:35

Resolved:: 2024-01-03 07:27

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration