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.
SQLite, another popular open-source SQL database, just released version 3.8.3, including support for recursive SQL queries using the WITH RECURSIVE syntax, in compliance with SQL:1999.
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.
About 8-9 years ago when I still was with MySQL there was a patch submitted by community user to implement CONNECT BY in MySQL. Decision was taken not to do that as we would get the “proper implementation according to standard soon”
Great example of great being enemy of good.
Please lets get this feature. Then all the PostgreSQL guys can stop bringing this up when they talk about how much better it is then MySQL.
Another feature which missing is WINDOW functions.
PostgreSQL has them too.
I filed a feature request for this some time ago (Now marked as duplicate) and I had one customer ask me about this last week. It would be a really nice feature to have.
Let’s hear it for trees and hierarchies! I find I need to express such things OFTEN, and it is a royal pain in MySQL.
Yes it would be very nice if MySQL had WITH RECURSIVE. Meanwhile, one can try the workaround below. It gives a reusable stored procedure (create it once, use it for all your various WITH RECURSIVE queries):
http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html
I have validated it with several classical WITH examples gathered over the web. Subclauses of the WITH RECURSIVE standard syntax translate to arguments of the procedure.
Vadim, yes, MySQL lacks window functions. This has also been a long-standing feature request, since April 2008: http://bugs.mysql.com/bug.php?id=35893
I haven’t done similar research to find out which brands of SQL RDBMS support window functions, or when they added support, but I bet it’s similar to the list I showed above for CTE support.
Around 2004 MySQL publicly declared an intent to support CONNECT BY, not WITH, as you can see from ancient copies of the manual http://teaching-2.cs.uml.edu/MySQLdocs/MySQL_Manual/TODO_future.html.
Hi Peter, yes PZ had commented about CONNECT BY earlier. That’s interesting history, but I would think that using the CTE methods should be preferred today, for widest compatibility with other vendors.
Falcon was the code name of the planned version 6 before the Oracle acquisition. Falcon was supporting CTEs exactly the same way as MS-SQL in practice (I still have one alpha version).
I don’t believe that Oracle will use Falcon code or any other code for CTEs (at least in the near future), for the same reason they will keep views in “bad” performance mode. It’s just a matter of balance between free open source and expensive commercial dbs. If Oracle managers decide to shoot their own knees, they must have a very good reason.