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 produces an ASCII-art image of the Mandelbrot set written entirely in SQL 2008 conforming SQL.
-- Based on: https://wiki.postgresql.org/wiki/Mandelbrot_set
WITH RECURSIVE x(i) AS (
SELECT CAST(0 AS DECIMAL(13, 10))
SELECT i + 1
WHERE i < 101
Z(Ix, Iy, Cx, Cy, X, Y, I) AS (
SELECT Ix, Iy, X, Y, X, Y, 0
FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X,
i AS Ix FROM x) AS xgen
CROSS JOIN (
SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y,
i AS iY FROM x
) AS ygen
SELECT Ix, Iy, Cx, Cy,
CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X,
CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1
WHERE X * X + Y * Y < 16.0
AND I < 27
Zt (Ix, Iy, I) AS (
SELECT Ix, Iy, MAX(I) AS I
GROUP BY Iy, Ix
ORDER BY Iy, Ix
' .,,,-----++++%%%%@@@@#### ',
) ORDER BY Ix SEPARATOR ''
) AS 'Mandelbrot Set'
GROUP BY Iy
ORDER BY Iy;
The code is best run on the new MySQL Shell or MySQL Workbench but works well on the old MySQL shell but with desegregated output.
|An abbreviated image of the Mandelbot SQL output (See above for listing) , truncated for size. Produced with the new MySQL Shell (mysqlsh) on MySQL 8.0.13|
But then Jesper mention he had tested the SQL the night before the lab and it runs quickly on MySQL - 0.7445 seconds on my Windows laptop.
|The Mandelbrot SQL code ran in 0.74445 seconds on MySQL 8.0.13|
But not on MariaDB. Jesper said he ran the same code on MariaDB 10.3 but killed it after fifteen minutes. It was late and he had to get up early to get to San Francisco.
With a fresh install of Fedora 29 and MariaDB 10.3.10, I ran the Mandelbrot SQL code. And I waited for the result. After an hour I went to lunch. But the query was still running when I returned. I went on to other work an occasionally checking back and running SHOW PROCESSLIST from time to time to make sure it had not died.
But after two hours I hit control-C as I had other tasks for that system. There are some interesting Recursive CTE problems listed on Jira,MariaDB.org but nothing obviously relevant.
But I was able to confirm that MySQL's implementation of Recursive CTEs works well but I can not say that about MariaDB's implementation.