Alice Sherepa added a comment - 2023-10-05 07:18 Thank you for the report! I repeated as described in MariaDB 10.4-11.2: SELECT 1 FROM ( SELECT 1 FROM ( SELECT 1 x )dt1 GROUP BY x HAVING x = ( x IS NULL OR x IS NULL ) ) dt ; 231005 9:15:35 [ERROR] mysqld got signal 11 ; Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2   sql/signal_handler.cc:238(handle_fatal_signal)[0x55e20f8f17e9] sigaction.c:0(__restore_rt)[0x7f0d8bbc5420] sql/item.cc:3411(Item_field::used_tables() const)[0x55e20f9605e7] sql/item.cc:7650(find_producing_item(Item*, st_select_lex*))[0x55e20f9818b8] sql/item.cc:7680(Item_field::derived_field_transformer_for_where(THD*, unsigned char*))[0x55e20f981c73] sql/item.cc:602(Item::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20f946b39] sql/item_func.cc:474(Item_args::transform_args(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20fa4fdd1] sql/item_func.cc:510(Item_func::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20fa5004f] sql/item_cmpfunc.cc:5071(Item_cond::do_transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*, bool))[0x55e20f9ee174] sql/item_cmpfunc.h:3022(Item_cond::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20fa0f11c] sql/item_func.cc:474(Item_args::transform_args(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20fa4fdd1] sql/item_func.cc:510(Item_func::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20fa5004f] sql/sql_derived.cc:1376(transform_condition_or_part(THD*, Item*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20f0615e3] sql/sql_lex.cc:9829(st_select_lex::pushdown_cond_into_where_clause(THD*, Item*, Item**, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55e20f0efd31] sql/sql_derived.cc:1568(pushdown_cond_for_derived(THD*, Item*, TABLE_LIST*))[0x55e20f0623d1] sql/sql_select.cc:2198(JOIN::optimize_inner())[0x55e20f1f00bd] sql/sql_select.cc:1711(JOIN::optimize())[0x55e20f1eac2b] sql/sql_derived.cc:1029(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55e20f05ef3f] sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55e20f0596ea] sql/sql_select.cc:2201(JOIN::optimize_inner())[0x55e20f1f0190] sql/sql_select.cc:1711(JOIN::optimize())[0x55e20f1eac2b] 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*))[0x55e20f20bc1d] sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e20f1dc922] sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e20f14872c] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55e20f135ea3] sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e20f151c07] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e20f12802d] sql/sql_parse.cc:1378(do_command(THD*))[0x55e20f124b58] sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55e20f5327fd] sql/sql_connect.cc:1325(handle_one_connection)[0x55e20f5320a1] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e2101dc99a] nptl/pthread_create.c:478(start_thread)[0x7f0d8bbb9609]   Query (0x62b0000a1420): SELECT 1 FROM ( SELECT 1 FROM ( SELECT 1 x )dt1 GROUP BY x HAVING x = ( x IS NULL OR x IS NULL ) ) dt

Sergei Petrunia added a comment - 2023-10-06 07:44 The crash happens when we do pushdown from HAVING into WHERE. We un-fix the items, then create their clone, then call table_map Item_field::used_tables() const { => if (field->table->const_table) with field=0 (because the item is not fixed yet).

Galina Shalygina (Inactive) added a comment - 2024-06-24 06:12 The testcase can be shortened to: CREATE VIEW v1 AS SELECT 1 AS a; SELECT * FROM v1 GROUP BY a HAVING a = (a IS NULL OR a IS NULL ); With switched off pushdown from HAVING into WHERE optimization the query doesn't crash: MariaDB [test]> set statement optimizer_switch= 'condition_pushdown_from_having=off' for -> SELECT * FROM v1 -> GROUP BY a HAVING a = (a IS NULL OR a IS NULL ); Empty set (0,001 sec) So, the problem should be searched in this optimization. When the optimization is entered HAVING condition is already transformed into: multiple equal(/*always not null*/ 1 is null or /*always not null*/ 1 is null, v1.a) /*always not null*/ is just v1.a , and both of these /*always not null*/ are references to the same object. During optimization (/*always not null*/ 1 is null or /*always not null*/ 1 is null) is marked as an immutable condition with IMMUTABLE_FL flag, that means that is will be not cleaned and fixed at the end of the optimization. However, during cleanup IMMUTABLE_FL is removed from the first reference of the /*always not null*/ object. When the second reference is met, it already doesn't have IMMUTABLE_FL and is cleaned up. After the cleanup fixing is made, but the whole condition (/*always not null*/ 1 is null or /*always not null*/ 1 is null) is still marked as immutable and is not fixed. That leads to the situation when the second reference to the /*always not null*/ object is cleaned up, but not fixed, and that causes a crash. This bug is similar to MDEV-32293 and will be fixed with MDEV-29363 fix.

Galina Shalygina (Inactive) added a comment - 2024-07-04 15:47 Fixed in 10.5. Pushed with MDEV-29363 commit . The testcase for this MDEV is included in the patch (find it here ). The initial query doesn't crash anymore: MariaDB [t]> SELECT -> ( WITH x AS -> ( ( SELECT * -> FROM ( SELECT 1 x ) x -> GROUP BY x HAVING x = ( x IS NULL OR x IS NULL ) ) ) ( SELECT 1.000000 FROM x ) ) ; + ---------------------------------------------------------------------------------------------------------------------------------------------------+ | ( WITH x AS ( ( SELECT * FROM ( SELECT 1 x ) x GROUP BY x HAVING x = ( x IS NULL OR x IS NULL ) ) ) ( SELECT 1.000000 FROM x ) ) | + ---------------------------------------------------------------------------------------------------------------------------------------------------+ | NULL | + ---------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,001 sec)