Date arithmetic with Julian days, BC dates, and Oracle rules

Here are routines that can handle date arithmetic on BC dates, Julian day functions, and simulation of Oracle’s support of old-style-calendar dates — including simulation of an Oracle bug. So the routines are good for extending the range of useable dates, compact storage, and import/export between DBMSs that have different rules.

If you need to refresh your understanding of dates, read our old-but-lovely article first: The Oracle Calendar.

I wrote the main routines with standard SQL so they should run on
any DBMS that supports the standard, but tested only with
MySQL and MariaDB. (UPDATE on 2019-02-04: corrections were needed with HSQLDB, see the comments.)


ocelot_date_to_julianday
Return number of days since 4713-01-01, given yyyy-mm-dd [BC] date
ocelot_date_validate
Return okay or error, given yyyy-mm-dd BC|AD date which may be invalid
ocelot_date_datediff
Return number of days difference, given two yyyy-mm-dd [BC] dates
ocelot_date_test
Return ‘OK’ after a thorough test of the entire range of dates

All function arguments look like this:
yyyy-mm-dd [BC] … CHAR|VARCHAR. yyyy-mm-dd is the standard date format for year and month and date, optionally followed by a space and ‘BC’. If ‘BC’ is missing, ‘AD’ is assumed. Must be between 4713-01-01 BC and 9999-12-31 for Julian-calendar dates, or between 4714-11-24 BC and 9999-12-31 for Gregorian-calendar dates. Routines will return bad results if dates are invalid, if there is any doubt then run ocelot_date_validate() first.
julian_day … INTEGER. For an explanation of what a “Julian day number” is, see Wikipedia. Do not confuse with “Julian-calendar date” — the name is similar but Julian days can be converted to or from dates in the Gregorian calendar too. Must be between 0 (which is 4713-01-01 BC) and a maximum (which is 9999-12-31).
‘J’ or ‘G’ or ‘O’ … CHAR. This is an “options” flag. ‘J’ means use the Julian (old style) calendar. ‘G’ means use the Gregorian (new style) calendar.’O’ means use the Oracle rules, which we described in the earlier article. If options is not ‘J’ or ‘G’ or ‘O’, ‘G’ is assumed.



Example expressions:

#1 ocelot_date_to_julianday(‘0001-01-01′,’G’) returns 1721426
#2 ocelot_date_to_julianday(‘0001-01-01′,’J’) returns 1721424
#3 ocelot_date_to_julianday(‘4712-01-01 BC’, ‘O’) returns 0
#4 ocelot_date_datediff(‘0001-01-01′,’0001-01-01 BC’,’G’) returns 366
#5 ocelot_date_to_julianday(‘1492-10-12′,’J’)%7; returns 4
/* Explanations: #3 returns 0 because there’s a year 0000,
#4 returns 366 because 0001 BC is a leap year,
#5 returns weekday = 4 for the original Columbus Day
and he used a Julian calendar. */

The source code

The code is original but the general idea is not — I gratefully acknowledge Peter Baum’s 1998 article “Date Algorithms”.

I use the Ocelot GUI (ocelotgui) when I write routines for MySQL/MariaDB. Since it recognizes all their syntax quirks it can give me hints when I’m typing something wrong, and saves me from the hassles of “delimiter”. And it has a debugger. Version 1.0.8 was released yesterday for download via github.

I start with a standard 2-clause BSD license and then show the CREATE statements for each routine. To install: just cut-and-paste what follows this paragraph until the end of this section. If you are not using ocelotgui you will have to say DELIMITER // and put // at the end of each CREATE statement.

/*
Copyright (c) 2019 Ocelot Computer Services Inc.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

*/

/*
ocelot_date_to_julianday(yyyy-mm-dd[ BC], J|G|O) Return number of days
————————
If J: will return 0 for ‘4713-01-01 BC’, all calculations use Julian calendar
If G: will return 0 for ‘4714-11-24 BC’, all calculations use Gregorian calendar
If O: will return 0 for ‘4712-01-01 BC’, switch between calendars after 1582-10-04
*/
CREATE FUNCTION ocelot_date_to_julianday(in_date VARCHAR(25), options CHAR(1)) RETURNS DECIMAL(8)
LANGUAGE SQL DETERMINISTIC CONTAINS SQL
BEGIN
DECLARE year, month, day, century, leap INT;
DECLARE jd DOUBLE PRECISION;
DECLARE bc_as_char CHAR(2);
SET year = CAST(SUBSTRING(in_date FROM 1 FOR 4) AS DECIMAL(8));
SET month = CAST(SUBSTRING(in_date FROM 6 FOR 2) AS DECIMAL(8));
SET day = CAST(SUBSTRING(in_date FROM 9 FOR 2) AS DECIMAL(8));
SET bc_as_char = SUBSTRING(in_date FROM CHAR_LENGTH(in_date) – 1 FOR 2);
IF bc_as_char = ‘BC’ THEN
IF options = ‘O’ THEN SET year = 0 – year;
ELSE SET year = (0 – year) + 1; END IF;
END IF;
IF month <= 2 THEN SET year = year - 1; SET month = month + 12; END IF; IF options = 'G' OR (options = 'O' AND in_date >= ‘1582-10-05’ AND bc_as_char <> ‘BC’) THEN
SET century = FLOOR(year / 100.0);
SET leap = 2 – century + FLOOR(century / 4.0);
ELSE
SET leap = 0;
END IF;
SET jd = FLOOR(365.25 * (year + 4716)) + FLOOR(30.6001 * (month + 1)) + day + leap – 1524;
RETURN CAST(jd AS DECIMAL(8));
END;

/*
ocelot_date_validate (yyyy-mm-dd[ BC] date, J|G|O) Return ‘OK’ or ‘Error …’
——————–
Possible errors:
Format of first parameter is not ‘yyyy-mm-dd’ or ‘yyyy-mm-dd BC’.
Second parameter is not ‘J’ or ‘G’ or ‘O’.
Minimum date = 4713-01-01 BC if J, 4712-01-01 BC if O, 4714-11-14 BC if G.
Maximum date = 9999-12-31.
If ‘O’: 0001-mm-dd BC, or between 1582-10-05 and 1582-10-14.
nnnn-02-29 if nnnn is not a leap year.
Month not between 1 and 12.
Day not between 1 and maximum for month.
Otherwise return ‘OK’.
*/
CREATE FUNCTION ocelot_date_validate(in_date VARCHAR(25), options CHAR(1)) RETURNS VARCHAR(50)
LANGUAGE SQL DETERMINISTIC CONTAINS SQL
BEGIN
DECLARE year, month, day, leap_days DECIMAL(8);
DECLARE bc_or_ad VARCHAR(3) DEFAULT ”;
IF options IS NULL
OR (options <> ‘J’ AND options <> ‘G’ AND options <> ‘O’) THEN
RETURN ‘Error, Options must be J or G or O’;
END IF;
IF in_date IS NULL
OR (CHAR_LENGTH(in_date) <> 10 AND CHAR_LENGTH(in_date) <> 13)
OR SUBSTRING(in_date FROM 1 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 2 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 3 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 4 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 5 FOR 1) <> ‘-‘
OR SUBSTRING(in_date FROM 6 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 7 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 8 FOR 1) <> ‘-‘
OR SUBSTRING(in_date FROM 9 FOR 1) NOT BETWEEN ‘0’ AND ‘9’
OR SUBSTRING(in_date FROM 10 FOR 1) NOT BETWEEN ‘0’ AND ‘9’ THEN
RETURN ‘Error, Date format is not nnnn-nn-nn’;
END IF;
IF CHAR_LENGTH(in_date) = 13 THEN
SET bc_or_ad = SUBSTRING(in_date FROM 11 FOR 3);
IF bc_or_ad <> ‘ BC’ THEN
RETURN ‘Error, only space + BC is allowed after yyyy-mm-dd’;
END IF;
END IF;
SET year = CAST(SUBSTRING(in_date FROM 1 FOR 4) AS DECIMAL(8));
SET month = CAST(SUBSTRING(in_date FROM 6 FOR 2) AS DECIMAL(8));
SET day = CAST(SUBSTRING(in_date FROM 9 FOR 2) AS DECIMAL(8));
IF year = 0 THEN
RETURN ‘Error, year 0’;
END IF;
IF bc_or_ad = ‘ BC’ THEN
IF options = ‘J’ AND year > 4713 THEN
RETURN ‘Error, minimum date = 4713-01-01 BC’;
END IF;
IF options = ‘O’ AND year > 4712 THEN
RETURN ‘Error, minimum date = 4712-01-01 BC’;
END IF;
IF OPTIONS = ‘G’ THEN
IF year > 4714
OR (year = 4714 AND month < 11) OR (Year = 4714 AND month = 11 AND day < 24) THEN RETURN 'Error, minimum date = 4714-11-24 BC'; END IF; END IF; END IF; IF month = 0 OR month > 12 THEN RETURN ‘Error, month not between 1 and 12’; END IF;
SET leap_days = 0;
IF month = 2 AND day = 29 THEN
IF bc_or_ad = ‘ BC’ AND options <> ‘O’ THEN SET year = year – 1; END IF;
IF year % 4 = 0 THEN
IF options = ‘J’ OR (options = ‘O’ AND (bc_or_ad = ‘ BC’ OR SUBSTRING(in_date FROM 1 FOR 10) < '1582-10-04')) THEN SET leap_days = 1; ELSE IF year % 100 <> 0 OR year % 400 = 0 THEN
SET leap_days = 1;
END IF;
END IF;
END IF;
IF leap_days = 0 THEN RETURN ‘Error, February 29, not a leap year’; END IF;
END IF;
IF month = 1 AND day > 31
OR month = 2 AND day – leap_days > 28
OR month = 3 AND day > 31
OR month = 4 AND day > 30
OR month = 5 AND day > 31
OR month = 6 AND day > 30
OR month = 7 AND day > 31
OR month = 8 AND day > 31
OR month = 9 AND day > 30
OR month = 10 AND day > 31
OR month = 11 AND day > 30
OR month = 12 AND day > 31 THEN
RETURN ‘Error, day > maximum day in mnth’;
END IF;
IF options = ‘O’
AND bc_or_ad <> ‘ BC’
AND SUBSTRING(in_date FROM 1 FOR 10) BETWEEN ‘1582-10-05’ AND ‘1582-10-14’ THEN
RETURN ‘Error, Date during Julian-to-Gregorian cutover’;
END IF;
RETURN ‘OK’;
END;

/*
ocelot_date_datediff(date, date, J|G|O) Return number of days between two dates
——————–
Results for positive Gregorian will be the same as MySQL/MariaDB datediff().
This is an extension of datediff() which works with BC Gregorian and other calendars.
Mostly it’s just to show how easily a routine can be written if there is a
Julian-day function.
*/
CREATE FUNCTION ocelot_date_datediff(date_1 VARCHAR(25), date_2 VARCHAR(25), options CHAR(1)) RETURNS INT
LANGUAGE SQL DETERMINISTIC CONTAINS SQL
RETURN ocelot_date_to_julianday(date_1, options) – ocelot_date_to_julianday(date_2, options);

/*
ocelot_date_test(J|G|O) Test that all legal dates have the correct Julian day
—————-
You only need to run this once. The Julian day routine looks bizarre so this
test is here to give assurance that the ocelot_date_to_julianday function is okay.
Start with a counter integer = 0 and a yyyy-mm-dd BC date = the minimum for the calendar.
For each iteration of the loop, increment the counter and increment the date,
call ocelot_date_to_julianday and check that it returns a value equal to the counter.
Stop when date is 9999-12-31.
For Oracle emulation we do not check dates which are invalid due to cutover or bugs.
Bonus test: positive Gregorian dates must match MySQL|MariaDB datediff results.
Bonus test: check validity of each incremented date.
*/
CREATE FUNCTION ocelot_date_test(options CHAR(1)) RETURNS CHAR(50)
LANGUAGE SQL DETERMINISTIC CONTAINS SQL
BEGIN
DECLARE tmp VARCHAR(25);
DECLARE tmp_validity VARCHAR(50);
DECLARE year_as_char, month_as_char, day_as_char VARCHAR(25);
DECLARE year_as_int, month_as_int, day_as_int DECIMAL(8);
DECLARE ju, ju2 INT;
DECLARE bc_as_char VARCHAR(3) DEFAULT ”;
DECLARE is_leap INT DEFAULT 1;
IF options = ‘J’ THEN
SET ju = 0; SET tmp = ‘4713-01-01 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 1;
END IF;
IF options = ‘G’ THEN
SET ju = 0; SET tmp = ‘4714-11-24 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 0;
END IF;
IF options = ‘O’ THEN
SET ju = 0; SET tmp = ‘4712-01-01 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 1;
END IF;
WHILE tmp <> ‘10000-01-01’ DO
IF options <> ‘O’
OR SUBSTRING(tmp FROM 1 FOR 4) <> ‘0000’
OR bc_as_char <> ‘ BC’ THEN
SET tmp_validity = ocelot_date_validate(tmp, options);
IF tmp_validity <> ‘OK’ THEN RETURN tmp_validity; END IF;
END IF;
SET ju2 = ocelot_date_to_julianday(tmp, options);
IF ju2 <> ju OR ju2 IS NULL THEN RETURN CONCAT(‘Fail ‘, tmp); END IF;

IF options = ‘G’ and bc_as_char <> ‘ BC’ THEN
IF ju2 – 1721426 <> DATEDIFF(tmp,’0001-01-01′) THEN
RETURN CONCAT(‘Difference from datediff() ‘, tmp);
END IF;
END IF;
SET year_as_char = SUBSTRING(tmp FROM 1 FOR 4);
SET month_as_char = SUBSTRING(tmp FROM 6 FOR 2);
SET day_as_char = SUBSTRING(tmp FROM 9 FOR 2);
SET year_as_int = CAST(year_as_char AS DECIMAL(8));
SET month_as_int = CAST(month_as_char AS DECIMAL(8));
SET day_as_int = CAST(day_as_char AS DECIMAL(8));
/* Increase day */
SET day_as_int = day_as_int + 1;
IF options = ‘O’ AND year_as_int = 1582 AND month_as_int = 10 AND day_as_int = 5 AND bc_as_char <> ‘ BC’ THEN
SET day_as_int = day_as_int + 10;
END IF;
IF month_as_int = 1 AND day_as_int > 31
OR month_as_int = 2 AND day_as_int – is_leap > 28
OR month_as_int = 3 AND day_as_int > 31
OR month_as_int = 4 AND day_as_int > 30
OR month_as_int = 5 AND day_as_int > 31
OR month_as_int = 6 AND day_as_int > 30
OR month_as_int = 7 AND day_as_int > 31
OR month_as_int = 8 AND day_as_int > 31
OR month_as_int = 9 AND day_as_int > 30
OR month_as_int = 10 AND day_as_int > 31
OR month_as_int = 11 AND day_as_int > 30
OR month_as_int = 12 AND day_as_int > 31 THEN
/* Increase month */
SET day_as_int = 1;
SET month_as_int = month_as_int + 1;
IF month_as_int > 12 THEN
/* Increase year */
SET month_as_int = 1;
IF bc_as_char = ‘ BC’ THEN SET year_as_int = year_as_int – 1;
ELSE SET year_as_int = year_as_int + 1; END IF;
IF (year_as_int = 0 AND (options = ‘J’ OR options = ‘G’))
OR (year_as_int =-1 AND options = ‘O’) THEN
SET year_as_int = 1;
SET bc_as_char = ”;
SET is_leap = 0;
END IF;
/* Recalculate is_leap */
BEGIN
DECLARE divisible_year_as_int INT;
SET divisible_year_as_int = year_as_int;
IF bc_as_char <> ‘ BC’ OR options = ‘O’ THEN
SET divisible_year_as_int = year_as_int;
ELSE
SET divisible_year_as_int = year_as_int – 1;
END IF;
SET is_leap = 0;
IF divisible_year_as_int % 4 = 0 THEN
SET is_leap = 1;
IF options = ‘G’
OR (options = ‘O’ AND bc_as_char <> ‘ BC’ AND year_as_int > 1582) THEN
IF divisible_year_as_int % 100 = 0
AND divisible_year_as_int % 400 <> 0 THEN
SET is_leap = 0;
END IF;
END IF;
END IF;
END;
END IF;
END IF;
SET day_as_char = CAST(day_as_int AS CHAR);
IF LENGTH(day_as_char) = 1 THEN SET day_as_char = CONCAT(‘0’, day_as_char); END IF;
SET month_as_char = CAST(month_as_int AS CHAR);
IF LENGTH(month_as_char) = 1 THEN SET month_as_char = CONCAT(‘0’, month_as_char); END IF;
SET year_as_char = CAST(year_as_int AS CHAR);
WHILE LENGTH(year_as_char) < 4 DO SET year_as_char = CONCAT('0', year_as_char); END WHILE; SET tmp = CONCAT(year_as_char, '-', month_as_char, '-', day_as_char, bc_as_char); SET ju = ju + 1; END WHILE; RETURN CONCAT('OK ', tmp); END;

sql_mode

The following statement usually is valid and the function returns 1. But sometimes it is invalid and sometimes the function returns 0.

CREATE FUNCTION f() RETURNS INT DETERMINISTIC
BEGIN
  DECLARE a CHAR DEFAULT 'a';
  IF a = 0 || a > 0 THEN RETURN 1; END IF;
  RETURN 0;
END;

Why?

First, consider that “||” is usually the same as “OR” because that’s the default. But if sql_mode is ‘ansi’ and the DBMS is MySQL 8.0, then “||” is the operator for concatenating strings. So the meaning of the IF condition changes, and it becomes false.

