[MDEV-28571] Select from view fails if definition of view has aggregate function ( like 'GROUP_CONCAT(COUNT(a))' ) in query Created: 2022-05-16  Updated: 2023-03-07  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
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: Critical
Reporter: Lena Startseva Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: view-protocol

Issue Links:
Duplicate
duplicates MDEV-30668 Set function aggregated in outer sele... Closed
PartOf
is part of MDEV-27691 make working view-protocol Open

 Description   

Select from view fails with error "Invalid use of group function" if definition of view has 'GROUP_CONCAT(COUNT(a))' in query.

Test:

CREATE TABLE t1 (a int, b int);
 
CREATE TABLE t2 (m int, n int);
 
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
 
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
 
 
 
CREATE VIEW v1 AS SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
 
  FROM t1 GROUP BY a;
 
 
 
SELECT * FROM v1;
 
 
 
DROP VIEW v1;
 
DROP table t1,t2;

Actual result:

At line 9: query 'SELECT * FROM v1' failed: 1111: Invalid use of group function

Expected result:

c
 
2
 
3
 
1,1



 Comments   
Comment by Lena Startseva [ 2022-05-16 ]

The same problem with MAX:
Test:

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
 
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
 
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
 
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
 
 
 
CREATE VIEW v1 AS SELECT tt.a, MAX(
 
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 
  LIMIT 1)
 
  FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
 
  FROM t1 as tt GROUP BY tt.a;
 
 
 
SELECT * FROM v1;
 
 
 
DROP VIEW v1;
 
DROP TABLE t1;

Actual result:

At line 12: query 'SELECT * FROM v1' failed: 1111: Invalid use of group function

Expected result:

a	test
 
1	n
 
2	o
 
3	p

Comment by Igor Babaev [ 2023-02-26 ]

Note that if we use CTE with the same specification query as for the view we get the expected result

MariaDB [test]> WITH cte AS
 
    -> (SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
 
    -> FROM t1
 
    -> GROUP BY a)
 
    -> SELECT * FROM cte;
 
+------+
 
| c    |
 
+------+
 
| 2    |
 
| 3    |
 
| 1,1  |
 
+------+
 
3 rows in set (0.007 sec)

Comment by Igor Babaev [ 2023-03-07 ]

The reported test case has been added in patch for MDEV-30668

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