Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32981

SQL Simplification for the SQL Query

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 11.2
    • N/A
    • Optimizer, Server
    • None
    • Ubuntu 20.04

    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?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Wangdada HeShan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.