[MDEV-32913] Crash when query uses recursive reference to CTE in ORDER BY clause Created: 2023-11-30  Updated: 2024-01-14

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4

Type: Bug Priority: Critical
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-32697 Assertion Failed in /mariadb-11.3.0/s... In Progress

 Description   

The following query leads to an assertion abort:

WITH RECURSIVE x (a) AS
 
 ( 
   SELECT 1
 
   UNION 
   SELECT (SELECT 1 AS a UNION SELECT 1 ORDER BY (SELECT 1 FROM x) ) FROM x
 
 )
 
 SELECT a FROM x;

In 10.4 the abort happens in handler::ha_rnd_next():

int handler::ha_rnd_next(uchar*): Assertion `inited == RND' failed.

in 10.5 the abort happens in handler::start_psi_batch_mode().

The above test case is a simplification of the test case for MDEV-29070.

 Comments   
Comment by Igor Babaev [ 2023-11-30 ]

It should be noted that one of the recursive references is used in the subquery of the ORDER BY clause. With default setting of the system variable standard_compliant_cte=1 we would expect the error message 

ERROR 4008 (HY000): Restrictions imposed on recursive definitions are violated for table 'x'

The following change fixes this problem

-643,6 +643,8 @@ void With_element::check_dependencies_in_unit(st_select_lex_unit *unit,

{ check_dependencies_in_select(sl, &unit_ctxt_elem, in_subq, dep_map); }

+ if ((sl= unit->fake_select_lex))
+ check_dependencies_in_select(sl, &unit_ctxt_elem, in_subq, dep_map);

However it does not help with the setting standard_compliant_cte=0.

Comment by Igor Babaev [ 2023-11-30 ]

Here's another more natural query using a base table that causes the same problem

CREATE TABLE t1 (b int);
 
INSERT INTO t1 VALUES (3), (7), (1), (8), (5);
 
 
 
WITH RECURSIVE x (a) AS
 
 (
 
   SELECT 1 
   UNION
 
   SELECT   
               (
 
                  SELECT b FROM t1 WHERE b < 3 
                  UNION
 
                  SELECT b FROM t1 WHERE b > 8  
                  ORDER BY (SELECT a+b FROM x)
 
               )
 
    FROM x
 
) 
SELECT a FROM x;

Comment by Alice Sherepa [ 2023-12-01 ]

This is probably the same as MDEV-32697 - could you please also check that test case after the fix.

Generated at Thu Feb 08 10:34:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.