[MDEV-31983] jointable materialization subquery optimization ignoring errors, then failing ASSERT. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.6, 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Fix Version/s: 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2, 11.3.1
Component/s: Optimizer
Labels:
- regression

Description

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT);

INSERT INTO t2 VALUES (3),(4);

CREATE TABLE t3 (c DATETIME, d INT, KEY(c));

INSERT INTO t3 VALUES ('2012-11-11',5),('2012-12-12',6);

--error ER_TRUNCATED_WRONG_VALUE

UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01');

# Cleanup

DROP TABLE t1, t2, t3;

10.6 a60462d9
mariadbd: /data/src/10.6/sql/opt_subselect.cc:6420: bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&): Assertion `subq_pred->test_set_strategy(8)' failed.
230822 17:41:58 [ERROR] mysqld got signal 6 ;

#8 0x00007ff689c45395 in __assert_fail_base (fmt=0x7ff689db9a70 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=file@entry=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=line@entry=6420, function=function@entry=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&)") at ./assert/assert.c:92
#9 0x00007ff689c53df2 in __GI___assert_fail (assertion=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=6420, function=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&)") at ./assert/assert.c:101
#10 0x000055e76f9fc69c in setup_jtbm_semi_joins (join=0x62b000088578, join_list=0x62b000082f98, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6420
#11 0x000055e76f549629 in JOIN::optimize_inner (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:2308
#12 0x000055e76f54477f in JOIN::optimize (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:1868
#13 0x000055e76f5662f6 in mysql_select (thd=0x62b00007e218, tables=0x62b0000853b8, fields=..., conds=0x62b0000880f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2200096997504, result=0x62b000088438, unit=0x62b0000825c0, select_lex=0x62b000082de0) at /data/src/10.6/sql/sql_select.cc:5077
#14 0x000055e76f7bb6ef in mysql_multi_update (thd=0x62b00007e218, table_list=0x62b0000853b8, fields=0x62b000083098, values=0x62b0000834e8, conds=0x62b0000880f8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x62b0000825c0, select_lex=0x62b000082de0, result=0x7ff682464f80) at /data/src/10.6/sql/sql_update.cc:1976
#15 0x000055e76f48c6fa in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4504
#16 0x000055e76f4a5827 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", length=86, parser_state=0x7ff6824659f0) at /data/src/10.6/sql/sql_parse.cc:8053
#17 0x000055e76f47b4e8 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=86, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
#18 0x000055e76f47821c in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
#19 0x000055e76f8e46ec in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
#20 0x000055e76f8e40ad in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318
#21 0x000055e770540b14 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#22 0x00007ff689ca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#23 0x00007ff689d285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

10.6 non-debug ca5c122a
#2 <signal handler called>
#3 0x000056307c3b6f96 in setup_table_map (tablenr=<optimized out>, table_list=0x7fe924015980, table=0x7fe924011cb8) at /data/src/10.6/sql/sql_base.h:361
#4 setup_jtbm_semi_joins (join=join@entry=0x7fe924013b78, join_list=<optimized out>, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6428
#5 0x000056307c2c8c1a in JOIN::optimize_inner (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:2308
#6 0x000056307c2c989a in JOIN::optimize (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:1868
#7 0x000056307c2c9963 in mysql_select (thd=thd@entry=0x7fe924000c68, tables=tables@entry=0x7fe924010b50, fields=..., conds=conds@entry=0x7fe924013750, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.6/sql/sql_select.cc:5077
#8 0x000056307c32f55f in mysql_multi_update (thd=thd@entry=0x7fe924000c68, table_list=0x7fe924010b50, fields=fields@entry=0x7fe924005928, values=values@entry=0x7fe924005d78, conds=<optimized out>, options=<optimized out>, handle_duplicates=<optimized out>, ignore=<optimized out>, unit=<optimized out>, select_lex=<optimized out>, result=<optimized out>) at /data/src/10.6/sql/sql_update.cc:1976
#9 0x000056307c25d54d in mysql_execute_command (thd=thd@entry=0x7fe924000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/10.6/sql/sql_parse.cc:4504
#10 0x000056307c260366 in mysql_parse (thd=0x7fe924000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:8053
#11 0x000056307c263065 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fe924000c68, packet=packet@entry=0x7fe9241004a9 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=packet_length@entry=86, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1993
#12 0x000056307c264874 in do_command (thd=0x7fe924000c68, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1409
#13 0x000056307c373a97 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56307f4aa258, put_in_cache=put_in_cache@entry=true) at /data/src/10.6/sql/sql_connect.cc:1416
#14 0x000056307c373e05 in handle_one_connection (arg=arg@entry=0x56307f4aa258) at /data/src/10.6/sql/sql_connect.cc:1318
#15 0x000056307c7071f0 in pfs_spawn_thread (arg=0x56307f45e688) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#16 0x00007fe9416a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#17 0x00007fe9417285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

The failure started happening after this commit in 10.6

commit ca5c122adcd39c34b1bd7059903668586496caf6

Author: Monty

Date:   Fri Aug 11 17:59:40 2023 +0300

    MDEV-9938 Prepared statement return wrong result (missing row)

Attachments

Issue Links

causes

MDEV-32682 Assertion `range->rows >= s->found_records' failed in best_access_path

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2023-08-22 12:29

