[MDEV-29731] Crash when HAVING in a correlated subquery references columns in the outer query - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 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), 11.3(EOL)
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3
Component/s: Optimizer
Labels:

Description

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;

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)
mysqld: /test/10.11_dbg/sql/item.cc:6009: virtual bool Item_field::fix_fields(THD, Item*): Assertion `field' failed.

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)
Core was generated by `/test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x151f28164700 (LWP 3246815))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x0000151f41ef2859 in __GI_abort () at abort.c:79
#2 0x0000151f41ef2729 in __assert_fail_base (fmt=0x151f42088588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55e95a8ce218 "field", file=0x55e95aa78780 "/test/10.11_dbg/sql/item.cc", line=6009, function=<optimized out>) at assert.c:92
#3 0x0000151f41f03fd6 in __GI___assert_fail (assertion=assertion@entry=0x55e95a8ce218 "field", file=file@entry=0x55e95aa78780 "/test/10.11_dbg/sql/item.cc", line=line@entry=6009, function=function@entry=0x55e95aa7b218 "virtual bool Item_field::fix_fields(THD, Item*)") at assert.c:101
#4 0x000055e95a00f1c4 in Item_field::fix_fields (this=0x151ec0069b10, thd=0x151ec0000d48, reference=0x151ec00151c8) at /test/10.11_dbg/sql/item.cc:6009
#5 0x000055e95a063a80 in Item::fix_fields_if_needed (ref=0x151ec00151c8, thd=0x151ec0000d48, this=<optimized out>) at /test/10.11_dbg/sql/item.h:1144
#6 Item_func::fix_fields (this=0x151ec0015150, thd=0x151ec0000d48, ref=<optimized out>) at /test/10.11_dbg/sql/item_func.cc:350
#7 0x000055e959cd1b6c in st_select_lex::pushdown_from_having_into_where (this=0x151ec0014058, thd=0x151ec0000d48, having=0x0) at /test/10.11_dbg/sql/sql_lex.cc:11260
#8 0x000055e959d7a7dc in JOIN::optimize_inner (this=this@entry=0x151ec0069528) at /test/10.11_dbg/sql/sql_select.cc:2281
#9 0x000055e959d7b464 in JOIN::optimize (this=this@entry=0x151ec0069528) at /test/10.11_dbg/sql/sql_select.cc:1864
#10 0x000055e959cbdb1a in st_select_lex::optimize_unflattened_subqueries (this=0x151ec00132f8, const_only=const_only@entry=false) at /test/10.11_dbg/sql/sql_lex.cc:4918
#11 0x000055e959eb1813 in JOIN::optimize_unflattened_subqueries (this=this@entry=0x151ec0068f40) at /test/10.11_dbg/sql/opt_subselect.cc:5655
#12 0x000055e959d7720a in JOIN::optimize_stage2 (this=this@entry=0x151ec0068f40) at /test/10.11_dbg/sql/sql_select.cc:3348
#13 0x000055e959d7b09e in JOIN::optimize_inner (this=this@entry=0x151ec0068f40) at /test/10.11_dbg/sql/sql_select.cc:2551
#14 0x000055e959d7b464 in JOIN::optimize (this=this@entry=0x151ec0068f40) at /test/10.11_dbg/sql/sql_select.cc:1864
#15 0x000055e959d7b557 in mysql_select (thd=thd@entry=0x151ec0000d48, tables=0x151ec0013838, fields=@0x151ec0013598: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x151ec00137f0, last = 0x151ec00137f0, elements = 1}, <No data fields>}, conds=0x151ec0016340, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x151ec0016db0, unit=0x151ec0004f80, select_lex=0x151ec00132f8) at /test/10.11_dbg/sql/sql_select.cc:5057
#16 0x000055e959d7bda0 in handle_select (thd=thd@entry=0x151ec0000d48, lex=lex@entry=0x151ec0004ea8, result=result@entry=0x151ec0016db0, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.11_dbg/sql/sql_select.cc:582
#17 0x000055e959ce5d94 in execute_sqlcom_select (thd=thd@entry=0x151ec0000d48, all_tables=0x151ec0013838) at /test/10.11_dbg/sql/sql_parse.cc:6261
#18 0x000055e959cf2109 in mysql_execute_command (thd=thd@entry=0x151ec0000d48, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.11_dbg/sql/sql_parse.cc:3945
#19 0x000055e959ce003c in mysql_parse (thd=thd@entry=0x151ec0000d48, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x151f28163330) at /test/10.11_dbg/sql/sql_parse.cc:8037
#20 0x000055e959ced66d in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x151ec0000d48, packet=packet@entry=0x151ec000aed9 "SELECT 1 FROM t WHERE c2=(SELECT 1 FROM t GROUP BY c HAVING c2=c+1) AND c2=1", packet_length=packet_length@entry=76, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_class.h:1345
#21 0x000055e959cefd97 in do_command (thd=0x151ec0000d48, blocking=blocking@entry=true) at /test/10.11_dbg/sql/sql_parse.cc:1407
#22 0x000055e959e53fb9 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55e95c330738, put_in_cache=put_in_cache@entry=true) at /test/10.11_dbg/sql/sql_connect.cc:1416
#23 0x000055e959e544c3 in handle_one_connection (arg=0x55e95c330738) at /test/10.11_dbg/sql/sql_connect.cc:1318
#24 0x0000151f42403609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x0000151f41fef133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.5.18 23a8654cdbd84336fd2004bdd2b0cb4a0ecd3d4a (Debug)
mysqld: /test/10.5_dbg/sql/item.cc:5954: virtual bool Item_field::fix_fields(THD, Item*): Assertion `context' failed.

10.5.18 23a8654cdbd84336fd2004bdd2b0cb4a0ecd3d4a (Debug)
Core was generated by `/test/MD190922-mariadb-10.5.18-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x14ee32dfe700 (LWP 3247036))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014ee4b7bd859 in __GI_abort () at abort.c:79
#2 0x000014ee4b7bd729 in __assert_fail_base (fmt=0x14ee4b953588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x559dfac0d8dd "context", file=0x559dfada1f84 "/test/10.5_dbg/sql/item.cc", line=5954, function=<optimized out>) at assert.c:92
#3 0x000014ee4b7cefd6 in __GI___assert_fail (assertion=assertion@entry=0x559dfac0d8dd "context", file=file@entry=0x559dfada1f84 "/test/10.5_dbg/sql/item.cc", line=line@entry=5954, function=function@entry=0x559dfada4a20 "virtual bool Item_field::fix_fields(THD, Item*)") at assert.c:101
#4 0x0000559dfa2d5191 in Item_field::fix_fields (this=0x14ee000179c0, thd=0x14ee00000d48, reference=0x14ee00014e18) at /test/10.5_dbg/sql/item.cc:5954
#5 0x0000559dfa32ce37 in Item::fix_fields_if_needed (ref=0x14ee00014e18, thd=0x14ee00000d48, this=0x14ee000179c0) at /test/10.5_dbg/sql/item.h:988
#6 Item_func::fix_fields (this=0x14ee00014d80, thd=0x14ee00000d48, ref=<optimized out>) at /test/10.5_dbg/sql/item_func.cc:355
#7 0x0000559df9fe0c27 in st_select_lex::pushdown_from_having_into_where (this=0x14ee00013bb0, thd=0x14ee00000d48, having=0x0) at /test/10.5_dbg/sql/sql_lex.cc:11137
#8 0x0000559dfa075b87 in JOIN::optimize_inner (this=this@entry=0x14ee00017288) at /test/10.5_dbg/sql/sql_select.cc:2084
#9 0x0000559dfa076822 in JOIN::optimize (this=this@entry=0x14ee00017288) at /test/10.5_dbg/sql/sql_select.cc:1686
#10 0x0000559df9fcc8e3 in st_select_lex::optimize_unflattened_subqueries (this=0x14ee00012e08, const_only=const_only@entry=false) at /test/10.5_dbg/sql/sql_lex.cc:4879
#11 0x0000559dfa199099 in JOIN::optimize_unflattened_subqueries (this=this@entry=0x14ee00016b38) at /test/10.5_dbg/sql/opt_subselect.cc:5643
#12 0x0000559dfa0726ec in JOIN::optimize_stage2 (this=this@entry=0x14ee00016b38) at /test/10.5_dbg/sql/sql_select.cc:3126
#13 0x0000559dfa076443 in JOIN::optimize_inner (this=this@entry=0x14ee00016b38) at /test/10.5_dbg/sql/sql_select.cc:2354
#14 0x0000559dfa076822 in JOIN::optimize (this=this@entry=0x14ee00016b38) at /test/10.5_dbg/sql/sql_select.cc:1686
#15 0x0000559dfa0768fd in mysql_select (thd=thd@entry=0x14ee00000d48, tables=0x14ee00013378, fields=@0x14ee00012f58: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14ee00013330, last = 0x14ee00013330, elements = 1}, <No data fields>}, conds=0x14ee00016048, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x14ee00016b10, unit=0x14ee00004cf0, select_lex=0x14ee00012e08) at /test/10.5_dbg/sql/sql_select.cc:4803
#16 0x0000559dfa077440 in handle_select (thd=thd@entry=0x14ee00000d48, lex=lex@entry=0x14ee00004c28, result=result@entry=0x14ee00016b10, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.5_dbg/sql/sql_select.cc:444
#17 0x0000559df9ff5377 in execute_sqlcom_select (thd=thd@entry=0x14ee00000d48, all_tables=0x14ee00013378) at /test/10.5_dbg/sql/sql_parse.cc:6315
#18 0x0000559dfa001e6b in mysql_execute_command (thd=thd@entry=0x14ee00000d48) at /test/10.5_dbg/sql/sql_parse.cc:4006
#19 0x0000559df9fef17d in mysql_parse (thd=thd@entry=0x14ee00000d48, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14ee32dfd340, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:8101
#20 0x0000559df9ffd1b4 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14ee00000d48, packet=packet@entry=0x14ee0000a979 "SELECT 1 FROM t WHERE c2=(SELECT 1 FROM t GROUP BY c HAVING c2=c+1) AND c2=1", packet_length=packet_length@entry=76, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_class.h:1296
#21 0x0000559df9fffce6 in do_command (thd=0x14ee00000d48) at /test/10.5_dbg/sql/sql_parse.cc:1375
#22 0x0000559dfa140948 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x559dfdd19198, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1416
#23 0x0000559dfa140e51 in handle_one_connection (arg=0x559dfdd19198) at /test/10.5_dbg/sql/sql_connect.cc:1318
#24 0x000014ee4bcce609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x000014ee4b8ba133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.8.5 593fdee3973ce4ab4890a6fb9f740b3a28a44edc (Optimized)
Core was generated by `/test/MD190922-mariadb-10.8.5-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x0000556ed602a8df in Item_field::fix_fields (this=0x152414048df0, thd=
0x152414000c58, reference=0x152414012678)
at /test/10.8_opt/sql/item.cc:6018
[Current thread is 1 (Thread 0x152478098700 (LWP 3246781))]
(gdb) bt
#0 0x0000556ed602a8df in Item_field::fix_fields (this=0x152414048df0, thd=0x152414000c58, reference=0x152414012678) at /test/10.8_opt/sql/item.cc:6018
#1 0x0000556ed606dd8d in Item::fix_fields_if_needed (ref=0x152414012678, thd=0x152414000c58, this=0x152414048df0) at /test/10.8_opt/sql/item.h:1142
#2 Item::fix_fields_if_needed (ref=0x152414012678, thd=0x152414000c58, this=0x152414048df0) at /test/10.8_opt/sql/item.h:1142
#3 Item_func::fix_fields (ref=<optimized out>, thd=0x152414000c58, this=0x152414012600) at /test/10.8_opt/sql/item_func.cc:350
#4 Item_func::fix_fields (this=0x152414012600, thd=0x152414000c58, ref=<optimized out>) at /test/10.8_opt/sql/item_func.cc:317
#5 0x0000556ed5dad802 in st_select_lex::pushdown_from_having_into_where (this=0x152414011510, thd=0x152414000c58, having=0x0) at /test/10.8_opt/sql/sql_lex.cc:11270
#6 0x0000556ed5e37539 in JOIN::optimize_inner (this=0x152414048840) at /test/10.8_opt/sql/sql_select.cc:2248
#7 0x0000556ed5e3a6a3 in JOIN::optimize (this=this@entry=0x152414048840) at /test/10.8_opt/sql/sql_select.cc:1834
#8 0x0000556ed5d9dd34 in st_select_lex::optimize_unflattened_subqueries (this=0x1524140107b8, const_only=const_only@entry=false) at /test/10.8_opt/sql/sql_lex.cc:4944
#9 0x0000556ed5f20872 in JOIN::optimize_unflattened_subqueries (this=this@entry=0x152414048290) at /test/10.8_opt/sql/opt_subselect.cc:5656
#10 0x0000556ed5e33c7e in JOIN::optimize_stage2 (this=<optimized out>) at /test/10.8_opt/sql/sql_select.cc:3315
#11 0x0000556ed5e382f8 in JOIN::optimize_inner (this=0x152414048290) at /test/10.8_opt/sql/sql_select.cc:2518
#12 0x0000556ed5e3a6a3 in JOIN::optimize (this=this@entry=0x152414048290) at /test/10.8_opt/sql/sql_select.cc:1834
#13 0x0000556ed5e3a78e in mysql_select (thd=0x152414000c58, tables=0x152414010cf8, fields=@0x152414010a58: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x152414010cb0, last = 0x152414010cb0, elements = 1}, <No data fields>}, conds=0x1524140137e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x152414014250, unit=0x152414004ca8, select_lex=0x1524140107b8) at /test/10.8_opt/sql/sql_select.cc:5024
#14 0x0000556ed5e3af27 in handle_select (thd=thd@entry=0x152414000c58, lex=lex@entry=0x152414004bd0, result=result@entry=0x152414014250, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.8_opt/sql/sql_select.cc:552
#15 0x0000556ed5dbe241 in execute_sqlcom_select (thd=0x152414000c58, all_tables=0x152414010cf8) at /test/10.8_opt/sql/sql_parse.cc:6253
#16 0x0000556ed5dcbd64 in mysql_execute_command (thd=0x152414000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.8_opt/sql/sql_parse.cc:3944
#17 0x0000556ed5db9315 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x152414000c58) at /test/10.8_opt/sql/sql_parse.cc:8028
#18 mysql_parse (thd=0x152414000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.8_opt/sql/sql_parse.cc:7950
#19 0x0000556ed5dc4f4a in dispatch_command (command=COM_QUERY, thd=0x152414000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.8_opt/sql/sql_class.h:1368
#20 0x0000556ed5dc6e92 in do_command (thd=0x152414000c58, blocking=blocking@entry=true) at /test/10.8_opt/sql/sql_parse.cc:1407
#21 0x0000556ed5edca3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x556ed83ea688, put_in_cache=put_in_cache@entry=true) at /test/10.8_opt/sql/sql_connect.cc:1416
#22 0x0000556ed5edcd1d in handle_one_connection (arg=0x556ed83ea688) at /test/10.8_opt/sql/sql_connect.cc:1318
#23 0x000015248f91a609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#24 0x000015248f506133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.27 (dbg), 10.4.27 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.37 (dbg), 10.3.37 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

10.3 Gives no errors and 'empty set' on second query.

Attachments

Issue Links

duplicates

MDEV-32399 pushdown_from_having_into_where: SEGV at /mariadb-11.3.0/sql/item.cc:6013

Closed

is duplicated by

MDEV-32610 Server crash on 10.11.5

Closed

relates to

MDEV-34155 Prepare statement with select from view fails on second execution when view definition has HAVING in a correlated subquery references columns in the outer query

Open

MDEV-28506 SIGSEGV's in find_field_in_table[s][_ref], Item_field::fix_fields, create_view_field and MemcmpInterceptorCommon | Assertions `(*select_ref)->fixed' or '->is_fixed' and `table_list->table' failed

