This bug has been actually fixed by the patch for MDEV-26402. Only a test case of MDEV-28082 will be added to 10.4.
Igor Babaev (Inactive)
added a comment - This bug has been actually fixed by the patch for MDEV-26402 . Only a test case of MDEV-28082 will be added to 10.4.
Igor Babaev (Inactive)
added a comment - Here's a more general test case that conforms to the Standard SQL.
CREATE TABLE t1 (a int , b int NOT NULL ) ;
INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10);
SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL ) ;
DROP TABLE t1;
Yet execution of the SELECT in this test case causes a crash of the server earlier:
sql/signal_handler.cc:222(handle_fatal_signal)[0x55c52ade0510]
/lib64/libpthread.so.0(+0xf890)[0x7fda531ff890]
sql/item.cc:3404(Item_field::used_tables() const)[0x55c52ae0b559]
sql/sql_select.cc:14135(update_depend_map_for_order(JOIN*, st_order*))[0x55c52ab11d19]
sql/sql_select.cc:14243(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x55c52ab120b7]
sql/sql_select.cc:2299(JOIN::optimize_inner())[0x55c52aaedf1a]
sql/sql_select.cc:1661(JOIN::optimize())[0x55c52aaeb8ad]
sql/sql_select.cc:4752(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*))[0x55c52aaf6f59]
sql/sql_select.cc:448(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55c52aae6666]
sql/sql_parse.cc:6465(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55c52aaab721]
sql/sql_parse.cc:3979(mysql_execute_command(THD*))[0x55c52aaa21c9]
sql/sql_parse.cc:8011(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55c52aaaf6bb]
sql/sql_parse.cc:1876(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55c52aa9bdb3]
sql/sql_parse.cc:1379(do_command(THD*))[0x55c52aa9a489]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55c52ac2c1a1]
sql/sql_connect.cc:1317(handle_one_connection)[0x55c52ac2be4b]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55c52b17ea81]
/lib64/libpthread.so.0(+0x80a4)[0x7fda531f80a4]
/lib64/libc.so.6(clone+0x6d)[0x7fda5263004d]
Thank you for the report!
It is reproducible on 10.4-10.9, workaround-set optimizer_switch='condition_pushdown_from_having=off'
with MyISAM:
10.4 069139a549a62f26d566c1ae
Version: '10.4.25-MariaDB-debug-log'
220318 15:54:37 [ERROR] mysqld got signal 11 ;
sigaction.c:0(__restore_rt)[0x7fc64dbd03c0]
sql/item.cc:6489(Item_field::make_send_field(THD*, Send_field*))[0x56062361b93f]
sql/field.cc:11218(Send_field::Send_field(THD*, Item*))[0x56062355bfe6]
sql/protocol.cc:997(Protocol_text::store_field_metadata(THD*, Item*, unsigned int))[0x560622b2f9e0]
sql/protocol.cc:914(Protocol::send_result_set_metadata(List<Item>*, unsigned int))[0x560622b2e835]
sql/sql_class.cc:3068(select_send::send_result_set_metadata(List<Item>&, unsigned int))[0x560622cdc928]
sql/sql_select.cc:14485(return_zero_rows(JOIN*, select_result*, List<TABLE_LIST>&, List<Item>&, bool, unsigned long long, char const*, Item*, List<Item>&))[0x560622f12db6]
sql/sql_select.cc:4465(JOIN::exec_inner())[0x560622ec978b]
sql/sql_select.cc:4325(JOIN::exec())[0x560622ec79da]
sql/sql_select.cc:4765(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*))[0x560622ecbbba]
sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560622e9cc70]
sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560622e0b25f]
sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x560622df8b3e]
sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560622e1474d]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560622deaed4]
sql/sql_parse.cc:1373(do_command(THD*))[0x560622de7951]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5606231e330d]
sql/sql_connect.cc:1317(handle_one_connection)[0x5606231e2a66]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x560623ee409d]
nptl/pthread_create.c:478(start_thread)[0x7fc64dbc4609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fc64d795163]
Query (0x62b0000a1290): SELECT * FROM v0 GROUP BY TRUE HAVING v4 = (v1 IS NULL)
With INNODB:
10.4 069139a549a62f26d566c1ae
Version: '10.4.25-MariaDB-debug-log'
220318 16:16:46 [ERROR] mysqld got signal 11 ;
sql/signal_handler.cc:222(handle_fatal_signal)[0x5643fe46d873]
sigaction.c:0(__restore_rt)[0x7f19702fa3c0]
sql/item_cmpfunc.h:2568(Item_func_isnull::arg_is_datetime_notnull_field())[0x5643fde5fb2e]
sql/item_cmpfunc.h:2578(Item_func_isnull::update_used_tables())[0x5643fe584ad3]
sql/item.h:5141(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(Item*))[0x5643fdb694e2]
sql/item.h:5151(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(unsigned int, Item**))[0x5643fdb69562]
sql/item_func.h:161(Item_func::update_used_tables())[0x5643fdb6da78]
sql/sql_select.cc:15873(eliminate_item_equal(THD*, Item*, COND_EQUAL*, Item_equal*))[0x5643fddf258d]
sql/sql_select.cc:16047(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x5643fddf3189]
sql/sql_select.cc:2461(JOIN::optimize_stage2())[0x5643fdd8b67e]
sql/sql_select.cc:2342(JOIN::optimize_inner())[0x5643fdd89fc2]
sql/sql_select.cc:1659(JOIN::optimize())[0x5643fdd82bda]
sql/sql_select.cc:4749(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*))[0x5643fdda39c9]
sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5643fdd74c70]
sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5643fdce325f]
sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x5643fdcd0b3e]
sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5643fdcec74d]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5643fdcc2ed4]
sql/sql_parse.cc:1373(do_command(THD*))[0x5643fdcbf951]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5643fe0bb30d]
sql/sql_connect.cc:1317(handle_one_connection)[0x5643fe0baa66]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5643fedbc09d]
nptl/pthread_create.c:478(start_thread)[0x7f19702ee609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f196febf163]
Query (0x62b0000a1290): select a,b from t1 group by a having a = (b is null)