Elena Stepanova made changes - 2023-08-22 14:44

Field	Original Value	New Value
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Description	{code:sql} {code} {noformat:title=10.6 a60462d9} mariadbd: /data/src/10.6/sql/opt_subselect.cc:6420: bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&): Assertion `subq_pred->test_set_strategy(8)' failed. 230822 15:27:07 [ERROR] mysqld got signal 6 ; #9 0x00007f830ac53df2 in __GI___assert_fail (assertion=0x55f2871b28a0 "subq_pred->test_set_strategy(8)", file=0x55f2871aec00 "/data/src/10.6/sql/opt_subselect.cc", line=6420, function=0x55f2871b2820 "bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&)") at ./assert/assert.c:101 #10 0x000055f2854c969c in setup_jtbm_semi_joins (join=0x629000370238, join_list=0x62b0000c1f98, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6420 #11 0x000055f285016629 in JOIN::optimize_inner (this=0x629000370238) at /data/src/10.6/sql/sql_select.cc:2308 #12 0x000055f28501177f in JOIN::optimize (this=0x629000370238) at /data/src/10.6/sql/sql_select.cc:1868 #13 0x000055f2850332f6 in mysql_select (thd=0x62b0000bd218, tables=0x62b0000c47b0, fields=..., conds=0x62900034e8a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2200096997504, result=0x62900035aed8, unit=0x62b0000c15c0, select_lex=0x62b0000c1de0) at /data/src/10.6/sql/sql_select.cc:5077 #14 0x000055f2852886ef in mysql_multi_update (thd=0x62b0000bd218, table_list=0x62b0000c47b0, fields=0x62b0000c2098, values=0x62b0000c24e8, conds=0x62900034e8a8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x62b0000c15c0, select_lex=0x62b0000c1de0, result=0x7f82fdb03f80) at /data/src/10.6/sql/sql_update.cc:1976 #15 0x000055f284f596fa in mysql_execute_command (thd=0x62b0000bd218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4504 #16 0x000055f284f72827 in mysql_parse (thd=0x62b0000bd218, rawbuf=0x62b0000c4238 "UPDATE (simple_db.C_InnoDB AS alias1, current_timestamp_db.A AS alias2, simple_db.view_AA_Aria AS alias3) SET alias3.pk = 26 WHERE (alias2.c11 IN (SELECT MIN(SQ1_alias1.c24) AS SQ1_cfield1 FROM (curre"..., length=554, parser_state=0x7f82fdb049f0) at /data/src/10.6/sql/sql_parse.cc:8053 #17 0x000055f284f484e8 in dispatch_command (command=COM_QUERY, thd=0x62b0000bd218, packet=0x629000280219 "UPDATE (simple_db.C_InnoDB AS alias1, current_timestamp_db.A AS alias2, simple_db.view_AA_Aria AS alias3) SET alias3.pk = 26 WHERE (alias2.c11 IN (SELECT MIN(SQ1_alias1.c24) AS SQ1_cfield1 FROM (curre"..., packet_length=554, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896 #18 0x000055f284f4521c in do_command (thd=0x62b0000bd218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409 #19 0x000055f2853b16ec in do_handle_one_connection (connect=0x6080000033b8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416 #20 0x000055f2853b10ad in handle_one_connection (arg=0x608000003338) at /data/src/10.6/sql/sql_connect.cc:1318 #21 0x000055f28600db14 in pfs_spawn_thread (arg=0x617000008218) at /data/src/10.6/storage/perfschema/pfs.cc:2201 #22 0x00007f830aca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442 #23 0x00007f830ad285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81 {noformat} The failure started happening after this commit in 10.6 {noformat} commit ca5c122adcd39c34b1bd7059903668586496caf6 Author: Monty Date: Fri Aug 11 17:59:40 2023 +0300 ~~MDEV-9938~~ Prepared statement return wrong result (missing row) {noformat}	{code:sql} CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (3),(4); CREATE TABLE t3 (c DATETIME, d INT, KEY(c)); INSERT INTO t3 VALUES ('2012-11-11',5),('2012-12-12',6); --error ER_TRUNCATED_WRONG_VALUE UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01'); # Cleanup DROP TABLE t1, t2, t3; {code} {noformat:title=10.6 a60462d9} #8 0x00007ff689c45395 in __assert_fail_base (fmt=0x7ff689db9a70 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=file@entry=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=line@entry=6420, function=function@entry=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&)") at ./assert/assert.c:92 #9 0x00007ff689c53df2 in __GI___assert_fail (assertion=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=6420, function=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN, List<TABLE_LIST>, List<Item>&)") at ./assert/assert.c:101 #10 0x000055e76f9fc69c in setup_jtbm_semi_joins (join=0x62b000088578, join_list=0x62b000082f98, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6420 #11 0x000055e76f549629 in JOIN::optimize_inner (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:2308 #12 0x000055e76f54477f in JOIN::optimize (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:1868 #13 0x000055e76f5662f6 in mysql_select (thd=0x62b00007e218, tables=0x62b0000853b8, fields=..., conds=0x62b0000880f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2200096997504, result=0x62b000088438, unit=0x62b0000825c0, select_lex=0x62b000082de0) at /data/src/10.6/sql/sql_select.cc:5077 #14 0x000055e76f7bb6ef in mysql_multi_update (thd=0x62b00007e218, table_list=0x62b0000853b8, fields=0x62b000083098, values=0x62b0000834e8, conds=0x62b0000880f8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x62b0000825c0, select_lex=0x62b000082de0, result=0x7ff682464f80) at /data/src/10.6/sql/sql_update.cc:1976 #15 0x000055e76f48c6fa in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4504 #16 0x000055e76f4a5827 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", length=86, parser_state=0x7ff6824659f0) at /data/src/10.6/sql/sql_parse.cc:8053 #17 0x000055e76f47b4e8 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=86, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896 #18 0x000055e76f47821c in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409 #19 0x000055e76f8e46ec in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416 #20 0x000055e76f8e40ad in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318 #21 0x000055e770540b14 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201 #22 0x00007ff689ca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442 #23 0x00007ff689d285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81 {noformat} {noformat:title=10.6 non-debug} #2 <signal handler called> #3 0x000056307c3b6f96 in setup_table_map (tablenr=<optimized out>, table_list=0x7fe924015980, table=0x7fe924011cb8) at /data/src/10.6/sql/sql_base.h:361 #4 setup_jtbm_semi_joins (join=join@entry=0x7fe924013b78, join_list=<optimized out>, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6428 #5 0x000056307c2c8c1a in JOIN::optimize_inner (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:2308 #6 0x000056307c2c989a in JOIN::optimize (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:1868 #7 0x000056307c2c9963 in mysql_select (thd=thd@entry=0x7fe924000c68, tables=tables@entry=0x7fe924010b50, fields=..., conds=conds@entry=0x7fe924013750, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.6/sql/sql_select.cc:5077 #8 0x000056307c32f55f in mysql_multi_update (thd=thd@entry=0x7fe924000c68, table_list=0x7fe924010b50, fields=fields@entry=0x7fe924005928, values=values@entry=0x7fe924005d78, conds=<optimized out>, options=<optimized out>, handle_duplicates=<optimized out>, ignore=<optimized out>, unit=<optimized out>, select_lex=<optimized out>, result=<optimized out>) at /data/src/10.6/sql/sql_update.cc:1976 #9 0x000056307c25d54d in mysql_execute_command (thd=thd@entry=0x7fe924000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/10.6/sql/sql_parse.cc:4504 #10 0x000056307c260366 in mysql_parse (thd=0x7fe924000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:8053 #11 0x000056307c263065 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fe924000c68, packet=packet@entry=0x7fe9241004a9 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=packet_length@entry=86, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1993 #12 0x000056307c264874 in do_command (thd=0x7fe924000c68, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1409 #13 0x000056307c373a97 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56307f4aa258, put_in_cache=put_in_cache@entry=true) at /data/src/10.6/sql/sql_connect.cc:1416 #14 0x000056307c373e05 in handle_one_connection (arg=arg@entry=0x56307f4aa258) at /data/src/10.6/sql/sql_connect.cc:1318 #15 0x000056307c7071f0 in pfs_spawn_thread (arg=0x56307f45e688) at /data/src/10.6/storage/perfschema/pfs.cc:2201 #16 0x00007fe9416a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442 #17 0x00007fe9417285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81 {noformat} The failure started happening after this commit in 10.6 {noformat} commit ca5c122adcd39c34b1bd7059903668586496caf6 Author: Monty Date: Fri Aug 11 17:59:40 2023 +0300 ~~MDEV-9938~~ Prepared statement return wrong result (missing row) {noformat}
Summary	Assertion `subq_pred->test_set_strategy(8)' failed in setup_jtbm_semi_joins	Server crash or assertion failure in setup_jtbm_semi_joins upon update with ER_TRUNCATED_WRONG_VALUE

