MySQL Bugs: #16244: SQL-99 Derived table WITH clause (CTE)

Bug #16244	SQL-99 Derived table WITH clause (CTE)
Submitted:	6 Jan 2006 5:22	Modified:	8 Dec 2017 16:25
Reporter:	Stuart Friedberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
I've just run into a query situation where the SQL-99 WITH clause for giving a derived a table a re-usable alias would have been the ideal solution.  Since temporary tables can't currently be used more than once within a query, that workaround is unavailable.

How to repeat:
N/A for a feature request

Thank you for a reasonable feature request.

Valeriy, you guys must have an unbelieveable backlog.  Thirty three months between filing a request and getting a first acknowledgement is a jaw-dropping length of time.  Thank you for considering the request.

Bug#20712 was marked as a duplicate of this one.

Bug #48610 was marked as a duplicate of this one.

Bug #23156 was marked as a duplicate of this one.

Not all the feature requests which were marked
"duplicate" say the same thing.
This one (Bug#16244) and Bug#23156 appear to be
about the basic non-recursive WITH ... SELECT.
Bug#20712 and Bug#48610 mention "recursive" or
"hierarchical" queries; that's an additional step.

This method (recursive CTEs) was shown off at PHPConference2010, London. Any news on if and when MySQL gets it?

Bug #60940 was marked as a duplicate of this one.

I am currently implementing breeding records for livestock, and am finding it impossible to do arbitrary blood-line reports. Proper implementation of SQL:1999 WITH RECURSIVE would allow me to do so!

Thanks for all your hard work!

This feature should really be implemented.

I don't know who makes decisions about feature set for MySQL, but as a developer with over 20 years of experience, I don't understand why the CTE support continues to be ignored. It just doesn't make any sense, particularly when I understand a reasonable implementation was provided by an open source developer, and it was not included. 

Please implement this feature as a matter of priority.

It's true that it's not yet implemented, and that the workaround of using CREATE TEMPORARY TABLE doesn't work well because one can't reference such table more than once in the FROM clause of the query. Yet another workaround, is to do CREATE VIEW; a view can be referenced more than once.
For what it's worth, I had written a stored procedure which emulates WITH RECURSIVE, which is more complex than non-recursive WITH. Available here:
http://guilhembichot.blogspot.fr/2013/11/with-recursive-and-mysql.html

The lack of Common Table Expressions and recursion is a great hindrance in moving to the MySQL platform. Please implement this ASAP!

+1. 

If SQLite can do this, MySQL should be able to!

This is planned for 8.0.

https://www.percona.com/blog/2016/09/01/percona-live-europe-featured-talk-manyi-lu-mysql-8...

A labs release of CTEs is available here:
http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes...

This is in 8.0.3 at least. Time to clone this bug?

Posted by developer:
 
Recursive CTEs are in MySQL 8.0.1 and newer.