Common Table Expressions (CTEs) are a very useful tool and
frankly a big improvement on sub-queries. But there are
differences in how they are implemented in MySQL and
MariaDB. That is not too surprising since the code
fork many years ago. Different engineers implementing the same
idea will have different approaches (and sometimes results). But
differences in implementation are often important and, in this
case, shockingly different.
Jesper Wisborg Krogh at Oracle OpenWorld and CodeOne gave a
series of presentations and hands on labs that were excellent. He
is an amazing Support Engineer and a great presenter of material
at conferences. In the lab for Common Table Expressions he
did point out to me an interesting problem in MariaDB's
implementation of CTEs.
The Problem In a Nutshell
On the PostgreSQL Wiki, there is a
an SQL query (requires PostgreSQL 8.4 or MySQL 8.0) that …
Occasionally at conference or a Meetup, someone will approach me
and ask me for help with a MySQL problem. Eight out of ten
times their difficulty includes a sub query. "I get an error
message about a corrugated or conflabugated sub query or some
such," they say, desperate for help. Usually with a
bit of fumbling we can get their problem solved. The
problem is not a lack of knowledge for either of us but that sub
queries are often hard to write.
MySQL 8 will be the first version of the most popular database on
the web with Common Table Expressions or CTEs. CTEs are a
way to create temporary tables and then use that temporary table
for queries. Think of them as easy to write sub queries!
WITH is The Magic WordThe new CTE magic is indicated with the
WITH clause.
mysql> WITH myfirstCTE[Read more]
AS (SELECT * FROM world.city WHERE …
There are two new MySQL books both from Apress Press. One is an
in depth master course on the subject and the other is a quick
introduction.
ProMySQL NDB Cluster is subtitled Master the MySQL
Cluster Lifecycle and at nearly 700 pages it is vital
resource to anyone that runs or is thinking about running NDB
Cluster. The authors, Jesper Wisborg Krogh and Mikiya Okuno, have
distilled their vast knowledge of this difficult subject in a
detail packed but easily readable book. MySQL Cluster is
much more complex in many areas than a regular MySQL server and
here you will find all those details. If you run MySQL NDB
Cluster then you need this book. The partitioning information in
chapter 2 is worth the price of the book alone. I am only a
third of the way …
