[MDEV-30706] Different results of selects from view and CTE with same definition Created: 2023-02-22  Updated: 2024-02-01  Resolved: 2023-03-07

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-28573 View has to fail with error ER_SUBQUE... Closed
is duplicated by MDEV-29224 Wrong result with aggregate function... Closed
is duplicated by MDEV-29573 Node crashes with Error: Certificatio... Open
Relates
relates to MDEV-31305 Crash caused by query with aggregatio... Closed

 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 |
 
+------+



 Comments   
Comment by Igor Babaev [ 2023-02-24 ]

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.

Comment by Oleksandr Byelkin [ 2023-03-02 ]

OK to push

Generated at Thu Feb 08 10:18:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.