Elena Stepanova made changes - 2023-08-22 14:46

Description

{code:sql}
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (3),(4);

CREATE TABLE t3 (c DATETIME, d INT, KEY(c));
INSERT INTO t3 VALUES ('2012-11-11',5),('2012-12-12',6);

--error ER_TRUNCATED_WRONG_VALUE
UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01');

# Cleanup
DROP TABLE t1, t2, t3;
{code}

{noformat:title=10.6 a60462d9}
#8 0x00007ff689c45395 in __assert_fail_base (fmt=0x7ff689db9a70 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=file@entry=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=line@entry=6420, function=function@entry=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&)") at ./assert/assert.c:92
#9 0x00007ff689c53df2 in __GI___assert_fail (assertion=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=6420, function=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&)") at ./assert/assert.c:101
#10 0x000055e76f9fc69c in setup_jtbm_semi_joins (join=0x62b000088578, join_list=0x62b000082f98, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6420
#11 0x000055e76f549629 in JOIN::optimize_inner (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:2308
#12 0x000055e76f54477f in JOIN::optimize (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:1868
#13 0x000055e76f5662f6 in mysql_select (thd=0x62b00007e218, tables=0x62b0000853b8, fields=..., conds=0x62b0000880f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2200096997504, result=0x62b000088438, unit=0x62b0000825c0, select_lex=0x62b000082de0) at /data/src/10.6/sql/sql_select.cc:5077
#14 0x000055e76f7bb6ef in mysql_multi_update (thd=0x62b00007e218, table_list=0x62b0000853b8, fields=0x62b000083098, values=0x62b0000834e8, conds=0x62b0000880f8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x62b0000825c0, select_lex=0x62b000082de0, result=0x7ff682464f80) at /data/src/10.6/sql/sql_update.cc:1976
#15 0x000055e76f48c6fa in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4504
#16 0x000055e76f4a5827 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", length=86, parser_state=0x7ff6824659f0) at /data/src/10.6/sql/sql_parse.cc:8053
#17 0x000055e76f47b4e8 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=86, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
#18 0x000055e76f47821c in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
#19 0x000055e76f8e46ec in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
#20 0x000055e76f8e40ad in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318
#21 0x000055e770540b14 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#22 0x00007ff689ca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#23 0x00007ff689d285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
{noformat}

{noformat:title=10.6 non-debug}
#2 <signal handler called>
#3 0x000056307c3b6f96 in setup_table_map (tablenr=<optimized out>, table_list=0x7fe924015980, table=0x7fe924011cb8) at /data/src/10.6/sql/sql_base.h:361
#4 setup_jtbm_semi_joins (join=join@entry=0x7fe924013b78, join_list=<optimized out>, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6428
#5 0x000056307c2c8c1a in JOIN::optimize_inner (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:2308
#6 0x000056307c2c989a in JOIN::optimize (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:1868
#7 0x000056307c2c9963 in mysql_select (thd=thd@entry=0x7fe924000c68, tables=tables@entry=0x7fe924010b50, fields=..., conds=conds@entry=0x7fe924013750, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.6/sql/sql_select.cc:5077
#8 0x000056307c32f55f in mysql_multi_update (thd=thd@entry=0x7fe924000c68, table_list=0x7fe924010b50, fields=fields@entry=0x7fe924005928, values=values@entry=0x7fe924005d78, conds=<optimized out>, options=<optimized out>, handle_duplicates=<optimized out>, ignore=<optimized out>, unit=<optimized out>, select_lex=<optimized out>, result=<optimized out>) at /data/src/10.6/sql/sql_update.cc:1976
#9 0x000056307c25d54d in mysql_execute_command (thd=thd@entry=0x7fe924000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/10.6/sql/sql_parse.cc:4504
#10 0x000056307c260366 in mysql_parse (thd=0x7fe924000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:8053
#11 0x000056307c263065 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fe924000c68, packet=packet@entry=0x7fe9241004a9 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=packet_length@entry=86, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1993
#12 0x000056307c264874 in do_command (thd=0x7fe924000c68, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1409
#13 0x000056307c373a97 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56307f4aa258, put_in_cache=put_in_cache@entry=true) at /data/src/10.6/sql/sql_connect.cc:1416
#14 0x000056307c373e05 in handle_one_connection (arg=arg@entry=0x56307f4aa258) at /data/src/10.6/sql/sql_connect.cc:1318
#15 0x000056307c7071f0 in pfs_spawn_thread (arg=0x56307f45e688) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#16 0x00007fe9416a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#17 0x00007fe9417285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
{noformat}

