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 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))

UNION ALL

SELECT i + 1

FROM x

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

UNION ALL

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

FROM Z

WHERE X * X + Y * Y < 16.0

AND I < 27

),

Zt (Ix, Iy, I) AS (

SELECT Ix, Iy, MAX(I) AS I

FROM Z

GROUP BY Iy, Ix

ORDER BY Iy, Ix

)

SELECT GROUP_CONCAT(

SUBSTRING(

' .,,,-----++++%%%%@@@@#### ',

GREATEST(I, 1),

1

) ORDER BY Ix SEPARATOR ''

) AS 'Mandelbrot Set'

FROM Zt

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.


Common Table Expressions: A Shocking Difference Between MySQL and MariaDB
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.


Common Table Expressions: A Shocking Difference Between MySQL and MariaDB
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.

Double Check

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.

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:Common Table Expressions: A Shocking Difference Between MySQL and MariaDB
本站链接:https://www.codesec.net/view/611308.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(83)