Stalled

Activity

Ascending order - Click to sort in descending order

View 7 older comments

Oleg Smirnov added a comment - 2023-09-15 12:19

I've noticed a strange thing regarding the name resolution during debugging the first test case:

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;

I added table names aliases here to find out what column names c and c2 refer to (inner or outer table):

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;

During the optimization phase dbug_print(select_lex) displays

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

(subquery#2) expands to

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

out_t.c2 in the subquery looks strange, I would expect the field c2 to be resolved to inn_t.c2.

Oleg Smirnov added a comment - 2023-09-15 12:19 I've noticed a strange thing regarding the name resolution during debugging the first test case: 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; I added table names aliases here to find out what column names c and c2 refer to (inner or outer table): 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; During the optimization phase dbug_print(select_lex) displays select 1 AS `1` from t out_t where out_t.c2 = 1 and 1 = (subquery#2) (subquery#2) expands to select 1 from t inn_t group by inn_t.c having out_t.c2 = inn_t.c + 1 out_t.c2 in the subquery looks strange, I would expect the field c2 to be resolved to inn_t.c2.

Oleg Smirnov added a comment - 2023-09-19 13:27

Answer to the previous comment by igor: "c2 cannot be resolved against column of the inner table because it's not included into the group by list. (The Standard probably requires to return an error in this case)."

The Standard, indeed, requires to return an error, but the ability to reference columns of the outer query is the extension of MySQL/MariaDB syntax, so the example above is valid.
For information: MySQL once tried to prohibit such references but then permitted them again due to users complaints (https://bugs.mysql.com/bug.php?id=79549).

The commit fixing this assertion failure:

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.

The fix (excluding added test cases) looks as simple as:

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,

This is in line with the MySQL's code.

Branch bb-10.4-mdev-29731.

Oleg Smirnov added a comment - 2023-09-19 13:27 Answer to the previous comment by igor : "c2 cannot be resolved against column of the inner table because it's not included into the group by list. (The Standard probably requires to return an error in this case)." The Standard, indeed, requires to return an error, but the ability to reference columns of the outer query is the extension of MySQL/MariaDB syntax, so the example above is valid. For information: MySQL once tried to prohibit such references but then permitted them again due to users complaints ( https://bugs.mysql.com/bug.php?id=79549 ). The commit fixing this assertion failure: 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. The fix (excluding added test cases) looks as simple as: 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, This is in line with the MySQL's code. Branch bb-10.4-mdev-29731.

Oleksandr Byelkin added a comment - 2023-09-19 14:07

OK to push

Oleksandr Byelkin added a comment - 2023-09-19 14:07 OK to push

Oleg Smirnov added a comment - 2023-09-20 04:49

Pushed to 10.4.

Oleg Smirnov added a comment - 2023-09-20 04:49 Pushed to 10.4.

Roel Van de Paar added a comment - 2023-09-20 05:23

This testcase

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

Leads to an additional stack:

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

However, oleg.smirnov kindly confirmed it is fixed by the current patch also.

Roel Van de Paar added a comment - 2023-09-20 05:23 This testcase 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;' ; Leads to an additional stack: SIGSEGV|Item_field::fix_fields|Item::fix_fields_if_needed|Item_func::fix_fields|Item::fix_fields_if_needed However, oleg.smirnov kindly confirmed it is fixed by the current patch also.

People

Assignee:: Oleg Smirnov

Reporter:: Roel Van de Paar

Votes:: 1 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2022-10-06 21:09

Updated:: 2024-07-31 23:52

Resolved:: 2023-09-20 04:49

Git Integration

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

MariaDB Server