1# 2# MDEV-20751: query using many CTEs with grant_tables enabled 3# 4connection default; 5CREATE DATABASE db; 6USE db; 7CREATE TABLE t1 (a int) ENGINE=MYISAM; 8INSERT INTO t1 VALUES (3), (7), (1); 9CREATE TABLE t2 (a int) ENGINE=MYISAM; 10INSERT INTO t2 VALUES (2), (8), (4); 11CREATE USER 'u1'@'localhost'; 12GRANT USAGE ON db.* TO 'u1'@'localhost'; 13GRANT SELECT ON db.t1 TO 'u1'@'localhost'; 14FLUSH PRIVILEGES; 15connect u1,'localhost',u1,,; 16connection u1; 17USE db; 18WITH 19cte1 AS 20(SELECT a FROM t1), 21cte2 AS 22(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a), 23cte3 AS 24(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a), 25cte4 AS 26(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a) 27SELECT * FROM cte4 as r; 28a 293 307 311 32WITH 33cte1 AS 34(SELECT a FROM t2), 35cte2 AS 36(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a), 37cte3 AS 38(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a), 39cte4 AS 40(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a) 41SELECT * FROM cte4 as r; 42ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2' 43disconnect u1; 44connection default; 45DROP USER 'u1'@'localhost'; 46DROP DATABASE db; 47USE test; 48# End of 10.2 tests 49