Second, consider that the function is written with SQL/PSM syntax. But if sql_mode is ‘oracle’ and the DBMS is MariaDB 10.3, then the function has to be written with PL/SQL syntax. And the requirements differ as soon as the word “RETURNS” comes along, so the result is a syntax error.

Our lesson is: you can’t know a statement’s meaning if you don’t know whether somebody said “SET sql_mode=…” earlier.

Usually SET sql_mode is a DIRECTIVE with the same sense as “pragma” in C, it is telling the compiler or interpreter how to treat the syntax of following statements. And in fact Oracle and SQLite actually use the word “pragma” in that sense. I use C pragmas despite a nagging feeling that somewhere there is a jeremiad blog post about how they’re as bad as goto and #ifndef, and I’ll recommended certain sql_mode values enthusiastically. The only strong warning is: be consistent, so you don’t end up with idiocy like my example above.

(By the way, speaking of C, one of the possible settings is “treat warnings as errors”, and if there were such a mode in MySQL/MariaDB then my example function wouldn’t have worked. But there isn’t an exact equivalent.)

The modes

The manuals’ lists are more complete, but this list has more advice.

ANSI: Avoid. It won’t deliver “American National Standards Institute” SQL. And try this:

SET @@sql_mode='';
CREATE TABLE x (s1 INT);
SHOW CREATE TABLE x;
SET @@sql_mode='ANSI';
SHOW CREATE TABLE x;

Compare the SHOW CREATE TABLE results. Before:


and after:


Notice that critical information is missing in the ‘ANSI’ result — information that would be necessary to reproduce the table correctly. So, although using standard SQL is good, the way to do it is by setting other SQL-mode settings that don’t have such ugly side effects.

TRADITIONAL: Don’t avoid. This has very limited effect, it only “treats warnings as errors” if you’re changing a database value, and it’s only safe if you’re using a storage engine that can do statement rollbacks, such as InnoDB. But most of the time it’s good to disallow bad data, which is why it’s “traditional” (which stands for “what everybody else has done for decades”).

ALLOW_INVALID_DATES, Avoid. This made sense when the objective was to avoid “invalid data” errors because transactions were hard to roll back. That’s less of a concern nowadays.

ANSI_QUOTES. Probably don’t avoid. If you frequently have single quotes inside string literals, such as “O’Hara”, it’s understandable that you’d want ANSI_QUOTES to be off. With ANSI_QUOTES on, you have to say ‘O”Hara” and it’s possible to lose the duplicated ‘. But ANSI_QUOTES are standard SQL.

HIGH_NOT_PRECEDENCE, NO_BACKSLASH_ESCAPES, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PIPES_AS_CONCAT, REAL_AS_FLOAT. Don’t avoid. These are standard SQL.

PAD_CHAR_TO_FULL_LENGTH, ERROR_FOR_DIVISION_BY_ZERO. Avoid in MySQL. They’re deprecated.

IGNORE_SPACE. Avoid. The effect on naming is too great, because so many new reserved words appear.

NO_AUTO_VALUE_ON_ZERO. Avoid. This is a case where a non-standard feature can be treated in more than one way, so it’s hard to care.

SIMULTANEOUS_ASSIGNMENT. See my earlier blog post, The simultaneous_assignment mode in MariaDB 10.3.5.

STRICT_ALL_TABLES + STRICT_TRANS_TABLES. Don’t avoid. Perhaps you have some way of cleaning up messes after they’re added, but setting is simpler.

TIME_TRUNCATE_FRACTIONAL. Avoid but not forever. I hear from reliable sources that there is some strange behaviour that will be corrected real soon.

DB2,MAXDB,MSSQL,MYSQL323,MYSQL40,ORACLE,POSTGRESQL, etc. Mostly avoid. These have disappeared in MySQL 8.0. They never delivered a serious amount of compatibility with other DBMSs. The only one that is worth any consideration is ORACLE in MariaDB 10.3, because it affects quite a few Oracle-related matters including PL/SQL support.

The statement

There are many variants.

SET sql_mode=value;
SET @sql_mode=value;
SET @@sql_mode=value;
SET SESSION | GLOBAL | PERSIST | PERSIST_ONLY sql_mode=value;
SET @@session.|@@global.|@@persist.|@@persist_only.sql_mode=value;

where value can be a string literal containing a mode name
or a series of mode names as in ‘ansi,ansi’, or a variable, or even
a keyword. When it’s a keyword, it can be DEFAULT or
it can be a mode name — this seems to be undocumented but
I’ve seen that one of the MariaDB developers likes to use

SET sql_mode=ORACLE;

The good news is that the setting is transient and local. By “transient” I mean its effect ends when the routine ends or the session ends. By “local” I mean changes won’t affect other users whose sessions already started. And both these non-effects are good. Wouldn’t it be awful if your SQL statements stopped working because you invoked a function that changed sql_mode, or because some other user on the system found a way to change it for everybody while they were online?

ocelotgui 1.0.7

Version 1.0.7 of our open-source ocelotgui MySQL/MariaDB client is out, and one of the features is that it recognizes all the current syntax of MySQL 8.0 and MariaDB 10.3, including the sql_mode bizarreness (though we can’t get it right if the source value is a variable). That means that it won’t get confused when parsing batches of SQL statements that include statements that change the dialect.

The major feature is that the debugger can now debug routines written in MySQL 8.0, and routines written in MariaDB 10.3 with sql_mode=oracle — that is, with PL/SQL syntax.

As usual, download for various Linux distros and for Windows is via github.

FOR … END FOR

Let’s start by combining one new MariaDB feature — VALUES (5) which is the standard equivalent of the old non-standard “SELECT 5” — with another new feature in MariaDB 10.3.5, the FOR … END FOR statement.

mariadb>BEGIN NOT ATOMIC
> DECLARE v INT DEFAULT 0;
> FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR;
> SELECT v;
>END;
Error 2013 (HY000) Lost connection to MySQL server during query

Oops. However, the MariaDB folks now know about this, it’s bug MDEV-15940″ and they know about the other bugs that I’ll mention in this post, so there’s no problem provided you wait for the next version.

FOR … END FOR works prettily so I decided to describe it. The official documentation isn’t out yet so this could change.

FOR x IN (subquery) DO … END FOR

Example:

BEGIN NOT ATOMIC
  DECLARE v INT DEFAULT 0;
  FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;
  SELECT v;
END;

The SELECT will return ‘1’ because the SET statement happened once, because SELECT 5 returns 1 row. We must put (SELECT 5) inside parentheses because there must be a way to find where it ends — the word DO is not reserved and therefore is useless as a marker for the parser.

FOR x IN cursor DO … END FOR

Example:

CREATE TABLE t (s1 INT);
INSERT INTO t VALUES (1),(2),(3);
BEGIN NOT ATOMIC
  DECLARE v2, v3 INT DEFAULT 0;
  BEGIN
    DECLARE cn CURSOR FOR SELECT * FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v3 = 1;
    FOR cn_record IN cn
    DO
      SET v2 = v2 + cn_record.s1;
    END FOR;
  END;
  SELECT v2, v3;
END;

FOR cn_record IN cn means “loop with cursor cn, which was declared earlier”. And cn_record according to MariaDB is a row variable that can be used within the loop, so cn_record.s1 is the value of s1 within a given row of table t.

Given that table t has 3 rows containing (1),(2),(3), and given that the obvious intent is that v2 will end up with the sum, you might think the SELECT will return v2 = 6, eh? Well, you’re thinking right, it does. This is a way to go through a cursor, with each iteration doing an automatic FETCH.

And, since there’s a CONTINUE HANDLER that says we’ll set v3 = 1 when there are no more rows to fetch, you might think the SELECT will return v3 = 1, eh? Well, you’re thinking reasonably (I think), but it doesn’t. The handler isn’t brought into play, the NOT FOUND condition is automatically cleared when the loop ends.

Summary: this kind of FOR does automatic cursor-open and cursor-fetch, but not automatic cursor-close. To me that looked like a bug, the way I interpret the standard document, “CLOSE cn;” is supposed to happen. And MariaDB agrees, it’s bug MDEV-15941.

FOR x IN low-number .. high-number DO … END FOR

Example:

SET @v = 0;
CREATE PROCEDURE p()
BEGIN
  FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;
  SELECT @v;
END;

MariaDB 10.3 comes with a boatload of Oracle/PostgreSQL syntax, often known as “PL/SQL” (their deviation from the standard “SQL/PSM” syntax which MySQL/MariaDB have followed in the past). But all the PL/SQL stuff is supplied if and only if you say

SET @@sql_mode='oracle';

The FOR … END FOR statement is an exception, it works in the default mode too. That’s deliberate. And it’s equivalent to

SET a = 1; WHILE a <= 3 DO ... SET a = a + 1; END WHILE;

So the SELECT in the above example displays '6'.

The ".." is a new operator, vaguely similar to the "range operators" in languages like Ruby and Perl. But SQL already has a range operator, it's called BETWEEN. And now MariaDB won't be able to accept the SQL Server syntax where ".." is for qualifiers ("a..b" means "schema a + default table + column b").

I encountered a problem during the test, with:
FOR a IN (SELECT 1) .. 3 DO SET @v = @v + a; END FOR;
Technically this should be legal because “low-number” can be any expression, including a subquery. In this case, though, subqueries mean something else, so it is not legal. This is now bug MDEV-15944″.

I enthusiastically recommend: don’t use this, use WHILE.

Differences from the standard

My first example looked like this:

  FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;

In standard SQL it would have looked like this:

  FOR (VALUES (5)) DO SET v = v + 1; END FOR;

… The standard doesn’t require mentioning “a” when there is no need to refer to “a” inside the loop. The fact that the standard would use “VALUES (5)” is, as we’ve seen, something that MariaDB will soon support too.

My second example looked like this:

    FOR cn_record IN cn
    DO
      SET v2 = v2 + cn_record.s1;
    END FOR;

In standard SQL it would have looked like this:

    FOR cn_record AS cn
    DO
      SET v2 = v2 + s1;
    END FOR;

So it’s a difference between “IN” and “AS”, and between “cn_record.s1” and “s1”. However, I could have said “cn_record.s1” in standard SQL too, it’s optional when there’s no ambiguity.

There are serious matters that underlie that innocent-looking difference with “cn_record.”, about which I now digress.

Shadowing, a Digression

In the following, should “SELECT v FROM t;” display 1, or 2, or 3?

BEGIN NOT ATOMIC
  a: DECLARE v INT DEFAULT 1;
  BEGIN
    b: DECLARE v INT DEFAULT 2;
    CREATE TABLE t (v INT);
    INSERT INTO t VALUES (3);
    SELECT v FROM t;
  END;
END;

