Fix tests that were temporary disabled in PS mode (MDEV-25973)

[MDEV-26135] Assertion failure when executing PS with a hanging recursive CTE Created: 2021-07-13  Updated: 2021-07-20  Resolved: 2021-07-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Technical task Priority: Critical
Reporter: Dmitry Shulga Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-25973 Fix tests that were temporary disable... Stalled

 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;



 Comments   
Comment by Igor Babaev [ 2021-07-17 ]

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)

Comment by Dmitry Shulga [ 2021-07-19 ]

OK to push

Comment by Igor Babaev [ 2021-07-20 ]

A fix for this bug was pushed into 10.2.

Generated at Thu Feb 08 09:43:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.