MySQL / MariaDB – SQL And Its Sequels

Category: MySQL / MariaDB

Charts

SELECT 0,1,2,3,4,5,6,7,8; result can look like this

but should it?

I’ll try to cover here what would be an obvious way to show number columns in charts, or if not obvious then at least simple, or if not obvious or simple then at least understandable, or if none of the above, well, good looking.

The initial part of this description will look a bit like what you see with DB2 magic commands for bar or line or pie charts, but I like to think the later part of this description shows something more sophisticated.

Bars

Start with the most obvious: any group of numbers can be shown as a group of bars.

Should they be horizontal by default? That appears to be the less common choice, A Complete Guide to Bar Charts says “While the vertical bar chart is usually the default …”, ChartJs says “The default for this property is ‘x’ and thus will show vertical bars.”

But a SAS blog 3 reasons to prefer a horizontal bar chart persuaded me because, when we’re a GUI displaying an SQL result set, we usually have more room on the x (width) axis than on the y (height) axis.

Now, as for simpleness: that should mean there’s no extra work or time for exporting, manipulating in a non-SQL environment, learning Python, or even having to think — if the total time including user time is more than a second, that’s a fail. Therefore it has to be possible to get
SELECT 1,2,3;
which ordinarily displays as

to display as

with a shortcut that includes a mnemonic. Therefore hitting Alt+Shift+B should switch immediately to the bar-chart display.

But it can get more complex, and that means users must enter instructions, and the only way to make that simpler is to provide instruction syntax that looks like SQL, thus:

SET ocelot_grid_chart = 'bar vertical';

… since users will know that SET value = literal is the SQL way to assign, and will know that ''s enclose literals, and will know what vertical means. So this is the result and it won’t be surprising:

Another bar-chart variant is stacking, that is, putting 3 values in one bar instead of 3 side-by-side bars. For example

SET ocelot_grid_chart = 'bar stacked';

SET ocelot_grid_chart = 'bar vertical stacked';

Lines

Lines are a bit less obvious than bars, because if there’s only one number in the series, they’re meaningless. Nevertheless, there should be a simple way to show a chart. Therefore Alt+Shift+L will cause

Maybe, although I suppose everyone has seen this sort of thing before, I should make some terminology notes around now …
X-axis: usually this is the horizontal, with the word “samples”
Y-axis: usually this is the vertical, with the word “values”
Legend: in the upper right corner, with a colour icon and a short indicator
Ticks: the tiny marks along the values, so users can see what the scale is.

For the French terms, see Statistique Canada.

But if users don’t know the terms, that’s fine, they understand the picture anyway.

Pies

Pies are obvious and simple too, all they require is
Alt+Shift+P or

SET ocelot_grid_chart='pie';

and SELECT 1,2,3; will look like this:

Fine. But putting numbers along the x-axis and y-axis wouldn’t be informative, and nobody has figured out what should happen with negative numbers. I think Microsoft Excel would take the absolute value, I think it makes more sense to ignore, but the point is: no solution is intuitive for all, therefore there’s a gotcha, therefore it’s not obvious and simple after all.

Groups

Every illustration so far has been simple, a 3-number series. There’s no complicated decision-making, the algorithm is just “if it’s a number, i.e. any of the data types that the DBMS fits in a general numeric category, it’s in, otherwise it’s out”.
It would follow, then, that

>SELECT 1,2,3,'',4,5,6,'',7,8,9;

would result in this if the setting was for bar charts:

There are three “groups” here, and “groups” is a better word than “series” because GROUP is an SQL-standard term.

Subgroups

Now things get a bit more complex. What makes us decide that every individual numeric-column occurrence should be part of a separate bar or line or pie segment? Answer: nothing does, that’s simple but not really the best for representing some of the typical chart situations.

Take the possibility that all the columns which have something in common are combined. The commonness could be the value. For example

SET ocelot_grid_chart='bar vertical subgroup by value % 3';

SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;

