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

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

      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.