field|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|st_select_lex::pushdown_from_having_into_where

context|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|st_select_lex::pushdown_from_having_into_where

SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item::fix_fields_if_needed|Item_func::fix_fields

CREATE TABLE t(c DOUBLE,v2 DOUBLE UNIQUE CHECK ((c=1)OR (c=FALSE) OR (c AND 86) OR (c=0))) ENGINE=InnoDB;

SELECT 1 FROM t WHERE v2=(SELECT 1 FROM t GROUP BY c HAVING c>v2 - 0)AND v2<=5 / 1 AND v2>=97;

SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item::fix_fields_if_needed|Item_func::fix_fields

context|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item::fix_fields_if_needed

field|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item::fix_fields_if_needed

CREATE TABLE t(c TEXT,c2 INT UNIQUE) ENGINE=InnoDB;

SELECT 1 FROM t WHERE c2 IN(SELECT 1 FROM t GROUP BY'',c HAVING (CASE c2 WHEN 1 +''THEN 3 ELSE c END)ORDER BY c2) GROUP BY c2 HAVING c2=1;

context|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item_func_case::fix_fields

field|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item_func_case::fix_fields

CREATE TABLE t(c INT,c2 CHAR KEY UNIQUE) ENGINE=InnoDB;

CREATE VIEW v AS SELECT * FROM t WHERE c LIKE''GROUP BY c2 HAVING c>c;

SELECT * FROM v AS v1 NATURAL JOIN v AS v5 NATURAL JOIN v WHERE c LIKE''AND c2 IN(SELECT''FROM t WHERE c LIKE''GROUP BY c HAVING c2 LIKE (c2< + 1 OR c>1)>=c2);

context|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item_func_like::fix_fields

field|SIGABRT|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item_func_like::fix_fields

CREATE TABLE t(c INT,c2 CHAR KEY UNIQUE) ENGINE=InnoDB;

CREATE VIEW v AS SELECT * FROM t WHERE c LIKE''GROUP BY c2 HAVING c>c;

SELECT * FROM v AS v1 NATURAL JOIN v AS v5 NATURAL JOIN v WHERE c LIKE''AND c2 IN(SELECT''FROM t WHERE c LIKE''GROUP BY c HAVING c2 LIKE (c2< + 1 OR c>1)>=c2);

/test/10.11_opt_san/sql/item.cc:6029:42: runtime error: member access within null pointer of type 'struct Name_resolution_context'

    #0 0x5629a9e61ce0 in Item_field::fix_fields(THD*, Item**) /test/10.11_opt_san/sql/item.cc:6029

    #1 0x5629aa1e1224 in Item::fix_fields_if_needed(THD*, Item**) /test/10.11_opt_san/sql/item.h:1144

    #2 0x5629aa1e1224 in Item_func::fix_fields(THD*, Item**) /test/10.11_opt_san/sql/item_func.cc:347

    #3 0x5629a9ffb5a0 in Item_func_like::fix_fields(THD*, Item**) /test/10.11_opt_san/sql/item_cmpfunc.cc:5785

    #4 0x5629aa1e1224 in Item::fix_fields_if_needed(THD*, Item**) /test/10.11_opt_san/sql/item.h:1144

    #5 0x5629aa1e1224 in Item_func::fix_fields(THD*, Item**) /test/10.11_opt_san/sql/item_func.cc:347

    #6 0x5629a877df4a in st_select_lex::pushdown_from_having_into_where(THD*, Item*) /test/10.11_opt_san/sql/sql_lex.cc:11258

    #7 0x5629a8bd8229 in JOIN::optimize_inner() /test/10.11_opt_san/sql/sql_select.cc:2281

    #8 0x5629a8c03f6f in JOIN::optimize() /test/10.11_opt_san/sql/sql_select.cc:1863

    #9 0x5629a86e5632 in st_select_lex::optimize_unflattened_subqueries(bool) /test/10.11_opt_san/sql/sql_lex.cc:4915

    #10 0x5629a8bbe96e in JOIN::optimize_stage2() /test/10.11_opt_san/sql/sql_select.cc:3347

    #11 0x5629a8bddd91 in JOIN::optimize_inner() /test/10.11_opt_san/sql/sql_select.cc:2550

    #12 0x5629a8c03f6f in JOIN::optimize() /test/10.11_opt_san/sql/sql_select.cc:1863

    #13 0x5629a8c151fa in 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*) /test/10.11_opt_san/sql/sql_select.cc:5056

    #14 0x5629a8c19093 in handle_select(THD*, LEX*, select_result*, unsigned long) /test/10.11_opt_san/sql/sql_select.cc:581

    #15 0x5629a8826c2f in execute_sqlcom_select /test/10.11_opt_san/sql/sql_parse.cc:6261

    #16 0x5629a887753b in mysql_execute_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:3945

    #17 0x5629a87f7500 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /test/10.11_opt_san/sql/sql_parse.cc:8035

    #18 0x5629a884c0ff in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /test/10.11_opt_san/sql/sql_parse.cc:1894

    #19 0x5629a88573fd in do_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:1407

    #20 0x5629a91404cd in do_handle_one_connection(CONNECT*, bool) /test/10.11_opt_san/sql/sql_connect.cc:1418

    #21 0x5629a9142b3c in handle_one_connection /test/10.11_opt_san/sql/sql_connect.cc:1312

    #22 0x14e54c2d1608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477

    #23 0x14e54b546132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132)