That is, the values which are divisible by 3 are together, but distributed across the landscape. Each of the things that are together are usually called “samples” if one uses chart terminology, and that’s why in this illustration the word “samples” appears at the bottom, and each subgroup has a separate colour, which is echoed by the legend.

More usefully, I think, the commonness could be the column_name prefix. The usefulness lies in the fact that users know how to put AS clauses after column names in the select list, so they can determine what the subgroups are. For example

SET ocelot_grid_chart='bar vertical subgroup by left(column_name, 1)';

SELECT 1 AS x_1, 2 AS y_1, 3 AS z_1, 4 AS x_2, 6 AS y_2, 7 AS z_2;

There might be other criteria for deciding how to subgroup, but they’re probably less important. What’s important is that once again the syntax is something any SQL user will understand.

Actually the word that chart-lovers prefer here is “categories”, and “subgroups” isn’t really a word you’ll find in typical SQL, but at the moment I’m thinking it’s “SQL-ish”.

Nulls

Since this is SQL, NULLs exist.

There’s no such thing as obvious when NULL might mean unknown or inapplicable, but this isn’t Oracle so it would be too trite to say NULLs are zeros, at least when it’s possible to hint otherwise. Thus

SET ocelot_grid_chart='bar vertical subgroup by left(column_name, 1)';

SELECT 1 AS m, 2 AS m, 3 AS m, 4 AS o, 5 AS o, NULL AS o;

doesn’t use a solid colour, but has the same size as whatever the largest bar is.

I worry that by now we’ve evolved from “obvious” to merely “understandable”, so I’ll explain this example more deeply. In this example there are three samples of ‘m’ and ‘o’. The first sample, label 0, has the first ‘m’ and it has value = 1, the black bar, and it has the first ‘o’ and it has value = 4, the red bar. Sample 1 has the second ‘m’ and the second ‘o’. Sample 2 has the third ‘m’ and ‘o’, and, since the third ‘o’ is NULL, it is not solid. The legend in the right corner is helpful for showing that
black is ‘m’ and red is ‘o’.

WHERE clauses

But surely not every numeric column should always be in a chart? I think of column names that end with ‘_id’, or the possibility that one will want to display both the chart and the numbers as text.

The SQL syntax for deciding what’s in a group could be CASE … WHEN, or IF … THEN, but what even the novice SQL user has seen is WHERE. The condition could be COLUMN_NAME relative-operator value, COLUMN_NUMBER relative-operator value, COLUMN_TYPE relative-operator value. By the way, since this is a GUI, users will see hints for autocompletion when they type WHERE, for example

and after

SET ocelot_grid_chart='bar vertical stacked' WHERE column_type = 'integer';

SELECT 1,1.1,2,7,-44;

the result will not include 1.1 in a chart because it is not an integer.

And this is where it becomes possible at last to show the statements that caused the example at the start of this post, where some of the columns were in a bar chart, some in a line chart, and some in a pie.

SET ocelot_grid_chart = 'line' WHERE column_number <= 3;
SET ocelot_grid_chart = 'bar' WHERE column_number >= 4 AND column_number <= 6;
SET ocelot_grid_chart = 'pie' WHERE column_number > 6;
SET ocelot_grid_chart = '' WHERE column_number > 100;
SELECT 0,1,2,3,4,5,6,7,8;

and we’ve arrived at the example we started with.

Turning it off

Alt+Shift+N or

SET ocelot_grid_chart='';

will erase all earlier chart statements. So there’s a simple way to go back to the regular way to display results.

Source Code

My main purpose describing all this is to show what the design of a GUI should look like, if my observations about goals make sense.

The illustrations show what the implementation looks like as of today, they’re screenshots of ocelotgui, and they work as I’ve described. But currently they’re only distributed in source code. Anyone who wants to reproduce them today can download the source from github, follow the special build instructions in file chart_options.txt, connect to MySQL or MariaDB or Tarantool, and see the same things. You will notice that you need to use the Qwt library, which should be no problem (ocelotgui is GPLv2, Qwt is LGPL, Qwt is on many distros). Pay no attention to the bug in the room.

