WITHer Recursive Queries?Bill Karwin
Over the past few years, we’ve seen MySQL technology advance in leaps and bounds, especially when it comes to scalability. But by focusing on the internals of the storage engine for so long, MySQL has fallen behind regarding support for advanced SQL features.
Why is this significant? It means that MySQL is now the only widely-used SQL implementation that does not support recursive queries. Fifteen years after it was defined in the SQL standard, almost every other SQL database of note has supported this feature:
- IBM DB2 UDB 8 (Dec. 2002)
- Microsoft SQL Server 2005 (Oct. 2005)
- Sybase SQL Anywhere 11 (Aug. 2008)
- Firebird 2.1 (Sep. 2008)
- PostgreSQL 8.4 (Jul. 2009)
- Oracle 11g release 2 (Sep. 2009)
- HSQLDB 2.3 (Jul. 2013)
- Teradata (date and version of support unknown, at least 2009)
- H2 (date and version of support unknown)
Only Informix among common RDBMS brands lacks support for WITH RECURSIVE, though Informix still supports recursive queries with the non-standard CONNECT BY syntax.
MySQL has been requested to support common table expressions using the WITH syntax for a long time:
- Bug #16244 SQL-99 Derived table WITH clause (CTE) (non-recursive form), January 2006
- Bug #20712 Please implement “with” statement (recursive form), June 2006
The CTE-style queries would allow us to share more advanced SQL queries with those that are being used by other brands of database, and do it with standard SQL instead of proprietary functions or tricks.
The most common example of a query solved with a recursive CTE is to query a tree of unknown depth. But there are quite a few other useful applications of this form of query, all the way up to fancy stunts like a query that generates a fractal design like the Mandelbrot Set. Recursion is powerful.
Is it time for the MySQL community to raise the priority of the CTE feature requests for MySQL? Visit the links I gave above at bugs.mysql.com, and add your voice by clicking the Affects Me button.