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

Can Split Materialization work with WITH ROLLUP?

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      This is a follow-up to MDEV-26337.

      The fix for MDEV-26337 has disabled the Split Materialization for subqueries that have WITH ROLLUP.

      Within this MDEV, we'll discuss whether it can be made to work.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26337 subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            psergei Sergei Petrunia added a comment -

            A reply to igor's comment: https://jira.mariadb.org/browse/MDEV-26337?focusedCommentId=209666&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-209666

            Ok so I create the tables described and fill them with data:

            create table t1 (a int, b int, c int);
            		 
            create table t2 (a int, b int, c int, key (a,b));
            		 
            # 100 groups  x 1K elements
            		 
            insert into t2 
            select
            		 
              A.seq,
            		 
              B.seq,
            		 
              C.seq
            		 
            from 
              seq_0_to_9 A,
            		 
              seq_0_to_9 B,
            		 
              seq_0_to_999 C;
            		 
             
            		 
            insert into t1 select 
              seq, seq, seq 
            from
            		 
              seq_1_to_10;

            Then, I run the provided query (adjusted it a bit to avoid syntax errors):

            explain
            		 
            select s from t1, 
              (select a,b,sum(c) as s 
               from t2 group by a,b) dt 
            where 
              t1.a=dt.a and 
              t1.b=dt.b and 
              t1.a in (1,3,5) and 
              t1.b between 2 and 4;


            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+
            		 
            | id   | select_type     | table      | type | possible_keys | key  | key_len | ref             | rows | Extra                 |
            		 
            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+
            		 
            |    1 | PRIMARY         | t1         | ALL  | NULL          | NULL | NULL    | NULL            | 10   | Using where           |
            		 
            |    1 | PRIMARY         | <derived2> | ref  | key0          | key0 | 10      | j2.t1.a,j2.t1.b | 2    |                       |
            		 
            |    2 | LATERAL DERIVED | t2         | ref  | a             | a    | 10      | j2.t1.a,j2.t1.b | 1    | Using index condition |
            		 
            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+

            Indeed, there's no sorting done.

            But if I add WITH ROLLUP, I see that the subquery now uses "Using filesort":

            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+
            		 
            | id   | select_type     | table      | type | possible_keys | key  | key_len | ref             | rows | Extra                       |
            		 
            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+
            		 
            |    1 | PRIMARY         | t1         | ALL  | NULL          | NULL | NULL    | NULL            | 10   | Using where                 |
            		 
            |    1 | PRIMARY         | <derived2> | ref  | key0          | key0 | 10      | j2.t1.a,j2.t1.b | 2    |                             |
            		 
            |    2 | LATERAL DERIVED | t2         | ref  | a             | a    | 10      | j2.t1.a,j2.t1.b | 1    | Using where; Using filesort |
            		 
            +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+

            psergei Sergei Petrunia added a comment - A reply to igor 's comment: https://jira.mariadb.org/browse/MDEV-26337?focusedCommentId=209666&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-209666 Ok so I create the tables described and fill them with data: create table t1 (a int , b int , c int ); create table t2 (a int , b int , c int , key (a,b)); # 100 groups x 1K elements insert into t2 select A.seq, B.seq, C.seq from seq_0_to_9 A, seq_0_to_9 B, seq_0_to_999 C;   insert into t1 select seq, seq, seq from seq_1_to_10; Then, I run the provided query (adjusted it a bit to avoid syntax errors): explain select s from t1, ( select a,b, sum (c) as s from t2 group by a,b) dt where t1.a=dt.a and t1.b=dt.b and t1.a in (1,3,5) and t1.b between 2 and 4; +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 10 | j2.t1.a,j2.t1.b | 2 | | | 2 | LATERAL DERIVED | t2 | ref | a | a | 10 | j2.t1.a,j2.t1.b | 1 | Using index condition | +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+ Indeed, there's no sorting done. But if I add WITH ROLLUP, I see that the subquery now uses "Using filesort": +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 10 | j2.t1.a,j2.t1.b | 2 | | | 2 | LATERAL DERIVED | t2 | ref | a | a | 10 | j2.t1.a,j2.t1.b | 1 | Using where; Using filesort | +------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.