But I’m not sure whether to call this the final look for the next executable release. Let me know in the comments if I’m missing tricks.

pgulutzan, August 8, 2023. Category: MySQL / MariaDB.

Delimiters in MySQL and MariaDB

In the SQL standard delimiters are for separating tokens; in MySQL/MariaDB they’re for ending statements, particularly compound statements as in

CREATE PROCEDURE p()
BEGIN
  VALUES (5);
END;

The problem is that a dumb client could stop at the first semicolon “;” in the statement (the one after VALUES (5)) instead of the one that truly ends (the one after END). The solution is to say that semicolon is no longer the default delimiter, and the way to specify a replacement is with a non-standard DELIMITER statement, such as

DELIMITER //

and after that the true statement end is //.

The idea isn’t unusual — in PostgreSQL’s psql client you can enclose compound statements with $$ which they call dollar quoting, with Microsoft you get used to saying “go” when you want something shipped to the server, with Oracle you might sometimes see SQLTERMINATOR. But it shouldn’t always be necessary — despite the fact that words like BEGIN are not reserved, a client with even a slight ability to recognize syntax can see where a compound statement ends without needing special tags.

Quirks

Some DELIMITER quirks are documented in either the MySQL manual or MariaDB manual, some are common to all mysql-client commands, but all are weird. They are:
() DELIMITER itself is not delimited
() After DELIMITER A B the delimiter is A
() After DELIMITER A; the delimiter is A;
() After DELIMITER 12345678901234567890 the delimiter is 123456789012345
() After DELIMITER ‘x’ or DELIMITER “x” or DELIMITER `x` the delimiter is x
() After DELIMITER ;; the delimiter is ;;
() Delimiters inside comments or character string literals or delimited identifiers are ignored
() After \dA the delimiter is A (\d is a substitute for DELIMITER and no space is necessary)
() After DELIMITER \ the effect can be odd, the MySQL manual recommends not to use \
() SELECT 5; DELIMITER ; will cause an error message
() DELIMITER ;; followed by SELECT 5;;; has a different effect from DELIMITER // followed by SELECT 5;//
() \G and \g will work whether or not there is a delimiter

It is not a gotcha that, when statements are in a .sql file and read by the SOURCE file-name statement, the rules are different: Compound statements will be recognized without special delimiters, and semicolons will always end non-compound statements even after a different delimiter has been specified.

It is not a quirk or a gotcha, it’s merely bad design, that DELIMITER forces the fundamental requirement of a parser — the tokenizer — to change what the tokenizing rules are while the tokenizing is going on.

Best practice

Both the MySQL and the MariaDB manual have example statements where the delimiter is // and that’s unlikely to cause confusion, since the only other uses of / are as a beginning of a bracketed comment or as a division operator, or in arguments to PROMPT or SOURCE or TEE.
Less frequently there is a recommendation for $$ but that is bad because $$ can occur inside regular identifiers.
DELIMITER !! is equally bad because !, unlike /, is a repeatable operator, for example SELECT !!5; returns 1.
DELIMITER ;; is worse and has caused bugs in the past, but the mysqldump utility will generate it before CREATE TRIGGER statements.

Avoid non-printable or non-ASCII characters, some clients will support them but others won’t.

You could set a delimiter at the start of a session and let it persist. But I believe the more common practice is to put DELIMITER [something appropriate] immediately before any CREATE PROCEDURE or CREATE TRIGGER or CREATE FUNCTION or BEGIN NOT ATOMIC statement, and DELIMITER ; immediately after. This might help readability by humans since otherwise they’d look at statements that end with mere semicolons and assume they’d go to the server immediately, not realizing that long ago there was a DELIMITER statement.

See also the Descriptive SQL style guide section “Semicolons”.

ocelotgui

