[MDEV-32989] SQL Simplification for the SQL Query Created: 2023-12-11  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-32718 Segmentation fault at /mariadb-11.3.0... Open

 Description   

Original reproduce step:

CREATE TABLE x ( x DEC PRIMARY KEY ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
 
UPDATE x SET x = 1 WHERE ( x BETWEEN 
    ( SELECT x FROM x AS x WHERE CASE WHEN x * ( 
        SELECT x * ( SELECT x FROM ( SELECT x FROM x GROUP BY ( 
            SELECT 1 ORDER BY x , x DESC , x , x IN ( SELECT DISTINCT x FROM x WHERE - 'x' >= x IS NOT NULL = ( 1 < x AND x < 'x' ) ) LIMIT 1 OFFSET 1 ) ) AS x 
            ORDER BY x , x DESC , x , x LIMIT 1 OFFSET 1 ) FROM x GROUP BY 'x' , ( SELECT x ) + ( SELECT ( SELECT x ) AS x FROM x WINDOW x AS ( PARTITION BY x ORDER BY x DESC ) 
            ORDER BY ( SELECT 1 FROM x WHERE x = x ) , ( x AND x IS NULL AND x IN ( 1 , 1 , 1 , 1 , 1 ) ) BETWEEN 1 AND CASE WHEN x = 1 THEN 'x' ELSE x END DESC , x , x ) >= 1 
            WINDOW x AS ( PARTITION BY FALSE ORDER BY x DESC ) ) <= 1 AND x = x THEN 'x' END BETWEEN 1 AND 1 GROUP BY x 
    )AND x )AND x = 1 AND ( x = 1 OR x = 1 ) ORDER BY x + x , x ;
 
 
 

Minimal reproduce step:

 
CREATE TABLE x ( x DEC PRIMARY KEY ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
UPDATE x SET x = 1 WHERE ( x BETWEEN 
    ( SELECT x FROM x  WHERE  
        ( SELECT ( SELECT x ) AS x FROM x ORDER BY  x BETWEEN 1 AND CASE WHEN x = 1 THEN 'x' ELSE x END , x  )   
    )AND 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 HeShan [ 2023-12-11 ]

The bug report of its origin is MDEV-32718

Comment by Sergei Golubchik [ 2023-12-11 ]

Thanks, yes, simplification is helpful. But a "simplification" is not a bug, don't create a new report for it. Please, in the future add a comment to the original bug.

Comment by HeShan [ 2023-12-11 ]

Sorry, I misunderstood your intention earlier. In the future, I will add a comment to the original bug instead of submitting a bug report.

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