Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31983

jointable materialization subquery optimization ignoring errors, then failing ASSERT.

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32682 Assertion `range->rows >= s->found_records' failed in best_access_path

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 12 older comments
            psergei Sergei Petrunia added a comment -

            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.

            psergei Sergei Petrunia added a comment - 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.
            psergei Sergei Petrunia added a comment -

            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...

            psergei Sergei Petrunia added a comment - 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...
            Johnston Rex Johnston added a comment -

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

            Johnston Rex Johnston added a comment - Absolutely not intentional. Monty slipped that in there without me noticing.
            psergei Sergei Petrunia added a comment - - 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

            psergei Sergei Petrunia added a comment - - 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
            psergei Sergei Petrunia added a comment -

            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.

            psergei Sergei Petrunia added a comment - 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.

            People

              Johnston Rex Johnston
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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