[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

Descending order - Click to sort in ascending order

Sergei Petrunia added a comment - 2023-10-24 19:14

Alternative commit message:

MDEV-31983 jointable materialization subquery optimization ignoring

...errors, then failing ASSERT.

UPDATE queries treat warnings as errors. In this case, an invalid

condition datetime_key_col >= '2012-01' caused warning-as-error inside

SQL_SELECT::test_quick_select.

The code that called test_quick_select() ignored this error and continued

join optimization. Then it eventually reached a thd->is_error() check

and failed to set up SJ-Materialization which failed an assert.

Fixed this by making SQL_SELECT::test_quick_select() return error in its

return value, and making any code that calls it to check for error condition

and abort the query if the error is returned.

Sergei Petrunia added a comment - 2023-10-24 19:14 Alternative commit message: MDEV-31983 jointable materialization subquery optimization ignoring ...errors, then failing ASSERT. UPDATE queries treat warnings as errors. In this case, an invalid condition datetime_key_col >= '2012-01' caused warning-as-error inside SQL_SELECT::test_quick_select. The code that called test_quick_select() ignored this error and continued join optimization. Then it eventually reached a thd->is_error() check and failed to set up SJ-Materialization which failed an assert. Fixed this by making SQL_SELECT::test_quick_select() return error in its return value, and making any code that calls it to check for error condition and abort the query if the error is returned.

Sergei Petrunia added a comment - 2023-10-24 18:44 - edited

The problem was caused by this scenario:

Take an UPDATE query, unlike SELECT it will treat warnings as errors.
IN subquery is converted into a non-mergeable semi-join (aka JTBM)
The optimizer reaches the point where it runs join optimization
for the SELECT inside the semi-join
Range optimizer produces a warning for datetime_key_col >= '2012-01'
It's an UPDATE so it is treated as error
The optimizer doesn't catch this error condition, as
SQL_SELECT::test_quick_select() doesn't have a return code for error.
The optimizer continues to run, until we finally detect an error condition
inside Item_in_subselect::setup_mat_engine().
It is caught by accident by the patch for ~~MDEV-9938~~ in Item_cond::fix_fields

The optimizer interprets this as that we're unable to run Materialization, but
we can't switch back to IN->EXISTs at this point so we fail an assertion.

Latest patch:

https://github.com/MariaDB/server/commit/39638a71adfd414d8616b013e78325c4983eac2d

Sergei Petrunia added a comment - 2023-10-24 18:44 - edited The problem was caused by this scenario: Take an UPDATE query, unlike SELECT it will treat warnings as errors. IN subquery is converted into a non-mergeable semi-join (aka JTBM) The optimizer reaches the point where it runs join optimization for the SELECT inside the semi-join Range optimizer produces a warning for datetime_key_col >= '2012-01' It's an UPDATE so it is treated as error The optimizer doesn't catch this error condition, as SQL_SELECT::test_quick_select() doesn't have a return code for error. The optimizer continues to run, until we finally detect an error condition inside Item_in_subselect::setup_mat_engine(). It is caught by accident by the patch for MDEV-9938 in Item_cond::fix_fields The optimizer interprets this as that we're unable to run Materialization, but we can't switch back to IN->EXISTs at this point so we fail an assertion. Latest patch: https://github.com/MariaDB/server/commit/39638a71adfd414d8616b013e78325c4983eac2d

Rex Johnston added a comment - 2023-10-23 18:06

Absolutely not intentional. Monty slipped that in there without me noticing.

Rex Johnston added a comment - 2023-10-23 18:06 Absolutely not intentional. Monty slipped that in there without me noticing.

Sergei Petrunia added a comment - 2023-10-23 16:37

one more question: before the patch, in make_join_statistics:

          records= get_quick_record_count(join->thd, select, s->table,

                                          &s->const_keys, join->row_limit);

          if (join->thd->is_error())

            /* get_quick_record_count generated an error */

            delete select;

            goto error;

'select' is deleted if error condition happens.

while in the new code it is not:

            if (get_quick_record_count(join->thd, select, s->table,

                                       &s->const_keys, join->row_limit, &records))

              goto error;

is this intentional? I would rather leave it as is...

Sergei Petrunia added a comment - 2023-10-23 16:37 one more question: before the patch, in make_join_statistics: records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); if (join->thd->is_error()) { /* get_quick_record_count generated an error */ delete select; goto error; } 'select' is deleted if error condition happens. while in the new code it is not: if (get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit, &records)) goto error; is this intentional? I would rather leave it as is...

Sergei Petrunia added a comment - 2023-10-23 16:15

Hi Johnston,

Generally the new patch is a step in the right direction.
I think i have found one bug in it, described below.
I've fixed it and added more error checking. Please check the last commit in bb-10.6-~~MDEV-31983~~-v3, 4941ac9192394a1489f0bf01b6dd2ee5ec2906c9 (made on top of your commit). Do you see any issues? If not please add this into your patch.

Calling convention before the patch:

SQL_SELECT::test_quick_select()

   -1 if error or impossible select (i.e. certainly no rows will be selected)

    0 if can't use quick_select

    1 if found usable ranges and quick select has been successfully created.

test_if_quick_select() - has the same return value.

test_if_use_dynamic_range_scan in the older version:

static

bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)

    return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) > 0);

After the patch:

bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)

    return (join_tab->use_quick == 2 &&

            test_if_quick_select(join_tab) == SQL_SELECT::OK);

this is not an equivalent check, we should also check if select has a quick.

Sergei Petrunia added a comment - 2023-10-23 16:15 Hi Johnston , Generally the new patch is a step in the right direction. I think i have found one bug in it, described below. I've fixed it and added more error checking. Please check the last commit in bb-10.6- MDEV-31983 -v3, 4941ac9192394a1489f0bf01b6dd2ee5ec2906c9 (made on top of your commit). Do you see any issues? If not please add this into your patch. Calling convention before the patch: SQL_SELECT::test_quick_select() -1 if error or impossible select (i.e. certainly no rows will be selected) 0 if can't use quick_select 1 if found usable ranges and quick select has been successfully created. test_if_quick_select() - has the same return value. test_if_use_dynamic_range_scan in the older version: static bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab) { return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) > 0); } After the patch: bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab) { return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) == SQL_SELECT::OK); } this is not an equivalent check, we should also check if select has a quick.

View 12 older comments

MariaDB Server

jointable materialization subquery optimization ignoring errors, then failing ASSERT.

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration