Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.4.4
-
Fix Version/s: N/A
-
Component/s: Optimizer - CTE
-
Labels:None
-
Environment:Ubuntu 18.04.1
Description
Common table expressions don't work with outer references.
Consider a recursive parent-child relationship between users:
CREATE TABLE `users` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`parent_id` int(10) unsigned DEFAULT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
This query selects all users with more than 10 descendants:
SELECT * |
FROM `users` |
WHERE ( |
WITH RECURSIVE `cte` AS ( |
SELECT * FROM `users` AS `alias` WHERE `alias`.`parent_id` = `users`.`id` |
UNION ALL |
SELECT `users`.* FROM `users` INNER JOIN `cte` ON `cte`.`id` = `users`.`parent_id` |
)
|
SELECT count(*) FROM `cte` |
) > 10
|
The query fails with an error:
Unknown column 'users.id' in 'where clause'
The query works on MySQL 8 (since 8.0.14), PostgreSQL, SQLite and SQL Server.
Attachments
Issue Links
- duplicates
-
MDEV-10061 Subquery with CTE does not see tables from the outer query
-
- Closed
-
- relates to
-
MDEV-19078 Support lateral derived tables
-
- Open
-