Here is the third in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release. In the first post we had explored the new SQL syntax, and in the second we had applied it to generating series.…
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.
[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.
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:
- 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.…
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:
create table sales (month int, day int, amount int);
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.…
After seeing that several blogs discuss storage of UUID values into MySQL, and that this topic is recurrent on forums, I thought I would compile some sensible ideas I have seen, and also add a couple new ones.
Say we have a table of users, and each user has a UUID.…
This is a follow-up post to my recent announcement of
only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).
In the SQL standard, GROUP BY should contain only table columns and never expressions.…
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.
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.…