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

Pushdown from HAVING into WHERE does not remove conditions from HAVING

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.4(EOL), 10.5, 10.6
    • N/A
    • Optimizer
    • None

    Description

      This test case is taken from having_cond_pushdown.test from the main suite.

      CREATE TABLE t1(a INT, b INT, c INT);
      CREATE TABLE t2(x INT, y INT);
       
      INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
      INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22);
       
      EXPLAIN FORMAT=JSON SELECT t1.a,MAX(t1.b),MIN(t1.c)
      FROM t1
      GROUP BY t1.a
      HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
      

      In the output we can see

      "attached_condition": "t1.a > 2 or t1.a < 3"

      which means those conjuncts have been pushed to the WHERE part, but at the same time

      "having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1"

      which means they haven't been removed from the HAVING part.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              oleg.smirnov Oleg Smirnov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.