[MDEV-19337] CTEs not working with outer references Created: 2019-04-25  Updated: 2023-02-28  Resolved: 2019-04-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.4.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jonas Staudenmeir Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 18.04.1

Issue Links:
Duplicate
duplicates MDEV-10061 Subquery with CTE does not see tables... Closed
is duplicated by MDEV-30744 WITH AS clause doesn't work with an o... Closed
Relates
relates to MDEV-19078 Support lateral derived tables Open

 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.

 Comments   
Comment by Alice Sherepa [ 2019-04-26 ]

Currently, Mariadb does not allow outer references in derived tables and CTE. If you'd like to follow the progress, please, watch task MDEV-19078

Generated at Thu Feb 08 08:50:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.