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.
What should be the result of -11 % 4, or MOD(11, -4)?
This has been controversial and you will get different results in different languages. However, in SQL there is only one correct answer, the one that the SQL standard requires. Good news: your favourite SQL DBMS gives the correct answer. But what it is, and what the proof is, will need lots of explaining.
Modulus moduli modulo
Oxford English Dictionary entry for modulus: “… 2b A whole number used as a divisor in a system of arithmetic (modular arithmetic) in which integers having the same remainder when divided by this number are regarded as equivalent. Cf. congruent adj. 5, modulo prep., residue n. 3b.”
Oxford English Dictionary entry for modulo: “Origin: A borrowing from Latin. Etymons: Latin modulō, modulus. Etymology: < classical Latin modulō, ablative of modulus modulus n. Compare earlier mod prep. The preposition was first used in this sense in a Latin context by Gauss in 1801 (see note s.v. mod prep.). … a. With respect to a modulus of (a specified value). See modulus n. 2b.”
Carl Friedrich Gauss wrote Disquisitiones Arithmeticae in Latin. In Latin, moduli is the plural of modulus and modulo is the ablative. In this context the ablative modulo indicates “by means of the modulus”.
And so R = N MOD M means get a result R by applying to a number N “by means of the modulus M“. In SQL this is the standard function
MOD(N, M)
or the common operator
N % M.
I highlight the fact that the modulus here is M — sometimes I’ve seen hints that people think the modulus is the result of the function, but that’s not the original meaning. There are good explanations of “modular arithmetic” on the Khan Academy site and in Encyclopedia Britannica which use the term in the original way, and use the common illustration of a circular clock that has 12 hours marked on it:
If it is 7 o’clock, and you add 7 hours, then it is 2 o’clock. It doesn’t mean 7+7=2, it means 7+7 and 2 are “congruent” by means of modulus 12. So are (7+7+12) and 2, (7+7+12*2) and 2, and so on. The mod function should answer: what’s the congruent number that fits on the clock?
Anybody can see that, but real-world examples of a “negative clock” are harder to find. I can think of a clock that counts down before a rocket launch, but it doesn’t go in a cycle.Nevertheless, if it did, then the hours are not from 0 to 11, they are from 0 to -11. Now, if you add 7 hours to “minus 7 o’clock” or if you subtract 7 hours from “minus 7 o’clock”, arguably you should get a negative number, as no positive number fits on a negative clock.
On the other hand, people want to use this function to get remainders and remainders are normally positive numbers regardless what you use as a divisor. So what’s desirable?
The three main possibilities
Daan Leijen wrote “Division and Modulus for Computer Scientists” following some work by Knuth and Wirth and others. It is not in Latin. But it does have some mathematical symbols, which I’ll translate …
Assume divisor <> 0.
(1) the quotient is a member of the set of integers.
(2) Dividend = divisor times quotient + remainder.
(3) ABS(remainder) < ABS(divisor).
Those are the universal rules. The additional rules can be:
(a) “Truncate”, i.e. trunc(dividend / divisor) – (dividend * result)
(b) “Floor”, i.e. it’s the remainder of round(quotient) towards infinity)
(c) “Euclidean”, i.e. what Mr Leijen regards as correct, see his lengthy explanation, but he admits it’s rarely used.
The key illustration is Mr Leijen’s “Comparison of T-, F- and E-division”
D,d qT,rT qF,rF qE,rE
--- ----- ----- -----
(+8,+3) (+2,+2) (+2,+2) (+2,+2)
(+8,−3) (−2,+2) (−3,−1) (−2,+2)
(−8,+3) (−2,−2) (−3,+1) (−3,+1)
(−8,−3) (+2,−2) (+2,−2) (+3,+1)
where T is Truncate, F is Floor, E is Euclidean, D is Dividend, d is divisor, q is quotient, r is remainder,
The exciting part is that when the Dividend is negative (-8,+3 or -8,-3), or when the divisor is negative (+8,-3 or -8,-3) the column with “rT”, i.e. remainder with Truncate, is not the same as the column with “rF”, i.e. remainder with Floor.
Modern C goes with (a) Truncate, which tends to be well supported by the processor. If you write and run this program:
#include <stdio.h>
void main()
{
printf("%d %d %d %d\n", ( 11)%( 4), ( 11)%(-4), (-11)%( 4), (-11)%(-4));
}
You will see: 3 3 -3 -3. Ditto Java and many other languages. But Lua uses (b) Floor and there is an argument for it in “A small practical example where Lua’s % behavior is better than C’s”. Also Python uses (b) Floor; Guido van Rossum explains in “Why Python’s Integer Division Floors”.
Lisp has separate mod and rem functions. Maybe that was for the best. But it’s too late for us.
What SQL vendors do
A copy-and-paste from the ocelotgui client’s History widget:
mysql>SELECT ( 11)%( 4) , ( 11)%(-4) , (-11)%( 4) , (-11)%(-4);
OK 1 rows affected (0.5 seconds)
+------------+------------+------------+------------+
| ( 11)%( 4) | ( 11)%(-4) | (-11)%( 4) | (-11)%(-4) |
+------------+------------+------------+------------+
| 3 | 3 | -3 | -3 |
+------------+------------+------------+------------+
Same in MariaDB. Same in Tarantool. Same in PostgreSQL. Same in Oracle … but Oracle warns: this is not the “classical” modulus operation. Then Oracle illustrates what they call “classical” and, it’s obvious from that illustration, they mean Floor. IBM uses the same example.
Therefore the overwhelming opinion among SQL vendors is (a) Truncate.
What the standard says
There is no % operator. There is a MOD() function.
"
<modulus expression> ::=
MOD <left paren>
<numeric value expression dividend>
<comma>
<numeric value expression divisor>
<right paren>
...
If <modulus expression> is specified,
then the declared type of each
<numeric value expression>
shall be exact numeric with scale 0 (zero).
The declared type of the result is the
declared type of the immediately contained
<numeric value expression divisor>.
...
9)If <modulus expression> is specified,
then let N be the value of the immediately
contained <numeric value expression
dividend> and let M be the value of the
immediately contained <numeric value
expression divisor>.
Case:
a)If at least one of N and M is the null
value, then the result is the null value.
b)If M is zero,
then an exception condition is raised:
data exception—division by zero.
c)Otherwise, the result is the unique exact
numeric value R with scale 0 (zero) such
that all of the following are true:
i)R has the same sign as N.
ii)The absolute value of R is less than
the absolute value of M.
iii)N = M * K + R for some exact numeric
value K with scale 0 (zero).
“
Look again at Mr Leijen’s universal rules, above. You’ll notice that the numeric-with-scale-0 requirement is equivalent to “(1) the quotient is a member of the set of integers.”, and you’ll notice that clause b) is equivalent to “Assume divisor <> 0”, and you’ll notice that clause c)iii) is equivalent to “(2) Dividend = divisor times quotient + remainder”, and you’ll notice that clause c)ii) is equivalent to “(3) ABS(remainder) < ABS(divisor).” So, except for clause c)i), all the rules are the rules that he described.
So what is -11 % 4?
It is the same as MOD(-11, 4).
Therefore -11 is the dividend and 4 is the divisor.
Therefore -11 is N and 4 is M.
a) does not apply because neither M nor N is NULL.
b) does not apply because M is not zero.
c) means for the expression N = M * K + R, we must solve for K and R.
Plugging in the values of N and M, we have
-11 = 4 * K + R.
Since R must have the same sign as N, R must be negative.
Therefore K must be negative!
To see why, plug in any value of K which is >= 0. For example:
-11 = 4 * 1 + -15
No. This violates c)ii) because ABS(-15) > ABS(4).
So now we know that
-11 = 4 * (K which is a negative number) + (R which is a negative number).
Let us try with K >= -1:
-11 = 4 * (-1) + (-7)
No. This violates c)ii) because ABS(-7) > ABS(4).
Okay, how about if K <= -3:
-11 = 4 * (-3) + (+1)
No. This violates c)i) because N is negative and R is positive.
Okay, how about K = -2:
-11 = 4 * (-2) + (-3)
This does not violate c)i) because N is negative and R is negative.
This does not violate c)ii) because ABS(-3) < ABS(4).
Great, so R = -3.
Thus it is established by example that the standard requires (a) Truncate.
Using non-standard extensions
Although in standard SQL the correct action for MOD(N, 0) is an error “division by zero”, not every vendor does that — they might return N or they might return NULL.
Although standard SQL MOD() is for integers, some vendors allow FLOAT or DECIMAL. And here is a trick: To get the scalar part of a DECIMAL number:
5.5 % 1
result should be .5.
Perhaps you are thinking “Nonsense! The correct way is
5.5 - FLOOR(5.5)
but I think it depends whether you want to preserve the sign.
Although the standard says that the result data type should be the same data type as M, some vendors will return a value with different precision and scale.
New ocelotgui version
Version 1.1.0 of ocelotgui was released on July 31. So head over to our github site now and download.
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.
I have written code that converts SQL stored procedures to Lua functions. This might interest people who want to know what’s involved for any C-like target language. Here I will display one step at a time, emphasizing the “Design Decisions” that I had to make, giving increasingly complex examples. Then I’ll note the limitations and say how to get the source code. Alpha.
Frame
CREATE LUA PROCEDURE p()
BEGIN
/* Empty block */
END;
The word LUA is just a signal that this is a translation job; everything following it is expected to be standard SQL or a supported dialect.
I already had a good
recognizer so parsing the input is easy. And, so far, it’s easy to find Lua syntax analogous to SQL syntax. The result is:
function P()
do
--[[/* Empty block */]]
end;
end;
Design Decision #1: I’ll try to preserve comments and punctuation as in the original, although it’s not necessary.
Design Decision #2: SQL’s BEGIN … END can become Lua’s do … end
The alternative was Lua’s while true … break; end but that could have confused some people.
Variables
CREATE LUA PROCEDURE p()
BEGIN
DECLARE i INTEGER;
DECLARE "Peter's string" VARCHAR(4);
END;
DECLARE + variable-name is easy because Lua declarations only need a name. Occasionally we have to do some extra processing though.
Result:
do
local I;
local Peter_39_s_32_string_33_;
end;
end;
Design Decision #3: I convert regular identifiers to upper case because that’s how SQL does things. This isn’t common practice in Lua.
Design Decision #4: I change delimited (quoted) identifiers by changing all special characters to _s but keeping the original case, and then we add a number to ensure the name is unique. Detail: _33 means “the definition was at token number 33 in the CREATE PROCEDURE statement; I use token numbers frequently to make sure I’m making something unique.
Design Decision #5: I don’t preserve the data type. Lua doesn’t need it often and when it does I can use Lua’s type() function to see whether somethingis a number or a string … except if the value is nil. Nil values will confuse us and users should try to avoid them. The alternative, though, was to store the data type and null status separately for every declared variable, and I think (well, “hope”) that won’t be necessary in practice.
Executing
CREATE LUA FUNCTION f() RETURNS INTEGER
BEGIN
DECLARE i INTEGER DEFAULT 1;
RETURN i + 3;
END;
Now we get into SQL statements that must be called from Lua. For this we will use a function named sql_execute(), which is a wrapper around code that is specific to a particular DBMS. The job of sql_execute is to:
Pass parameters.
Send a statement to the DBMS server (for example with mysql_real_query if the DBMS is MySQL/MariaDB).
NB: sending should be done with Lua pcall because results must always be intercepted.
Check return values:
(If there was an error) set sqlstate to error, for example ‘45000’, and return false.
(If there was a result set)
Copy the result set to sqlresult.
(If the result set is empty) set sqlstate to not found, for example ‘02000’, and return nil.
(If the result set is not empty) set sqlstate to okay, for example ‘00000’, and return true.
(If there was no result set) set sqlstate to okay, for example ‘00000’, and return true.
The job of the function calling sql_execute is to:
Define sqlstate (a string) and define sqlresult (a table) accessible throughout the function.
Call sql_execute with whatever parameters are necessary.
Check what sql_execute returns, if necessary.
Result:
function F()
local sqlstate;
local sqlresult = {};
local sqlmessage;
local function sql_execute(statement, parameters)
--[[not illustrated here, see description]]
end;
do
local I; sql_execute([[SELECT 1;
]],{}); I = sqlresult[1][1];
sql_execute([[
SELECT ? + 3;
]],{I}); return sqlresult[1][1];
end; ::end_8::
end;
For the DEFAULT 1 clause, the function will call on the DBMS to execute “SELECT 1;”, and the result will be in the first column of the first row of sqlresult table, so I = select-result. There are no parameters in this case, which is why ,{} appears.
For the RETURN i + 3 statement, the function will call on the DBMS to execute “SELECT ? + 3;”, and since the ? is a placeholder for a parameter there will have to be a parameter, which is why ,{I} appears. Once again sql_execute() will put the result set, which happens to be one row with one column, in sqlresult. So return sqlresult[1][1] will cause the function to return 4.
Design Decision #6: all expressions will go to SQL for evaluation. Certainly for the example here it would be possible to do the addition in Lua, but when expressions contain SQL functions or operators we have to get SQL to do it. So, for simplicity, I always ask SQL to do it.
Design Decision #7: I’ll assume that sql_execute can’t fail for statements that are assigning, like DEFAULT and RETURN. This isn’t necessarily true, but I figured: if there was something wrong with the expression, it would have failed already.
Getting Out of a block
CREATE LUA PROCEDURE p()
BEGIN
BEGIN
CREATE TABLE t (s1 VARCHAR(4) PRIMARY KEY);
INSERT INTO t VALUES ('a');
INSERT INTO t VALUES ('b');
END;
END;
Adding statements in a BEGIN … END causes the usual sql_execute() calls, but also some goto instructions.
Result:
function P()
local sqlstate;
local sqlresult = {};
local sqlmessage;
local function sql_execute(statement, parameters)
--[[See earlier description.]]
end;
do
do
sql_execute([[
CREATE TABLE t (s1 VARCHAR(4) PRIMARY KEY);
]],{});
if string.sub(sqlstate,1,2) > '02' then goto end_7; end;
sql_execute([[
INSERT INTO t VALUES ('a');
]],{});
if string.sub(sqlstate,1,2) > '02' then goto end_7; end;
sql_execute([[
INSERT INTO t VALUES ('b');
]],{});
end; ::end_7::
end; ::end_6::
end;
The instruction
if string.sub(sqlstate,1,2) > '02' then goto end_7; end;
is my substitute for SQL’s implicit action
“Get out of the BEGIN … END block if there is an error”.
Remember that sql_execute() returns an sqlstate value, and the first two characters of this value must be greater than ’02’ if there is an error. So the goto will go to a point just past the end of the inner Lua do … end block, which is marked by a label ::end_7::. This isn’t necessary for INSERT INTO t VALUES (‘b’); because it is the last thing in the block, so it will pass out of the block anyway.
Design Decision #8: goto is the best way to get out of the block. The alternatives were: (a) use while true … end so that break will get out of the block, as discussed earlier, (b) put “if string.sub(sqlstate,1,2) <= '02'" before every sql_execute() call in the block. Although the alternatives would work in this example, I decided they make the code unreadable when the examples get complicated.
Unconditional Flow control
CREATE LUA PROCEDURE p()
BEGIN
label_1:
LOOP
ITERATE label_1;
LEAVE label_1;
END LOOP;
END;
I can replace SQL’s
LOOP ... END LOOP
with Lua’s
while true ... end
. I can replace SQL’s ITERATE with a goto that goes to just before the end, so that the loop repeats. I can replace SQL’s LEAVE with a goto that goes to just after the end, so that the loop ends.
Result:
function P()
local sqlstate;
local sqlresult = {};
local sqlmessage;
local function sql_execute(statement, parameters)
--[[See earlier description.]]
end;
do
while true do
goto LABEL_1_1;
goto LABEL_1_2;
::LABEL_1_1::end;::LABEL_1_2::
end; ::end_6::
end;
Since the ITERATE-derived “goto LABEL_1_1;” skips past the LEAVE-derived “goto LABEL_1_2;” this is an infinite loop. Its only saving grace is that it is valid Lua code.
Conditional Flow Control
CREATE LUA PROCEDURE p()
BEGIN
DECLARE i INTEGER DEFAULT 1;
WHILE i < 5 DO
INSERT INTO t VALUES (i);
SET i = i + 1;
END WHILE;
END;
WHILE, IF, and REPEAT are statements that depend on a condition. I support them all the same way: by passing "SELECT condition;" to SQL, and then (since such a statement will always return one row with a Boolean value) asking whether the first column in the first row of sqlresult is true.
Result:
function P()
local sqlstate;
local sqlresult = {};
local sqlmessage;
local function sql_execute(statement, parameters)
--[[See earlier description.]]
end;
do
local I; sql_execute([[SELECT 1;
]],{}); I = sqlresult[1][1];
while sql_execute([[
SELECT ? < 5 ;
]],{I}) == true and sqlresult[1][1] == true do
sql_execute([[
INSERT INTO t VALUES (?);
]],{I});
if string.sub(sqlstate,1,2) > '02' then goto end_6; end;
sql_execute([[
SELECT ? + 1;
]],{I})
if string.sub(sqlstate,1,2) > '02' then goto end_6; end;
I = sqlresult[1][1];
end;
end; ::end_6::
end;
We've already seen what the rest of the statements in this loop are supposed to generate, the only new thing is that "SELECT ? < 5;" will return true (because the passed parameter I is less than 5) until I becomes 5 (because "SELECT ? + 1;" is inside the loop).
Design Decision #9: I decided to test first if sql_execute() returns true (that is, does not return an error or not found) before checking whether the value in the result set is true. That's reasonable caution -- but I didn't decide to do a similar test for assignment. That's because a failure during condition evaluation could cause an infinite loop, so it is more serious.
Design Decision #10: I'm saying "SELECT ? < 5;" although I dislike non-standard code -- standard code is "VALUES (? < 5);". This is my concession to what seems to be popular, but I am already regretting it, I probably will change this.
Cursors
CREATE LUA FUNCTION f() RETURNS INTEGER
BEGIN
DECLARE i INTEGER;
BEGIN
DECLARE c CURSOR FOR SELECT 5;
OPEN c;
FETCH c INTO i;
CLOSE c;
END;
RETURN i;
END;
Remember that sql_execute() will put the results of a SELECT into a table named sqlresult. So all I need to do is call sql_execute("SELECT 5;") for the OPEN, pick up a value from sqlresult for the FETCH statement, and rub out sqlresult for the CLOSE statement.
Result:
function F()
local sqlstate;
local sqlresult = {};
local sqlmessage;
local function sql_execute(statement, parameters)
--[[See earlier description.]]
end;
do
local I;
do
local C_CURSOR = {};
local C_CURSOR_OFFSET = 0;
local C_CURSOR_STATUS = 'not open';
C_CURSOR = sql_execute([[
SELECT 5;
]],{});
if string.sub(sqlstate,1,2) > '02' then goto end_13; end;
if string.sub(sqlstate,1,2) == '00' or string.sub(sqlstate,1,2) == '02' then
C_CURSOR_OFFSET = 0;
C_CURSOR_STATUS = 'open';
end;
if C_CURSOR_STATUS ~= 'open' then sqlstate = '07000';
else if C_CURSOR_OFFSET >= #C_CURSOR then sqlstate = '02000';
else do
C_CURSOR_OFFSET = C_CURSOR_OFFSET + 1;
I = C_CURSOR[C_CURSOR_OFFSET][1];
end; end; end;
C_CURSOR= {};
C_CURSOR_OFFSET = 0;
C_CURSOR_STATUS = 'not open';
end; ::end_13::
if string.sub(sqlstate,1,2) > '02' then goto end_8; end;
sql_execute([[
SELECT ?;
]],{I}); return sqlresult[1][1];
end; ::end_8::
end;
Here C_CURSOR is a Lua table with a copy of resultset, and C_CURSOR_OFFSET is something that FETCH can increment whenever it succeeds. FETCH will make its own decision about whether sqlstate = '02000' (which is the SQLSTATE value for NOT FOUND) by checking whether C_CURSOR_OFFSET is greater or equal to the Lua table's size.
Design Decision #11: This is a normal way to get a result set. MySQL/MariaDB users are accustomed to seeing SELECT display results if it is in a routine. I could do the same by looping through sqlresult and calling Lua's print() function, but decided that's not what everyone would expect.
Handlers
CREATE LUA PROCEDURE p()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
DROP TABLE t;
SIGNAL SQLSTATE '45000'; /* SIGNAL SQLSTATE '45000'; */
END;
I am no longer looking at something easy, because Lua has no equivalent for a handler. I struggled a lot before coming up with the idea of a nested function. After that I struggled a lot more with handlers that have multiple conditions, EXIT handlers, multiple handlers that need to be sorted according to how specific they are, and handlers in blocks that encompass the block that the statement is in. Those complexities are all taken care of now, by generating complex code. Here I'll show only the simplest case.
Result:
do
local function handler_0()
sql_execute([[
DROP TABLE t;
]],{}); end;
sqlstate = '45000';
if string.sub(sqlstate,1,2) > '02' then handler_0(); end;
end; ::end_6::
end;
Here, afer sqlstate = '45000'; which was generated for the SIGNAL statement, we again check to see whether there was an error by looking at sqlstate, but this time, instead of jumping out of the BEGIN/END block when that happens, the function calls handler_0() which has the code generated for DECLARE CONTINUE HANDLER.
Design Decision #12: Defining a function within a function ("nesting" the function) is great, because the generated code ends up in the same relative position that it occupies in the SQL source. However, this makes me depend on a Lua feature. Also, I cannot LEAVE from the handler code into the main code. Nevertheless, the alternative -- putting an indefinite number of conditional goto statements after the handler code -- does not inspire.
Illustration
The following screenshots show a run of the last example, In the second screenshot the code of sql_execute() appears instead of the stub that I've been showing, but it only works with Tarantool. As I said earlier, users have to modify the sql_execute() code to fit their DBMS dialect.
Screenshot #1: showing entry of CREATE LUA PROCEDURE.
Screenshot #2: showing dialog box with result Lua code.
These were done with ocelotgui. I have done no retouching, which is why the indenting is a bit rough. Before entering, I had to ensure that the syntax checker was running ...
SET OCELOT_STATEMENT_SYNTAX_CHECKER = '3';
(Turning on syntax checking invokes the recognizer, without which nothing would work.)
Also, for MySQL/MariaDB only:
SET SESSION SQL_MODE='ansi_quotes';
Limitations
As I said, it's alpha.
Sure, bugs exist and I haven't tested much, because I want to get the design straight, which is why I've focused in this blog post on "Design Decisions". Some particular matters:
CASE, GET, RESIGNAL don't work.
No support for OUT or INOUT parameters.
Only SQL/PSM, not PL/SQL (ocelotgui supports PL/SQL but CREATE LUA does not.)
No handlers within handlers.
No ITERATE or LEAVE to get out of handlers.
Really, this might be useful for any DBMS, even one that doesn't support SQL/PSM, since SQL/PSM is what I took care of. But for a DBMS that we don't support, you'll have to connect to either MySQL/MariaDB or Tarantool and enter generic standard SQL statements, then change to your dialect's SQL statements after you have the Lua function.
Where is this code
I pushed the source code to github.com/ocelot-inc/ocelotgui. It is part of ocelotgui. It has to be part of ocelotgui because we need ocelotgui's recognizer. So anyone wanting to alpha-test would have to:
download the ocelotgui source
build as instructed in the README
start ocelotgui and connect
make sure syntax checking is on, ansi_quotes is off, oracle mode is off
enter a CREATE LUA PROCEDURE or CREATE LUA FUNCTION statement
look at the resulting dialog box
repeat.
The code of the new feature is mostly in file ocelotgui.cpp, function clf().
I want to know whether anyone agrees with the approach. That will affect whether I eventually move the feature to beta.
Comment on this post, or (if the comment period has expired) add a feature request on github/issues, or write to pgulutzan at-sign ocelot.ca.
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;
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.
Let’s start by combining one new MariaDB feature — VALUES (5) which is the standard equivalent of the old non-standard “SELECT 5” — with another new feature in MariaDB 10.3.5, the FOR … END FOR statement.
mariadb>BEGIN NOT ATOMIC
> DECLARE v INT DEFAULT 0;
> FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR;
> SELECT v;
>END;
Error 2013 (HY000) Lost connection to MySQL server during query
Oops. However, the MariaDB folks now know about this, it’s bug MDEV-15940″ and they know about the other bugs that I’ll mention in this post, so there’s no problem provided you wait for the next version.
FOR … END FOR works prettily so I decided to describe it. The official documentation isn’t out yet so this could change.
FOR x IN (subquery) DO … END FOR
Example:
BEGIN NOT ATOMIC
DECLARE v INT DEFAULT 0;
FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;
SELECT v;
END;
The SELECT will return ‘1’ because the SET statement happened once, because SELECT 5 returns 1 row. We must put (SELECT 5) inside parentheses because there must be a way to find where it ends — the word DO is not reserved and therefore is useless as a marker for the parser.
FOR x IN cursor DO … END FOR
Example:
CREATE TABLE t (s1 INT);
INSERT INTO t VALUES (1),(2),(3);
BEGIN NOT ATOMIC
DECLARE v2, v3 INT DEFAULT 0;
BEGIN
DECLARE cn CURSOR FOR SELECT * FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v3 = 1;
FOR cn_record IN cn
DO
SET v2 = v2 + cn_record.s1;
END FOR;
END;
SELECT v2, v3;
END;
FOR cn_record IN cn means “loop with cursor cn, which was declared earlier”. And cn_record according to MariaDB is a row variable that can be used within the loop, so cn_record.s1 is the value of s1 within a given row of table t.
Given that table t has 3 rows containing (1),(2),(3), and given that the obvious intent is that v2 will end up with the sum, you might think the SELECT will return v2 = 6, eh? Well, you’re thinking right, it does. This is a way to go through a cursor, with each iteration doing an automatic FETCH.
And, since there’s a CONTINUE HANDLER that says we’ll set v3 = 1 when there are no more rows to fetch, you might think the SELECT will return v3 = 1, eh? Well, you’re thinking reasonably (I think), but it doesn’t. The handler isn’t brought into play, the NOT FOUND condition is automatically cleared when the loop ends.
Summary: this kind of FOR does automatic cursor-open and cursor-fetch, but not automatic cursor-close. To me that looked like a bug, the way I interpret the standard document, “CLOSE cn;” is supposed to happen. And MariaDB agrees, it’s bug MDEV-15941.
FOR x IN low-number .. high-number DO … END FOR
Example:
SET @v = 0;
CREATE PROCEDURE p()
BEGIN
FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;
SELECT @v;
END;
MariaDB 10.3 comes with a boatload of Oracle/PostgreSQL syntax, often known as “PL/SQL” (their deviation from the standard “SQL/PSM” syntax which MySQL/MariaDB have followed in the past). But all the PL/SQL stuff is supplied if and only if you say
SET @@sql_mode='oracle';
The FOR … END FOR statement is an exception, it works in the default mode too. That’s deliberate. And it’s equivalent to
SET a = 1; WHILE a <= 3 DO ... SET a = a + 1; END WHILE;
So the SELECT in the above example displays '6'.
The ".." is a new operator, vaguely similar to the "range operators" in languages like Ruby and Perl. But SQL already has a range operator, it's called BETWEEN. And now MariaDB won't be able to accept the SQL Server syntax where ".." is for qualifiers ("a..b" means "schema a + default table + column b").
I encountered a problem during the test, with:
FOR a IN (SELECT 1) .. 3 DO SET @v = @v + a; END FOR;
Technically this should be legal because “low-number” can be any expression, including a subquery. In this case, though, subqueries mean something else, so it is not legal. This is now bug MDEV-15944″.
I enthusiastically recommend: don’t use this, use WHILE.
Differences from the standard
My first example looked like this:
FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;
In standard SQL it would have looked like this:
FOR (VALUES (5)) DO SET v = v + 1; END FOR;
… The standard doesn’t require mentioning “a” when there is no need to refer to “a” inside the loop. The fact that the standard would use “VALUES (5)” is, as we’ve seen, something that MariaDB will soon support too.
My second example looked like this:
FOR cn_record IN cn
DO
SET v2 = v2 + cn_record.s1;
END FOR;
In standard SQL it would have looked like this:
FOR cn_record AS cn
DO
SET v2 = v2 + s1;
END FOR;
So it’s a difference between “IN” and “AS”, and between “cn_record.s1” and “s1”. However, I could have said “cn_record.s1” in standard SQL too, it’s optional when there’s no ambiguity.
There are serious matters that underlie that innocent-looking difference with “cn_record.”, about which I now digress.
Shadowing, a Digression
In the following, should “SELECT v FROM t;” display 1, or 2, or 3?
BEGIN NOT ATOMIC
a: DECLARE v INT DEFAULT 1;
BEGIN
b: DECLARE v INT DEFAULT 2;
CREATE TABLE t (v INT);
INSERT INTO t VALUES (3);
SELECT v FROM t;
END;
END;
Answer: in MariaDB it’s 2. Inner scope beats outer scope, and variable declaration beats table definition. This is an old MySQL flaw that MariaDB has inherited (Bug #5967 Stored procedure declared variable used instead of column). The workaround, if you actually want “column v of table t”, is to use a qualifier, as in “SELECT t.v FROM t;”.
But what if you wanted to refer to the first declared variable? That would be easy too, in standard SQL you would qualify with the label, as in “SELECT a.v FROM t;”. Unfortunately — another old MySQL flaw that MariaDB has inherited — you can’t use [label.]variable_name for qualification.
As a result of these two flaws, we could have trouble in our FOR … END FOR loop if we used unqualified names like “s1”. Therefore in MariaDB you have to say “cn_record.s1” if you want the FOR variable, that is, qualification is compulsory. That’s a solution.
But the solution depends on a third flaw.
In standard SQL in this FOR loop “cn_record” becomes a label, and that’s why “cn_record.s1” would be legal — cn_record is a label qualifier. That’s not the case with Oracle/MariaDB, where “cn_record” is a variable with type = ROW — cn_record is a row-variable qualifier. The effect is the same for the particular example that I used, but that’s just luck.
The flaws do not mean that the implementor did something wrong for FOR … END FOR, rather it means that the blame lies in MySQL’s history. It would be grossly wrong to blame the standard, which has no flaws.
Differences from the Standard, continued
My third example looked like this:
FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;
In standard SQL it would have looked like this:
[empty prairie with crickets chirping in the darkness]
For more about the original plan for adding FOR … END FOR to MySQL in a standard way, go to “WL#3309: Stored Procedures: FOR statement” and click “High-level architecture”.
ocelotgui
Of course we’ve changed Ocelot’s GUI client for MySQL/MariaDB so that it recognizes most of the new syntax in MariaDB 10.3. We haven’t changed the debugger, though, so it won’t handle statements containing FOR … END FOR.
Starting with MariaDB 10.3.5, if you say sql_mode = ‘simultaneous_assignment’, then
UPDATE t SET a = b, b = a;
will swap b and a because a gets what’s originally in b, while b gets what’s originally in a (as of the start of the SET for a given row), instead of what was going on before, which was “Assignments are evaluated in left-to-right order”.
I will list things that I like or don’t like, and behaviour-change cases to watch for.
I like it that this is standard SQL at last
I don’t know any other vendor who ever did left-to-right assignments, and they had a good reason.
Way back in SQL-86, section 8.11 said how to handle a situation with (simplified BNF)
SET <object column> = <value expression> | NULL [, <object column> = <value expression> | NULL …
“If the <value expression> contains a reference to a column of T, the reference is to the value of that column in the object row before any value of the object row is updated.”
In case anyone still didn’t get it, SQL-92 added the sentence:
“The <value expression>s are effectively evaluated before updating the object row.”
Unfortunately later versions of the standard were slightly denser, such as:
“11) Each <update source> contained in SCL is effectively evaluated for the current row before any of the current row’s object rows is updated.”
The abbreviation “SCL” is the set clause list. The word “rows” is not a typo, but it’s for a special situation, we can still interpret as “before the object row is updated”.
Thus what we’re talking about is optionally following the standard.
So hurrah, let’s put equestrian statues of the MariaDB seal in main squares of our cities.
I don’t like the name
I’ll admit that the effect can be the same, and I’ll admit that some SQL Server commenters like to refer to this as doing things “all at once”. (See here and here.) But the standard description isn’t really demanding that all the assignments be simultaneous.
And the term “simultaneous assignment” can make some programmers
think of “parallel assignment”, as in this example (from Wikipedia)
a, b := 0, 1
A name like “evaluate_sources_first_during_updates” would have been better.
I don’t like that it’s a mode
We all know the problems of setting a global variable that people don’t see or think about when they create SQL statements.
So perhaps it would have been better to make a change to the UPDATE syntax, e.g.
UPDATE /*! new-style-update */ NEW STYLE t SET …;
It doesn’t solve the problem cleanly — now every statement has non-standard syntax in order to perform in a standard way — but it means you don’t get the behaviour change in every statement, only in the ones where you ask.
Or perhaps it would have been better to produce a warning: “You are using an sql_mode value that changes the behaviour of this particular UPDATE statement because evaluation is no longer left to right.” I don’t know if it would be hard. I don’t know if it’s too late.
Setup for examples
Following examples are done with a one-row table, made thus:
CREATE TABLE t (col1 INT, col2 INT, PRIMARY KEY (col1, col2));
INSERT INTO t VALUES (1, 2);
The MariaDB version is 10.3.5 downloaded on 2018-03-20.
Assigning with swaps and variables
SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 1, col2 = 2;
UPDATE t SET col1 = col2, col2 = col1;
SELECT * FROM t;
-- Result: col1 is 2, col2 is 1.
This is different from what happens with @@sql_mode=”. This is the best example that shows the new mode works.
SET @@sql_mode='simultaneous_assignment';
SET @x = 100;
UPDATE t SET col1 = @x, col2 = @x := 200;
SELECT * FROM t;
-- Result: col1 is 100, col2 is 200.
This is not different from what happens with @@sql_mode=”. Conclusion: if you use the non-standard variable-assignment trick, the value is not updated in advance.
What about the SET statement?
DROP PROCEDURE p;
SET @@sql_mode='simultaneous_assignment';
CREATE PROCEDURE p()
BEGIN
DECLARE v1 INT DEFAULT 1;
DECLARE v2 INT DEFAULT 2;
SET v1 = v2, v2 = v1;
SELECT v1, v2;
END;
CALL p();
-- Result: v1 is 2, v2 is 2.
This is not different from what happens with @@sql_mode=”. Conclusion ‘simultaneous_assignment’ does not affect assignments with the SET statement.
Perhaps people will expect otherwise because SET statements look so similar to SET clauses, but this is actually correct. The way the standard defines things, for syntax that is similar to (though not exactly the same as) “SET v1 = v2, v2 = v1;”, is: the statement should be treated as equivalent to
SET v1 = v2;
SET v2 = v1;
Good. But come to think of it, this is another reason to dislike the misleading mode name ‘simultaneous_assignment’. SET statements are assignments but @@sql_mode won’t affect them.
What about the INSERT statement?
SET @@sql_mode='simultaneous_assignment';
TRUNCATE TABLE t;
INSERT INTO t SET col1 = col2, col2 = 99;
SELECT * FROM t;
-- Result: col1 is 0, col2 is 99.
Conclusion: ‘simultaneous_assignment’ does not affect INSERT … SET statements, processing is left to right. This looks inconsistent to me, but it’s not wrong.
What about the INSERT ON DUPLICATE KEY UPDATE statement?
SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 0, col2 = 1;
INSERT INTO t VALUES (0, 1) ON DUPLICATE KEY UPDATE col1 = col2, col2 = col1;
SELECT * FROM t;
-- Result: col1 is 1, col2 is 0.
Conclusion: ‘simultaneous_assignment does affect INSERT ON DUPLICATE KEY UPDATE. Nobody said that it has to (this syntax is a MySQL/MariaDB extension), but surely this is what everyone would expect. Good.
Triggers and constraints
I could not come up with a case where ‘simultaneous_assignment’ affected the order in which triggers or constraints are processed. Good.
Assigning twice to the same column
SET @@sql_mode='simultaneous_assignment';
UPDATE t SET col1 = 3, col2 = 4, col1 = col2;
-- Result: error.
Good. There’s supposed to be an error, because (quoting the standard document again) “Equivalent <object column>s shall not appear more than once in a <set clause list>.”
Actually this doesn’t tell us something new about ‘simultaneous_assignment’, I only tried it because I thought that MariaDB would not return an error. Probably I was remembering some old version before this was fixed.
Multiple-table updates
From the bugs.mysql.com site quoting the MySQL manual:
“Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.”
I don’t see an equivalent statement in the MariaDB manual. So I conclude that even for multiple-table updates the assignments will be done in a standard way, but couldn’t think of a good way to test.
There are other things
The really big things in MariaDB 10.3.5 are PL/SQL and the myrocks engine. So, just because I’ve looked at a small thing, don’t get the impression that 10.3.5 is going to be a minor enhancement.
ocelotgui
There have been no significant updates to ocelotgui since my last blog post.
Tarantool, a Lua application server plus NoSQL DBMS, is now an SQL DBMS too, in alpha version 1.8. I was interested in how the combination “Lua + NoSQL + SQL” works. Disclaimer: I do paid work related to Tarantool but it has nothing to do with this blog.
First let’s verify that it’s really SQL. The illustrations are all unretouched screenshots from ocelotgui for Windows, connected to a Tarantool 1.8 server on Linux, which I built from a source download on github.
Example of SELECT
Yes, the “history” below the query window shows successful CREATE and INSERT statements, the “result set” at the bottom shows a successful SELECT statement’s output. A close look at the clauses shows that there’s support for constraints, foreign keys, triggers, … and so on. In all, it’s a reasonable subset of the SQL standard, pretty well the same as what I found for SQLite in an earlier post.
That’s not surprising because Tarantool started with SQLite’s parser; however, the storage layer is Tarantool’s NoSQL.
Combine Lua with SQL
Now it’s time for the first “combination”. I want to store and retrieve pictures, which are in .png and .jpg files. With MySQL/MariaDB I’d use load_file() but that’s a built-in function that Tarantool doesn’t have. Am I daunted? No, because I can write such a function in Lua — or actually I can copy such a function because it’s one of the examples in the Tarantool manual.
-- Lua function to set a variable to a file's contents
function load_file(file_name)
local fio = require('fio')
local errno = require('errno')
local f = fio.open(file_name, {'O_RDONLY' })
if not f then
error("Failed to open file: "..errno.strerror())
end
local data = f:read(1000000)
f:close()
return data
end;
Not a difficult matter. As is clear from the display,
the function is syntactically okay (there would be squiggly red lines under the error if my Lua was bad). To explain the function: it says “read a file and return either an error message or the whole contents”.
I can’t call Lua functions directly from SQL yet, but I can do so from NoSQL, and with NoSQL I can INSERT into the same table that I created with SQL …
box.space.timages:insert{1,load_file(‘shot1.jpg’),’shot1′);
And then go back to SQL to handle the retrieval …
SELECT * FROM “timages”;
and the .jpg image is at the bottom of the screen.
So, although they’re not standard SQL/PSM or Oracle-like PL/SQL, Tarantool does have server-side stored procedures.
Combine NoSQL With SQL
Now it’s time for the second “combination”. I want to read some NoSQL data that was not produced or populated with SQL in mind. Specifically we’ve got: a variable number of fields, some of which are unnamed, and they’re not all scalar, there are arrays and structures. A typical tuple related to index metadata looks like this:
[[ Example of tuple ]]
- [312, 1, 'owner', 'tree', {'unique': false}, [[0, 'unsigned']]]
- [312, 2, 'object', 'tree', {'unique': false}, [[2, 'string'], [3, 'unsigned']]]
- [313, 0, 'primary', 'tree', {'unique': true}, [[1, 'unsigned'], [2, 'string'], [3, 'unsigned']]]
- [313, 1, 'owner', 'tree', {'unique': false}, [[0, 'unsigned']]]
- [313, 2, 'object', 'tree', {'unique': false}, [[2, 'string'], [3, 'unsigned']]]
- [320, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [320, 1, 'uuid', 'tree', {'unique': true}, [[1, 'string']]]
For this I wrote some C code in the client instead of on the server, but I don’t think that’s cheating — it doesn’t show a Tarantool feature, but it does show that one can transfer the data into an SQL table and go from there. The syntax added to the client looks like this:
CREATE SERVER id FOREIGN DATA WRAPPER ocelot_tarantool
OPTIONS (PORT 3301, HOST 'localhost', USER 'guest');
CREATE TABLE convertee SERVER id LUA 'return box.space._index:select()';
SELECT * FROM convertee;
The selection after converting looks like this:
(I’m changing the fonts and the window order now to make relevant parts look bigger.)
I wish I could call this “flattening”, but that term has been hijacked for other concepts. Anyway, whatever it can be called, it’s the reason that schemaless data doesn’t need a new query language.
Things You Don’t Always See Elsewhere
I’ll mention a few things that are in Tarantool SQL that are not in MySQL/MariaDB, or are fairly new in MySQL/MariaDB. This short list does not mean Tarantool is “ahead”, I’m talking about an alpha where many things are to-be-announced. I like to look at what’s new and different.
COLLATE
The point isn’t that there’s a COLLATE clause, the point is that the collation is ICU. I’ve talked about the advantages of ICU earlier. The collation name will change soon, probably to ‘unicode_s1’ or ‘unicode_ci’.
WITH
A non-recursive WITH clause is the same thing as a temporary view that lasts only for the statement that encloses it.
A recursive WITH clause is a temporary view that is populated by taking a seed (the query to the left of the UNION) and adding to it with a populator (the query to the right of the UNION), repeatedly, until some condition fails. I find it hard to understand, I suppose my problem is that this is procedural (a loop), and for procedural problems I prefer C or Lua or SQL/PSM.
EXCEPT and INTERSECT
SELECT * FROM "t" EXCEPT SELECT * FROM f;
These operators can fit in the same place as UNION, but have different effects. In the example, the EXCEPT would mean “take out the rows that match” instead of “add rows that do not match”.
NaN and Inf
This is a differentiator, since in standard SQL and in some implementations these values are not supported, even though they’re supported in IEEE 754.
CHECK
A constraint’s effect is: “if the condition inside the parentheses is true or unknown, then and only then it is legal to have this row.” This feature is also in MariaDB 10.2.1.
INDEXED BY
This is non-standard (and obviously always will be): you can force Tarantool to use a particular index, or no index at all, bypassing the optimizer.
VALUES
This means: return a result set containing a row with three columns containing 1, 2, 3. MySQL and MariaDB have a non-standard way to do this:
SELECT 1,2,3;
I like the logic of VALUES and the fact that I can say
VALUES (1,2,3),(4,5,6);
but Tarantool also supports the MySQL/MariaDB way, and I expect that it will always be more popular.
Game Changer?
Tarantool’s aiming high and Tarantool’s owner has a $9-billion market cap so the resources are there, but I’m not sure that Oracle sees them on its threat radar just yet. Tarantool SQL is not a drop-in replacement for all the code you’ve written for Oracle or MySQL/MariaDB, and the distinguishing features that I’ve mentioned are only going to cause a few people to migrate, at least in 2018. Other people will use Tarantool as an “add-on” or “engine”.
I do see that this is close enough to SQLite that it will probably be reasonable to switch from there, if people need the extra multi-user / replication capabilities and the Lua integration and the speed (the main engine is an in-memory DBMS).
More dimly, I see some other NoSQL DBMSs looking bad compared to Tarantool because their SQL support is trivial by comparison. I’m thinking especially of the ones that already get beaten by Tarantool in YCSB tests of NoSQL performance.
Tarantool’s licence is BSD.
Alphas Are Not Betas
Everything I’ve described above might change before Tarantool SQL is ready for use in production.
If you want to try to replicate the examples exactly, start with the old version-1.7 manual, move up to the SQL tutorial for version-1.8. The ocelotgui client additional instructions for connecting to Tarantool are here.
UPDATE: Tarantool’s SQL feature is now part of Tarantool 2.1 instead of Tarantool 1.8. The SQL tutorial is now here. The instructions for connecting ocelotgui to Tarantool are the same as before, except for the change in version number. Tarantool 2.1 is beta.
UPDATE: ocelotgui support for Tarantool was greatly enhanced after this post was written. See https://github.com/ocelot-inc/ocelotgui-tarantool.
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 | |
+----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
