[MDEV-32988] 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-32715 Segmentation fault at /mariadb-11.3.0... Open

 Description   

Original reproduce step:

CREATE TABLE x ( x FLOAT PRIMARY KEY ) ;
 
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
 
 
UPDATE x SET x = 1 ORDER BY 
    ( 
    SELECT 1 / 1 WHERE ( ( x = ( SELECT x FROM ( SELECT x FROM x WHERE ( FALSE IS NOT NULL AND x BETWEEN 1 AND 1 ) OR
 
     ( ( CASE 'x' WHEN 'x' THEN 1 ELSE 1 END OR x = 'x' ) AND x IS NOT NULL AND NULL BETWEEN x NOT LIKE 1.000000 + 1 AND 'x' ) GROUP BY x ) AS x WHERE x IN ( 
        SELECT ( TRUE , x , x ) < ( 1 , 1 , 1 ) FROM ( SELECT * FROM x WHERE x IN ( SELECT x FROM x ) ) AS x WHERE CASE WHEN x = 1 THEN x WHEN 'x' THEN 'x' WHEN 'x' THEN 'x' ELSE (
 
         SELECT x WHERE x != 1 GROUP BY x ) != 'x' END = x GROUP BY x ) ) OR x = 'x' ) AND x IS NOT NULL ) OR x = 1 
    ) != 'x' + 1 ;

Minimal reproduce step:

CREATE TABLE x ( x FLOAT PRIMARY KEY ) ;
 
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
 
 
 
 
UPDATE x SET x = 1 ORDER BY
 
    ( 
    SELECT 1  WHERE  ( SELECT x FROM ( SELECT x FROM x  GROUP BY x ) AS x 
    WHERE x IN ( SELECT ( TRUE , x , x ) < ( 1 , 1 , 1 ) FROM x  ) )    
    )
 
    != 'x' + 1 ;

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-32715

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