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

CREATE VIEW extraction causes row loss for grouped aggregate with `HAVING MIN(SET/ENUM) >= const`

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.11, 11.4, 11.8, 12.3, 11.4.10
    • N/A
    • Data types, Optimizer
    • None
    • Not for Release Notes

    Description

      Under semantic-equivalent rewriting, extracting a grouped aggregate into a view causes a stable row-count mismatch on MariaDB.
      The source and mutated queries are logically equivalent, but produce different results.

      CREATE TABLE t3 (c1 INT, c6 INT, c14 SET('a'));
      INSERT INTO t3 VALUES (1, 1, 'a');
       
      -- Source Original SQL
      SELECT SUM(DISTINCT c1) AS col_1
      FROM t3
      GROUP BY c1, c6
      HAVING MIN(c14) >= 1
      ORDER BY c6;
       
      -- View SQL
      CREATE VIEW v AS
      SELECT SUM(DISTINCT c1) AS col_1, c6
      FROM t3
      GROUP BY c1, c6
      HAVING MIN(c14) >= 1;
       
      -- Mutated SQL
      SELECT col_1 FROM v ORDER BY c6;
      

      Observed result - Query A (original):

      col_1
      1

      Observed result - Query B (mutated):

      Empty set

      Attachments

        Issue Links

          Activity

            People

              shipjain Shipra Jain
              pyuan Peiyuan Liu
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.