Details
Description
When the definition of view contains subselect with aggregation function COUNT() that returns more 1 row it has to fail with error ER_SUBQUERY_NO_1_ROW (1242) (or smth. like that ) but it doesn't
Test:
CREATE TABLE t1 (a INTEGER, b INTEGER); |
CREATE TABLE t2 (x INTEGER); |
INSERT INTO t1 VALUES (1,11), (2,22), (2,22); |
INSERT INTO t2 VALUES (1), (2); |
|
CREATE VIEW v1 AS SELECT (SELECT COUNT(b) FROM t2) FROM t1; |
SELECT * from v1; |
|
DROP VIEW v1; |
DROP TABLES t1,t2; |
Actual result:
(SELECT COUNT(b) FROM t2) |
2
|
2
|
2
|
Using EXPLAIN EXTENDED it is seen the difference between:
original select:
EXPLAIN EXTENDED SELECT (SELECT COUNT(b) FROM t2) FROM t1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 |
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 |
Warnings:
|
Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 |
Note 1981 Aggregate function 'count()' of SELECT #2 belongs to SELECT #1 |
Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`b`,count(`test`.`t1`.`b`),count(`test`.`t1`.`b`)>((/* select#2 */ select count(`test`.`t1`.`b`) from `test`.`t2`)) AS `(SELECT COUNT(b) FROM t2)` from `test`.`t1` |
and select from view:
CREATE VIEW v1 AS SELECT (SELECT COUNT(b) FROM t2) FROM t1; |
EXPLAIN EXTENDED SELECT * from v1; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 |
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 |
Warnings:
|
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #2 |
Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`b`>((/* select#3 */ select count(`test`.`t1`.`b`) from `test`.`t2`)) AS `(SELECT COUNT(b) FROM t2)` from `test`.`t1` |
Attachments
Issue Links
- duplicates
-
MDEV-30706 Different results of selects from view and CTE with same definition
- Closed
- is part of
-
MDEV-27691 make working view-protocol
- Open