All posts by Guilhem Bichot

MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Two – how to generate series

Here is the second in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release. Here is the first post; there is also a third post. The first one ended with:

Inside the recursive CTE definition (the part in AS (…)), some syntax constraints must be respected […]

  • a recursive SELECT mustn’t contain GROUP BY, aggregate functions
    (like SUM), ORDER BY, LIMIT, DISTINCT (this rule doesn’t apply to the non-recursive/anchor/seed SELECT)
  • a recursive SELECT must reference the CTE only once and only in its
    FROM clause, not in any subquery.

MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs)

[Note: this is the first post in a series; there is also a second post].

The MySQL development team just published a Labs release of the MySQL Server (available under “MySQL Server 8.0.0 Optimizer”).
A prominent feature of this release, which I developed, is [Recursive] Common Table Expressions, also known as

  • [recursive] CTE,
  • [recursive] subquery factoring,
  • WITH [RECURSIVE] clause.

New and old ways to emulate CHECK constraints, DOMAIN

Correctness of data comes in different forms. One is referential integrity, also known as foreign keys. Another is CHECK constraints. While MySQL supports foreign keys, it doesn’t support CHECK constraints. Today we will look at three ways to emulate them:

  • triggers
  • views
  • generated columns (new in MySQL 5.7)

This is also relevant to another SQL feature: DOMAIN, which, in short, is a user-defined type composed of a base type (INT, CHAR, …), a default value, and a constraint on acceptable values – the latter being some simple sort of CHECK constraint.…

Using the aggregate functions ANY, SOME, EVERY with MySQL

If you have used SQL a bit, you are certainly familiar with so-called set functions or aggregate functions COUNT, SUM, AVG, described in the manual. For example, let’s say that I am the owner of a shop and I keep track of daily sales in this table:

The first column is the number of the month, between 1 and 12, the second column is the number of the day in the month, between 1 and 31, and the third column is how much we sold on that date.…

Re-factoring some internals of prepared statements in 5.7

When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables.

WITH RECURSIVE and MySQL

If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called “WITH clause” of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression. A form of the WITH CLAUSE, WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration.…