Details

    Description

      Running of the following test case

      CREATE TABLE t1 (id INT);
      CREATE TABLE t2 (c1 INT, c2 INT);
       
      PREPARE stmt FROM 'with recursive rcte(a) as (select 1 union select cast(a+1 as unsigned) from rcte where a < 10), cte1 as (select count(*) as c1 from t1), cte2 as (select count(*) as c2 from t2) select * from cte1,cte2';
      

      leads to firing of assert in the function reinit_stmt_before_use()

        Assertion failed: (sl->join == 0), function <unknown>, file bb-10.6-MDEV-25973/sql/sql_prepare.cc, line 3174.
      

      and server crash.

      The data member SELECT_LEX::join points to a JOIN object created by the following calling sequence:

      check_prepared_statement
        mysql_test_select
          open_normal_and_derived_tables
            mysql_handle_derived
              mysql_derived_prepare
                st_select_lex_unit::prepare
                  st_select_lex_unit::prepare_join
      

      By some unknown reason the SELECT_LEX object that is owner of this JOIN object is not added to a list which head addressed by lex->unit.first_select()

      In result, when later the method Prepared_statement::prepare() calls lex->unit.cleanup() to clean up objects created on preparing the statement, the method JOIN::destroy() is not called for the JOIN object created on handling the statement

      PREPARE stmt FROM 'with recursive rcte(a) as (select 1 union select cast(a+1 as unsigned) from rcte where a < 10), cte1 as (select count(*) as c1 from t1), cte2 as (select count(*) as c2 from t2) select * from cte1,cte2';
      

      As a consequence, the data member SELECT_LEX::join is not reset and the assert (sl->join == 0) fired later on attempt to run the statement

      EXECUTE stmt;
      

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            If we execute a PS created for a select query containing a WITH clause with a recursive CTE and this CTE is used in the query as a joined table then execution of the PS works fine and returns a result set. Here's a test case demonstrating how it works

            create table t1 (a int);
            insert into t1 values (5), (7);
            create table t2 (b int);
            insert into t2 values (3), (7), (1);
            prepare stmt from "
            with recursive r as (select a from t1 union select a+1 from r where a < 10)
            select * from t2, r where a = b;
            ";
            

            MariaDB [test]> prepare stmt from "
                "> with recursive r as (select a from t1 union select a+1 from r where a < 10)
                "> select * from t2, r where a = b;
                "> ";
            Query OK, 0 rows affected (0.00 sec)
            Statement prepared
             
            MariaDB [test]> execute stmt;
            +------+------+
            | b    | a    |
            +------+------+
            |    7 |    7 |
            +------+------+
            1 row in set (0.01 sec)
             
            MariaDB [test]> execute stmt;
            +------+------+
            | b    | a    |
            +------+------+
            |    7 |    7 |
            +------+------+
            1 row in set (0.01 sec)
            

            However if the main query does not use the recursive CTE and the CTE remains hanging

            MariaDB [test]> prepare stmt from " 
                "> with recursive r as (select a from t1 union select a+1 from r where a < 10)
                "> select * from t2;
                "> ";
            Query OK, 0 rows affected (0.00 sec)
            Statement prepared
            

            execution of PS comes to an assertion failure.

            MariaDB [test]> execute stmt;
            sql_prepare.cc:3094: void reinit_stmt_before_use(THD*, LEX*): Assertion `sl->join == 0' failed.
            

            At the same time direct execution of the above query works fine

            MariaDB [test]> with recursive r as (select a from t1 union select a+1 from r where a < 10) select * from t2;
            +------+
            | b    |
            +------+
            |    3 |
            |    7 |
            |    1 |
            +------+
            3 rows in set (0.00 sec)
            

            igor Igor Babaev added a comment - If we execute a PS created for a select query containing a WITH clause with a recursive CTE and this CTE is used in the query as a joined table then execution of the PS works fine and returns a result set. Here's a test case demonstrating how it works create table t1 (a int ); insert into t1 values (5), (7); create table t2 (b int ); insert into t2 values (3), (7), (1); prepare stmt from " with recursive r as (select a from t1 union select a+1 from r where a < 10) select * from t2, r where a = b; " ; MariaDB [test]> prepare stmt from " "> with recursive r as (select a from t1 union select a+1 from r where a < 10) "> select * from t2, r where a = b; "> "; Query OK, 0 rows affected (0.00 sec) Statement prepared   MariaDB [test]> execute stmt; +------+------+ | b | a | +------+------+ | 7 | 7 | +------+------+ 1 row in set (0.01 sec)   MariaDB [test]> execute stmt; +------+------+ | b | a | +------+------+ | 7 | 7 | +------+------+ 1 row in set (0.01 sec) However if the main query does not use the recursive CTE and the CTE remains hanging MariaDB [test]> prepare stmt from " "> with recursive r as (select a from t1 union select a+1 from r where a < 10) "> select * from t2; "> "; Query OK, 0 rows affected (0.00 sec) Statement prepared execution of PS comes to an assertion failure. MariaDB [test]> execute stmt; sql_prepare.cc:3094: void reinit_stmt_before_use(THD*, LEX*): Assertion `sl->join == 0' failed. At the same time direct execution of the above query works fine MariaDB [test]> with recursive r as (select a from t1 union select a+1 from r where a < 10) select * from t2; +------+ | b | +------+ | 3 | | 7 | | 1 | +------+ 3 rows in set (0.00 sec)
            shulga Dmitry Shulga added a comment -

            OK to push

            shulga Dmitry Shulga added a comment - OK to push
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.2.

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2.

            People

              igor Igor Babaev
              shulga Dmitry Shulga
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.