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

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 t0 ( c49 DOUBLE ( 249 , 19 ) ) ;
      INSERT INTO t0 VALUES ( -120 ) , ( 105 ) ;
      CREATE INDEX i0 ON t0 ( c49 ) ;
      INSERT INTO t0 VALUES ( ) , ( ) ;
       
      SELECT t2 . c5 AS c22 FROM 
      ( SELECT ~ OCT ( t0 . c49 ) << CONVERT ( -1968393419284614186 , UNSIGNED ) % RAND ( ) 
          - RAND ( -76 ) << + EXISTS ( SELECT -126 AS c42 ) AS c15 FROM t0 ) AS t1 
      JOIN ( SELECT ROUND ( 88 , ORD ( -25 ) / ATAN ( 32 IN ( SELECT t0 . c49
          BETWEEN -24 AND 126 AS c10 FROM t0 ) ) / UNHEX ( 57 ) = ALL ( SELECT t0 . c49 AS c18 FROM t0 ) )
          NOT BETWEEN 118 AND 20 AS c5 FROM t0 ) AS t2 ON t1 . c15 = t1 . c15 
      WHERE LTRIM ( t1 . c15 ) % 42.121931 = -58 GROUP BY c5 , c15 HAVING c15 = 2 REGEXP EXP ( t2 . c5 ) ;
       
      
      

      Minimal reproduce step:

      CREATE TABLE t0 ( c49 DOUBLE ( 249 , 19 ) ) ;
      INSERT INTO t0 VALUES ( -120 ) , ( 105 ) ;
      CREATE INDEX i0 ON t0 ( c49 ) ;
      INSERT INTO t0 VALUES ( ) , ( ) ;
       
       
      SELECT t2 . c5  FROM 
      ( SELECT  ( SELECT -126 ) AS c15 ) AS t1 JOIN 
      ( SELECT    ATAN ( 32 IN ( SELECT t0 . c49 FROM t0 ) ) /
        57  = ALL ( SELECT t0 . c49  FROM t0 )  AS c5 FROM t0 ) AS t2 
      WHERE  t1 . c15 
      GROUP BY  c15 
      HAVING c15 = 2 REGEXP EXP ( t2 . c5 ) ;
      

      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.