Compiled with GCC >=7.5.0 (I use GCC 9.4.0) and:

    -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_UBSAN=ON -DWITH_RAPID=OFF -DWSREP_LIB_WITH_ASAN=ON

Set before execution:

    export UBSAN_OPTIONS=print_stacktrace=1

SET sql_mode='only_full_group_by';

EXECUTE IMMEDIATE 'SELECT LEAD(c) OVER (ORDER BY c) FROM (SELECT 0 AS c) AS a NATURAL JOIN (SELECT 0 AS c) AS b;';

Core was generated by `/test/MD171222-mariadb-10.11.2-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.

Program terminated with signal SIGSEGV, Segmentation fault.

#0  0x000055de9c220afc in Item_field::fix_fields (this=0x1554b8023908, thd=

    0x1554b8000d48, reference=<optimized out>)

    at /test/10.11_dbg/sql/item.cc:6256

6256	      else if (thd->lex->in_sum_func->nest_level !=

[Current thread is 1 (Thread 0x1554ed81d700 (LWP 2635384))]

(gdb) bt

#0  0x000055de9c220afc in Item_field::fix_fields (this=0x1554b8023908, thd=0x1554b8000d48, reference=<optimized out>) at /test/10.11_dbg/sql/item.cc:6256

#1  0x000055de9be91530 in Item::fix_fields_if_needed (ref=0x1554ed81a830, thd=0x1554b8000d48, this=<optimized out>) at /test/10.11_dbg/sql/item.h:1147

#2  find_field_in_natural_join (actual_table=0x1554ed81a958, register_tree_change=true, ref=<optimized out>, length=1, name=0x1554b801f888 "c", table_ref=0x1554b8022810, thd=0x1554b8000d48) at /test/10.11_dbg/sql/sql_base.cc:6244

#3  find_field_in_table_ref (thd=thd@entry=0x1554b8000d48, table_list=table_list@entry=0x1554b8022810, name=name@entry=0x1554b801f888 "c", length=length@entry=1, item_name=<optimized out>, db_name=<optimized out>, db_name@entry=0x0, table_name=0x0, ignored_tables=0x0, ref=0x1554b801fa98, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0x1554b801f978, register_tree_change=true, actual_table=0x1554ed81a958) at /test/10.11_dbg/sql/sql_base.cc:6496

#4  0x000055de9be91ed2 in find_field_in_tables (thd=thd@entry=0x1554b8000d48, item=item@entry=0x1554b801f890, first_table=<optimized out>, last_table=0x0, ignored_tables=0x0, ref=ref@entry=0x1554b801fa98, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /test/10.11_dbg/sql/sql_base.cc:6765

#5  0x000055de9c22037a in Item_field::fix_fields (this=0x1554b801f890, thd=0x1554b8000d48, reference=0x1554b801fa98) at /test/10.11_dbg/sql/item.cc:6026

#6  0x000055de9c3cbe9a in Item::fix_fields_if_needed (ref=<optimized out>, thd=0x1554b8000d48, this=0x1554b801f890) at /test/10.11_dbg/sql/item.h:1156

#7  Item::fix_fields_if_needed_for_scalar (ref=<optimized out>, thd=0x1554b8000d48, this=0x1554b801f890) at /test/10.11_dbg/sql/item.h:1156

#8  Item_sum_hybrid_simple::fix_fields (this=0x1554b801fa20, thd=0x1554b8000d48, ref=0x1554b801fe28) at /test/10.11_dbg/sql/item_windowfunc.cc:350

#9  0x000055de9c3cbab3 in Item_window_func::fix_fields (this=0x1554b801fd58, thd=0x1554b8000d48, ref=0x1554b801fe28) at /test/10.11_dbg/sql/item_windowfunc.h:1079

#10 0x000055de9be92b46 in Item::fix_fields_if_needed (ref=0x1554b801fe28, thd=0x1554b8000d48, this=0x1554b801fd58) at /test/10.11_dbg/sql/item.h:1156

#11 Item::fix_fields_if_needed_for_scalar (ref=0x1554b801fe28, thd=0x1554b8000d48, this=0x1554b801fd58) at /test/10.11_dbg/sql/item.h:1156

#12 setup_fields (thd=0x1554b8000d48, ref_pointer_array=<optimized out>, fields=<optimized out>, column_usage=column_usage@entry=MARK_COLUMNS_READ, sum_func_list=sum_func_list@entry=0x1554b8014f10, pre_fix=0x1554b801f6c0, allow_sum_func=true) at /test/10.11_dbg/sql/sql_base.cc:7977

#13 0x000055de9bf81eff in JOIN::prepare (this=this@entry=0x1554b8014b78, tables_init=tables_init@entry=0x1554b8020c20, conds_init=conds_init@entry=0x0, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=skip_order_by@entry=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x1554b801f408, unit_arg=0x1554b801d768) at /test/10.11_dbg/sql/sql_select.cc:1459

#14 0x000055de9bf98c3e in mysql_select (thd=thd@entry=0x1554b8000d48, tables=0x1554b8020c20, fields=@0x1554b801f6a8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1554b801fe20, last = 0x1554b801fe20, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2201187781376, result=0x1554b8023820, unit=0x1554b801d768, select_lex=0x1554b801f408) at /test/10.11_dbg/sql/sql_select.cc:5055

#15 0x000055de9bf98e2d in handle_select (thd=thd@entry=0x1554b8000d48, lex=lex@entry=0x1554b801d690, result=result@entry=0x1554b8023820, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.11_dbg/sql/sql_select.cc:581

#16 0x000055de9bf0636d in execute_sqlcom_select (thd=thd@entry=0x1554b8000d48, all_tables=0x1554b8020c20) at /test/10.11_dbg/sql/sql_parse.cc:6265

#17 0x000055de9bf124a2 in mysql_execute_command (thd=0x1554b8000d48, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=true) at /test/10.11_dbg/sql/sql_parse.cc:3949

#18 0x000055de9bf3b106 in Prepared_statement::execute (this=this@entry=0x1554b8019328, expanded_query=expanded_query@entry=0x1554ed81bb80, open_cursor=open_cursor@entry=false) at /test/10.11_dbg/sql/sql_prepare.cc:5225

#19 0x000055de9bf3b49f in Prepared_statement::execute_loop (this=this@entry=0x1554b8019328, expanded_query=expanded_query@entry=0x1554ed81bb80, open_cursor=open_cursor@entry=false, packet=packet@entry=0x0, packet_end=packet_end@entry=0x0) at /test/10.11_dbg/sql/sql_prepare.cc:4648

#20 0x000055de9bf3cbc1 in Prepared_statement::execute_immediate (this=this@entry=0x1554b8019328, query=<optimized out>, query_len=<optimized out>) at /test/10.11_dbg/sql/sql_prepare.cc:5376

#21 0x000055de9bf3cd90 in mysql_sql_stmt_execute_immediate (thd=thd@entry=0x1554b8000d48) at /test/10.11_dbg/sql/sql_prepare.cc:3099

#22 0x000055de9bf124e5 in mysql_execute_command (thd=thd@entry=0x1554b8000d48, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.11_dbg/sql/sql_parse.cc:3955

#23 0x000055de9bf007f4 in mysql_parse (thd=thd@entry=0x1554b8000d48, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1554ed81c300) at /test/10.11_dbg/sql/sql_parse.cc:8000

#24 0x000055de9bf0dd2f in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x1554b8000d48, packet=packet@entry=0x1554b800adf9 "EXECUTE IMMEDIATE 'SELECT LEAD(c) OVER (ORDER BY c) FROM (SELECT 0 AS c) AS a NATURAL JOIN (SELECT 0 AS c) AS b;'", packet_length=packet_length@entry=113, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_class.h:1346

#25 0x000055de9bf1016d in do_command (thd=0x1554b8000d48, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_parse.cc:1407

#26 0x000055de9c06b013 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55de9f0d3928, put_in_cache=put_in_cache@entry=true) at /test/10.11_dbg/sql/sql_connect.cc:1416

#27 0x000055de9c06b4e2 in handle_one_connection (arg=0x55de9f0d3928) at /test/10.11_dbg/sql/sql_connect.cc:1318

#28 0x0000155506685609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#29 0x0000155506271133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

SET optimizer_switch='derived_merge=off';

CREATE TABLE t (c INT) ENGINE=InnoDB;

SET SESSION sql_mode='only_full_group_by';

UPDATE t SET c=''WHERE 1 IN (SELECT * FROM (SELECT * FROM (SELECT * FROM t AS v5 NATURAL JOIN t AS v4 NATURAL JOIN t) AS v3 NATURAL JOIN t GROUP BY c) AS v2 WHERE (0,c) IN ((0,-1),(+1,0)) ORDER BY 1+AVG(c) OVER (ORDER BY c)) ORDER BY c;

SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item_row::fix_fields|Item::fix_fields_if_needed

SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item::fix_fields_if_needed|Item_row::fix_fields

UBSAN|member access within null pointer of type 'struct SELECT_LEX'|sql/item.cc|Item_field::fix_fields|Item::fix_fields_if_needed|Item_row::fix_fields|Item::fix_fields_if_needed

CREATE TABLE t(c INT,c2 INT UNIQUE) ENGINE=InnoDB;

SELECT 1 FROM t

  WHERE c2=(SELECT 1 FROM t GROUP BY c HAVING c2=c+1) AND c2=1;

SELECT 1 FROM t AS out_t

  WHERE c2 = (SELECT 1 FROM t AS inn_t GROUP BY c HAVING c2 = c+1) and c2=1;

select 1 AS `1` from t out_t where out_t.c2 = 1 and 1 = (subquery#2)

select 1 from t inn_t group by inn_t.c having out_t.c2 = inn_t.c + 1

commit 2510e286284d77cca9c9a20901b9a537f3063234 (HEAD -> bb-10.4-mdev-29731, origin/bb-10.4-mdev-29731, 10.4)

Author: Oleg Smirnov <olernov@gmail.com>

Date:   Tue Sep 19 19:20:21 2023 +0700

    MDEV-29731 Assertion failure when HAVING in a correlated subquery references columns in the outer query

    When resolving a column from the HAVING clause, a new Item_field

    object may be created inside Item_ref::fix_fields().

    But the object is created with an empty name resolution context,

    which then leads to debug assertion failure during

    Item_field::fix_fields().

    The solution is to pass the correct name resolution context

    when creating the Item_field object.

diff --git a/sql/item.cc b/sql/item.cc

index 685fdc2b69f..efd2acb2987 100644

--- a/sql/item.cc

+++ b/sql/item.cc

@@ -8041,7 +8041,7 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)

       if (from_field != not_found_field)

       {

         Item_field* fld;

-        if (!(fld= new (thd->mem_root) Item_field(thd, from_field)))

+        if (!(fld= new (thd->mem_root) Item_field(thd, context, from_field)))

           goto error;

         thd->change_item_tree(reference, fld);

         mark_as_dependent(thd, last_checked_context->select_lex,

CREATE TABLE t (c INT PRIMARY KEY,c1 BLOB,c2 TEXT) ENGINE=InnoDB;

SET SESSION sql_mode='only_full_group_by';

EXECUTE IMMEDIATE 'SELECT SUM(c1) OVER (ORDER BY c1) FROM t NATURAL JOIN t AS a;';

SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item::fix_fields_if_needed