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

Incorrect Empty Set with HAVING clause when SELECT and GROUP BY use different aliases for the same column

    XMLWordPrintable

Details

    Description

      The server returns an incorrect Empty Set under specific conditions involving GROUP BY, aliases, and a HAVING clause with IS NOT TRUE.

      Specifically, if a derived table selects the same underlying column twice with different aliases (e.g., c1 AS ca1, c1 AS ca2), and the outer query selects ca1 but groups by ca2, the optimizer incorrectly filters out NULL values that satisfy the HAVING condition.

      If the SELECT list and GROUP BY clause use the same alias, the query returns the correct result.

      CREATE TABLE t1(c1 TEXT);
      		 
      INSERT INTO t1 (c1) VALUES (NULL), ('abc');
      		 
       
      		 
      mysql> SELECT ca1 FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 GROUP BY ca2 HAVING (ca2 = ANY (SELECT c1 FROM t1))IS NOT TRUE;
      		 
      Empty set (0.002 sec)
      		 
       
      		 
      mysql> SELECT ca2 FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 GROUP BY ca2 HAVING (ca2 = ANY (SELECT c1 FROM t1))IS NOT TRUE;
      		 
      +------+
      		 
      | ca2  |
      		 
      +------+
      		 
      | NULL |
      		 
      +------+
      		 
      1 row in set (0.003 sec)

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29300 Assertion `*ref && (*ref)->fixed()' failed in Item_field::fix_outer_field on SELECT

          • Major - Major loss of function.
          • Closed
          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-38476 Wrong Result (Empty Set) with derived_merge=on using AVG() on text column in HAVING clause

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-38476 Wrong Result (Empty Set) with derived_merge=on using AVG() on text column in HAVING clause

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

          Activity

            People

              Johnston Rex Johnston
              March SerenMarch
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.