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

SQL Simplification for the SQL Query

    XMLWordPrintable

Details

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

    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?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Wangdada HeShan
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.