[MDEV-28570] Select from view fails if definition of view has 'HAVING' 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 if definition of view has 'HAVING' in query.

Test:

CREATE TABLE t1 (a int, b int);
 
CREATE TABLE t2 (c int, d int);
 
 
 
INSERT INTO t1 VALUES
 
  (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
 
INSERT INTO t2 VALUES
 
  (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
 
 
 
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
 
SELECT * FROM t2;
 
 
 
CREATE VIEW v1 as SELECT a FROM t1 GROUP BY a
 
  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
 
 
 
SELECT * FROM  v1;
 
 
 
DROP VIEW v1;
 
DROP TABLE t1,t2;

Actual result:

mysqltest: At line 18: query 'SELECT * FROM  v1' failed: 1356: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Expected result:

a
 
2
 
4



 Comments   
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 a FROM t1 GROUP BY a   HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20))
 
    -> SELECT * FROM cte;
 
+------+
 
| a    |
 
+------+
 
|    2 |
 
|    4 |
 
+------+
 
2 rows in set (0.002 sec)

Comment by Igor Babaev [ 2023-03-07 ]

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

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