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

group by optimization incorrectly removing subquery where subject buried in a function

Details

    Description

      poc:

      CREATE TABLE v1169 ( v1170 FLOAT NOT NULL ) ;
       INSERT INTO v1169 ( v1170 ) VALUES ( 40 ) ;
       UPDATE v1169 SET v1170 = -2147483648 WHERE v1170 = 5 ;
       INSERT INTO v1169 ( v1170 ) VALUES ( -128 ) , ( 52 ) ;
       WITH v1172 AS ( SELECT v1170 FROM ( SELECT v1170 FROM v1169 GROUP BY v1170 ) AS v1171 ) SELECT v1170 FROM v1172 WHERE v1170 BETWEEN FALSE AND ( ( ( v1170 OR NOT v1170 ) BETWEEN ( ( ( NOT ( ( 90778113.000000 ^ 90656932.000000 AND ( v1170 NOT IN ( NOT ( NOT ( 'x' = TRUE AND v1170 = 61 ) ) ) AND v1170 IN ( ( SELECT v1170 FROM v1169 WHERE ( FALSE <= -128 BETWEEN 10 AND 34 , v1170 ) IN ( WITH v1176 AS ( SELECT v1174 FROM ( SELECT ( SELECT v1170 FROM ( SELECT ( v1170 NOT IN ( 11097710.000000 , 12206111.000000 NOT BETWEEN 'x' AND 'x' ) AND v1170 NOT IN ( 2147483647 % v1170 ) ) , CASE WHEN v1170 % 30004927.000000 THEN 'x' ELSE v1170 END / 46 FROM v1169 UNION SELECT v1170 , v1170 FROM v1169 ) AS v1173 ) * 0 AS v1174 FROM v1169 ) AS v1175 ) SELECT ( v1174 NOT IN ( ( NOT ( 'x' / v1174 = v1174 + CASE v1174 WHEN 0 THEN v1174 ELSE ( WITH RECURSIVE v1177 ( v1178 ) AS ( SELECT v1170 FROM v1169 ) SELECT 81155100.000000 AS v1179 FROM v1177 ORDER BY v1178 DESC LIMIT 1 OFFSET 1 ) - 0 END AND v1174 = 'x' ) ) IS NULL ) AND v1174 NOT IN ( 33 ^ v1174 ) ) , v1174 + v1174 FROM v1176 GROUP BY v1174 ) ) * 2147483647 ) ) = -32768 ) * NULL ) ) ) ) AND 719937.000000 ) ) ;
      

      output:
      mysqld: /sql/item_subselect.cc:766: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.

      The full error log is in the attachment.

      Attachments

        Issue Links

          Activity

            Johnston Rex Johnston added a comment - - edited

            There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary.

            A comment in JOIN::prepare

                 Permanently remove redundant parts from the query if
                   1) This is a subquery
                   2) This is the first time this query is optimized (since the
                      transformation is permanent
                   3) Not normalizing a view. Removal should take place when a
                      query involving a view is optimized, not when the view
                      is created

            implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution.

            The very simplest solution I've found lies in bb-10.4-MDEV-28621-no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.

            Johnston Rex Johnston added a comment - - edited There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary. A comment in JOIN::prepare Permanently remove redundant parts from the query if 1) This is a subquery 2) This is the first time this query is optimized (since the transformation is permanent 3) Not normalizing a view. Removal should take place when a query involving a view is optimized, not when the view is created implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution. The very simplest solution I've found lies in bb-10.4- MDEV-28621 -no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.

            On the question in the comment above https://jira.mariadb.org/browse/MDEV-28621?focusedCommentId=284062&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-284062

            about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error.

            Trying various databases:

            create table t1 (a int);
            insert into t1 values (1),(2),(3);
             
            create table t2 (a int, b int);
            insert into t2 values (1,1), (2,2),(2,3);
            select count(*) from t1 group by (select b from t2 where a=2);
            

            SQLite 3.39: succeeds.
            PostgreSQL 13.7: error
            MySQL 8: error
            Yugabyte, Timesten: error.
            Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY".

            I suspect that the optimization "Don't compute constant GROUP BY value" is not common.

            Trying something more common:

            create table t1 (a int);
            insert into t1 values (1),(2),(3);
            create table t2 (a int, b int);
            insert into t2 values (1,1), (2,2),(2,3);
            with T as 
            (
              select 
                a, 
                (select t1.a from t2 where a=2) as b
              from t1
             
            )
            select a from T;
            

            SQL Server : OK
            Oracle: OK
            PostgreSQL: OK
            SQLite: Ok
            MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail.
            MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).

            psergei Sergei Petrunia added a comment - On the question in the comment above https://jira.mariadb.org/browse/MDEV-28621?focusedCommentId=284062&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-284062 about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error. Trying various databases: create table t1 (a int ); insert into t1 values (1),(2),(3);   create table t2 (a int , b int ); insert into t2 values (1,1), (2,2),(2,3); select count (*) from t1 group by ( select b from t2 where a=2); SQLite 3.39: succeeds. PostgreSQL 13.7: error MySQL 8: error Yugabyte, Timesten: error. Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY". I suspect that the optimization "Don't compute constant GROUP BY value" is not common. Trying something more common: create table t1 (a int ); insert into t1 values (1),(2),(3); create table t2 (a int , b int ); insert into t2 values (1,1), (2,2),(2,3); with T as ( select a, ( select t1.a from t2 where a=2) as b from t1   ) select a from T; SQL Server : OK Oracle: OK PostgreSQL: OK SQLite: Ok MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail. MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).

            Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.

            Roel Roel Van de Paar added a comment - Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.

            Getting this pushed:
            https://github.com/MariaDB/server/commit/40b3525fcc79aef62ea1ae057ec5687a55c0630b

            MDEV-28621: group by optimization incorrectly removing subquery where…
            … subject buried in a function
             
            Workaround patch: Do not remove GROUP BY clause when it has
            subquer(ies) in it.
            

            This will be followed by a more extensive fix.

            psergei Sergei Petrunia added a comment - Getting this pushed: https://github.com/MariaDB/server/commit/40b3525fcc79aef62ea1ae057ec5687a55c0630b MDEV-28621: group by optimization incorrectly removing subquery where… … subject buried in a function   Workaround patch: Do not remove GROUP BY clause when it has subquer(ies) in it. This will be followed by a more extensive fix.

            Filed MDEV-34202 to do a full fix (do remove GROUP BY always, even if it has subqueries in it).

            psergei Sergei Petrunia added a comment - Filed MDEV-34202 to do a full fix (do remove GROUP BY always, even if it has subqueries in it).

            People

              psergei Sergei Petrunia
              nobody Shihao Wen
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.