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

DELETE with ORDER BY and semijoin optimization causing crash

Details

    Description

      The latest version of MariDB Server: Git commit hash: (8d9bc61d0bf783fa792e6c3be37b0eceecbeec89) crashes when executing the following query:

      drop database if exists test1;
      create database test1;
      use test1;
      create table v0(c1 INT);
      CREATE TABLE v2 ( INDEX ( v0 ( 100 ) ) ) ;
      CREATE TEMPORARY TABLE v3 ( c4 TEXT , c5 INT UNIQUE KEY DEFAULT 100 ) ;
      INSERT HIGH_PRIORITY v0 SET v0 . c1 = c1 MOD CASE c1 NOT LIKE c1 WHEN c1 NOT LIKE c1 THEN c1 NOT LIKE c1 ELSE NOT c1 NOT LIKE c1 END NOT BETWEEN c1 ^ c1 AND EXISTS ( SELECT * FROM DUAL ) DIV c1 NOT LIKE MINUTE ( c1 MOD c1 NOT LIKE SECOND ( c1 NOT LIKE c1 ) ) IS NOT NULL IS TRUE ON DUPLICATE KEY UPDATE c1 = DEFAULT ;
      INSERT HIGH_PRIORITY IGNORE v3 SET c4 = NOT NOT c5 MOD c5 MOD c4 MOD DAY ( NOT c4 ) REGEXP EXISTS ( SELECT * WHERE c5 MOD c5 NOT BETWEEN c4 MOD c4 AND c5 REGEXP c4 ) IS NOT NULL IS NULL IS NOT FALSE ON DUPLICATE KEY UPDATE c4 = DEFAULT ;
      DELETE FROM v3 WHERE c4 IN ( SELECT * FROM v0 a6 ) ORDER BY EXISTS ( SELECT * ) REGEXP c4 OR c5 REGEXP c4 ;

      Here is the crashing stack trace from version 8d9bc61d0b:

      #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
      #1 0x0000ffffa4f92aac in __GI_abort () at abort.c:79
      #2 0x0000aaaad38fcde0 in ut_dbg_assertion_failed (expr=expr@entry=0xaaaad56b83e0 "node->pcur->rel_pos == BTR_PCUR_ON",
      file=file@entry=0xaaaad56b7460 "/home/mysql/mariadb/storage/innobase/row/row0mysql.cc", line=line@entry=1652) at /home/mysql/mariadb/storage/innobase/ut/ut0dbg.cc:60
      #3 0x0000aaaad4d26ff8 in row_update_for_mysql (prebuilt=0xffff9d306108) at /home/mysql/mariadb/storage/innobase/row/row0mysql.cc:1652
      #4 0x0000aaaad4b4b39c in ha_innobase::delete_row (this=0xffff9ef582b8, record=<optimized out>) at /home/mysql/mariadb/storage/innobase/handler/ha_innodb.cc:8729
      #5 0x0000aaaad4350fd4 in handler::ha_delete_row (this=0xffff9ef582b8, buf=0xffffa0b6f4c8 "\374\001") at /home/mysql/mariadb/sql/handler.cc:7968
      #6 0x0000aaaad3bbbe80 in TABLE::delete_row (this=<optimized out>) at /home/mysql/mariadb/sql/sql_delete.cc:289
      #7 TABLE::delete_row (this=0xffffa0b6f998) at /home/mysql/mariadb/sql/sql_delete.cc:286
      #8 multi_delete::send_data (this=0xffff9e796560, values=...) at /home/mysql/mariadb/sql/sql_delete.cc:1183
      #9 0x0000aaaad3dae6b4 in select_result_sink::send_data_with_check (u=<optimized out>, sent=<optimized out>, items=..., this=<optimized out>)
      at /home/mysql/mariadb/sql/sql_class.h:5840
      #10 select_result_sink::send_data_with_check (sent=<optimized out>, u=<optimized out>, items=..., this=<optimized out>) at /home/mysql/mariadb/sql/sql_class.h:5830
      #11 end_send (join=0xffff9e7965f8, join_tab=0xffff9e79da78, end_of_records=<optimized out>) at /home/mysql/mariadb/sql/sql_select.cc:24710
      #12 0x0000aaaad3d2f3ac in evaluate_join_record (join=join@entry=0xffff9e7965f8, join_tab=0xffff9e79d600, error=error@entry=0)
      at /home/mysql/mariadb/sql/sql_select.cc:23677
      #13 0x0000aaaad3dd7010 in AGGR_OP::end_send (this=0xffff9e799c30) at /home/mysql/mariadb/sql/sql_select.cc:32335
      #14 0x0000aaaad3dd74f4 in sub_select_postjoin_aggr (join=0xffff9e7965f8, join_tab=0xffff9e79d600, end_of_records=<optimized out>)
      at /home/mysql/mariadb/sql/sql_select.cc:23128
      #15 0x0000aaaad3df1a28 in do_select (procedure=<optimized out>, join=0xffff9e7965f8) at /home/mysql/mariadb/sql/sql_select.cc:22963
      #16 JOIN::exec_inner (this=this@entry=0xffff9e7965f8) at /home/mysql/mariadb/sql/sql_select.cc:4941
      #17 0x0000aaaad3df2fe0 in JOIN::exec (this=this@entry=0xffff9e7965f8) at /home/mysql/mariadb/sql/sql_select.cc:4718
      #18 0x0000aaaad3df3268 in Sql_cmd_dml::execute_inner (this=this@entry=0xffff9e7964f8, thd=thd@entry=0xffff7775b218) at /home/mysql/mariadb/sql/sql_select.cc:33413
      #19 0x0000aaaad3bc564c in Sql_cmd_delete::execute_inner (this=0xffff9e7964f8, thd=0xffff7775b218) at /home/mysql/mariadb/sql/sql_delete.cc:1773
      #20 0x0000aaaad3d2820c in Sql_cmd_dml::execute (this=0xffff9e7964f8, thd=0xffff7775b218) at /home/mysql/mariadb/sql/sql_select.cc:33350
      #21 0x0000aaaad3ca17cc in mysql_execute_command (thd=thd@entry=0xffff7775b218, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)
      at /home/mysql/mariadb/sql/sql_parse.cc:4360
      #22 0x0000aaaad3c747d0 in mysql_parse (thd=thd@entry=0xffff7775b218, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0xffffa0ace7b0)
      at /home/mysql/mariadb/sql/sql_parse.cc:7732
      #23 0x0000aaaad3c97afc in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0xffff7775b218,
      packet=packet@entry=0xffff9e76e219 "DELETE FROM v3 WHERE c4 IN ( SELECT * FROM v0 a6 ) ORDER BY EXISTS ( SELECT * ) REGEXP c4 OR c5 REGEXP c4",
      packet_length=packet_length@entry=105, blocking=<optimized out>) at /home/mysql/mariadb/sql/sql_class.h:1528
      #24 0x0000aaaad3c9c878 in do_command (thd=0xffff7775b218, blocking=blocking@entry=true) at /home/mysql/mariadb/sql/sql_parse.cc:1406
      #25 0x0000aaaad3fd1458 in do_handle_one_connection (connect=<optimized out>, put_in_cache=put_in_cache@entry=true) at /home/mysql/mariadb/sql/sql_connect.cc:1445
      #26 0x0000aaaad3fd1c3c in handle_one_connection (arg=arg@entry=0xffff9f90adb8) at /home/mysql/mariadb/sql/sql_connect.cc:1347
      #27 0x0000aaaad49a524c in pfs_spawn_thread (arg=0xffff9e10dd18) at /home/mysql/mariadb/storage/perfschema/pfs.cc:2201
      #28 0x0000ffffa53b4624 in start_thread (arg=0xffffa57d5918 <asan_thread_start(void*)>) at pthread_create.c:477
      #29 0x0000ffffa504349c in thread_start () at ../sysdeps/unix/sysv/linux/aarch64/clone.S:78

      Some other useful information:

      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,sargable_casefold=on

      The bug is similar to existing bug report: MDEV-22667. However, the PoC mentioned in that thread cannot trigger any unexpected behavior from the latest version of the server: (8d9bc61), but the PoC in this report does trigger the Assertion Failure. Not sure whether they come from the same root cause.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22667 Multi-delete on table with unique blob and partitions: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON, Error 126: Index is corrupted, Assertion `bitmap_is_set(&(m_part_info->read_partitions), m_last_part)' failed

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33165 Incorrect result interceptor passed to mysql_explain_union()

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            Thank you for the report. I was only able to reproduce this when both tables were created in the InnoDB format. Here is my simplified version of the test case:

            --source include/have_innodb.inc
            		 
            create table v0(c1 INT) ENGINE=InnoDB;
            		 
            CREATE TEMPORARY TABLE v3 ( c4 TEXT , c5 INT UNIQUE KEY DEFAULT 100 ) ENGINE=InnoDB;
            		 
            INSERT INTO v0 SET c1=0;
            		 
            INSERT INTO v3 SET c4=0,c5=100;
            		 
            DELETE FROM v3 WHERE c4 IN ( SELECT * FROM v0 a6 ) ORDER BY EXISTS ( SELECT * ) REGEXP c4 OR c5 REGEXP c4 ;
            		 
            DROP TABLE v0;

            11.3 8d9bc61d0bf783fa792e6c3be37b0eceecbeec89

            		 
            Version: '11.3.0-MariaDB-debug-log'  socket: '/dev/shm/11/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
            		 
            2023-09-21 09:14:56 0x7f98d62c66c0  InnoDB: Assertion failure in file /mariadb/11/storage/innobase/row/row0mysql.cc line 1652
            		 
            InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON

            DELETE and UPDATE work by first issuing a locking read, to find the matching rows that would be deleted or updated.

            There would be 3 calls to row_search_mvcc() before the only call to ha_innobase::delete() occurs. The first two calls to row_search_mvcc() are with mode=PAGE_CUR_G, but the last one is with mode=PAGE_CUR_UNSUPP. The third row_search_mvcc() did not find a match, that is, it had stored the persistent cursor position on a page supremum, hence, rel_pos=BTR_PCUR_AFTER.

            It seems to me that DELETE is wrongly trying to remove a record after the read did not find any match.

            The test doesn’t crash on the following revisions that I tested:
            10.6 60b039a8647e3254ac6e10346194226cd2455c46
            11.0 030ee267874c3b20ff3ddf999e2eb526a4d97988
            The test crashes on:
            11.1 e6ec2b3b5276e08a408b9de6f7af3c9cc4222ea5
            11.2 eece7f135f1d87c66faa8a51090401c09adc1edc

            marko Marko Mäkelä added a comment - Thank you for the report. I was only able to reproduce this when both tables were created in the InnoDB format. Here is my simplified version of the test case: --source include/have_innodb.inc create table v0(c1 INT ) ENGINE=InnoDB; CREATE TEMPORARY TABLE v3 ( c4 TEXT , c5 INT UNIQUE KEY DEFAULT 100 ) ENGINE=InnoDB; INSERT INTO v0 SET c1=0; INSERT INTO v3 SET c4=0,c5=100; DELETE FROM v3 WHERE c4 IN ( SELECT * FROM v0 a6 ) ORDER BY EXISTS ( SELECT * ) REGEXP c4 OR c5 REGEXP c4 ; DROP TABLE v0; 11.3 8d9bc61d0bf783fa792e6c3be37b0eceecbeec89 Version: '11.3.0-MariaDB-debug-log' socket: '/dev/shm/11/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution 2023-09-21 09:14:56 0x7f98d62c66c0 InnoDB: Assertion failure in file /mariadb/11/storage/innobase/row/row0mysql.cc line 1652 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON DELETE and UPDATE work by first issuing a locking read, to find the matching rows that would be deleted or updated. There would be 3 calls to row_search_mvcc() before the only call to ha_innobase::delete() occurs. The first two calls to row_search_mvcc() are with mode=PAGE_CUR_G , but the last one is with mode=PAGE_CUR_UNSUPP . The third row_search_mvcc() did not find a match, that is, it had stored the persistent cursor position on a page supremum, hence, rel_pos=BTR_PCUR_AFTER . It seems to me that DELETE is wrongly trying to remove a record after the read did not find any match. The test doesn’t crash on the following revisions that I tested: 10.6 60b039a8647e3254ac6e10346194226cd2455c46 11.0 030ee267874c3b20ff3ddf999e2eb526a4d97988 The test crashes on: 11.1 e6ec2b3b5276e08a408b9de6f7af3c9cc4222ea5 11.2 eece7f135f1d87c66faa8a51090401c09adc1edc
            Johnston Rex Johnston added a comment - - edited

            Semi-join optimization on a single table syntax query like this

            DELETE FROM t1 WHERE c1 IN (select c2 from t2) ORDER BY c1;

            transforms the effective query into

            DELETE FROM t1 using t1, t2 where c1 = c2 order by c1;

            which the parser will tell you is invalid due to the presence of an order
            by clause.

            It is worth noting that the SQL standard does not support any ordering in
            <delete statement: searched>.

            <delete
            		 
            statement: searched> ::=
            		 
            DELETE FROM <target table>
            		 
            [ FOR PORTION OF <application time period name>
            		 
            FROM <point in time 1> TO <point in time 2> ]
            		 
            [ [ AS ] <correlation name> ]
            		 
            [ WHERE <search condition> ]

            During multi_delete::initialize_tables for the top level join object, a table is initialized missing a keep_current_rowid flag, needed to position a handler for removal of the correct row after the filesort structure has been built.

            We have two options.
            1) Disable semijoin optimization (forcing the subquery to be materialized)
            under these circumstances.
            2) Allow the optimization, but ignore the ORDER BY clause only when there is
            no LIMIT clause. Currently the presence of a LIMIT clause causes semijoin
            optimization to be disabled, see
            Sql_cmd_delete::processing_as_multitable_delete_prohibited()
            3) Fix the flag

            bb-11.1-MDEV-32212 implements option 1

            bb-11.1-MDEV-32212-v2 implements option 3 (with option 2 present but disabled).

            Johnston Rex Johnston added a comment - - edited Semi-join optimization on a single table syntax query like this DELETE FROM t1 WHERE c1 IN (select c2 from t2) ORDER BY c1; transforms the effective query into DELETE FROM t1 using t1, t2 where c1 = c2 order by c1; which the parser will tell you is invalid due to the presence of an order by clause. It is worth noting that the SQL standard does not support any ordering in <delete statement: searched>. <delete statement: searched> ::= DELETE FROM <target table> [ FOR PORTION OF <application time period name> FROM <point in time 1> TO <point in time 2> ] [ [ AS ] <correlation name> ] [ WHERE <search condition> ] During multi_delete::initialize_tables for the top level join object, a table is initialized missing a keep_current_rowid flag, needed to position a handler for removal of the correct row after the filesort structure has been built. We have two options. 1) Disable semijoin optimization (forcing the subquery to be materialized) under these circumstances. 2) Allow the optimization, but ignore the ORDER BY clause only when there is no LIMIT clause. Currently the presence of a LIMIT clause causes semijoin optimization to be disabled, see Sql_cmd_delete::processing_as_multitable_delete_prohibited() 3) Fix the flag bb-11.1- MDEV-32212 implements option 1 bb-11.1- MDEV-32212 -v2 implements option 3 (with option 2 present but disabled).
            psergei Sergei Petrunia added a comment - - edited

            Note for the changelog:
            queries in form DELETE ... WHERE colX IN (SELECT ...) ... ORDER BY colY could cause crash.

            psergei Sergei Petrunia added a comment - - edited Note for the changelog: queries in form DELETE ... WHERE colX IN (SELECT ...) ... ORDER BY colY could cause crash.

            People

              Johnston Rex Johnston
              luy70 Yu Liang
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.