[MDEV-32980] SQL Simplification for the SQL Query Created: 2023-12-10  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-32405 Early subquery exec: SEGV at /mariadb... Confirmed

 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?

 Comments   
Comment by Alice Sherepa [ 2023-12-11 ]

Thanks! It is useful, but please just add the comment to the original bug report.

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