[MDEV-19337] CTEs not working with outer references - Jira

XML

Word

Printable

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.

duplicates

MDEV-10061 Subquery with CTE does not see tables from the outer query

relates to

MDEV-19078 Support lateral derived tables