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

CREATE VIEW extraction causes row loss in CROSS JOIN + GROUP BY/HAVING query

    XMLWordPrintable

Details

    Description

      Under semantic-equivalent rewriting, extracting the filtered `t3` relation into a view causes a stable row-count mismatch.
      The source and mutated queries are logically equivalent, but produce different results.

      How to repeat:

      CREATE TABLE t2 (
        c1 INT,
        c7 CHAR(1),
        c8 BINARY(1),
        c11 SET('a','b','c','d')
      );
      CREATE TABLE t3 (
        c2 INT,
        c3 INT
      );
      INSERT INTO t2 VALUES (3580, 'x', X'01', 'a,b,d');
      INSERT INTO t3 VALUES (6342, 3580);
       
      -- Source Original SQL
      SELECT COUNT(DISTINCT t2.c8) AS col_1 FROM t2 AS t2 CROSS JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE NOT t3.c2 IS NULL GROUP BY t3.c2, t2.c7 HAVING (MIN(t2.c11) >= 6) ORDER BY t2.c7 DESC;
       
      -- View SQL
      CREATE VIEW V_t3_7e0b8e91 AS
      SELECT * FROM t3 AS t3 WHERE (t3.c2 IS NOT NULL);
       
      -- Mutated SQL
      SELECT COUNT(DISTINCT t2.c8) AS col_1 FROM t2 CROSS JOIN V_t3_7e0b8e91 ON (V_t3_7e0b8e91.c3 = t2.c1) GROUP BY t2.c7, V_t3_7e0b8e91.c2 HAVING MIN(t2.c11) >= 6 ORDER BY t2.c7 DESC;
      

      Observed result - Query A (original):

      col_1
      1

      Observed result - Query B (mutated):

      Empty set

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              pyuan Peiyuan Liu
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.