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
- is duplicated by
-
MDEV-28573 View has to fail with error ER_SUBQUERY_NO_1_ROW (1242) but it doesn't
- Closed
-
MDEV-29224 Wrong result with aggregate function in uncorrelated SELECT subquery in view definition
- Closed
-
MDEV-29573 Node crashes with Error: Certification exception: Attempt to reuse the same seqno
- Open
- relates to
-
MDEV-31305 Crash caused by query with aggregation over materialized derived
- Closed