The ocelotgui client doesn’t need DELIMITER statements because obviously a client that includes a stored-procedure debugger can recognize compound statements, but it will accept them because it tries to be compatible with the mysql client. It does not duplicate mysql client behaviour for the SOURCE statement but that should be fixed in the next version, and the fix is now in the source code which can be downloaded from github.

pgulutzan, May 31, 2023. Category: MySQL / MariaDB.

Duplicate column names

A former colleague from my MySQL AB days asked me about
SELECT pk, SUM(amount) AS pk FROM t ORDER BY pk;
should the duplicate use of the name PK be illegal?

My reply is: A relational-database expert deplores it in the select list; the SQL standard says it’s conceptually illegal in the order-by clause; MySQL and MariaDB handle it in an odd way; the Ocelot GUI for MySQL and MariaDB doesn’t flag it.

A relational-database expert deplores

CJ.Date, in Date On Databases, Writings 2000-2006, has a multi-page section about “Duplicate Column Names”. I’ll just quote the firmest negatives.

… we can avoid the duplicate names if we want to. The problem is, we don’t have to, and SQL does not have to deal with the possibility that a given table might have column names that aren’t unique. As an aside, I should explain that … we don’t have to use the “AS column name” construct is because it wasn’t part of the original standard (it was introduced with SQL:1992), and compatibility therefore dictates that its use has to be optional. (I should also note in passing that — believe it or not — names introduced by AS don’t have to be unique!

*Every SQL table has a left-to-right column ordering *Every column in every named SQL table (i.e. base table or view) has a user-known name, and that name is unique within the table in question. But neither of these properties holds, in general, for unnamed tables (i.e. intermediate and final result tables). … I’d like to point out that duplicate and missing column names both constitute a fairly egregious violation of The Naming Principle

So nothing’s ambiguous about Mr Date’s evaluation, but he is acknowledging that SQL won’t be changing due to compatibility with old standards, and at least it wouldn’t allow duplicate column names in the select list of a CREATE VIEW statement.

The SQL standard says it might be illegal

The standard says this about Concepts:

This Clause describes concepts that are, for the most part, specified precisely in other parts of ISO/IEC 9075. In any case of discrepancy, the specification in the other part is to be presumed correct.

So we usually expect a specific syntax rule, but if it’s missing, we maybe can look at Concepts.

And this is SQL:2016, Foundation, 4.13 Columns, fields, and attributes, in the Concepts section:

Sometimes the Syntax Rules forbid two elements at different ordinal positions from having equivalent names (particularly when created through an SQL schema statement). When two elements with equivalent names are permitted, then the element name is ambiguous and attempts to reference either element by name will raise a syntax error.

That suggests that if pk appears twice in select list then ORDER BY pk; should fail, but it’s not explicitly stated in the Syntax Rules. This in contrast with GROUP BY, which has a clear Syntax Rule, that is, “Each grouping column reference shall unambiguously reference a column of the table resulting from the from clause.” Perhaps when I was on the ANSI committee I should have asked why Syntax Rule for ORDER BY doesn’t also say “unambiguously”, but it’s too late now.

MySQL and MariaDB handle it in an odd way

I tried variations of the syntax with MySQL 8.0.31 and MariaDB 10.10.1, sql_mode=only_full_group_by is not true, sql_mode=ansi_quotes is true. They both return the same results.

CREATE TABLE t (pk INT, amount INT); INSERT INTO t VALUES (1,2), (2,1),(1,2),(2,1); SELECT pk, (amount) AS pk FROM t ORDER BY pk;
Result: Error 1052 (23000) Column ‘pk’ in order clause is ambiguous
SQLSTATE values starting with 23 are for “integrity constraint violation”, so it would be better to have something starting with 42 (syntax error or access violation), I described good old ’42’ in an earlier blog post. However, the error message is good and consistent with standard SQL.

SELECT pk, SUM(amount) AS pk FROM t ORDER BY pk;
Result: 1,6.
SELECT pk, 5 AS pk, amount FROM t ORDER BY pk,amount DESC;
Result: (1,5,2),(1,5,2),(2,5,1),(2,5,1)
SELECT pk, (SELECT SUM(amount) AS pk FROM t) AS "pk" FROM t ORDER BY pk;
Result: (1,6),(2,6),(1,6),(2,6)
These results are harmless — there’s either an implied grouping or a literal for the main sort, so they’re always the same value, so they can be optimized away. Nevertheless, they’re inconsistent — if “Column ‘pk’ in order clause is ambiguous” is detectable, and it’s a syntax error not a runtime error, it shouldn’t have been ignored.

SELECT pk * - 1 AS pk, pk FROM t ORDER by pk;
Result: (-2,2),(-2,2),(-1,1),(-1,1)
SELECT pk, pk * - 1 AS pk FROM t ORDER by pk;
Result: (2,-2),(2,-2),(1,-1),(1,-1)
These results are odd, they show that we’re ordering by the pk that has an AS clause, regardless of which column is first in the select list.

SELECT SUM(amount) AS pk, pk FROM t GROUP BY pk ORDER BY pk;
Result: (2,2),(4,1)
Also there is a warning: Column ‘pk’ in group statement is ambiguous. But it does succeed, and once again the item with the AS clause is preferred. This syntax is explicitly declared illegal in standard SQL.

SELECT * FROM (SELECT 1 AS a, 1 AS a) AS t1, (SELECT 1 AS a, 1 AS a) AS t2;
Result: Error 1060 (42S21) Duplicate column name ‘a’
This is the right result, and the SQLSTATE is better because it starts with ’42’.
The reason it’s correct is that Trudy Pelzer long ago reported a bug with duplicate names in subqueries, which are illegal due to a different item in the standard, and Oleksandr Byelkin fixed it.

The Ocelot GUI for MySQL and MariaDB doesn’t flag it

An obvious thought is: since ocelotgui recognizes MySQL/MariaDB syntax, and gives hints or autocompletions while the user is entering a statement, should I be adding code to catch this? After all, we stop most illegal syntax before it goes to the server.

But I’m wary. The behaviour that I describe here is undocumented, so it could change without warning, and besides who are we to reject what the server would accept? It won’t be at the top of the “To Do” pile.

Speaking of “To Do” piles, ocelotgui 1.8 has now been released, and is downloadable from github.

pgulutzan, November 27, 2022. Category: MySQL / MariaDB, Standard SQL.

ocelotgui version 1.7

Version 1.7 of ocelotgui has these changes.

Bug fix: due to a recent MariaDB change, our stored-procedure debugger could not be installed when connecting to a MariaDB server (MySQL debugging was not affected). Now all is well again.

Here, for example, is a screenshot of ocelotgui after connecting to MariaDB 10.8, saying $debug P; and then Alt+5 (“Step”) 4 times, then clicking menu item “Refresh user variables” to see the previous and current values of @a:

Incidentally I’ve just noticed some old comments about ocelotgui on https://stackoverflow.com/questions/3007625/mysql-stored-procedure-debugging with one user saying “… helped me a lot!” and another saying “I connected, debugged their tutorial, and am amazed. it’s a GREAT step debugger with variable data view on demand. lots to learn, but the #1 choice so far for free.” Don’t get the wrong idea though — debugging is a unique feature but not the major one.

New platform: Actually ocelotgui runs on any Linux distro (and Windows of course); however, the only specific instructions for creating packages were ones for .deb (Debian-based) and .rpm (Red-Hat-based). Now there is a script file for creating packages on Arch-Linux-based distros too, for example Manjaro or EndeavourOS. It’s PKGBUILD.

Enhancement: Detaching wasn’t quite as spiffy as it should be because our size calculations were slightly wrong. Here’s a screenshot showing the same widgets as before, after detaching them all and doing some manual fiddling:

As usual, downloads and executables are available on our github repository.

pgulutzan, June 9, 2022. Category: MySQL / MariaDB.

Autocompletion with the mysql client

The concept of autocompletion is old and simple. You type some characters; the program shows some choices for what characters might follow; you can choose a choice by hitting a special key. It can save on typing, and save on looking up in dictionaries or information schemas.

I will illustrate what the manuals mean, then show features or quirks that the manuals fail to say, then compare with a GUI.

The Example

For The Example, I used MySQL 8.0 mysql client and MariaDB 10.5 mysql client, on Linux. I understand that they work on Windows 10 if you install a Linux shell.

You should find it easy to follow along with your own copy of mysql client, even if you started with non-default options and have minimal privileges.

The first thing to do is declare a “current database” with USE, and say that you want to enable autocompletion with REHASH.
I’ll discuss later why these might be unnecessary statements, but there’s no harm in making sure.

USE information_schema;
REHASH;

Now type lo then type the [Tab] key twice. Your screen will look like this:

Now type the [Backspace] key twice, then type LO, then type the [Tab] key twice. Your screen will look like this (the items might be in a different order depending on the server):

Now type the [Backspace] key twice, then type LOCALTIMEST, then type the [Tab] key once. Your screen will look like this:

At this point you have probably figured out these things:
* Searching is case sensitive, lo and LO are different
* If there is more than one choice, [Tab] [Tab] causes a display
* If there is only one choice, [Tab] causes a replacement
* Context is irrelevant, most of the choices would be illegal as statement start
* Choices include table names, column names, and keywords.

To complete what you could figure out, you could try these additional tests:
inf [Tab] [Tab] — you’ll see information_schema because choices also include database names
BEG [Tab] [Tab] — you’ll see BEGIN because choices also include non-reserved keywords
INF [Tab] [Tab] — there will be a question whether you want to see all choices because 100+ choices may not be a useful hint.

Command line options

To back up a bit, the reason that the USE and REHASH statements might have been unnecessary is that there might be defaults.

If you started mysql with --database=database_name or -D database_name, then USE is automatic when you connect.

If you started mysql with –auto_rehash, or you just omitted the option because it is default default, then REHASH is automatic when you USE.

Do not let the “auto” in “–auto-rehash” deceive you too much though. REHASH will not happen automatically if anyone changes the current database with CREATE or DROP or ALTER. REHASH will not happen automatically if you say USE x; when x is already the default database.

So if you worry about depending on obsolete choices, you will need to do manual REHASHes occasionally.

The REHASH Statement

prompt> REHASH; /* or /# */

REHASH is not a good name — it hints about how the job is implemented, rather than what it does, which is: select the names of databases, names of tables (both base tables and viewed tables but not temporary tables) in the current database, names of columns of those tables, keywords (both reserved words and unreserved words), and names of mysql commands. Completion will not work if REHASH has not happened, not even for keywords.

REHASH will not select names of tables that you don’t have privileges for. There is a small security breach though: If you have a column-level privilege for any column in the table, then the menu choices will include all columns in the table, including the ones you don’t have privileges for. (In theory you are not supposed to know about such columns, which is why you won’t see them in information_schema.)

Unlike USE, which tells you “Reading table information for completion of table and column names”, REHASH gives no feedback. But it does succeed. If you have only a few thousand tables and columns, it takes negligible time.

What library mysql is using

For most editing operations, including [Tab] handling, the mysql client passes off the work to an open-source library, either readline or libedit.

If you have an old MySQL version, or if you have MariaDB, your mysql client probably has readline. One way to tell is to say

mysql --help | grep readline

Any non-blank response, for example

mysql  Ver 15.1 Distrib 10.1.47-MariaDB,
for debian-linux-gnu (x86_64)
using readline 5.2

indicates that your mysql client has readline. Alternatively you could say “whereis mysql … ldd mysql-location” and look for libreadline.so in the output.

If you have MySQL 5.6.5 or later, your mysql client almost certainly has bundled libedit. (In version 5.5 there was still a CMake option -DWITH_READLINE=1, but it is gone now.) If you have MariaDB, your mysql client probably does not have libedit unless you built from source and passed the -DWITH_LIBEDIT option to cmake. One way to tell is to create a file named ~/.editrc and insert this temporary line:

mysql:bind

(If the file already exists, no problem, just add a line saying mysql:bind at the start.) Now start the mysql client. If it displays a list titled “Standard key bindings”, then your client has libedit. Specifically it is close to the NetBSD variation of libedit. Now remove the line saying mysql:bind from libedit.

It has been suggested that MySQL/Oracle’s changed preference was due more to licence considerations than to features (readline is GPL and libedit is BSD), and indeed it is true that there are more things you can do with autocompletion if you use the mysql client from MariaDB, as we will see.

Today (November 6 2020) the MySQL 8.0 manual still has mentions of readline, here and here and here. They are obsolete, ignore them.

Using a different key instead of [Tab]

The special key for completion is [Tab] so sometimes autocompletion is called “tab completion”. But it doesn’t have to be. You can change the “key binding” — the way that a particular key is connected to a particular operation — by changing a file that the library reads when you start the mysql client. There is no denying that [Tab] is the more popular choice on Linux, partly because it is the default for both libedit and readline, and some Linux utilities such as bash depend on readline. But [Tab] is a displayable character so it is unusual — and I think bizarre — to see it within an application that is editing text. There is a way to say “Let [Tab] be [Tab], and use a control character for completion.”

If the library is libreadline, then the file to change is ~/.inputrc. If it does not already exist, then you can create it, but then be sure to $include /etc/inputrc at the top, otherwise the user’s copy will just override the system one. Make it look like this:

$include /etc/inputrc
Control-I: tab-insert
Control-N: complete

This would also work:

$include /etc/inputrc
TAB: self-insert
Control-N: complete

Unless you have an exotic terminal, [Tab] and Control-I have the same effect.

If the library is libedit, then the file to change is ~/.editrc and the key binding that matters for completion is rl_complete. Edit ~/.editrc again and remove the line that says bind, and add these lines:

mysql:bind "^N" rl_complete
mysql:bind "\t" ed-insert

I like it that I can specify that I only want to affect mysql.

Now start the mysql client again, and once again say

USE information_schema;
REHASH;
DROP TABLE

and now type control-N twice. You will see that it displays exactly the same thing that you saw when you typed [Tab] twice. However, if you now type [Tab], you will get a tab.

Warning: before changing a key binding, make sure that the key is not used by other programs or has some default libreadline/libedit binding already. And check with stty -a.

Undocumented readline features

Actually these key bindings are documented for readline,
but the MySQL and MariaDB manuals don’t list them so I’m
guessing most mysql users don’t know about them. To see what the relevant key bindings are, I use bind:

$ bind -P | grep complete
complete can be found on "\C-n", "\e\e".
complete-command can be found on "\e!".
complete-filename can be found on "\e/".
complete-hostname can be found on "\e@".
complete-into-braces can be found on "\e{".
complete-username can be found on "\e~".
complete-variable can be found on "\e$".
dynamic-complete-history can be found on "\e\C-i".
glob-complete-word can be found on "\eg".
menu-complete is not bound to any keys
menu-complete-backward is not bound to any keys
old-menu-complete is not bound to any keys
vi-complete is not bound to any keys

This is rather cryptic but you probably can guess that “\C-n” means “Control-N”, which is the suggestion that I made for ~/.inputrc. However, the interesting items are menu-complete and menu-complete-backward. Let’s bind them to some keys and see what a readline-based mysql client does with them.

First change ~/.inputrc again so now it looks like this:

$include /etc/inputrc
Control-I: tab-insert
Control-N: complete
Control-J: menu-complete
Control-K: menu-complete-backward

Now start mysql again and do some of the same steps as before:

USE information_schema;
REHASH;

And now type LO and then type control-N twice
(not [Tab] any more), and you’ll see

Now type Control-J. Suddenly the word becomes the first choice
in the menu

Now type Control-J again. Now the word becomes the second choice in the menu

Keep typing Control-J and eventually readline will cycle back
to the first choice. Or type control-K and completions will be of the previous items.

This, I think, is how autocompletion should always work.
If you have a menu of choices, you should have a way to
navigate to a specific choice by typing a single key.

Try out even more behaviour changes by adding these lines at the end of ~/.inputrc:

# Case-insensitive (disabled)
set completion-ignore-case On
# Don't do anything until user types at least 3 characters (disabled)
set completion-prefix-display-length 3
# If there are more than 5 choices ask "Display all xx possibilities?"
set completion-query-items 5
# Of course disable-completion No is default
set disable-completion No
# If there are many choices don't show a page at a time and ask for "More"
set page-completions Off
# Menu options should be vertical, that would be normal (disabled)
set print-completions-horizontally No
# If there are two or more choices show them after hitting special key once
set show-all-if-ambiguous Yes
# Show what is changed (disabled)
set show-all-if-unmodified On
# If it is completed then put the cursor after it
set skip-completed-text On
# Don't hear a bell, see a bell (disabled)
set bell-style visible

These are not libedit features, but I shouldn’t omit mentioning that libedit has many other key bindings and customization chances, unrelated to autocompletion. It is not hard to use MariaDB’s mysql client to connect to MySQL’s server, but I doubt it’s worthwhile just for some extra autocomplete gizmos.

In a GUI client

It should be clear that the mysql client does an adequate autocompletion job. But I can think of many clients that do a better job, from either MySQL or third parties. I am only going to illustrate one of them — our ocelotgui — but I am not suggesting that it is unique in this respect, since any competent GUI will have at least a few of these additional features. And I am not suggesting that any one of these additional features is important — only “autocompletion” itself is important. But I think that as a whole they save some time and trouble.

You should be able to follow along with your own copy of ocelotgui, if you have downloaded the latest version from github.

Say USE information_schema and REHASH.

Change so completion is done with Alt-N rather than [Tab].

Type DROP TABLE

Move the cursor and hover, to see instructions.

Use the down-arrow to select one of the menu items.

Type Alt-N or click Autocomplete on the main Edit menu

Wait OCELOT_COMPLETER_TIMEOUT seconds for the menu to disappear.

Notice these differences …
(1) Changing [Tab] can be done within the program (although of course it can also be done by changing a file).
(2) The choices appear immediately, with a GUI there’s no need for [Tab] or a special key to make them appear.
(3) The choices include only what is relevant in context, i.e. after DROP TABLE the only possible items are IF and table names.
(4) The colour of the choices is the same as the colour of the highlighting, which is why IF (a keyword) and * (an identifier) look different.
(5) The navigation is done with arrow keys on a purely vertical menu with a scroll bar, regardless of number of choices.
(6) Hints and menu choices appear temporarily, as in IDEs.
Another difference is that this works the same way on Windows as well as Linux, out of the box.

Of course you can’t actually drop a table in information_schema, and ocelotgui will fail to warn you about that. I admit as much. “Perfection” is still on the to-do list.

pgulutzan, November 6, 2020. Category: MySQL / MariaDB.

Descriptive SQL Style Guide

I have added a large new file on github:

Descriptive SQL Style Guide.

It is open source and I emphasize it is “descriptive” because I am describing rather than prescribing.

pgulutzan, April 30, 2020. Category: MySQL / MariaDB, Standard SQL.

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;

pgulutzan, January 9, 2019. Category: MySQL / MariaDB, Standard SQL.

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.

pgulutzan, September 5, 2018. Category: MySQL / MariaDB.

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.

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

pgulutzan, November 3, 2017. Category: MySQL / MariaDB.

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.

pgulutzan, April 11, 2017. Category: MySQL / MariaDB.