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

Crash caused by multi-table UPDATE over derived with hanging CTE

Details

    Description

      poc:

      CREATE TABLE v1269 ( v1270 VARCHAR ( 1 ) , v1271 INT , v1272 INT ) ;
       CREATE TABLE v1273 ( v1274 BOOLEAN NOT NULL , v1275 INT , v1276 INT ) ;
       INSERT INTO v1269 ( v1271 ) VALUES ( v1271 ) ;
       UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( 17138038.000000 AND v1274 = 78 ) ) = 0 AND v1274 = -128 ) % 45 , ( 79 = 13 OR v1276 > 'x' ) FROM v1273 WHERE v1275 - v1276 ) AS v1277 NATURAL JOIN ( WITH v1279 AS ( SELECT v1276 FROM ( SELECT NOT v1276 <= 'x' , v1276 FROM v1273 GROUP BY v1274 ) AS v1278 ) SELECT DISTINCT v1270 , ( v1270 = 5 OR v1272 > 'x' ) FROM v1269 ) AS v1280 NATURAL JOIN v1269 WHERE v1270 = v1274 ) AS v1281 FROM v1273 ) AS v1282 NATURAL JOIN v1269 AS v1283 NATURAL JOIN v1273 ORDER BY v1271 ) AND v1270 = -1 FROM v1269 ) AND 'x' ) = 12 AND v1271 = 64 ) % 0 , ( v1271 = 37 OR v1270 > 'x' ) FROM v1269 WHERE v1271 = -1 AND ( v1271 = 85 OR v1270 = 0 OR v1270 = 45 ) ) AS v1284 NATURAL JOIN v1269 SET v1271 = -1 WHERE v1270 = 62 ;
       INSERT INTO v1273 ( v1275 ) VALUES ( ( ( SELECT ARRAY [ 16 , 255 , -1 ] ) ) [ 93 ] ) , ( 255 ) ;
       SELECT COUNT ( v1270 ) OVER v1285 , NTILE ( v1271 ) OVER v1285 FROM v1269 WINDOW v1285 AS ( PARTITION BY v1271 ORDER BY v1272 DESC ) ;
      

      output:
      mysqld: /sql/handler.cc:2853: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.

      The full error log is in the attachment.

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment - - edited

            Given the table t1 built with the commands

            CREATE TABLE t1 (a int) ENGINE=MYISAM;
            INSERT INTO t1 VALUES (3), (7), (1);
            

            the crash can also be reproduced with any of the following UPDATE statements.

            UPDATE   
              (SELECT (5, (WITH cte AS (SELECT 1) SELECT a FROM t1))) dt
              JOIN t1 t 
              ON t.a=dt.a
            SET t.a = 1;
             
             
            UPDATE   
              (SELECT a FROM t1 
                 WHERE (5, (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)) <= (5,a)) dt
              JOIN t1 t 
              ON t.a=dt.a
            SET t.a = 1;
            

            The first statement is expected to return the error message:

            ERROR 1241 (21000): Operand should contain 1 column(s)
            

            The second statement is expected to update the record of t1 where a=7:

            MariaDB [test]> SELECT * FROM t1;
            +------+
            | a    |
            +------+
            |    3 |
            |    1 |
            |    1 |
            +------+
            

            igor Igor Babaev added a comment - - edited Given the table t1 built with the commands CREATE TABLE t1 (a int ) ENGINE=MYISAM; INSERT INTO t1 VALUES (3), (7), (1); the crash can also be reproduced with any of the following UPDATE statements. UPDATE ( SELECT (5, ( WITH cte AS ( SELECT 1) SELECT a FROM t1))) dt JOIN t1 t ON t.a=dt.a SET t.a = 1;     UPDATE ( SELECT a FROM t1 WHERE (5, ( WITH cte AS ( SELECT 1) SELECT a FROM t1 WHERE a > 4)) <= (5,a)) dt JOIN t1 t ON t.a=dt.a SET t.a = 1; The first statement is expected to return the error message: ERROR 1241 (21000): Operand should contain 1 column(s) The second statement is expected to update the record of t1 where a=7: MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 3 | | 1 | | 1 | +------+
            igor Igor Babaev added a comment -

            The following patch fixes the problem.

            diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
            index 2e67259..9c8e3c0 100644
            --- a/sql/sql_cte.cc
            +++ b/sql/sql_cte.cc
            @@ -1241,14 +1241,14 @@ bool With_element::prepare_unreferenced(THD *thd)
                    sl= sl->next_select())
                 sl->context.outer_context= 0;
             
            +  uint8 save_context_analysys_only= thd->lex->context_analysis_only;
               thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
               if (!spec->prepared &&
                   (spec->prepare(spec->derived, 0, 0) ||
                    rename_columns_of_derived_unit(thd, spec) ||
                    check_duplicate_names(thd, first_sl->item_list, 1)))
                 rc= true;
            -
            -  thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
            +  thd->lex->context_analysis_only= save_context_analysys_only;
               return rc;
             }
            

            As we can see the code of the function With_element::prepare_unreferenced() before the patch did not properly restored the value of thd->lex->context_analysis_only. As a result it became 0 after the call of this function. This function is called when JOIN::prepare() is called for the subquery

               (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)
            

            This happens when Item_row::fix_fields() calls fix_fields() for its second argument.
            Note that after the call of With_element::prepare_unreferenced() remains 0. As a result the subquery is considered as a constant and Item_row::fix_fields() tries to execute it calling the virtual function is_null() for it.
            It causes an assertion failure because the call of Item_row::fix_fields() happens during the invocation of Multiupdate_prelocking_strategy::handle_end() that calls mysql_derived_prepare for the derived table dt when proper locks for used tables has not been acquired yet.
            With this patch the value of thd->lex->context_analysis_only is restored to CONTEXT_ANALYSIS_ONLY_DERIVED
            that is set in the function mysql_multi_update_prepare().

            igor Igor Babaev added a comment - The following patch fixes the problem. diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 2e67259..9c8e3c0 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1241,14 +1241,14 @@ bool With_element::prepare_unreferenced(THD *thd) sl= sl->next_select()) sl->context.outer_context= 0; + uint8 save_context_analysys_only= thd->lex->context_analysis_only; thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; if (!spec->prepared && (spec->prepare(spec->derived, 0, 0) || rename_columns_of_derived_unit(thd, spec) || check_duplicate_names(thd, first_sl->item_list, 1))) rc= true; - - thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; + thd->lex->context_analysis_only= save_context_analysys_only; return rc; } As we can see the code of the function With_element::prepare_unreferenced() before the patch did not properly restored the value of thd->lex->context_analysis_only. As a result it became 0 after the call of this function. This function is called when JOIN::prepare() is called for the subquery (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4) This happens when Item_row::fix_fields() calls fix_fields() for its second argument. Note that after the call of With_element::prepare_unreferenced() remains 0. As a result the subquery is considered as a constant and Item_row::fix_fields() tries to execute it calling the virtual function is_null() for it. It causes an assertion failure because the call of Item_row::fix_fields() happens during the invocation of Multiupdate_prelocking_strategy::handle_end() that calls mysql_derived_prepare for the derived table dt when proper locks for used tables has not been acquired yet. With this patch the value of thd->lex->context_analysis_only is restored to CONTEXT_ANALYSIS_ONLY_DERIVED that is set in the function mysql_multi_update_prepare().
            igor Igor Babaev added a comment -

            Please review. The patch is trivial.

            igor Igor Babaev added a comment - Please review. The patch is trivial.

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.4. It has to be merged upstream as it is.

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.4. It has to be merged upstream as it is.

            People

              igor Igor Babaev
              nobody Shihao Wen
              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.