[MDEV-32981] SQL Simplification for the SQL Query Created: 2023-12-10  Updated: 2023-12-11  Resolved: 2023-12-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 11.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: HeShan Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 20.04


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

 Description   

Original reproduce step:

CREATE TABLE x ( x BOOLEAN ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
UPDATE x SET x = CASE WHEN NOT x != 1 THEN 1 ELSE 1 END WHERE x = 1 ;
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
 
 
WITH RECURSIVE x ( x ) AS 
( SELECT 'x' EXCEPT SELECT x + 1 FROM x GROUP BY - 'x' >= x IS NOT NULL = 
    ( x = 1 AND x * 1 / ( SELECT 1 AS x UNION SELECT 1.000000 ORDER BY x IN
    ( SELECT x FROM ( SELECT DISTINCT - x , x FROM x WHERE x = ( SELECT CASE WHEN x = 1 THEN 'x' ELSE x END FROM x 
    WHERE ( SELECT x IN ( SELECT x FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE x IS NULL AND x IN ( 1.000000 , 1 ) ) FROM x WHERE x != 'x' )
    NOT BETWEEN ( SELECT x AS x FROM x AS x GROUP BY x HAVING x ) AND 1 ) GROUP BY 1 , 'x' , NULL HAVING EXISTS ( WITH RECURSIVE x AS ( SELECT 1 ) SELECT * FROM x WHERE x = x ) ) AS x ) ) IN
    ( WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT 1.000000 + 1 FROM x ) SELECT x FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x )
    AND 1 + 1 ) 
) 
      
SELECT 1 EXCEPT SELECT 1.000000 EXCEPT SELECT x + 1 FROM x WHERE x LIKE ( x BETWEEN 1 AND 1 ) GROUP BY x HAVING x > 'x' ;

Minimal reproduce step:

CREATE TABLE x ( x BOOLEAN ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
UPDATE x SET x = CASE WHEN NOT x != 1 THEN 1 ELSE 1 END WHERE x = 1 ;
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
 
WITH RECURSIVE x ( x ) AS 
( SELECT 'x' EXCEPT SELECT x + 1 
    FROM x 
    GROUP BY - 'x' >= x IS NOT NULL = 
        ( SELECT 1 AS x UNION SELECT 1.000000 ORDER BY x IN( 
            SELECT x FROM ( SELECT   x FROM x  ) AS x )  ) 
) 
      
SELECT x + 1 FROM x ;

Additional Notes:

  • The original query was significantly longer and more complex; it was simplified for ease of debugging and readability.
  • The simplified version of the query continues to cause the observer to crash, indicating a fundamental issue in query processing.

Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?



 Comments   
Comment by Alice Sherepa [ 2023-12-11 ]

Thank you very much! Yes, it is quite useful, but please just add the comment to the original bug report, as it is still the same bug.

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