Answer: in MariaDB it’s 2. Inner scope beats outer scope, and variable declaration beats table definition. This is an old MySQL flaw that MariaDB has inherited (Bug #5967 Stored procedure declared variable used instead of column). The workaround, if you actually want “column v of table t”, is to use a qualifier, as in “SELECT t.v FROM t;”.

But what if you wanted to refer to the first declared variable? That would be easy too, in standard SQL you would qualify with the label, as in “SELECT a.v FROM t;”. Unfortunately — another old MySQL flaw that MariaDB has inherited — you can’t use [label.]variable_name for qualification.

As a result of these two flaws, we could have trouble in our FOR … END FOR loop if we used unqualified names like “s1”. Therefore in MariaDB you have to say “cn_record.s1” if you want the FOR variable, that is, qualification is compulsory. That’s a solution.

But the solution depends on a third flaw.

In standard SQL in this FOR loop “cn_record” becomes a label, and that’s why “cn_record.s1” would be legal — cn_record is a label qualifier. That’s not the case with Oracle/MariaDB, where “cn_record” is a variable with type = ROW — cn_record is a row-variable qualifier. The effect is the same for the particular example that I used, but that’s just luck.

The flaws do not mean that the implementor did something wrong for FOR … END FOR, rather it means that the blame lies in MySQL’s history. It would be grossly wrong to blame the standard, which has no flaws.

Differences from the Standard, continued

My third example looked like this:

  FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;

In standard SQL it would have looked like this:

  [empty prairie with crickets chirping in the darkness]

For more about the original plan for adding FOR … END FOR to MySQL in a standard way, go to “WL#3309: Stored Procedures: FOR statement” and click “High-level architecture”.

ocelotgui

Of course we’ve changed Ocelot’s GUI client for MySQL/MariaDB so that it recognizes most of the new syntax in MariaDB 10.3. We haven’t changed the debugger, though, so it won’t handle statements containing FOR … END FOR.

The simultaneous_assignment mode in MariaDB 10.3.5

Starting with MariaDB 10.3.5, if you say sql_mode = ‘simultaneous_assignment’, then
UPDATE t SET a = b, b = a;
will swap b and a because a gets what’s originally in b, while b gets what’s originally in a (as of the start of the SET for a given row), instead of what was going on before, which was “Assignments are evaluated in left-to-right order”.

I will list things that I like or don’t like, and behaviour-change cases to watch for.

I like it that this is standard SQL at last

I don’t know any other vendor who ever did left-to-right assignments, and they had a good reason.

Way back in SQL-86, section 8.11 said how to handle a situation with (simplified BNF)
SET <object column> = <value expression> | NULL [, <object column> = <value expression> | NULL …
“If the <value expression> contains a reference to a column of T, the reference is to the value of that column in the object row before any value of the object row is updated.”

In case anyone still didn’t get it, SQL-92 added the sentence:
“The <value expression>s are effectively evaluated before updating the object row.”

Unfortunately later versions of the standard were slightly denser, such as:
“11) Each <update source> contained in SCL is effectively evaluated for the current row before any of the current row’s object rows is updated.”
The abbreviation “SCL” is the set clause list. The word “rows” is not a typo, but it’s for a special situation, we can still interpret as “before the object row is updated”.

Thus what we’re talking about is optionally following the standard.

So hurrah, let’s put equestrian statues of the MariaDB seal in main squares of our cities.

I don’t like the name

I’ll admit that the effect can be the same, and I’ll admit that some SQL Server commenters like to refer to this as doing things “all at once”. (See here and here.) But the standard description isn’t really demanding that all the assignments be simultaneous.

And the term “simultaneous assignment” can make some programmers
think of “parallel assignment”, as in this example (from Wikipedia)
a, b := 0, 1

A name like “evaluate_sources_first_during_updates” would have been better.

I don’t like that it’s a mode

We all know the problems of setting a global variable that people don’t see or think about when they create SQL statements.

So perhaps it would have been better to make a change to the UPDATE syntax, e.g.
UPDATE /*! new-style-update */ NEW STYLE t SET …;
It doesn’t solve the problem cleanly — now every statement has non-standard syntax in order to perform in a standard way — but it means you don’t get the behaviour change in every statement, only in the ones where you ask.

Or perhaps it would have been better to produce a warning: “You are using an sql_mode value that changes the behaviour of this particular UPDATE statement because evaluation is no longer left to right.” I don’t know if it would be hard. I don’t know if it’s too late.

Setup for examples

Following examples are done with a one-row table, made thus:

CREATE TABLE t (col1 INT, col2 INT, PRIMARY KEY (col1, col2));
INSERT INTO t VALUES (1, 2);

The MariaDB version is 10.3.5 downloaded on 2018-03-20.

Assigning with swaps and variables

SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 1, col2 = 2;
UPDATE t SET col1 = col2, col2 = col1;
SELECT * FROM t;
-- Result: col1 is 2, col2 is 1.

This is different from what happens with @@sql_mode=”. This is the best example that shows the new mode works.

SET @@sql_mode='simultaneous_assignment';
SET @x = 100;
UPDATE t SET col1 = @x, col2 = @x := 200;
SELECT * FROM t;
-- Result: col1 is 100, col2 is 200.

This is not different from what happens with @@sql_mode=”. Conclusion: if you use the non-standard variable-assignment trick, the value is not updated in advance.

What about the SET statement?

DROP PROCEDURE p;
SET @@sql_mode='simultaneous_assignment';
CREATE PROCEDURE p()
BEGIN
  DECLARE v1 INT DEFAULT 1;
  DECLARE v2 INT DEFAULT 2;
  SET v1 = v2, v2 = v1;
  SELECT v1, v2;
END;
CALL p();
-- Result: v1 is 2, v2 is 2.

This is not different from what happens with @@sql_mode=”. Conclusion ‘simultaneous_assignment’ does not affect assignments with the SET statement.

Perhaps people will expect otherwise because SET statements look so similar to SET clauses, but this is actually correct. The way the standard defines things, for syntax that is similar to (though not exactly the same as) “SET v1 = v2, v2 = v1;”, is: the statement should be treated as equivalent to

SET v1 = v2;
SET v2 = v1;

Good. But come to think of it, this is another reason to dislike the misleading mode name ‘simultaneous_assignment’. SET statements are assignments but @@sql_mode won’t affect them.

What about the INSERT statement?

SET @@sql_mode='simultaneous_assignment';
TRUNCATE TABLE t;
INSERT INTO t SET col1 = col2, col2 = 99;
SELECT * FROM t;
-- Result: col1 is 0, col2 is 99.

Conclusion: ‘simultaneous_assignment’ does not affect INSERT … SET statements, processing is left to right. This looks inconsistent to me, but it’s not wrong.

What about the INSERT ON DUPLICATE KEY UPDATE statement?

SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 0, col2 = 1;
INSERT INTO t VALUES (0, 1) ON DUPLICATE KEY UPDATE col1 = col2, col2 = col1;
SELECT * FROM t;
-- Result: col1 is 1, col2 is 0.

Conclusion: ‘simultaneous_assignment does affect INSERT ON DUPLICATE KEY UPDATE. Nobody said that it has to (this syntax is a MySQL/MariaDB extension), but surely this is what everyone would expect. Good.

Triggers and constraints

I could not come up with a case where ‘simultaneous_assignment’ affected the order in which triggers or constraints are processed. Good.

Assigning twice to the same column

SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 3, col2 = 4, col1 = col2;
-- Result: error.

Good. There’s supposed to be an error, because (quoting the standard document again) “Equivalent <object column>s shall not appear more than once in a <set clause list>.”

Actually this doesn’t tell us something new about ‘simultaneous_assignment’, I only tried it because I thought that MariaDB would not return an error. Probably I was remembering some old version before this was fixed.

Multiple-table updates

From the bugs.mysql.com site quoting the MySQL manual:
“Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.”
I don’t see an equivalent statement in the MariaDB manual. So I conclude that even for multiple-table updates the assignments will be done in a standard way, but couldn’t think of a good way to test.

There are other things

The really big things in MariaDB 10.3.5 are PL/SQL and the myrocks engine. So, just because I’ve looked at a small thing, don’t get the impression that 10.3.5 is going to be a minor enhancement.

ocelotgui

There have been no significant updates to ocelotgui since my last blog post.

The Tarantool SQL Alpha

Tarantool, a Lua application server plus NoSQL DBMS, is now an SQL DBMS too, in alpha version 1.8. I was interested in how the combination “Lua + NoSQL + SQL” works. Disclaimer: I do paid work related to Tarantool but it has nothing to do with this blog.

First let’s verify that it’s really SQL. The illustrations are all unretouched screenshots from ocelotgui for Windows, connected to a Tarantool 1.8 server on Linux, which I built from a source download on github.

Example of SELECT

tarantool2

Yes, the “history” below the query window shows successful CREATE and INSERT statements, the “result set” at the bottom shows a successful SELECT statement’s output. A close look at the clauses shows that there’s support for constraints, foreign keys, triggers, … and so on. In all, it’s a reasonable subset of the SQL standard, pretty well the same as what I found for SQLite in an earlier post.

That’s not surprising because Tarantool started with SQLite’s parser; however, the storage layer is Tarantool’s NoSQL.

Combine Lua with SQL

Now it’s time for the first “combination”. I want to store and retrieve pictures, which are in .png and .jpg files. With MySQL/MariaDB I’d use load_file() but that’s a built-in function that Tarantool doesn’t have. Am I daunted? No, because I can write such a function in Lua — or actually I can copy such a function because it’s one of the examples in the Tarantool manual.

-- Lua function to set a variable to a file's contents
function load_file(file_name)
  local fio = require('fio')
  local errno = require('errno')
  local f = fio.open(file_name, {'O_RDONLY' })
  if not f then
    error("Failed to open file: "..errno.strerror())
  end
  local data = f:read(1000000)
  f:close()
  return data
end;

Not a difficult matter. As is clear from the display,
tarantool4
the function is syntactically okay (there would be squiggly red lines under the error if my Lua was bad). To explain the function: it says “read a file and return either an error message or the whole contents”.

I can’t call Lua functions directly from SQL yet, but I can do so from NoSQL, and with NoSQL I can INSERT into the same table that I created with SQL …
box.space.timages:insert{1,load_file(‘shot1.jpg’),’shot1′);
And then go back to SQL to handle the retrieval …
SELECT * FROM “timages”;
and the .jpg image is at the bottom of the screen.

So, although they’re not standard SQL/PSM or Oracle-like PL/SQL, Tarantool does have server-side stored procedures.

Combine NoSQL With SQL

Now it’s time for the second “combination”. I want to read some NoSQL data that was not produced or populated with SQL in mind. Specifically we’ve got: a variable number of fields, some of which are unnamed, and they’re not all scalar, there are arrays and structures. A typical tuple related to index metadata looks like this:

[[ Example of tuple ]]
  - [312, 1, 'owner', 'tree', {'unique': false}, [[0, 'unsigned']]]
  - [312, 2, 'object', 'tree', {'unique': false}, [[2, 'string'], [3, 'unsigned']]]
  - [313, 0, 'primary', 'tree', {'unique': true}, [[1, 'unsigned'], [2, 'string'], [3, 'unsigned']]]
  - [313, 1, 'owner', 'tree', {'unique': false}, [[0, 'unsigned']]]
  - [313, 2, 'object', 'tree', {'unique': false}, [[2, 'string'], [3, 'unsigned']]]
  - [320, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
  - [320, 1, 'uuid', 'tree', {'unique': true}, [[1, 'string']]]

For this I wrote some C code in the client instead of on the server, but I don’t think that’s cheating — it doesn’t show a Tarantool feature, but it does show that one can transfer the data into an SQL table and go from there. The syntax added to the client looks like this:

CREATE SERVER id FOREIGN DATA WRAPPER ocelot_tarantool
 OPTIONS (PORT 3301, HOST 'localhost', USER 'guest');
CREATE TABLE convertee SERVER id LUA 'return box.space._index:select()';
SELECT * FROM convertee;

The selection after converting looks like this:
tarantool6

(I’m changing the fonts and the window order now to make relevant parts look bigger.)

I wish I could call this “flattening”, but that term has been hijacked for other concepts. Anyway, whatever it can be called, it’s the reason that schemaless data doesn’t need a new query language.

Things You Don’t Always See Elsewhere

I’ll mention a few things that are in Tarantool SQL that are not in MySQL/MariaDB, or are fairly new in MySQL/MariaDB. This short list does not mean Tarantool is “ahead”, I’m talking about an alpha where many things are to-be-announced. I like to look at what’s new and different.

COLLATE

tarantool8

The point isn’t that there’s a COLLATE clause, the point is that the collation is ICU. I’ve talked about the advantages of ICU earlier. The collation name will change soon, probably to ‘unicode_s1’ or ‘unicode_ci’.

WITH

tarantool9

A non-recursive WITH clause is the same thing as a temporary view that lasts only for the statement that encloses it.
A recursive WITH clause is a temporary view that is populated by taking a seed (the query to the left of the UNION) and adding to it with a populator (the query to the right of the UNION), repeatedly, until some condition fails. I find it hard to understand, I suppose my problem is that this is procedural (a loop), and for procedural problems I prefer C or Lua or SQL/PSM.

EXCEPT and INTERSECT

SELECT * FROM "t" EXCEPT SELECT * FROM f;

These operators can fit in the same place as UNION, but have different effects. In the example, the EXCEPT would mean “take out the rows that match” instead of “add rows that do not match”.

NaN and Inf

tarantool10

This is a differentiator, since in standard SQL and in some implementations these values are not supported, even though they’re supported in IEEE 754.

CHECK

tarantool11

A constraint’s effect is: “if the condition inside the parentheses is true or unknown, then and only then it is legal to have this row.” This feature is also in MariaDB 10.2.1.

INDEXED BY

tarantool12

This is non-standard (and obviously always will be): you can force Tarantool to use a particular index, or no index at all, bypassing the optimizer.

VALUES

tarantool13

This means: return a result set containing a row with three columns containing 1, 2, 3. MySQL and MariaDB have a non-standard way to do this:
SELECT 1,2,3;
I like the logic of VALUES and the fact that I can say
VALUES (1,2,3),(4,5,6);
but Tarantool also supports the MySQL/MariaDB way, and I expect that it will always be more popular.

Game Changer?

Tarantool’s aiming high and Tarantool’s owner has a $9-billion market cap so the resources are there, but I’m not sure that Oracle sees them on its threat radar just yet. Tarantool SQL is not a drop-in replacement for all the code you’ve written for Oracle or MySQL/MariaDB, and the distinguishing features that I’ve mentioned are only going to cause a few people to migrate, at least in 2018. Other people will use Tarantool as an “add-on” or “engine”.

I do see that this is close enough to SQLite that it will probably be reasonable to switch from there, if people need the extra multi-user / replication capabilities and the Lua integration and the speed (the main engine is an in-memory DBMS).

More dimly, I see some other NoSQL DBMSs looking bad compared to Tarantool because their SQL support is trivial by comparison. I’m thinking especially of the ones that already get beaten by Tarantool in YCSB tests of NoSQL performance.

Tarantool’s licence is BSD.

Alphas Are Not Betas

Everything I’ve described above might change before Tarantool SQL is ready for use in production.

If you want to try to replicate the examples exactly, start with the old version-1.7 manual, move up to the SQL tutorial for version-1.8. The ocelotgui client additional instructions for connecting to Tarantool are here.

UPDATE: Tarantool’s SQL feature is now part of Tarantool 2.1 instead of Tarantool 1.8. The SQL tutorial is now here. The instructions for connecting ocelotgui to Tarantool are the same as before, except for the change in version number. Tarantool 2.1 is beta.

UPDATE: ocelotgui support for Tarantool was greatly enhanced after this post was written. See https://github.com/ocelot-inc/ocelotgui-tarantool.

Reserved Words

In the 1990s C.J.Date said: “The rule by which it is determined within the standard that one key word needs to be reserved while another need not be is not clear to this writer.”

Nothing has changed since then, except there are more reserved words. No DBMS uses the standard list. So I think that it is probably best to know what words are reserved in product X that are not reserved in product Y. If you know, you can avoid syntax errors when you update or migrate.

I’ll present several comparisons, ending with a grand chart of all the reserved words in the standard and six current DBMSs.

First here’s a screenshot of ocelotgui where I’m hovering over the word BEGIN.
screenshot_for_blog
What I’m illustrating is that you can’t depend on intuition and assume BEGIN is reserved, but a GUI client can tell you from context: it’s a declared variable.

20 words are reserved in MariaDB but not in MySQL:

+-------------------------+
| word                    |
+-------------------------+
| CURRENT_ROLE            |
| DO_DOMAIN_IDS           |
| GENERAL                 |
| IGNORE_DOMAIN_IDS       |
| IGNORE_SERVER_IDS       |
| INTERSECT               |
| LEFT                    |
| MASTER_HEARTBEAT_PERIOD |
| MAX                     |
| MODIFIES                |
| PAGE_CHECKSUM           |
| PARSE_VCOL_EXPR         |
| REF_SYSTEM_ID           |
| REPLACE                 |
| RETURNING               |
| SCHEMA                  |
| SLOW                    |
| STATS_AUTO_RECALC       |
| STATS_PERSISTENT        |
| STATS_SAMPLE_PAGES      |
+-------------------------+

36 words are reserved in MySQL but not in MariaDB:

+-------------------+
| word              |
+-------------------+
| ADMIN             |
| ANALYSE           |
| CUBE              |
| CUME_DIST         |
| DENSE_RANK        |
| EMPTY             |
| FIRST_VALUE       |
| FUNCTION          |
| GENERATED         |
| GET               |
| GROUPING          |
| GROUPS            |
| IO_AFTER_GTIDS    |
| IO_BEFORE_GTIDS   |
| JSON_TABLE        |
| LAG               |
| LAST_VALUE        |
| LEAD              |
| LEAVESLEFT        |
| MASTER_BIND       |
| MODEMODIFIES      |
| NTH_VALUE         |
| NTILE             |
| OF                |
| OPTIMIZER_COSTS   |
| PERCENT_RANK      |
| PERSIST           |
| PERSIST_ONLY      |
| RANK              |
| REPEATABLEREPLACE |
| ROW               |
| ROW_NUMBER        |
| SCHEDULESCHEMA    |
| STORED            |
| SYSTEM            |
| VIRTUAL           |
+-------------------+

15 words are reserved in MariaDB 10.3 but not in MariaDB 10.2:

+--------------------+
| word               |
+--------------------+
| CURRENT_ROLE       |
| DO_DOMAIN_IDS      |
| EXCEPT             |
| IGNORE_DOMAIN_IDS  |
| INTERSECT          |
| MAX                |
| OVER               |
| PAGE_CHECKSUM      |
| PARSE_VCOL_EXPR    |
| REF_SYSTEM_ID      |
| RETURNING          |
| STATS_AUTO_RECALC  |
| STATS_PERSISTENT   |
| STATS_SAMPLE_PAGES |
| WINDOW             |
+--------------------+

(My MariaDB-10.3 list comes from the code source, my MariaDB-10.2 list comes from the manual, which may not be up to date.)

6 words are reserved in all of (DB2 and Oracle and Microsoft) but not in (MySQL or MariaDB):

+---------+
| word    |
+---------+
| ANY     |
| CURRENT |
| FILE    |
| PUBLIC  |
| USER    |
| VIEW    |
+---------+

We said in SQL-99 Complete, Really: “[The standard] suggests that you include either a digit or an underline character in your regular identifiers and avoid names that begin with CURRENT_, SESSION_, SYSTEM_, or TIMEZONE_ and those that end with _LENGTH to avoid conflicts with reserved keywords added in future revisions.” It’s also good to avoid words that begin with SYS, or words that begin with the product name such as “IBM…” or “sql…”. And of course it might also be good to use “delimiters”, if you can avoid case-sensitivity confusions.

My original reason for making lists was to answer some questions about Tarantool. I do some paid work for this group, including tutorials about SQL like this one. In a forthcoming post I will show why I believe that this product is far ahead of the others that I discussed in an earlier post, What’s in the SQL of NoSQL and even has some useful characteristics that MySQL/MariaDB lack.

Ocelot news: We have just uploaded a Windows version of the ocelotgui client, with an executable ocelotgui.exe static-linked to MariaDB Connector C and Qt. So it should be easy to download the release from github and run. See the windows.txt file on github for more explanation. Alpha.

To end this post, here is the grand finale list — all reserved words in all dialects. Sta = Standard, Mar = MariaDB, MyS = MySQL, Db2 = DB2, Ora = Oracle, Mic = Microsoft, Odb = Odbc, Tar = Tarantool. (The Mic and Odb columns represent what Microsoft recommends but doesn’t always enforce.) (The Tar column is still subject to change.)

+----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
| word                             | Sta | Mar | MyS | Db2 | Ora | Mic | Odb | Tar |
+----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
| ABS                              | x   |     |     |     |     |     |     |     |
| ABSOLUTE                         |     |     |     |     |     |     | x   |     |
| ACCESS                           |     |     |     |     | x   |     |     |     |
| ACCESSIBLE                       |     | x   | x   |     |     |     |     |     |
| ACTION                           |     |     |     |     |     |     | x   |     |
| ACTIVATE                         |     |     |     | x   |     |     |     |     |
| ADA                              |     |     |     |     |     |     | x   |     |
| ADD                              |     | x   | x   | x   | x   | x   | x   |     |
| ADMIN                            |     |     | x   |     |     |     |     |     |
| AFTER                            |     |     |     | x   |     |     |     |     |
| ALIAS                            |     |     |     | x   |     |     |     |     |
| ALL                              | x   | x   | x   | x   | x   | x   | x   | x   |
| ALLOCATE                         | x   |     |     | x   |     |     | x   |     |
| ALLOW                            |     |     |     | x   |     |     |     |     |
| ALTER                            | x   | x   | x   | x   | x   | x   | x   | x   |
| ANALYSE                          |     |     | x   |     |     |     |     |     |
| ANALYZE                          |     | x   | x   |     |     |     |     | x   |
| AND                              | x   | x   | x   | x   | x   | x   | x   | x   |
| ANY                              | x   |     |     | x   | x   | x   | x   | x   |
| ARE                              | x   |     |     |     |     |     | x   |     |
| ARRAY                            | x   |     |     |     |     |     |     |     |
| ARRAY_AGG                        | x   |     |     |     |     |     |     |     |
| ARRAY_MAX_CARDINALITY            | x   |     |     |     |     |     |     |     |
| AS                               | x   | x   | x   | x   | x   | x   | x   | x   |
| ASC                              |     | x   | x   |     | x   | x   | x   | x   |
| ASENSITIVE                       | x   | x   | x   | x   |     |     |     | x   |
| ASSERTION                        |     |     |     |     |     |     | x   |     |
| ASSOCIATE                        |     |     |     | x   |     |     |     |     |
| ASUTIME                          |     |     |     | x   |     |     |     |     |
| ASYMMETRIC                       | x   |     |     |     |     |     |     |     |
| AT                               | x   |     |     | x   |     |     | x   |     |
| ATOMIC                           | x   |     |     |     |     |     |     |     |
| ATTRIBUTES                       |     |     |     | x   |     |     |     |     |
| AUDIT                            |     |     |     | x   | x   |     |     |     |
| AUTHORIZATION                    | x   |     |     | x   |     | x   | x   |     |
| AUTOINCREMENT                    |     |     |     |     |     |     |     | x   |
| AUX                              |     |     |     | x   |     |     |     |     |
| AUXILIARY                        |     |     |     | x   |     |     |     |     |
| AVG                              | x   |     |     |     |     |     | x   |     |
| BACKUP                           |     |     |     |     |     | x   |     |     |
| BEFORE                           |     | x   | x   | x   |     |     |     |     |
| BEGIN                            | x   |     |     | x   |     | x   | x   | x   |
| BEGIN_FRAME                      | x   |     |     |     |     |     |     |     |
| BEGIN_PARTITION                  | x   |     |     |     |     |     |     |     |
| BETWEEN                          | x   | x   | x   | x   | x   | x   | x   | x   |
| BIGINT                           | x   | x   | x   |     |     |     |     |     |
| BINARY                           | x   | x   | x   | x   |     |     |     | x   |
| BIT                              |     |     |     |     |     |     | x   |     |
| BIT_LENGTH                       |     |     |     |     |     |     | x   |     |
| BLOB                             | x   | x   | x   |     |     |     |     |     |
| BOOLEAN                          | x   |     |     |     |     |     |     |     |
| BOTH                             | x   | x   | x   |     |     |     | x   |     |
| BREAK                            |     |     |     |     |     | x   |     |     |
| BROWSE                           |     |     |     |     |     | x   |     |     |
| BUFFERPOOL                       |     |     |     | x   |     |     |     |     |
| BULK                             |     |     |     |     |     | x   |     |     |
| BY                               | x   | x   | x   | x   | x   | x   | x   | x   |
| CACHE                            |     |     |     | x   |     |     |     |     |
| CALL                             | x   | x   | x   | x   |     |     |     | x   |
| CALLED                           | x   |     |     | x   |     |     |     |     |
| CAPTURE                          |     |     |     | x   |     |     |     |     |
| CARDINALITY                      | x   |     |     | x   |     |     |     |     |
| CASCADE                          |     | x   | x   |     |     | x   | x   |     |
| CASCADED                         | x   |     |     | x   |     |     | x   |     |
| CASE                             | x   | x   | x   | x   |     | x   | x   | x   |
| CAST                             | x   |     |     | x   |     |     | x   | x   |
| CATALOG                          |     |     |     |     |     |     | x   |     |
| CCSID                            |     |     |     | x   |     |     |     |     |
| CEIL                             | x   |     |     |     |     |     |     |     |
| CEILING                          | x   |     |     |     |     |     |     |     |
| CHANGE                           |     | x   | x   |     |     |     |     |     |
| CHAR                             | x   | x   | x   | x   | x   |     | x   | x   |
| CHARACTER                        | x   | x   | x   | x   |     |     | x   | x   |
| CHARACTER_LENGTH                 | x   |     |     |     |     |     | x   |     |
| CHAR_LENGTH                      | x   |     |     |     |     |     | x   |     |
| CHECK                            | x   | x   | x   | x   | x   | x   | x   | x   |
| CHECKPOINT                       |     |     |     |     |     | x   |     |     |
| CLASSIFIER                       | x   |     |     |     |     |     |     |     |
| CLOB                             | x   |     |     |     |     |     |     |     |
| CLONE                            |     |     |     | x   |     |     |     |     |
| CLOSE                            | x   |     |     | x   |     | x   | x   |     |
| CLUSTER                          |     |     |     | x   | x   |     |     |     |
| CLUSTERED                        |     |     |     |     |     | x   |     |     |
| COALESCE                         | x   |     |     |     |     | x   | x   |     |
| COLLATE                          | x   | x   | x   |     |     | x   | x   | x   |
| COLLATION                        |     |     |     |     |     |     | x   |     |
| COLLECT                          | x   |     |     |     |     |     |     |     |
| COLLECTION                       |     |     |     | x   |     |     |     |     |
| COLLID                           |     |     |     | x   |     |     |     |     |
| COLUMN                           | x   | x   | x   | x   | x   | x   | x   | x   |
| COLUMN_VALUE                     |     |     |     |     | x   |     |     |     |
| COMMENT                          |     |     |     | x   | x   |     |     |     |
| COMMIT                           | x   |     |     | x   |     | x   | x   | x   |
| COMPRESS                         |     |     |     |     | x   |     |     |     |
| COMPUTE                          |     |     |     |     |     | x   |     |     |
| CONCAT                           |     |     |     | x   |     |     |     |     |
| CONDITION                        | x   | x   | x   | x   |     |     |     | x   |
| CONNECT                          | x   |     |     | x   | x   |     | x   | x   |
| CONNECTION                       |     |     |     | x   |     |     | x   |     |
| CONSTRAINT                       | x   | x   | x   | x   |     | x   | x   | x   |
| CONSTRAINTS                      |     |     |     |     |     |     | x   |     |
| CONTAINS                         | x   |     |     | x   |     | x   |     |     |
| CONTAINSTABLE                    |     |     |     |     |     | x   |     |     |
| CONTINUE                         |     | x   | x   | x   |     | x   | x   |     |
| CONVERT                          | x   | x   | x   |     |     | x   | x   |     |
| CORR                             | x   |     |     |     |     |     |     |     |
| CORRESPONDING                    | x   |     |     |     |     |     | x   |     |
| COUNT                            | x   |     |     | x   |     |     | x   |     |
| COUNT_BIG                        |     |     |     | x   |     |     |     |     |
| COVAR_POP                        | x   |     |     |     |     |     |     |     |
| COVAR_SAMP                       | x   |     |     |     |     |     |     |     |
| CREATE                           | x   | x   | x   | x   | x   | x   | x   | x   |
| CROSS                            | x   | x   | x   | x   |     | x   | x   | x   |
| CUBE                             | x   |     | x   |     |     |     |     |     |
| CUME_DIST                        | x   |     | x   |     |     |     |     |     |
| CURRENT                          | x   |     |     | x   | x   | x   | x   | x   |
| CURRENT_CATALOG                  | x   |     |     |     |     |     |     |     |
| CURRENT_DATE                     | x   | x   | x   | x   |     | x   | x   | x   |
| CURRENT_DEFAULT_TRANSFORM_GROUP  | x   |     |     |     |     |     |     |     |
| CURRENT_LC_CTYPE                 |     |     |     | x   |     |     |     |     |
| CURRENT_PATH                     | x   |     |     | x   |     |     |     |     |
| CURRENT_ROLE                     | x   | x   |     |     |     |     |     |     |
| CURRENT_ROW                      | x   |     |     |     |     |     |     |     |
| CURRENT_SCHEMA                   | x   |     |     | x   |     |     |     |     |
| CURRENT_SERVER                   |     |     |     | x   |     |     |     |     |
| CURRENT_TIME                     | x   | x   | x   | x   |     | x   | x   | x   |
| CURRENT_TIMESTAMP                | x   | x   | x   | x   |     | x   | x   | x   |
| CURRENT_TIMEZONE                 |     |     |     | x   |     |     |     |     |
| CURRENT_TRANSFORM_GROUP_FOR_TYPE | x   |     |     |     |     |     |     |     |
| CURRENT_USER                     | x   | x   | x   | x   |     | x   | x   | x   |
| CURSOR                           | x   | x   | x   | x   |     | x   | x   | x   |
| CYCLE                            | x   |     |     | x   |     |     |     |     |
| DATA                             |     |     |     | x   |     |     |     |     |
| DATABASE                         |     | x   | x   | x   |     | x   |     |     |
| DATABASES                        |     | x   | x   |     |     |     |     |     |
| DATAPARTITIONNAME                |     |     |     | x   |     |     |     |     |
| DATAPARTITIONNUM                 |     |     |     | x   |     |     |     |     |
| DATE                             | x   |     |     | x   | x   |     | x   | x   |
| DAY                              | x   |     |     | x   |     |     | x   |     |
| DAYS                             |     |     |     | x   |     |     |     |     |
| DAY_HOUR                         |     | x   | x   |     |     |     |     |     |
| DAY_MICROSECOND                  |     | x   | x   |     |     |     |     |     |
| DAY_MINUTE                       |     | x   | x   |     |     |     |     |     |
| DAY_SECOND                       |     | x   | x   |     |     |     |     |     |
| DB2GENERAL                       |     |     |     | x   |     |     |     |     |
| DB2GENRL                         |     |     |     | x   |     |     |     |     |
| DB2SQL                           |     |     |     | x   |     |     |     |     |
| DBCC                             |     |     |     |     |     | x   |     |     |
| DBINFO                           |     |     |     | x   |     |     |     |     |
| DBPARTITIONNAME                  |     |     |     | x   |     |     |     |     |
| DBPARTITIONNUM                   |     |     |     | x   |     |     |     |     |
| DEALLOCATE                       | x   |     |     | x   |     | x   | x   |     |
| DEC                              | x   | x   | x   |     |     |     | x   |     |
| DECFLOAT                         | x   |     |     |     |     |     |     |     |
| DECIMAL                          | x   | x   | x   |     | x   |     | x   | x   |
| DECLARE                          | x   | x   | x   | x   |     | x   | x   | x   |
| DEFAULT                          | x   | x   | x   | x   | x   | x   | x   | x   |
| DEFAULTS                         |     |     |     | x   |     |     |     |     |
| DEFERRABLE                       |     |     |     |     |     |     | x   | x   |
| DEFERRED                         |     |     |     |     |     |     | x   |     |
| DEFINE                           | x   |     |     |     |     |     |     |     |
| DEFINITION                       |     |     |     | x   |     |     |     |     |
| DELAYED                          |     | x   | x   |     |     |     |     |     |
| DELETE                           | x   | x   | x   | x   | x   | x   | x   | x   |
| DENSERANK                        |     |     |     | x   |     |     |     |     |
| DENSE_RANK                       | x   |     | x   | x   |     |     |     | x   |
| DENY                             |     |     |     |     |     | x   |     |     |
| DEREF                            | x   |     |     |     |     |     |     |     |
| DESC                             |     | x   | x   |     | x   | x   | x   | x   |
| DESCRIBE                         | x   | x   | x   | x   |     |     | x   | x   |
| DESCRIPTOR                       |     |     |     | x   |     |     | x   |     |
| DETERMINISTIC                    | x   | x   | x   | x   |     |     |     | x   |
| DIAGNOSTICS                      |     |     |     | x   |     |     | x   |     |
| DISABLE                          |     |     |     | x   |     |     |     |     |
| DISALLOW                         |     |     |     | x   |     |     |     |     |
| DISCONNECT                       | x   |     |     | x   |     |     | x   |     |
| DISK                             |     |     |     |     |     | x   |     |     |
| DISTINCT                         | x   | x   | x   | x   | x   | x   | x   | x   |
| DISTINCTROW                      |     | x   | x   |     |     |     |     |     |
| DISTRIBUTED                      |     |     |     |     |     | x   |     |     |
| DIV                              |     | x   | x   |     |     |     |     |     |
| DO                               | x   |     |     | x   |     |     |     |     |
| DOCUMENT                         |     |     |     | x   |     |     |     |     |
| DOMAIN                           |     |     |     |     |     |     | x   |     |
| DOUBLE                           | x   | x   | x   | x   |     | x   | x   | x   |
| DO_DOMAIN_IDS                    |     | x   |     |     |     |     |     |     |
| DROP                             | x   | x   | x   | x   | x   | x   | x   | x   |
| DSSIZE                           |     |     |     | x   |     |     |     |     |
| DUAL                             |     | x   | x   |     |     |     |     |     |
| DUMP                             |     |     |     |     |     | x   |     |     |
| DYNAMIC                          | x   |     |     | x   |     |     |     |     |
| EACH                             | x   | x   | x   | x   |     |     |     | x   |
| EDITPROC                         |     |     |     | x   |     |     |     |     |
| ELEMENT                          | x   |     |     |     |     |     |     |     |
| ELSE                             | x   | x   | x   | x   | x   | x   | x   | x   |
| ELSEIF                           | x   | x   | x   | x   |     |     |     | x   |
| EMPTY                            | x   |     | x   |     |     |     |     |     |
| ENABLE                           |     |     |     | x   |     |     |     |     |
| ENCLOSED                         |     | x   | x   |     |     |     |     |     |
| ENCODING                         |     |     |     | x   |     |     |     |     |
| ENCRYPTION                       |     |     |     | x   |     |     |     |     |
| END                              | x   |     |     | x   |     | x   | x   | x   |
| END-EXEC                         | x   |     |     | x   |     |     | x   |     |
| ENDING                           |     |     |     | x   |     |     |     |     |
| END_FRAME                        | x   |     |     |     |     |     |     |     |
| END_PARTITION                    | x   |     |     |     |     |     |     |     |
| EQUALS                           | x   |     |     |     |     |     |     |     |
| ERASE                            |     |     |     | x   |     |     |     |     |
| ERRLVL                           |     |     |     |     |     | x   |     |     |
| ESCAPE                           | x   |     |     | x   |     | x   | x   | x   |
| ESCAPED                          |     | x   | x   |     |     |     |     |     |
| EVERY                            | x   |     |     | x   |     |     |     |     |
| EXCEPT                           | x   | x   | x   | x   |     | x   | x   | x   |
| EXCEPTION                        |     |     |     | x   |     |     |     |     |
| EXCEPTION                        |     |     |     |     |     |     | x   |     |
| EXCLUDING                        |     |     |     | x   |     |     |     |     |
| EXCLUSIVE                        |     |     |     | x   | x   |     |     |     |
| EXEC                             | x   |     |     |     |     | x   | x   |     |
| EXECUTE                          | x   |     |     | x   |     | x   | x   |     |
| EXISTS                           | x   | x   | x   | x   | x   | x   | x   | x   |
| EXIT                             |     | x   | x   | x   |     | x   |     |     |
| EXP                              | x   |     |     |     |     |     |     |     |
| EXPLAIN                          |     | x   | x   | x   |     |     |     | x   |
| EXTERNAL                         | x   |     |     | x   |     | x   | x   |     |
| EXTRACT                          | x   |     |     | x   |     |     | x   |     |
| FALSE                            | x   | x   | x   |     |     |     | x   |     |
| FENCED                           |     |     |     | x   |     |     |     |     |
| FETCH                            | x   | x   | x   | x   |     | x   | x   | x   |
| FIELDPROC                        |     |     |     | x   |     |     |     |     |
| FILE                             |     |     |     | x   | x   | x   |     |     |
| FILLFACTOR                       |     |     |     |     |     | x   |     |     |
| FILTER                           | x   |     |     |     |     |     |     |     |
| FINAL                            |     |     |     | x   |     |     |     |     |
| FIRST                            |     |     |     |     |     |     | x   |     |
| FIRST_VALUE                      | x   |     | x   |     |     |     |     |     |
| FLOAT                            | x   | x   | x   |     | x   |     | x   | x   |
| FLOAT4                           |     | x   | x   |     |     |     |     |     |
| FLOAT8                           |     | x   | x   |     |     |     |     |     |
| FLOOR                            | x   |     |     |     |     |     |     |     |
| FOR                              | x   | x   | x   | x   | x   | x   | x   | x   |
| FORCE                            |     | x   | x   |     |     |     |     |     |
| FOREIGN                          | x   | x   | x   | x   |     | x   | x   | x   |
| FORTRAN                          |     |     |     |     |     |     | x   |     |
| FOUND                            |     |     |     |     |     |     | x   |     |
| FRAME_ROW                        | x   |     |     |     |     |     |     |     |
| FREE                             | x   |     |     | x   |     |     |     |     |
| FREETEXT                         |     |     |     |     |     | x   |     |     |
| FREETEXTTABLE                    |     |     |     |     |     | x   |     |     |
| FROM                             | x   | x   | x   | x   | x   | x   | x   | x   |
| FULL                             | x   |     |     | x   |     | x   | x   |     |
| FULLTEXT                         |     | x   | x   |     |     |     |     |     |
| FUNCTION                         | x   |     | x   | x   |     | x   |     | x   |
| FUSION                           | x   |     |     |     |     |     |     |     |
| GENERAL                          |     | x   |     | x   |     |     |     |     |
| GENERATED                        |     |     | x   | x   |     |     |     |     |
| GET                              | x   |     | x   | x   |     |     | x   | x   |
| GLOB                             |     |     |     |     |     |     |     | x   |
| GLOBAL                           | x   |     |     | x   |     |     | x   |     |
| GO                               |     |     |     | x   |     |     | x   |     |
| GOTO                             |     |     |     | x   |     | x   | x   |     |
| GRANT                            | x   | x   | x   | x   | x   | x   | x   | x   |
| GRAPHIC                          |     |     |     | x   |     |     |     |     |
| GROUP                            | x   | x   | x   | x   | x   | x   | x   | x   |
| GROUPING                         | x   |     | x   |     |     |     |     |     |
| GROUPS                           | x   |     | x   |     |     |     |     |     |
| HANDLER                          | x   |     |     | x   |     |     |     |     |
| HASH                             |     |     |     | x   |     |     |     |     |
| HASHED_VALUE                     |     |     |     | x   |     |     |     |     |
| HAVING                           | x   | x   | x   | x   | x   | x   | x   | x   |
| HIGH_PRIORITY                    |     | x   | x   |     |     |     |     |     |
| HINT                             |     |     |     | x   |     |     |     |     |
| HOLD                             | x   |     |     | x   |     |     |     |     |
| HOLDLOCK                         |     |     |     |     |     | x   |     |     |
| HOUR                             | x   |     |     | x   |     |     | x   |     |
| HOURS                            |     |     |     | x   |     |     |     |     |
| HOUR_MICROSECOND                 |     | x   | x   |     |     |     |     |     |
| HOUR_MINUTE                      |     | x   | x   |     |     |     |     |     |
| HOUR_SECOND                      |     | x   | x   |     |     |     |     |     |
| IDENTIFIED                       |     |     |     |     | x   |     |     |     |
| IDENTITY                         | x   |     |     | x   |     | x   | x   |     |
| IDENTITYCOL                      |     |     |     |     |     | x   |     |     |
| IDENTITY_INSERT                  |     |     |     |     |     | x   |     |     |
| IF                               | x   | x   | x   | x   |     | x   |     | x   |
| IGNORE                           |     | x   | x   |     |     |     |     |     |
| IGNORE_DOMAIN_IDS                |     | x   |     |     |     |     |     |     |
| IGNORE_SERVER_IDS                |     | x   |     |     |     |     |     |     |
| IMMEDIATE                        |     |     |     | x   | x   |     | x   | x   |
| IN                               | x   | x   | x   | x   | x   | x   | x   | x   |
| INCLUDE                          |     |     |     |     |     |     | x   |     |
| INCLUDING                        |     |     |     | x   |     |     |     |     |
| INCLUSIVE                        |     |     |     | x   |     |     |     |     |
| INCREMENT                        |     |     |     | x   | x   |     |     |     |
| INDEX                            |     | x   | x   | x   | x   | x   | x   | x   |
| INDICATOR                        | x   |     |     | x   |     |     | x   |     |
| INF                              |     |     |     | x   |     |     |     |     |
| INFILE                           |     | x   | x   |     |     |     |     |     |
| INFINITY                         |     |     |     | x   |     |     |     |     |
| INHERIT                          |     |     |     | x   |     |     |     |     |
| INITIAL                          | x   |     |     |     | x   |     |     |     |
| INITIALLY                        |     |     |     |     |     |     | x   |     |
| INNER                            | x   | x   | x   | x   |     | x   | x   | x   |
| INOUT                            | x   | x   | x   | x   |     |     |     | x   |
| INPUT                            |     |     |     |     |     |     | x   |     |
| INSENSITIVE                      | x   | x   | x   | x   |     |     | x   | x   |
| INSERT                           | x   | x   | x   | x   | x   | x   | x   | x   |
| INT                              | x   | x   | x   |     |     |     | x   |     |
| INT1                             |     | x   | x   |     |     |     |     |     |
| INT2                             |     | x   | x   |     |     |     |     |     |
| INT3                             |     | x   | x   |     |     |     |     |     |
| INT4                             |     | x   | x   |     |     |     |     |     |
| INT8                             |     | x   | x   |     |     |     |     |     |
| INTEGER                          | x   | x   | x   |     | x   |     | x   | x   |
| INTEGRITY                        |     |     |     | x   |     |     |     |     |
| INTERSECT                        | x   | x   |     | x   | x   | x   | x   | x   |
| INTERSECTION                     | x   |     |     |     |     |     |     |     |
| INTERVAL                         | x   | x   | x   |     |     |     | x   |     |
| INTO                             | x   | x   | x   | x   | x   | x   | x   | x   |
| IO_AFTER_GTIDS                   |     |     | x   |     |     |     |     |     |
| IO_BEFORE_GTIDS                  |     |     | x   |     |     |     |     |     |
| IS                               | x   | x   | x   | x   | x   | x   | x   | x   |
| ISOBID                           |     |     |     | x   |     |     |     |     |
| ISOLATION                        |     |     |     | x   |     |     | x   |     |
| ITERATE                          | x   | x   | x   | x   |     |     |     | x   |
| JAR                              |     |     |     | x   |     |     |     |     |
| JAVA                             |     |     |     | x   |     |     |     |     |
| JOIN                             | x   | x   | x   | x   |     | x   | x   | x   |
| JSON_ARRAY                       | x   |     |     |     |     |     |     |     |
| JSON_ARRAYAGG                    | x   |     |     |     |     |     |     |     |
| JSON_EXISTS                      | x   |     |     |     |     |     |     |     |
| JSON_OBJECT                      | x   |     |     |     |     |     |     |     |
| JSON_OBJECTAGG                   | x   |     |     |     |     |     |     |     |
| JSON_QUERY                       | x   |     |     |     |     |     |     |     |
| JSON_TABLE                       | x   |     | x   |     |     |     |     |     |
| JSON_TABLE_PRIMITIVE             | x   |     |     |     |     |     |     |     |
| JSON_VALUE                       | x   |     |     |     |     |     |     |     |
| KEEP                             |     |     |     | x   |     |     |     |     |
| KEY                              |     | x   | x   | x   |     | x   | x   |     |
| KEYS                             |     | x   | x   |     |     |     |     |     |
| KILL                             |     | x   | x   |     |     | x   |     |     |
| LABEL                            |     |     |     | x   |     |     |     |     |
| LAG                              | x   |     | x   |     |     |     |     |     |
| LANGUAGE                         | x   |     |     | x   |     |     | x   |     |
| LARGE                            | x   |     |     |     |     |     |     |     |
| LAST                             |     |     |     |     |     |     | x   |     |
| LAST_VALUE                       | x   |     | x   |     |     |     |     |     |
| LATERAL                          | x   |     |     | x   |     |     |     |     |
| LC_CTYPE                         |     |     |     | x   |     |     |     |     |
| LEAD                             | x   |     | x   |     |     |     |     |     |
| LEADING                          | x   | x   | x   |     |     |     | x   |     |
| LEAVE                            | x   | x   | x   | x   |     |     |     | x   |
| LEAVESLEFT                       |     |     | x   |     |     |     |     |     |
| LEFT                             | x   | x   |     | x   |     | x   | x   | x   |
| LEVEL                            |     |     |     |     | x   |     | x   |     |
| LIKE                             | x   | x   | x   | x   | x   | x   | x   | x   |
| LIKE_REGEX                       | x   |     |     |     |     |     |     |     |
| LIMIT                            |     | x   | x   |     |     |     |     | x   |
| LINEAR                           |     | x   | x   |     |     |     |     |     |
| LINENO                           |     |     |     |     |     | x   |     |     |
| LINES                            |     | x   | x   |     |     |     |     |     |
| LINKTYPE                         |     |     |     | x   |     |     |     |     |
| LN                               | x   |     |     |     |     |     |     |     |
| LOAD                             |     | x   | x   |     |     | x   |     |     |
| LOCAL                            | x   |     |     | x   |     |     | x   |     |
| LOCALDATE                        |     |     |     | x   |     |     |     |     |
| LOCALE                           |     |     |     | x   |     |     |     |     |
| LOCALTIME                        | x   | x   | x   | x   |     |     |     | x   |
| LOCALTIMESTAMP                   | x   | x   | x   | x   |     |     |     | x   |
| LOCATOR                          |     |     |     | x   |     |     |     |     |
| LOCATORS                         |     |     |     | x   |     |     |     |     |
| LOCK                             |     | x   | x   | x   | x   |     |     |     |
| LOCKMAX                          |     |     |     | x   |     |     |     |     |
| LOCKSIZE                         |     |     |     | x   |     |     |     |     |
| LONG                             |     | x   | x   | x   | x   |     |     |     |
| LONGBLOB                         |     | x   | x   |     |     |     |     |     |
| LONGTEXT                         |     | x   | x   |     |     |     |     |     |
| LOOP                             | x   | x   | x   | x   |     |     |     | x   |
| LOWER                            | x   |     |     |     |     |     | x   |     |
| LOW_PRIORITY                     |     | x   | x   |     |     |     |     |     |
| MAINTAINED                       |     |     |     | x   |     |     |     |     |
| MASTER_BIND                      |     |     | x   |     |     |     |     |     |
| MASTER_HEARTBEAT_PERIOD          |     | x   |     |     |     |     |     |     |
| MASTER_SSL_VERIFY_SERVER_CERT    |     | x   | x   |     |     |     |     |     |
| MATCH                            | x   | x   | x   |     |     |     | x   | x   |
| MATCHES                          | x   |     |     |     |     |     |     |     |
| MATCH_NUMBER                     | x   |     |     |     |     |     |     |     |
| MATCH_RECOGNIZE                  | x   |     |     |     |     |     |     |     |
| MATERIALIZED                     |     |     |     | x   |     |     |     |     |
| MAX                              | x   | x   |     |     |     |     | x   |     |
| MAXEXTENTS                       |     |     |     |     | x   |     |     |     |
| MAXVALUE                         |     | x   | x   | x   |     |     |     |     |
| MEDIUMBLOB                       |     | x   | x   |     |     |     |     |     |
| MEDIUMINT                        |     | x   | x   |     |     |     |     |     |
| MEDIUMTEXT                       |     | x   | x   |     |     |     |     |     |
| MEMBER                           | x   |     |     |     |     |     |     |     |
| MERGE                            | x   |     |     |     |     | x   |     |     |
| METHOD                           | x   |     |     |     |     |     |     |     |
| MICROSECOND                      |     |     |     | x   |     |     |     |     |
| MICROSECONDS                     |     |     |     | x   |     |     |     |     |
| MIDDLEINT                        |     | x   | x   |     |     |     |     |     |
| MIN                              | x   |     |     |     |     |     | x   |     |
| MINUS                            |     |     |     |     | x   |     |     |     |
| MINUTE                           | x   |     |     | x   |     |     | x   |     |
| MINUTES                          |     |     |     | x   |     |     |     |     |
| MINUTE_MICROSECOND               |     | x   | x   |     |     |     |     |     |
| MINUTE_SECOND                    |     | x   | x   |     |     |     |     |     |
| MINVALUE                         |     |     |     | x   |     |     |     |     |
| MLSLABEL                         |     |     |     |     | x   |     |     |     |
| MOD                              | x   | x   | x   |     |     |     |     |     |
| MODE                             |     |     |     | x   | x   |     |     |     |
| MODEMODIFIES                     |     |     | x   |     |     |     |     |     |
| MODIFIES                         | x   | x   |     | x   |     |     |     |     |
| MODIFY                           |     |     |     |     | x   |     |     |     |
| MODULE                           | x   |     |     |     |     |     | x   |     |
| MONTH                            | x   |     |     | x   |     |     | x   |     |
| MONTHS                           |     |     |     | x   |     |     |     |     |
| MULTISET                         | x   |     |     |     |     |     |     |     |
| NAMES                            |     |     |     |     |     |     | x   |     |
| NAN                              |     |     |     | x   |     |     |     |     |
| NATIONAL                         | x   |     |     |     |     | x   | x   |     |
| NATURAL                          | x   | x   | x   |     |     |     | x   | x   |
| NCHAR                            | x   |     |     |     |     |     | x   |     |
| NCLOB                            | x   |     |     |     |     |     |     |     |
| NESTED_TABLE_ID                  |     |     |     |     | x   |     |     |     |
| NEW                              | x   |     |     | x   |     |     |     |     |
| NEW_TABLE                        |     |     |     | x   |     |     |     |     |
| NEXT                             |     |     |     |     |     |     | x   |     |
| NEXTVAL                          |     |     |     | x   |     |     |     |     |
| NO                               | x   |     |     | x   |     |     | x   |     |
| NOAUDIT                          |     |     |     |     | x   |     |     |     |
| NOCACHE                          |     |     |     | x   |     |     |     |     |
| NOCHECK                          |     |     |     |     |     | x   |     |     |
| NOCOMPRESS                       |     |     |     |     | x   |     |     |     |
| NOCYCLE                          |     |     |     | x   |     |     |     |     |
| NODENAME                         |     |     |     | x   |     |     |     |     |
| NODENUMBER                       |     |     |     | x   |     |     |     |     |
| NOMAXVALUE                       |     |     |     | x   |     |     |     |     |
| NOMINVALUE                       |     |     |     | x   |     |     |     |     |
| NONCLUSTERED                     |     |     |     |     |     | x   |     |     |
| NONE                             | x   |     |     | x   |     |     | x   |     |
| NOORDER                          |     |     |     | x   |     |     |     |     |
| NORMALIZE                        | x   |     |     |     |     |     |     |     |
| NORMALIZED                       |     |     |     | x   |     |     |     |     |
| NOT                              | x   | x   | x   | x   | x   | x   | x   | x   |
| NOTNULL                          |     |     |     |     |     |     |     | x   |
| NOWAIT                           |     |     |     |     | x   |     |     |     |
| NO_WRITE_TO_BINLOG               |     | x   | x   |     |     |     |     |     |
| NTH_VALUE                        | x   |     | x   |     |     |     |     |     |
| NTILE                            | x   |     | x   |     |     |     |     |     |
| NULL                             | x   | x   | x   | x   | x   | x   | x   | x   |
| NULLIF                           | x   |     |     |     |     | x   | x   |     |
| NULLS                            |     |     |     | x   |     |     |     |     |
| NUMBER                           |     |     |     |     | x   |     |     |     |
| NUMERIC                          | x   | x   | x   |     |     |     | x   |     |
| NUMPARTS                         |     |     |     | x   |     |     |     |     |
| OBID                             |     |     |     | x   |     |     |     |     |
| OCCURRENCES_REGEX                | x   |     |     |     |     |     |     |     |
| OCTET_LENGTH                     | x   |     |     |     |     |     | x   |     |
| OF                               | x   |     | x   | x   | x   | x   | x   | x   |
| OFF                              |     |     |     |     |     | x   |     |     |
| OFFLINE                          |     |     |     |     | x   |     |     |     |
| OFFSET                           | x   |     |     |     |     |     |     |     |
| OFFSETS                          |     |     |     |     |     | x   |     |     |
| OLD                              | x   |     |     | x   |     |     |     |     |
| OLD_TABLE                        |     |     |     | x   |     |     |     |     |
| OMIT                             | x   |     |     |     |     |     |     |     |
| ON                               | x   | x   | x   | x   | x   | x   | x   | x   |
| ONE                              | x   |     |     |     |     |     |     |     |
| ONLINE                           |     |     |     |     | x   |     |     |     |
| ONLY                             | x   |     |     |     |     |     | x   |     |
| OPEN                             | x   |     |     | x   |     | x   | x   |     |
| OPENDATASOURCE                   |     |     |     |     |     | x   |     |     |
| OPENQUERY                        |     |     |     |     |     | x   |     |     |
| OPENROWSET                       |     |     |     |     |     | x   |     |     |
| OPENXML                          |     |     |     |     |     | x   |     |     |
| OPTIMIZATION                     |     |     |     | x   |     |     |     |     |
| OPTIMIZE                         |     | x   | x   | x   |     |     |     |     |
| OPTIMIZER_COSTS                  |     |     | x   |     |     |     |     |     |
| OPTION                           |     | x   | x   | x   | x   | x   | x   |     |
| OPTIONALLY                       |     | x   | x   |     |     |     |     |     |
| OR                               | x   | x   | x   | x   | x   | x   | x   | x   |
| ORDER                            | x   | x   | x   | x   | x   | x   | x   | x   |
| OUT                              | x   | x   | x   | x   |     |     |     | x   |
| OUTER                            | x   | x   | x   | x   |     | x   | x   | x   |
| OUTFILE                          |     | x   | x   |     |     |     |     |     |
| OUTPUT                           |     |     |     |     |     |     | x   |     |
| OVER                             | x   | x   | x   | x   |     | x   |     | x   |
| OVERLAPS                         | x   |     |     |     |     |     | x   |     |
| OVERLAY                          | x   |     |     |     |     |     |     |     |
| OVERRIDING                       |     |     |     | x   |     |     |     |     |
| PACKAGE                          |     |     |     | x   |     |     |     |     |
| PAD                              |     |     |     |     |     |     | x   |     |
| PADDED                           |     |     |     | x   |     |     |     |     |
| PAGESIZE                         |     |     |     | x   |     |     |     |     |
| PAGE_CHECKSUM                    |     | x   |     |     |     |     |     |     |
| PARAMETER                        | x   |     |     | x   |     |     |     |     |
| PARSE_VCOL_EXPR                  |     | x   |     |     |     |     |     |     |
| PART                             |     |     |     | x   |     |     |     |     |
| PARTIAL                          |     |     |     |     |     |     | x   |     |
| PARTITION                        | x   | x   | x   | x   |     |     |     | x   |
| PARTITIONED                      |     |     |     | x   |     |     |     |     |
| PARTITIONING                     |     |     |     | x   |     |     |     |     |
| PARTITIONS                       |     |     |     | x   |     |     |     |     |
| PASCAL                           |     |     |     |     |     |     | x   |     |
| PASSWORD                         |     |     |     | x   |     |     |     |     |
| PATH                             |     |     |     | x   |     |     |     |     |
| PATTERN                          | x   |     |     |     |     |     |     |     |
| PCTFREE                          |     |     |     |     | x   |     |     |     |
| PER                              | x   |     |     |     |     |     |     |     |
| PERCENT                          | x   |     |     |     |     | x   |     |     |
| PERCENTILE_CONT                  | x   |     |     |     |     |     |     |     |
| PERCENTILE_DISC                  | x   |     |     |     |     |     |     |     |
| PERCENT_RANK                     | x   |     | x   |     |     |     |     |     |
| PERIOD                           | x   |     |     |     |     |     |     |     |
| PERSIST                          |     |     | x   |     |     |     |     |     |
| PERSIST_ONLY                     |     |     | x   |     |     |     |     |     |
| PIECESIZE                        |     |     |     | x   |     |     |     |     |
| PIVOT                            |     |     |     |     |     | x   |     |     |
| PLAN                             |     |     |     | x   |     | x   |     |     |
| PORTION                          | x   |     |     |     |     |     |     |     |
| POSITION                         | x   |     |     | x   |     |     | x   |     |
| POSITION_REGEX                   | x   |     |     |     |     |     |     |     |
| POWER                            | x   |     |     |     |     |     |     |     |
| PRAGMA                           |     |     |     |     |     |     |     | x   |
| PRECEDES                         | x   |     |     |     |     |     |     |     |
| PRECISION                        | x   | x   | x   | x   |     | x   | x   | x   |
| PREPARE                          | x   |     |     | x   |     |     | x   |     |
| PRESERVE                         |     |     |     |     |     |     | x   |     |
| PREVVAL                          |     |     |     | x   |     |     |     |     |
| PRIMARY                          | x   | x   | x   | x   |     | x   | x   | x   |
| PRINT                            |     |     |     |     |     | x   |     |     |
| PRIOR                            |     |     |     |     | x   |     | x   |     |
| PRIQTY                           |     |     |     | x   |     |     |     |     |
| PRIVILEGES                       |     |     |     | x   |     |     | x   |     |
| PROC                             |     |     |     |     |     | x   |     |     |
| PROCEDURE                        | x   | x   | x   | x   |     | x   | x   | x   |
| PROGRAM                          |     |     |     | x   |     |     |     |     |
| PSID                             |     |     |     | x   |     |     |     |     |
| PUBLIC                           |     |     |     | x   | x   | x   | x   |     |
| PURGE                            |     | x   | x   |     |     |     |     |     |
| QUERY                            |     |     |     | x   |     |     |     |     |
| QUERYNO                          |     |     |     | x   |     |     |     |     |
| RAISERROR                        |     |     |     |     |     | x   |     |     |
| RANGE                            | x   | x   | x   | x   |     |     |     | x   |
| RANK                             | x   |     | x   | x   |     |     |     | x   |
| RAW                              |     |     |     |     | x   |     |     |     |
| READ                             |     | x   | x   | x   |     | x   | x   |     |
| READS                            | x   | x   | x   | x   |     |     |     | x   |
| READTEXT                         |     |     |     |     |     | x   |     |     |
| READ_WRITE                       |     | x   | x   |     |     |     |     |     |
| REAL                             | x   | x   | x   |     |     |     | x   |     |
| RECONFIGURE                      |     |     |     |     |     | x   |     |     |
| RECOVERY                         |     |     |     | x   |     |     |     |     |
| RECURSIVE                        | x   | x   | x   |     |     |     |     | x   |
| REF                              | x   |     |     |     |     |     |     |     |
| REFERENCES                       | x   | x   | x   | x   |     | x   | x   | x   |
| REFERENCING                      | x   |     |     | x   |     |     |     |     |
| REFRESH                          |     |     |     | x   |     |     |     |     |
| REF_SYSTEM_ID                    |     | x   |     |     |     |     |     |     |
| REGEXP                           |     | x   | x   |     |     |     |     | x   |
| REGR_AVGX                        | x   |     |     |     |     |     |     |     |
| REGR_AVGY                        | x   |     |     |     |     |     |     |     |
| REGR_COUNT                       | x   |     |     |     |     |     |     |     |
| REGR_INTERCEPT                   | x   |     |     |     |     |     |     |     |
| REGR_R2                          | x   |     |     |     |     |     |     |     |
| REGR_SLOPE                       | x   |     |     |     |     |     |     |     |
| REGR_SXX                         | x   |     |     |     |     |     |     |     |
| REGR_SXY                         | x   |     |     |     |     |     |     |     |
| REGR_SYY                         | x   |     |     |     |     |     |     |     |
| REINDEX                          |     |     |     |     |     |     |     | x   |
| RELATIVE                         |     |     |     |     |     |     | x   |     |
| RELEASE                          | x   | x   | x   | x   |     |     |     | x   |
| RENAME                           |     | x   | x   | x   | x   |     |     | x   |
| REPEAT                           | x   | x   | x   | x   |     |     |     | x   |
| REPEATABLEREPLACE                |     |     | x   |     |     |     |     |     |
| REPLACE                          |     | x   |     |     |     |     |     | x   |
| REPLICATION                      |     |     |     |     |     | x   |     |     |
| REQUIRE                          |     | x   | x   |     |     |     |     |     |
| RESET                            |     |     |     | x   |     |     |     |     |
| RESIGNAL                         | x   | x   | x   | x   |     |     |     | x   |
| RESOURCE                         |     |     |     |     | x   |     |     |     |
| RESTART                          |     |     |     | x   |     |     |     |     |
| RESTORE                          |     |     |     |     |     | x   |     |     |
| RESTRICT                         |     | x   | x   | x   |     | x   | x   |     |
| RESULT                           | x   |     |     | x   |     |     |     |     |
| RESULT_SET_LOCATOR               |     |     |     | x   |     |     |     |     |
| RETURN                           | x   | x   | x   | x   |     | x   |     | x   |
| RETURNING                        |     | x   |     |     |     |     |     |     |
| RETURNS                          | x   |     |     | x   |     |     |     |     |
| REVERT                           |     |     |     |     |     | x   |     |     |
| REVOKE                           | x   | x   | x   | x   | x   | x   | x   | x   |
| RIGHT                            | x   | x   | x   | x   |     | x   | x   | x   |
| RLIKE                            |     | x   | x   |     |     |     |     |     |
| ROLE                             |     |     |     | x   |     |     |     |     |
| ROLLBACK                         | x   |     |     | x   |     | x   | x   | x   |
| ROLLUP                           | x   |     |     |     |     |     |     |     |
| ROUND_CEILING                    |     |     |     | x   |     |     |     |     |
| ROUND_DOWN                       |     |     |     | x   |     |     |     |     |
| ROUND_FLOOR                      |     |     |     | x   |     |     |     |     |
| ROUND_HALF_DOWN                  |     |     |     | x   |     |     |     |     |
| ROUND_HALF_EVEN                  |     |     |     | x   |     |     |     |     |
| ROUND_HALF_UP                    |     |     |     | x   |     |     |     |     |
| ROUND_UP                         |     |     |     | x   |     |     |     |     |
| ROUTINE                          |     |     |     | x   |     |     |     |     |
| ROW                              | x   |     | x   | x   | x   |     |     | x   |
| ROWCOUNT                         |     |     |     |     |     | x   |     |     |
| ROWGUIDCOL                       |     |     |     |     |     | x   |     |     |
| ROWID                            |     |     |     |     | x   |     |     |     |
| ROWNUM                           |     |     |     |     | x   |     |     |     |
| ROWNUMBER                        |     |     |     | x   |     |     |     |     |
| ROWS                             | x   | x   | x   | x   | x   |     | x   | x   |
| ROWSET                           |     |     |     | x   |     |     |     |     |
| ROW_NUMBER                       | x   |     | x   | x   |     |     |     | x   |
| RRN                              |     |     |     | x   |     |     |     |     |
| RULE                             |     |     |     |     |     | x   |     |     |
| RUN                              |     |     |     | x   |     |     |     |     |
| RUNNING                          | x   |     |     |     |     |     |     |     |
| SAVE                             |     |     |     |     |     | x   |     |     |
| SAVEPOINT                        | x   |     |     | x   |     |     |     | x   |
| SCHEDULESCHEMA                   |     |     | x   |     |     |     |     |     |
| SCHEMA                           |     | x   |     | x   |     | x   | x   |     |
| SCHEMAS                          |     | x   | x   |     |     |     |     |     |
| SCOPE                            | x   |     |     |     |     |     |     |     |
| SCRATCHPAD                       |     |     |     | x   |     |     |     |     |
| SCROLL                           | x   |     |     | x   |     |     | x   |     |
| SEARCH                           | x   |     |     | x   |     |     |     |     |
| SECOND                           | x   |     |     | x   |     |     | x   |     |
| SECONDS                          |     |     |     | x   |     |     |     |     |
| SECOND_MICROSECOND               |     | x   | x   |     |     |     |     |     |
| SECQTY                           |     |     |     | x   |     |     |     |     |
| SECTION                          |     |     |     |     |     |     | x   |     |
| SECURITY                         |     |     |     | x   |     |     |     |     |
| SECURITYAUDIT                    |     |     |     |     |     | x   |     |     |
| SEEK                             | x   |     |     |     |     |     |     |     |
| SELECT                           | x   | x   | x   | x   | x   | x   | x   | x   |
| SEMANTICKEYPHRASETABLE           |     |     |     |     |     | x   |     |     |
| SEMANTICSIMILARITYDETAILSTABLE   |     |     |     |     |     | x   |     |     |
| SEMANTICSIMILARITYTABLE          |     |     |     |     |     | x   |     |     |
| SENSITIVE                        | x   | x   | x   | x   |     |     |     | x   |
| SEPARATOR                        |     | x   | x   |     |     |     |     |     |
| SEQUENCE                         |     |     |     | x   |     |     |     |     |
| SESSION                          |     |     |     | x   | x   |     | x   |     |
| SESSION_USER                     | x   |     |     | x   |     | x   | x   |     |
| SET                              | x   | x   | x   | x   | x   | x   | x   | x   |
| SETUSER                          |     |     |     |     |     | x   |     |     |
| SHARE                            |     |     |     |     | x   |     |     |     |
| SHOW                             | x   | x   | x   |     |     |     |     |     |
| SHUTDOWN                         |     |     |     |     |     | x   |     |     |
| SIGNAL                           | x   | x   | x   | x   |     |     |     | x   |
| SIMILAR                          | x   |     |     |     |     |     |     |     |
| SIMPLE                           |     |     |     | x   |     |     |     |     |
| SIZE                             |     |     |     |     | x   |     | x   |     |
| SKIP                             | x   |     |     |     |     |     |     |     |
| SLOW                             |     | x   |     |     |     |     |     |     |
| SMALLINT                         | x   | x   | x   |     | x   |     | x   | x   |
| SNAN                             |     |     |     | x   |     |     |     |     |
| SOME                             | x   |     |     | x   |     | x   | x   |     |
| SOURCE                           |     |     |     | x   |     |     |     |     |
| SPACE                            |     |     |     |     |     |     | x   |     |
| SPATIAL                          |     | x   | x   |     |     |     |     |     |
| SPECIFIC                         | x   | x   | x   | x   |     |     |     | x   |
| SPECIFICTYPE                     | x   |     |     |     |     |     |     |     |
| SQL                              | x   | x   | x   | x   |     |     | x   | x   |
| SQLCA                            |     |     |     |     |     |     | x   |     |
| SQLCODE                          |     |     |     |     |     |     | x   |     |
| SQLERROR                         |     |     |     |     |     |     | x   |     |
| SQLEXCEPTION                     | x   | x   | x   |     |     |     |     |     |
| SQLID                            |     |     |     | x   |     |     |     |     |
| SQLSTATE                         | x   | x   | x   |     |     |     | x   |     |
| SQLWARNING                       | x   | x   | x   |     |     |     | x   |     |
| SQL_BIG_RESULT                   |     | x   | x   |     |     |     |     |     |
| SQL_CALC_FOUND_ROWS              |     | x   | x   |     |     |     |     |     |
| SQL_SMALL_RESULT                 |     | x   | x   |     |     |     |     |     |
| SQRT                             | x   |     |     |     |     |     |     |     |
| SSL                              |     | x   | x   |     |     |     |     |     |
| STACKED                          |     |     |     | x   |     |     |     |     |
| STANDARD                         |     |     |     | x   |     |     |     |     |
| START                            | x   |     |     | x   | x   |     |     | x   |
| STARTING                         |     | x   | x   | x   |     |     |     |     |
| STATEMENT                        |     |     |     | x   |     |     |     |     |
| STATIC                           | x   |     |     | x   |     |     |     |     |
| STATISTICS                       |     |     |     |     |     | x   |     |     |
| STATMENT                         |     |     |     | x   |     |     |     |     |
| STATS_AUTO_RECALC                |     | x   |     |     |     |     |     |     |
| STATS_PERSISTENT                 |     | x   |     |     |     |     |     |     |
| STATS_SAMPLE_PAGES               |     | x   |     |     |     |     |     |     |
| STAY                             |     |     |     | x   |     |     |     |     |
| STDDEV_POP                       | x   |     |     |     |     |     |     |     |
| STDDEV_SAMP                      | x   |     |     |     |     |     |     |     |
| STOGROUP                         |     |     |     | x   |     |     |     |     |
| STORED                           |     |     | x   |     |     |     |     |     |
| STORES                           |     |     |     | x   |     |     |     |     |
| STRAIGHT_JOIN                    |     | x   | x   |     |     |     |     |     |
| STYLE                            |     |     |     | x   |     |     |     |     |
| SUBMULTISET                      | x   |     |     |     |     |     |     |     |
| SUBSET                           | x   |     |     |     |     |     |     |     |
| SUBSTRING                        | x   |     |     | x   |     |     | x   |     |
| SUBSTRING_REGEX                  | x   |     |     |     |     |     |     |     |
| SUCCEEDS                         | x   |     |     |     |     |     |     |     |
| SUCCESSFUL                       |     |     |     |     | x   |     |     |     |
| SUM                              | x   |     |     |     |     |     | x   |     |
| SUMMARY                          |     |     |     | x   |     |     |     |     |
| SYMMETRIC                        | x   |     |     |     |     |     |     |     |
| SYNONYM                          |     |     |     | x   | x   |     |     |     |
| SYSDATE                          |     |     |     |     | x   |     |     |     |
| SYSFUN                           |     |     |     | x   |     |     |     |     |
| SYSIBM                           |     |     |     | x   |     |     |     |     |
| SYSPROC                          |     |     |     | x   |     |     |     |     |
| SYSTEM                           | x   |     | x   | x   |     |     |     | x   |
| SYSTEM_TIME                      | x   |     |     |     |     |     |     |     |
| SYSTEM_USER                      | x   |     |     | x   |     | x   | x   |     |
| SYS_*                            |     |     |     |     | x   |     |     |     |
| TABLE                            | x   | x   | x   | x   | x   | x   | x   | x   |
| TABLESAMPLE                      | x   |     |     |     |     | x   |     |     |
| TABLESPACE                       |     |     |     | x   |     |     |     |     |
| TEMPORARY                        |     |     |     |     |     |     | x   |     |
| TERMINATED                       |     | x   | x   |     |     |     |     |     |
| TEXTSIZE                         |     |     |     |     |     | x   |     |     |
| THEN                             | x   | x   | x   | x   | x   | x   | x   | x   |
| TIME                             | x   |     |     | x   |     |     | x   |     |
| TIMESTAMP                        | x   |     |     | x   |     |     | x   |     |
| TIMEZONE_HOUR                    | x   |     |     |     |     |     | x   |     |
| TIMEZONE_MINUTE                  | x   |     |     |     |     |     | x   |     |
| TINYBLOB                         |     | x   | x   |     |     |     |     |     |
| TINYINT                          |     | x   | x   |     |     |     |     |     |
| TINYTEXT                         |     | x   | x   |     |     |     |     |     |
| TO                               | x   | x   | x   | x   | x   | x   | x   | x   |
| TOP                              |     |     |     |     |     | x   |     |     |
| TRAILING                         | x   | x   | x   |     |     |     | x   |     |
| TRAN                             |     |     |     |     |     | x   |     |     |
| TRANSACTION                      |     |     |     | x   |     | x   | x   | x   |
| TRANSLATE                        | x   |     |     |     |     |     | x   |     |
| TRANSLATE_REGEX                  | x   |     |     |     |     |     |     |     |
| TRANSLATION                      | x   |     |     |     |     |     | x   |     |
| TREAT                            | x   |     |     |     |     |     |     |     |
| TRIGGER                          | x   | x   | x   | x   | x   | x   |     | x   |
| TRIM                             | x   |     |     | x   |     |     | x   |     |
| TRIM_ARRAY                       | x   |     |     |     |     |     |     |     |
| TRUE                             | x   | x   | x   |     |     |     | x   |     |
| TRUNCATE                         | x   |     |     | x   |     | x   |     |     |
| TRY_CONVERT                      |     |     |     |     |     | x   |     |     |
| TSEQUAL                          |     |     |     |     |     | x   |     |     |
| TYPE                             |     |     |     | x   |     |     |     |     |
| UESCAPE                          | x   |     |     |     |     |     |     |     |
| UID                              |     |     |     |     | x   |     |     |     |
| UNDO                             |     | x   | x   | x   |     |     |     |     |
| UNION                            | x   | x   | x   | x   | x   | x   | x   | x   |
| UNIQUE                           | x   | x   | x   | x   | x   | x   | x   | x   |
| UNKNOWN                          | x   |     |     |     |     |     | x   |     |
| UNLOCK                           |     | x   | x   |     |     |     |     |     |
| UNNEST                           | x   |     |     |     |     |     |     |     |
| UNPIVOT                          |     |     |     |     |     | x   |     |     |
| UNSIGNED                         |     | x   | x   |     |     |     |     |     |
| UNTIL                            | x   |     |     | x   |     |     |     |     |
| UPDATE                           | x   | x   | x   | x   | x   | x   | x   | x   |
| UPDATETEXT                       |     |     |     |     |     | x   |     |     |
| UPPER                            | x   |     |     |     |     |     | x   |     |
| USAGE                            |     | x   | x   | x   |     |     | x   |     |
| USE                              |     | x   | x   |     |     | x   |     |     |
| USER                             | x   |     |     | x   | x   | x   | x   | x   |
| USING                            | x   | x   | x   | x   |     |     | x   | x   |
| UTC_DATE                         |     | x   | x   |     |     |     |     |     |
| UTC_TIME                         |     | x   | x   |     |     |     |     |     |
| UTC_TIMESTAMP                    |     | x   | x   |     |     |     |     |     |
| VALIDATE                         |     |     |     |     | x   |     |     |     |
| VALIDPROC                        |     |     |     | x   |     |     |     |     |
| VALUE                            | x   |     |     | x   |     |     | x   |     |
| VALUES                           | x   | x   | x   | x   | x   | x   | x   | x   |
| VALUE_OF                         | x   |     |     |     |     |     |     |     |
| VARBINARY                        | x   | x   | x   |     |     |     |     |     |
| VARCHAR                          | x   | x   | x   |     | x   |     | x   | x   |
| VARCHAR2                         |     |     |     |     | x   |     |     |     |
| VARCHARACTER                     |     | x   | x   |     |     |     |     |     |
| VARIABLE                         |     |     |     | x   |     |     |     |     |
| VARIANT                          |     |     |     | x   |     |     |     |     |
| VARYING                          | x   | x   | x   |     |     | x   | x   |     |
| VAR_POP                          | x   |     |     |     |     |     |     |     |
| VAR_SAMP                         | x   |     |     |     |     |     |     |     |
| VCAT                             |     |     |     | x   |     |     |     |     |
| VERSION                          |     |     |     | x   |     |     |     |     |
| VERSIONING                       | x   |     |     |     |     |     |     |     |
| VIEW                             |     |     |     | x   | x   | x   | x   | x   |
| VIRTUAL                          |     |     | x   |     |     |     |     |     |
| VOLATILE                         |     |     |     | x   |     |     |     |     |
| VOLUMES                          |     |     |     | x   |     |     |     |     |
| WAITFOR                          |     |     |     |     |     | x   |     |     |
| WHEN                             | x   | x   | x   | x   |     | x   | x   | x   |
| WHENEVER                         | x   |     |     | x   | x   |     | x   | x   |
| WHERE                            | x   | x   | x   | x   | x   | x   | x   | x   |
| WHILE                            | x   | x   | x   | x   |     | x   |     | x   |
| WIDTH_BUCKET                     | x   |     |     |     |     |     |     |     |
| WINDOW                           | x   | x   | x   |     |     |     |     |     |
| WITH                             | x   | x   | x   | x   | x   | x   | x   | x   |
| WITHIN                           | x   |     |     |     |     |     |     |     |
| WITHIN GROUP                     |     |     |     |     |     | x   |     |     |
| WITHOUT                          | x   |     |     | x   |     |     |     | x   |
| WLM                              |     |     |     | x   |     |     |     |     |
| WORK                             |     |     |     |     |     |     | x   |     |
| WRITE                            |     | x   | x   | x   |     |     | x   |     |
| WRITETEXT                        |     |     |     |     |     | x   |     |     |
| XMLELEMENT                       |     |     |     | x   |     |     |     |     |
| XMLEXISTS                        |     |     |     | x   |     |     |     |     |
| XMLNAMESPACES                    |     |     |     | x   |     |     |     |     |
| XOR                              |     | x   | x   |     |     |     |     |     |
| YEAR                             | x   |     |     | x   |     |     | x   |     |
| YEARS                            |     |     |     | x   |     |     |     |     |
| YEAR_MONTH                       |     | x   | x   |     |     |     |     |     |
| ZEROFILL                         |     | x   | x   |     |     |     |     |     |
| ZONE                             |     |     |     |     |     |     | x   |     |
+----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+

No more mysql.proc in MySQL 8.0

MySQL has thrown away the mysql.proc table for version 8.0 Development Releases.

The best explanation that I’ve seen is the one that Dmitry Lenev gave at a conference last October.

To summarize it: mysql.proc and its ilk are non-transactional, redundancy is bad, and MySQL can fix some known bugs by moving over to information_schema tables backed by InnoDB. Of course I approve for a separate reason: mysql.proc is non-standard and therefore it is a mistake.

On the other hand, programmers that have invested some time in using mysql.proc will have some trouble changing them to use information_schema.routines instead.

Table definition differences

I did a complex left join of the information_schema.columns for
mysql.proc (P) and for information_schema.routines (R) in MySQL 5.7, and saw this.

P_column_name P_column_type P_collation_name R_column_name R_column_type R_collation_name
db char(64) utf8_bin ROUTINE_SCHEMA varchar(64) utf8_general_ci
name char(64) utf8_general_ci ROUTINE_NAME varchar(64) utf8_general_ci
type enum(‘FUNCTION’,’PRO utf8_general_ci ROUTINE_TYPE varchar(9) utf8_general_ci
specific_name char(64) utf8_general_ci SPECIFIC_NAME varchar(64) utf8_general_ci
language enum(‘SQL’) utf8_general_ci EXTERNAL_LANGUAGE varchar(64) utf8_general_ci
sql_data_access enum(‘CONTAINS_SQL’, utf8_general_ci SQL_DATA_ACCESS varchar(64) utf8_general_ci
is_deterministic enum(‘YES’,’NO’) utf8_general_ci IS_DETERMINISTIC varchar(3) utf8_general_ci
security_type enum(‘INVOKER’,’DEFI utf8_general_ci SECURITY_TYPE varchar(7) utf8_general_ci
param_list blob NULL NULL NULL NULL
returns longblob NULL NULL NULL NULL
body longblob NULL NULL NULL NULL
definer char(77) utf8_bin DEFINER varchar(77) utf8_general_ci
created timestamp NULL CREATED datetime NULL
modified timestamp NULL LAST_ALTERED datetime NULL
sql_mode set(‘REAL_AS_FLOAT’, utf8_general_ci SQL_MODE varchar(8192) utf8_general_ci
comment text utf8_bin ROUTINE_COMMENT longtext utf8_general_ci
character_set_client char(32) utf8_bin CHARACTER_SET_CLIENT varchar(32) utf8_general_ci
collation_connection char(32) utf8_bin COLLATION_CONNECTION varchar(32) utf8_general_ci
db_collation char(32) utf8_bin DATABASE_COLLATION varchar(32) utf8_general_ci
body_utf8 longblob NULL ROUTINE_DEFINITION longtext utf8_general_ci

Remember the above chart is for MySQL version 5.7.
For MySQL 8.0.2 these column definitions have changed:
ROUTINE_SCHEMA: was varchar(64) utf8_general_ci, will be varchar(64) utf8_tolower_ci
ROUTINE_TYPE: was varchar(9) utf8_general_ci, will be enum
DATA_TYPE: was varchar(9), will be longtext
EXTERNAL_LANGUAGE: was varchar(64), will be binary(0)
SQL_DATA_ACCESS: was varchar(64), will be enum
SECURITY_TYPE: was varchar(7), will be enum
CREATED: was datetime, will be timestamp
LAST_ALTERED: was datetime, will be timestamp
DEFINER: was varchar(77) utf8_general_ci, will be varchar(93) utf8_bin
CHARACTER_SET_CLIENT: was varchar(32), will be varchar(64)
COLLATION_CONNECTION: was varchar(32), will be varchar(64)
DATABASE_COLLATION: was varchar(32), will be varchar(64)
… and more changes are possible.

I have included the dirt about column data type and collation so that it’s clear they are never exactly the same. This might affect applications that depend on exact size allocations and precise ordering. But usually it will make no difference to either programmers or end users.

What the chart shows is that mysql.proc.db corresponds to information_schema.routines.ROUTINE_SCHEMA, mysql.proc.name corresponds to information_schema.routines.ROUTINE_NAME, and so on. So if I had a simple SQL statement like

SELECT db, name FROM mysql.proc;

I could convert with ease to

SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM information_schema.routines;

(By the way I used name rather than specific_name because it’s in the primary key; the value is the same.)

However, three mysql.proc columns — param_list, returns, body — have no corresponding columns in information_schema.routines. Converting them will be more work.

param_list and returns

Let’s try

CREATE FUNCTION fx1(paramx1 INT, paramx2 DOUBLE) RETURNS CHAR(5) RETURN 'xxxxx';

Let’s look at it via mysql.proc (whenever I show mysql.proc I’m using MySQL 5.7):

SELECT param_list, returns FROM mysql.proc WHERE name='fx1';

Result:

+-------------+------------------------+
| param_list  | returns                |
+-------------+------------------------+
| paramx1 int | char(5) CHARSET latin1 |
+-------------+------------------------+

Now let’s look at it with

SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, DTD_IDENTIFIER
FROM information_schema.routines
WHERE routine_name='fx1';

Result:

+-----------+--------------------------+--------------------+----------------+
| DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_SET_NAME | DTD_IDENTIFIER |
+-----------+--------------------------+--------------------+----------------+
| char      |                        5 | latin1             | char(5)        |
+-----------+--------------------------+--------------------+----------------+

This isn’t too bad — all we have to do, (with sql_mode=’pipes_as_concat’) is concatenate
DATA_TYPE || ‘(‘ || CHARACTER_MAXIMUM_LENGTH || ‘)’ || ‘ CHARSET || CHARACTER_SET_NAME
or, even simpler,
DTD_IDENTIFIER || ‘ CHARSET ‘ || CHARACTER_SET_NAME
and we’ve got “char(5) CHARSET latin1”, the same as what’s in mysql.proc.returns. Using DTD_IDENTIFIER avoids complications with other data types so I’ll always go with it.

It’s trickier to find a substitute for param_list, because parameters aren’t in information_schema.routines at all. We’d find them by saying

SELECT ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DTD_IDENTIFIER
FROM information_schema.parameters
WHERE specific_name = 'fx1';
+------------------+----------------+----------------+----------------+
| ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DTD_IDENTIFIER |
+------------------+----------------+----------------+----------------+
|                0 | NULL           | NULL           | char(5)        |
|                1 | IN             | paramx1        | int(11)        |
|                2 | IN             | paramx2        | double         |
+------------------+----------------+----------------+----------------+

We don’t need to do anything with parameter #0 (it’s just a copy of what’s in information_schema.routines.returns); we only need to merge parameter #1 and parameter #2 into the main query. Like this (with sql_mode=’pipes_as_concat’), but skipping the details we’ve already seen:

SELECT routine_name, routine_body,
       (SELECT group_concat(parameter_name || ' ' || dtd_identifier)
        FROM information_schema.parameters p
        WHERE p.specific_name = outertable.routine_name
        AND ordinal_position > 0)
       AS param_list
FROM information_schema.routines outertable
WHERE routine_name = 'fx1';

Result:

+--------------+--------------+--------------------------------+
| ROUTINE_NAME | ROUTINE_BODY | param_list                     |
+--------------+--------------+--------------------------------+
| fx1          | SQL          | paramx1 int(11),paramx2 double |
+--------------+--------------+--------------------------------+

In other words, we can get param_list from information_schema.routines by adding a subquery that accesses information_schema.parameters. Notice the assumption that the list will be ordered, I’m depending on a quirk.

body

Let’s try:

CREATE PROCEDURE px1() SELECT _latin1 0xe3;
SELECT body, body_utf8, _latin1 0xe3 FROM mysql.proc WHERE name = 'px1';

Result:

+---------------------+--------------+--------------+
| body                | body_utf8    | _latin1 0xe3 |
+---------------------+--------------+--------------+
| SELECT _latin1 0xe3 | SELECT  0xe3 | ã            |
+---------------------+--------------+--------------+

Now let’s try:

CREATE PROCEDURE px2() SELECT 'abc''def';
SELECT body, body_utf8 FROM mysql.proc WHERE name = 'px2';

Result:

+-------------------+------------------+
| body              | body_utf8        |
+-------------------+------------------+
| SELECT 'abc''def' | SELECT 'abc'def' |
+-------------------+------------------+

So you can see that body and body_utf8 are different. In a sense, both are correct — body is what you want if you are going to make a copy of the routine, body_utf8 is what you want if you want to see what the output would look like if you invoked the routine. So it’s pretty useful that mysql.proc has both.

Unfortunately, information_schema.routines does not. It has no equivalent of body. It only has an equivalent of body_utf8.

Loyal readers may recall that I’ve talked before about the possible information losses when making everything UTF8 but this is even worse. Without an equivalent of mysql.proc.body, you cannot reliably make exact copies of routines even if they are in UTF8.

Privilege differences

Actually the change amounts to more than just the differences between the definitions of the tables. There’s also the fact that PRIVILEGES are different — you can GRANT on mysql.* tables, you cannot GRANT on information_schema.* tables. And what users can see differs depending on how granting is done.

Therefore, for all users who currently hold a SELECT privilege on mysql.proc, we are going to have to work around the problem that there is no way to grant the exact same privilege on information_schema.routines. In other words, if the old (MySQL 5.7) statement was

GRANT SELECT ON mysql.proc TO 'jean'@'localhost';

To be the same as that, you need a way to let jean see all the columns in all the routines, but not anything other than the routines. This is possible with the DEFINER clause in routines and views. For example, assuming ‘root’@’localhost’ is a powerful user:

CREATE
DEFINER = 'root'@'localhost'
SQL SECURITY DEFINER
VIEW v_routines
AS SELECT * FROM information_schema.routines;
GRANT SELECT ON v_routines TO 'jean'@'localhost';

SHOW

Quote from MySQL 5.7 manual describing SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION:
“To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table.”

Quote from MySQL 8.0 manual describing SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION:
“To use either statement, you must have the global SELECT privilege.”

Essentially, the privilege requirement in 5.7 is what’s needed for looking at mysql.proc, but the privilege requirement in 8.0 is what’s needed for looking at information_schema.routines.

But this time we can’t work around by creating a view. If I’m interpreting “global SELECT privilege” correctly, the expectation is that if you want to say SHOW CREATE PROCEDURE, you need

GRANT SELECT ON *.* TO user_who_wants_to_show_create_procedure;

Either I’m misinterpreting, or MySQL is demanding that you grant a very broad privilege for a very narrow requirement.

This is too bad because, though SHOW statements are junk, this one will be necessary in MySQL 8.0. That’s because it has the body value right: it does not turn SELECT ‘abc”def’ into SELECT ‘abc’def’ and so on. Thus, it is the only way to get the equivalent of MySQL 5.7’s mysql.proc.body value. Using a connector, you can put this correct value into another table with something like this, leaving out details:

  mysql_real_query("SHOW PROCEDURE p;")
  mysql_fetch_row() /* there's only going to be one row at most */
  mysql_fetch_field() /* the third field is `Create procedure` */
  mysql_real_query("UPDATE copy_of_routines") /* set body value */

Other ways to get lists of routines

Although MySQL 8.0 is missing mysql.proc, it isn’t missing all the grant-related tables (yet). So you can still say

SELECT * FROM mysql.procs_priv;

Not only that, you can still use SHOW PROCEDURE STATUS — and then put the results in a @variable! As far as I know this is undocumented, but it’s been around for years and nobody has bothered to disallow it.
Try this:

SET @procedurelist = '';
SHOW PROCEDURE STATUS WHERE (@procedurelist := CONCAT(@procedurelist, `Name`, ','));
SELECT @procedurelist;

Result:

+----------------------------------------------------- ...
| create_synonym_db,diagnostics,execute_prepared_stmt, ...
+----------------------------------------------------- ...

Demonstrably, the @procedurelist variable now has a list of all procedures. Its only recommended use is to show unbelievers that with MySQL all things are possible.

Effect on ocelotgui debugger

As you might have guessed by now, we have a program that uses mysql.proc, namely the Ocelot open source GUI client for MySQL and MariaDB (ocelotgui). Well, one of its feature components is a debugger for MySQL stored routines, and you can see all the “mysql.proc” references in our source code for that module.

Our plan is: wait and see if MySQL makes the transition easier. But if that doesn’t happen, within a few months we’ll change ocelotgui to implement all the methods that I’ve described. So if you want to see how it’s done in detail, with an actual working application, just watch the code related to mysql.proc until it changes. It’s on github.
UPDATE (September 2018) our latest release 1.0.7 can be used for debugging MySQL 8.0 routines.

The SQL Standard is SQL:2016

Now the words “SQL Standard” mean ISO/IEC 9075 Information technology — Database languages — SQL … 2016″ or more briefly SQL:2016. I am no longer a member of the committee, I don’t have the latest copy, but I believe these are the notable changes.

The LIST_AGG function

The example I’ve seen (credit to Markus Winand) is

SELECT grp, LIST_AGG(val, ',') WITHIN GROUP (ORDER BY val) FROM t ORDER BY grp;

and the result is supposed to be: take each “val” value and concatenate it with the previous one, separating by “,” as specified.
Notice a similarity to GROUP_CONCAT() in MySQL/MariaDB

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

So I think we can say this is functionally equivalent to SQL:2016, it would just have to be tweaked a bit to be exactly equivalent.

Polymorphic Table Functions

You’ve probably heard of “flexible schemas” as opposed to “fixed schemas”. To me it means the name and the data type can accompany the data instead of being fixed by the CREATE TABLE statement. You still need to figure out the metadata when you retrieve, though, so a Polymorphic Table Function (PTF) is one way of doing so. The functions have to be user-defined. I’m not in love with PTFs, but I keep putting off my description of a better idea.

JSON

I talked about JSON with respect to MySQL in a previous post.

The news I have seen subsequently is that MariaDB 10.2 has some JSON functions but I can’t say yet whether they’re better than MySQL’s implementation.

Meanwhile Oleg Bartunov is working on a JSON change for PostgreSQL,
and explicitly referencing SQL:2016.

Advantage PostgreSQL.

Row Pattern Matching

I think the main thing here is that the Oracle-like MATCH_RECOGNIZE is coming in. It’s vaguely related to the windowing functions, and MariaDB is way ahead of MySQL with respect to windowing functions, so if this feature ever appears I’d expect it to appear first in MariaDB.

Temporal Tables

Actually the big temporal things were introduced in SQL:2011, but clarifications came subsequently, so I could count this as a “post-2011 standard feature”. In this respect I see that MariaDB intends to add AS OF / temporal tables” based apparently on the work of Alexander Krizhanovsky, and Mr Krizhanovsky has talked about those clarifications — so I think we can hope for system versioning or something like it soon.

MySQL, MariaDB, International Components for Unicode

In an earlier blog post I wrote “MySQL has far better support for character sets and collations than any other open-source DBMS, except sometimes MariaDB.”

That’s no longer always true, because ICU.

ICU — International Components for Unicode — was a Sun + IBM initiative that started over 20 years ago, and has become a major component of major products. The key advantage is that it provides a lax-licensed library that does all the work that’s needed for the Unicode Collation ALgorithm and the CLDRs. No competitive products do that.

When I was with MySQL we considered using ICU. We decided “no”. We had good reasons then: it didn’t do anything new for the major languages that we already handled well, it seemed to change frequently, we preferred to listen to our user base, there wasn’t a big list of appropriate rules in a “Common Locale Data Repository” (CLDR) in those days, we expected it to be slow, we worried about the license, and it was quite large. But since then the world has moved on.

The support for ICU among DBMSs

ICU is an essential or an optional part of many products (they’re listed on the ICU page in section “who uses”). So there’s no problem finding it in Lucene, PHP 6, or a major Linux distro. But our main concern is DBMSs.

DB2          total support, IBM is an ICU evangelist
Firebird     total support
SQLite       optional support (you have to download and recompile yourself)
CouchDB      you're supposed to download ICU but
             you seem to have choices
Sybase IQ    for sortkeys and for Thai    

PostgreSQL catches up?

MySQL/MariaDB have their own code for collations while PostgreSQL depends on the operating system’s libraries (libc etc.) to do all its collating with strcoll(), strxfrm(), and equivalents. PostgreSQL is inferior for these reasons:

(1) when the operating system is upgraded your indexes might become corrupt because now the keys aren’t where they’re supposed to be according to the OS’s new rules, and you won’t be warned. For a typical horror story see here.

(2) libc had problems and it still does, for example see the bug report “strxfrm results do not match strcoll”.

(3) libc is less sophisticated than ICU, for example towupper() looks at only one character at a time (sometimes capitalization should be affected by prior or following characters)

(4) ORDER BY worked differently on Windows than on Linux.

(5) Mac OS X in particular, and sometimes BSD, caused surprise when people found they lacked what libc had in Linux. Sample remarks: “you will have to realize that collations will not work on any BSD-ish OS (incl. OSX) for an UTF8 encoding.”, and “It works fine if you use the English language, or if you don’t use utf-8.”

I’ve observed before that sometimes MySQL is more standards-compliant than PostgreSQL and this PostgreSQL behaviour is consistent with that observation. Although some people added or suggested ICU patches — EnterpriseDB and Postgresapp come to mind — those were improvements that didn’t become part of the main line.

In August 2016 a well-known PostgreSQL developer proposed a patch in a thread titled ICU integration. Many others jumped in with support or with rather intelligent criticisms. In March 2017 the well-known developer posted the dread word “Committed”. Hurrahs followed. Sample remark: “Congratulations on getting this done. It’s great work, and it’ll make a whole class of potential bugs and platform portability warts go away if widely adopted.”

This doesn’t destroy all of MySQL/MariaDB’s advantages in the collation area — a built-in bespoke routine will probably be faster than a generic one that’s bloated with checks for things that will never happen, and PostgreSQL perhaps can’t do case insensitive ordering without using upper(), and the ICU approach forces some hard trade-off decisions, as we’ll see. But the boast “Only MySQL has consistent per-column collation support for multiple languages and multiple platforms” will lose sting.

The problems

If MySQL and/or MariaDB decided to add ICU to their existing collation support, what problems would they face?

LICENSE.
The licence has changed recently, now it is a “Unicode license”. You have to acknowledge the copyright and permission everywhere. It is compatible with GPL with some restrictions that shouldn’t matter. So whatever license problems existed (I forget what they were) are gone.

SIZE.
The Fedora .tgz file is 15MB, the Windows zip file is 36 MB. The executables are a bit smaller, but you get the idea — it takes longer to download and takes more storage space. For SQLite this was frightening because its applications embed the library, but to others this doesn’t look like a big deal in the era of multi-gigabyte disk drives. The other consideration is that the library might already be there — it’s optional for many Linux packages (I’d also seen a report that it would be the norm in FreeBSD 11 but I didn’t find it in the release notes).

SPEED.
According to ICU’s own tests ICU can be faster than glibc. According to EnterpriseDB a sort or an index-build can be twice as fast with ICU as without it I’d be surprised if it ever beats MySQL/MariaDB’s built-in code, but that’s not a factor — the built-in collations would stay. These tests just establish that the new improved ones would be at least passable.

CLIENTS.
One of the PostgreSQL folks worried about ICU because the results coming from the DBMS might not match what the results would be if they used strcoll() in their C programs and lc in their directory searches. But I’ve never heard of anyone having a problem with this in MySQL, which has never used the same algorithms as strcoll().

DISTROS.
If an open-source application comes via a distro, it might have to accept the ICU version that comes with the distro. That’s caused problems for Firebird and it’s caused fear that you can’t bundle your own ICU (“RH [Red Hat] and Debian would instantly rip it out and replace it with their packaged ICU anyway” was one comment on the PostgreSQL thread). EnterpriseDB did bundle, but they had to, because RHEL 6 had a uselessly old (4.2) ICU version on it. Ordinarily this means that the DBMS vendor does not have total control over what ICU version it will use.

RULE CHANGES.
If you can’t bundle a specific version of ICU and freeze it, you have to worry: what if the collation rules change? I mentioned before how this frightened us MySQL oldies. For example, in early versions of the Unicode Collation Algorithm (what ICU implements), the Polish L-with-slash moved (ah, sweet memories of bygone bogus bug reports). and Upper(German Sharp S) changed (previously ß had no upper case). Such changes would have caused disasters if we’d used ICU in those days: indexes would have keys in the wrong order, CHECK clauses (if we’d had them) would have variable meaning, and some rows could be in different partitions.

But it’s been years since a movement of a modern letter happened in a major European language. Look at the “Migration issues” that are described in the Unicode Collation Algorithm document:

UCA 6.1.0 2012-02-01 -- added the ignoreSP option
                        added an option for parametric tailoring
UCA 6.3.0 2013-08-13 -- removed the ignoreSP option
                        changed weight of U+FFFD
                        removed fourth-level weights
UCA 7.0.0 2014-05-23 -- clarifications of the text description
UCA 8.0.0 2015-06-01 -- removed contractions for Cyrillic accent letters except Й
UCA 9.0.0 2016-05-18 -- added support for Tangut weights

… If none of these match your idea of an issue, you probably don’t have an issue. Plus you have a guarantee: “The contents of the DUCET [Default Unicode Collation Element Table which has the root collation for every character] will remain unchanged in any particular version of the UCA.” That’s wonderful because the DUCET is good for most languages; only the tailorings — the special-purpose specifications in the CLDR — seem to see changes with every release. But if you have them, I guess you would have to say:
* If there’s an upgrade and the ICU version number is new, check indexes are in order
* If there’s a network, the ICU version should be the same on all nodes, i.e. upgrade everything together
* Don’t store weights (the equivalent of what strxfrm produces) as index keys.

Other news: there is no new release of Ocelot’s GUI client for MySQL + MariaDB (ocelotgui) this month, but a few program changes have been made for those who download the source from github.

Sequences

What sequences are, who supports them, and why they’re better than GUIDs or auto-increment columns except when they aren’t.

What sequences are

When I say “sequence” I actually mean “sequence generator” because that’s what SQL’s CREATE SEQUENCE statement actually creates. So a sequence is a black box that produces an integer with a guarantee that this integer is greater than the previous integer that it produced, and less than the next integer that it will produce. Thus if I invoke it five times I might get 2, 4, 5, 6, 7. In poker that wouldn’t be considered a sequence because there’s a gap between 2 and 4. We can make sequences with few gaps or no gaps, but they’re expensive.

Sequences are good for generating unique keys, although they’re not the only tool for that.

How they work

This is the minimal scenario with a good DBMS.

An administrator says “create sequence x”, with most things default: start with 1, go up by 1, cache 20.

A user says “get the next value from x”.

The server grabs the numbers between 1 and 20. It writes to a log: “last grab value = 20”. It saves in memory: 1 number is used, 19 remain in the cache. The user gets a number: 1.

A user says “get the next value from x”. The server does not need to grab more numbers or write to a log. It saves in memory: 2 numbers are used, 18 remain in the cache. The user gets a number: 2.

This continues until there are no more numbers left in the cache — at that point the server does another “grab” and another “log write”, then proceeds with a new cache.

Ordinarily the process needs some mutexes but not DBMS locks, although some DBMS vendors like to maintain an in-memory system table that contains the highest value for user convenience. So sequence overhead might be cheap compared to the overhead of maintaining a persistent table with an auto-increment column.

There are two reasons that there will be gaps:
(1) because once a value is retrieved it’s gone, so ROLLBACK won’t cause the sequence generator to put a number back
(2) because a crash can happen any time after the grab, and when the server restarts it will use the last value written to the log, so up to 20 numbers can be left unused. Of course a different cache size would mean a different number of possible unused, or “burnt”, numbers.

Who supports them

In the table below, the left column has pieces of a CREATE SEQUENCE statement, the other columns show which DBMSs support it.

CREATE                            ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL
[OR REPLACE]                          DB2                         Syb
[ TEMPORARY | TEMP ]                                          Pos
SEQUENCE                          ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL
[IF NOT EXISTS]                                   Inf         Pos
sequence-name                     ANS DB2 Fir HSQ Inf Ing Ora Pos Syb SQL
[AS data type]                    ANS DB2     HSQ     Ing             SQL
[START WITH n]                    ANS DB2     HSQ Inf Ing Ora Pos Syb SQL
[ INCREMENT BY n ]                ANS DB2     HSQ Inf?Ing Ora Pos Syb SQL
[ NO MINVALUE | MINVALUE n ]      ANS DB2         Inf     Ora Pos Syb SQL
[ NO MAXVALUE | MAXVALUE n ]      ANS DB2         Inf Ing Ora Pos Syb SQL
[ NO CYCLE | CYCLE ]              ANS DB2         Inf Ing Ora Pos Syb SQL
[ NO CACHE | CACHE n ]                DB2         Inf Ing Ora Pos Syb SQL
[ OWNED BY column-name } ]                                    Pos
[ ORDER | NOORDER ]                                       Ora
[ KEEP | NOKEEP ]                                         Ora
[ SESSION | GLOBAL ]                                      Ora
[ SEQUENTIAL | UNORDERED]                             Ing

ANS = ANSI/ISO 9075-2 (the SQL:2011 standard) with optional feature T176 “external sequence generator”
DB2 = IBM DB2 for LUW
Fir = Firebird
HSQ = HSQLDB
Inf = Informix
Ing = Ingres
Ora = Oracle
Pos = PostgreSQL
Syb = Sybase IQ (not Sybase ASE)
SQL = Microsoft SQL Server

Example statement:
CREATE SEQUENCE x_seq AS DECIMAL(20) START WITH -1000000 CACHE 1000;

The chart doesn’t show some trivial variations, for example some DBMSs allow NOMAXVALUE along with or instead of NO MAXVALUE, and in some DBMSs WITH or n can be optional.

I believe that clauses like START WITH, INCREMENT BY, MIN[IMUM]VALUE have obvious meanings. The truly significant ones are [AS data-type] and CACHE.

[ AS data-type ]: this is the only clause that’s not supported by all three of the Big Three: DB2 and SQL Server have it but Oracle doesn’t. Oracle just says there are up to 28 digits. INTEGER (+ or minus 2 billion) might be enough, but if you do a billion “next value” calculations in a day, you’d run out of numbers in (4294967295 / 1000000000) = 4 days.

[CACHE n]: this is not an ANSI clause because it’s strictly for performance, and for an idea of how important it is I’ll quote IBM developerWorks:
“The main performance issue to be aware of for identity and sequence is that, for recoverability reasons, generated values must be logged. To reduce the log overhead, the values can be pre-reserved (cached), and one log record is written each time the cache is exhausted. … As the results of tests 41-51 in Appendix A show, not having a cache is extremely costly (almost nine times slower than the default).”

NEXT VALUE

The operation for “get next value” is NEXT VALUE FOR sequence-name. Some DBMSs shorten it to NEXTVAL and some DBMSs allow for “getting current value” or “getting previous value” i.e. repeating the NEXT VALUE result. But the main things to know are:

The DBMS increments once per row, not once per time that the DBMS sees “next value”. This is remarkable, because it’s not how a function invocation would work. In this respect PostgreSQL is non-standard, because it will increment every time it sees “next value”.

There is no race-condition danger for “getting current value”. User #1 does not see the number that User #2 gets, because it’s stored within the session memory not global memory.

Getting from a sequence is certainly not deterministic, and is usually disallowed for some operations that would require “next value” to come up often. Oracle disallows it with DISTINCT, ORDER BY, GROUP BY, UNION, WHERE, CHECK, DEFAULT, subqueries, and views; other DBMSs are more easygoing.

You’ll want to find out what you’re inserting. The simple way is to get the next value first into a variable, then insert it:
SET x = NEXT VALUE FOR x_seq;
INSERT INTO t VALUES (x, …)
but that involves two trips to the server. If possible, one wants to do the database operation and return the next value to the user, all via one statement, as in the SQL:2011 non-core (optional) feature T495 “Combined data change and retrieval”. Unfortunately (I’ve decried this heresy before), you’ll often see this done with UPDATE … RETURNING.

The GUID alternative

If I just want a unique number, why don’t I get the client or the middleware to generate a GUID for me? First let me state what’s good.
* Zero locks and zero log writes.
* A GUID has a 122-bit number. No, not a 128-bit number because there are a few bytes for version number and for future purposes. No, we can’t hope that there will be even distribution across the range so 2**122 is a dream. But (2**122) = 5.316912e+36, so the chances of generating duplicate numbers is still estimated as: not in this lifetime.
* So what if there’s a duplicate? If you’re generating a GUID for the sake of a surrogate key, then it will get inserted in a unique index, and so there will be a nice clean error message if the same value reappears.

Now this is what’s bad.
* GUIDs are long. It’s not just a difference between 128 bits and 64 bits, because serial numbers start at small numbers like ‘1’, while GUIDs are always long strings when you print them out. Not only does this mean that end-users wouldn’t want to be subjected to them, it means that they’d take more space to store. And, if they’re used for primary keys of an index-organized table, they might form part of secondary keys, so the extra space is being consumed more than once.

Therefore it’s not a settled matter. But people do prefer sequences for the other main reason: despite their problems, they’re generally sequential.

The IDENTITY alternative

The other way to get a sequence is to define a column as IDENTITY, or some close equivalent such as PRIMARY KEY AUTO_INCREMENT.

Clearly, SEQUENCE is better when the same generated value is useful for multiple tables, or for no tables. But what about the common situation where the generated value has something to do with a single column in a single table — then maybe IDENTITY is better for performance reasons.

I recently encountered a good insult: “You’re not stupid, you just have bad luck when you think.” Here is an example of thinking. See if you can spot the bad luck.

“The DBMS is going to generate a number as a primary key. Primary keys have B-tree indexes. The NEXT VALUE will be the current largest value, plus one. That value is in the last page of the index. I have to read that page anyway, because that’s where I’m going to add the key for the NEXT VALUE that I’m INSERTing. Therefore the overhead of using IDENTITY is negligible.”

Did you have any trouble spotting that that is not true, because the largest value might have been DELETEd? An identity value must not be re-used! Therefore there is a bit of overhead, to store what was the maximum value for all time, separately.

(Incidental note: MySQL and MariaDB and Oracle Rdb and SQL Server 2012 unfortunately do destroy the maximum auto_increment value when I say TRUNCATE TABLE. But DBMSs like DB2 for i will by default preserve the maximum IDENTITY-column value, as the SQL standard requires.)

People have compared IDENTITY versus SEQUENCE performance. On SQL Server 2012 one tester found that identity is always better but another tester found that sequence catches up when cache size = 100,000. On Oracle 12c a tester found that sequences took 26 seconds while identities took 28 seconds. On DB2 a tester found that “Identity performs up to a few percent better.” Averaging the results: it depends.

And it’s not necessarily a GUID versus SEQUENCE versus AUTO_INCREMENT contest. There are lots of ways that users can make their own unique values, with timestamps and branch-identifiers and moon phases. They’re not standardized, though, so I leave such ideas as exercises for readers who think they can do better.

Bottlenecks

The two things you want to avoid are hot spots and cache misses. Choose any one.

A hot spot is caused when multiple users are all trying to modify the same index pages. That’s inevitable if User #1 gets value 123456, User #2 gets value 123457, and User #3 gets value 123458. Naturally those will probably fall on the same page, and so cause contention. The solution is to hash or reverse the number before writing.

A cache miss is caused when multiple users are not all reading the same index pages. If they were, then the chances would be high that the page you want would be in the cache, and you’d save a disk read. The situation can arise when everybody needs to access a group of rows that were put in around the same time, for example “rows inserted today”. The solution is to not hash or reverse the number before writing.

Gaps

The easiest way to reduce gaps is to say CACHE 0 or NO CACHE when you create the sequence. As we’ve seen, this has bad effects on performance, so do it only when you have some rule saying “no gaps” (read “Gapless sequences” for an example due to German tax laws). It’s likely that you’ll still have something that’s faster than a separate auto-incrementing table.

Or you can try to plug the gaps after they’re made. Itzak ben-Gan wrote a few tips about that, and so did Baron Schwartz.

Hey, What about MySQL and MariaDB?

In 2003 in MySQL Worklog Task #827 I explained: “In the Bordeaux group leaders’ meeting (November 2003), “Oracle-type sequences” was specified as an item for version 5.1.” You can judge how well that came out.

MariaDB’s worklog task 10139 is somewhat more current — there’s an assignee and there’s recent activity. I’m dubious about the proposed plan, it specifies very little and it’s something involving a read-uncommitted table that contains the latest high number, and then the dread words appear “see [URL of postgresql.org documentation] for details”. However, plans often change.

Unless I’m behind the times, I predict that MySQL or MariaDB would have a problem imitating Oracle’s GRANT SEQUENCE (a separate privilege that only affects sequences). Making a new privilege is still hard, I believe.

When or if the syntax changes, our GUI client for MySQL and MariaDB will of course be updated to recognize it.

See also

My old DBAzine article “Sequences and identity columns” is still mostly valid.