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:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: Guilhem Bichot CPU Architecture:Any

[6 Jan 2006 5:22] Stuart Friedberg
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
[7 Oct 2008 13:21] Valeriy Kravchuk
Thank you for a reasonable feature request.
[7 Oct 2008 19:57] Stuart Friedberg
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.
[1 Oct 2009 22:23] Peter Gulutzan
Bug#20712 was marked as a duplicate of this one.
[7 Nov 2009 9:16] Valeriy Kravchuk
Bug #48610 was marked as a duplicate of this one.
[7 Nov 2009 9:22] Valeriy Kravchuk
Bug #23156 was marked as a duplicate of this one.
[7 Nov 2009 16:16] Peter Gulutzan
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.
[3 Mar 2010 10:04] Andrew Riddlestone
This method (recursive CTEs) was shown off at PHPConference2010, London. Any news on if and when MySQL gets it?
[21 Apr 2011 7:32] Valeriy Kravchuk
Bug #60940 was marked as a duplicate of this one.
[7 Mar 2014 16:40] Jan Steinman
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!
[4 May 2014 22:39] John Dillinger
This feature should really be implemented.
[12 Jan 2015 17:09] Gavin Glynn
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.
[15 Apr 2015 8:28] Guilhem Bichot
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:
[9 Sep 2015 20:12] K Miesse
The lack of Common Table Expressions and recursion is a great hindrance in moving to the MySQL platform. Please implement this ASAP!
[22 Feb 2016 2:51] Rob Lewis

If SQLite can do this, MySQL should be able to!
[13 Sep 2016 18:52] Daniël van Eeden
This is planned for 8.0.

[29 Sep 2016 14:15] Morgan Tocker
A labs release of CTEs is available here:
[8 Dec 2017 14:05] Daniël van Eeden
This is in 8.0.3 at least. Time to clone this bug?
[8 Dec 2017 16:25] Guilhem Bichot
Posted by developer:
Recursive CTEs are in MySQL 8.0.1 and newer.