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

Different results of selects from view and CTE with same definition

    XMLWordPrintable

Details

    Description

      Different results can be observed for selects from a view and a CTE with the same specification as for the view:

      MariaDB [test]> CREATE VIEW v AS
          -> SELECT
          ->   (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
          -> FROM t3, t4
          ->   WHERE t3.a = t4.b;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [test]> SELECT * FROM v;
      +------+
      | m    |
      +------+
      |    2 |
      |    7 |
      +------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> WITH cte AS (
          -> SELECT
          ->   (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
          -> FROM t3, t4
          ->   WHERE t3.a = t4.b
          -> )
          -> SELECT * FROM cte;
      +------+
      | m    |
      +------+
      |    9 |
      +------+
      1 row in set (0.001 sec)
      

      This can be demonstrated with the following test case:

      CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,1), (2,2);
      CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1,1), (3,3);
      CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (2,2), (4,4), (7,7);
      CREATE TABLE t4 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM;
      INSERT INTO t4 VALUES (2,2), (5,5), (7,7);
       
      CREATE VIEW v AS
      SELECT
        (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
      FROM t3, t4
        WHERE t3.a = t4.b;
       
      SELECT * FROM v;
       
      WITH cte AS (
      SELECT
        (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
      FROM t3, t4
        WHERE t3.a = t4.b
      )
      SELECT * FROM cte;
       
      DROP VIEW v;
      DROP TABLE t1,t2,t3,t4;
      

      Note that the first select returns a wrong result.
      The specification select returns the expected result

      MariaDB [test]> SELECT
          ->   (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
          -> FROM t3, t4
          ->   WHERE t3.a = t4.b;
      +------+
      | m    |
      +------+
      |    9 |
      +------+
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              igor Igor Babaev
              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.