The failure started happening after this commit in 10.6
{noformat}
commit ca5c122adcd39c34b1bd7059903668586496caf6
Author: Monty
Date: Fri Aug 11 17:59:40 2023 +0300

~~MDEV-9938~~ Prepared statement return wrong result (missing row)
{noformat}

{code:sql}
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (3),(4);

CREATE TABLE t3 (c DATETIME, d INT, KEY(c));
INSERT INTO t3 VALUES ('2012-11-11',5),('2012-12-12',6);

--error ER_TRUNCATED_WRONG_VALUE
UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01');

# Cleanup
DROP TABLE t1, t2, t3;
{code}

{noformat:title=10.6 a60462d9}
mariadbd: /data/src/10.6/sql/opt_subselect.cc:6420: bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&): Assertion `subq_pred->test_set_strategy(8)' failed.
230822 17:41:58 [ERROR] mysqld got signal 6 ;

#8 0x00007ff689c45395 in __assert_fail_base (fmt=0x7ff689db9a70 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=file@entry=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=line@entry=6420, function=function@entry=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&)") at ./assert/assert.c:92
#9 0x00007ff689c53df2 in __GI___assert_fail (assertion=0x55e7716e58a0 "subq_pred->test_set_strategy(8)", file=0x55e7716e1c00 "/data/src/10.6/sql/opt_subselect.cc", line=6420, function=0x55e7716e5820 "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&)") at ./assert/assert.c:101
#10 0x000055e76f9fc69c in setup_jtbm_semi_joins (join=0x62b000088578, join_list=0x62b000082f98, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6420
#11 0x000055e76f549629 in JOIN::optimize_inner (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:2308
#12 0x000055e76f54477f in JOIN::optimize (this=0x62b000088578) at /data/src/10.6/sql/sql_select.cc:1868
#13 0x000055e76f5662f6 in mysql_select (thd=0x62b00007e218, tables=0x62b0000853b8, fields=..., conds=0x62b0000880f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2200096997504, result=0x62b000088438, unit=0x62b0000825c0, select_lex=0x62b000082de0) at /data/src/10.6/sql/sql_select.cc:5077
#14 0x000055e76f7bb6ef in mysql_multi_update (thd=0x62b00007e218, table_list=0x62b0000853b8, fields=0x62b000083098, values=0x62b0000834e8, conds=0x62b0000880f8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x62b0000825c0, select_lex=0x62b000082de0, result=0x7ff682464f80) at /data/src/10.6/sql/sql_update.cc:1976
#15 0x000055e76f48c6fa in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4504
#16 0x000055e76f4a5827 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", length=86, parser_state=0x7ff6824659f0) at /data/src/10.6/sql/sql_parse.cc:8053
#17 0x000055e76f47b4e8 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=86, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
#18 0x000055e76f47821c in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
#19 0x000055e76f8e46ec in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
#20 0x000055e76f8e40ad in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318
#21 0x000055e770540b14 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#22 0x00007ff689ca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#23 0x00007ff689d285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
{noformat}

{noformat:title=10.6 non-debug ca5c122a}
#2 <signal handler called>
#3 0x000056307c3b6f96 in setup_table_map (tablenr=<optimized out>, table_list=0x7fe924015980, table=0x7fe924011cb8) at /data/src/10.6/sql/sql_base.h:361
#4 setup_jtbm_semi_joins (join=join@entry=0x7fe924013b78, join_list=<optimized out>, eq_list=...) at /data/src/10.6/sql/opt_subselect.cc:6428
#5 0x000056307c2c8c1a in JOIN::optimize_inner (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:2308
#6 0x000056307c2c989a in JOIN::optimize (this=this@entry=0x7fe924013b78) at /data/src/10.6/sql/sql_select.cc:1868
#7 0x000056307c2c9963 in mysql_select (thd=thd@entry=0x7fe924000c68, tables=tables@entry=0x7fe924010b50, fields=..., conds=conds@entry=0x7fe924013750, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.6/sql/sql_select.cc:5077
#8 0x000056307c32f55f in mysql_multi_update (thd=thd@entry=0x7fe924000c68, table_list=0x7fe924010b50, fields=fields@entry=0x7fe924005928, values=values@entry=0x7fe924005d78, conds=<optimized out>, options=<optimized out>, handle_duplicates=<optimized out>, ignore=<optimized out>, unit=<optimized out>, select_lex=<optimized out>, result=<optimized out>) at /data/src/10.6/sql/sql_update.cc:1976
#9 0x000056307c25d54d in mysql_execute_command (thd=thd@entry=0x7fe924000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/10.6/sql/sql_parse.cc:4504
#10 0x000056307c260366 in mysql_parse (thd=0x7fe924000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:8053
#11 0x000056307c263065 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fe924000c68, packet=packet@entry=0x7fe9241004a9 "UPDATE t1, t2 SET t1.a = 26 WHERE t2.b IN (SELECT MIN(d) FROM t3 WHERE c >= '2012-01')", packet_length=packet_length@entry=86, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1993
#12 0x000056307c264874 in do_command (thd=0x7fe924000c68, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1409
#13 0x000056307c373a97 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56307f4aa258, put_in_cache=put_in_cache@entry=true) at /data/src/10.6/sql/sql_connect.cc:1416
#14 0x000056307c373e05 in handle_one_connection (arg=arg@entry=0x56307f4aa258) at /data/src/10.6/sql/sql_connect.cc:1318
#15 0x000056307c7071f0 in pfs_spawn_thread (arg=0x56307f45e688) at /data/src/10.6/storage/perfschema/pfs.cc:2201
#16 0x00007fe9416a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#17 0x00007fe9417285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
{noformat}

The failure started happening after this commit in 10.6
{noformat}
commit ca5c122adcd39c34b1bd7059903668586496caf6
Author: Monty
Date: Fri Aug 11 17:59:40 2023 +0300

~~MDEV-9938~~ Prepared statement return wrong result (missing row)
{noformat}

Rex Johnston made changes - 2023-09-10 23:41

Assignee

Michael Widenius [ monty ]

Rex Johnston [ JIRAUSER52533 ]

Rex Johnston made changes - 2023-09-10 23:43

Summary

Server crash or assertion failure in setup_jtbm_semi_joins upon update with ER_TRUNCATED_WRONG_VALUE

jointable materialization subquery optimization ignoring errors, then failing ASSERT.

Rex Johnston made changes - 2023-09-10 23:44

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-10-17 19:44

Assignee

Rex Johnston [ JIRAUSER52533 ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2023-10-20 12:23

Assignee

Sergei Petrunia [ psergey ]

Rex Johnston [ JIRAUSER52533 ]

Sergei Petrunia made changes - 2023-10-25 17:06

Fix Version/s		10.6.16 [ 29014 ]
Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s		11.2.2 [ 29035 ]
Fix Version/s		11.3.1 [ 29416 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.1 [ 28549 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Petrunia made changes - 2023-11-07 09:03

Link

This issue causes ~~MDEV-32682~~ [ ~~MDEV-32682~~ ]

People

Assignee:: Rex Johnston

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-08-22 12:29

Updated:: 2023-11-08 11:08

Resolved:: 2023-10-25 17:06

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration