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
-
-
-
- Closed
-
-
-
- Open
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
If we look at EXPLAIN output for the reported queries
MariaDB [test]> EXPLAIN-> SELECT * FROM v;+------+--------------------+-------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+-------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t4.b | 1 | Using index || 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort || 3 | DEPENDENT SUBQUERY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.b | 1 | Using index |+------+--------------------+-------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+4 rows in set (0.001 sec)MariaDB [test]> EXPLAIN-> 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;+------+--------------------+------------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+------------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | || 2 | DERIVED | t4 | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t4.b | 1 | Using index || 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort || 3 | DEPENDENT SUBQUERY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.b | 1 | Using index |+------+--------------------+------------+--------+---------------+---------+---------+-----------+------+----------------------------------------------+5 rows in set (0.001 sec)we see that for the query with CTE the table cte is materialized while for the other query the view v is not materialized and considered as mergeable. The specification of view is a select with implicit grouping. With the current merging technique such select is not considered as mergeable because it requires creation of Item_ref objects for set functions aggregated in this select.