pgulutzan – Page 3 – SQL And Its Sequels

About pgulutzan

Co-author of four computer books. Software Architect at MySQL/Sun/Oracle from 2003-2011, and at HP for a little while after that. Currently with Ocelot Computer Services Inc. in Edmonton Canada.

View all posts by pgulutzan

Convert SQL Stored Procedures to Lua Functions

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.

pgulutzan, January 12, 2020. Category: Standard SQL.

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

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

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

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



ocelot_date_to_julianday

  Return number of days since 4713-01-01, given yyyy-mm-dd [BC] date

ocelot_date_validate

  Return okay or error, given yyyy-mm-dd BC|AD date which may be invalid

ocelot_date_datediff

  Return number of days difference, given two yyyy-mm-dd [BC] dates

ocelot_date_test

  Return ‘OK’ after a thorough test of the entire range of dates

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

Example expressions:



#1 ocelot_date_to_julianday(‘0001-01-01′,’G’) returns 1721426

#2 ocelot_date_to_julianday(‘0001-01-01′,’J’) returns 1721424

#3 ocelot_date_to_julianday(‘4712-01-01 BC’, ‘O’) returns 0

#4 ocelot_date_datediff(‘0001-01-01′,’0001-01-01 BC’,’G’) returns 366

#5 ocelot_date_to_julianday(‘1492-10-12′,’J’)%7; returns 4

/* Explanations: #3 returns 0 because there’s a year 0000,

#4 returns 366 because 0001 BC is a leap year,

#5 returns weekday = 4 for the original Columbus Day

and he used a Julian calendar. */

The source code

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

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

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


/*

Copyright (c) 2019 Ocelot Computer Services Inc.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/*

  ocelot_date_to_julianday(yyyy-mm-dd[ BC], J|G|O) Return number of days

  ————————

  If J: will return 0 for ‘4713-01-01 BC’, all calculations use Julian calendar

  If G: will return 0 for ‘4714-11-24 BC’, all calculations use Gregorian calendar

  If O: will return 0 for ‘4712-01-01 BC’, switch between calendars after 1582-10-04

*/

CREATE FUNCTION ocelot_date_to_julianday(in_date VARCHAR(25), options CHAR(1)) RETURNS DECIMAL(8)

LANGUAGE SQL DETERMINISTIC CONTAINS SQL

BEGIN

  DECLARE year, month, day, century, leap INT;

  DECLARE jd DOUBLE PRECISION;

  DECLARE bc_as_char CHAR(2);

  SET year = CAST(SUBSTRING(in_date FROM 1 FOR 4) AS DECIMAL(8));

  SET month = CAST(SUBSTRING(in_date FROM 6 FOR 2) AS DECIMAL(8));

  SET day = CAST(SUBSTRING(in_date FROM 9 FOR 2) AS DECIMAL(8));

  SET bc_as_char = SUBSTRING(in_date FROM CHAR_LENGTH(in_date) – 1 FOR 2);

  IF bc_as_char = ‘BC’ THEN

    IF options = ‘O’ THEN SET year = 0 – year;

    ELSE SET year = (0 – year) + 1; END IF;

  END IF;

  IF month <= 2 THEN
    SET year = year - 1;
    SET month = month + 12;
  END IF;
  IF options = 'G'
  OR (options = 'O' AND in_date >= ‘1582-10-05’ AND bc_as_char <> ‘BC’) THEN

    SET century = FLOOR(year / 100.0);

    SET leap = 2 – century + FLOOR(century / 4.0);

  ELSE

    SET leap = 0;

  END IF;

  SET jd = FLOOR(365.25 * (year + 4716)) + FLOOR(30.6001 * (month + 1)) + day + leap – 1524;

  RETURN CAST(jd AS DECIMAL(8));

END;
/*

  ocelot_date_validate (yyyy-mm-dd[ BC] date, J|G|O) Return ‘OK’ or ‘Error …’

  ——————–

  Possible errors:

    Format of first parameter is not ‘yyyy-mm-dd’ or ‘yyyy-mm-dd BC’.

    Second parameter is not ‘J’ or ‘G’ or ‘O’.

    Minimum date = 4713-01-01 BC if J, 4712-01-01 BC if O, 4714-11-14 BC if G.

    Maximum date = 9999-12-31.

    If ‘O’: 0001-mm-dd BC, or between 1582-10-05 and 1582-10-14.

    nnnn-02-29 if nnnn is not a leap year.

    Month not between 1 and 12.

    Day not between 1 and maximum for month.

  Otherwise return ‘OK’.

*/

CREATE FUNCTION ocelot_date_validate(in_date VARCHAR(25), options CHAR(1)) RETURNS VARCHAR(50)

LANGUAGE SQL DETERMINISTIC CONTAINS SQL

BEGIN

  DECLARE year, month, day, leap_days DECIMAL(8);

  DECLARE bc_or_ad VARCHAR(3) DEFAULT ”;

  IF options IS NULL

  OR (options <> ‘J’ AND options <> ‘G’ AND options <> ‘O’) THEN

    RETURN ‘Error, Options must be J or G or O’;

  END IF;

  IF in_date IS NULL

  OR (CHAR_LENGTH(in_date) <> 10 AND CHAR_LENGTH(in_date) <> 13)

  OR SUBSTRING(in_date FROM 1 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 2 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 3 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 4 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 5 FOR 1) <> ‘-‘

  OR SUBSTRING(in_date FROM 6 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 7 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR  SUBSTRING(in_date FROM 8 FOR 1) <> ‘-‘

  OR SUBSTRING(in_date FROM 9 FOR 1) NOT BETWEEN ‘0’ AND ‘9’

  OR SUBSTRING(in_date FROM 10 FOR 1) NOT BETWEEN ‘0’ AND ‘9’ THEN

     RETURN ‘Error, Date format is not nnnn-nn-nn’;

  END IF;

  IF CHAR_LENGTH(in_date) = 13 THEN

    SET bc_or_ad = SUBSTRING(in_date FROM 11 FOR 3);

    IF bc_or_ad <> ‘ BC’ THEN

      RETURN ‘Error, only space + BC is allowed after yyyy-mm-dd’;

    END IF;

  END IF;

  SET year = CAST(SUBSTRING(in_date FROM 1 FOR 4) AS DECIMAL(8));

  SET month = CAST(SUBSTRING(in_date FROM 6 FOR 2) AS DECIMAL(8));

  SET day = CAST(SUBSTRING(in_date FROM 9 FOR 2) AS DECIMAL(8));

  IF year = 0 THEN

    RETURN ‘Error, year 0’;

  END IF;

  IF bc_or_ad = ‘ BC’ THEN

    IF options = ‘J’ AND year > 4713 THEN

      RETURN ‘Error, minimum date = 4713-01-01 BC’;

    END IF;

    IF options = ‘O’ AND year > 4712 THEN

      RETURN ‘Error, minimum date = 4712-01-01 BC’;

    END IF;

    IF OPTIONS = ‘G’ THEN

      IF year > 4714

      OR (year = 4714 AND month < 11)
      OR (Year = 4714 AND month = 11 AND day < 24) THEN
        RETURN 'Error, minimum date = 4714-11-24 BC';
      END IF;
    END IF;
  END IF;
  IF month = 0 OR month > 12 THEN RETURN ‘Error, month not between 1 and 12’; END IF;

  SET leap_days = 0;

  IF month = 2 AND day = 29 THEN

    IF bc_or_ad = ‘ BC’ AND options <> ‘O’ THEN SET year = year – 1; END IF;

    IF year % 4 = 0 THEN

      IF options = ‘J’ OR (options = ‘O’ AND (bc_or_ad = ‘ BC’ OR SUBSTRING(in_date FROM 1 FOR 10) < '1582-10-04')) THEN
        SET leap_days = 1;
      ELSE
        IF year % 100 <> 0 OR year % 400 = 0 THEN

          SET leap_days = 1;

        END IF;

      END IF;

    END IF;

    IF leap_days = 0 THEN RETURN ‘Error, February 29, not a leap year’; END IF;

  END IF;

  IF month = 1 AND day > 31

  OR month = 2 AND day – leap_days > 28

  OR month = 3 AND day > 31

  OR month = 4 AND day > 30

  OR month = 5 AND day > 31

  OR month = 6 AND day > 30

  OR month = 7 AND day > 31

  OR month = 8 AND day > 31

  OR month = 9 AND day > 30

  OR month = 10 AND day > 31

  OR month = 11 AND day > 30

  OR month = 12 AND day > 31 THEN

    RETURN ‘Error, day > maximum day in mnth’;

  END IF;

  IF options = ‘O’

  AND bc_or_ad <> ‘ BC’

  AND SUBSTRING(in_date FROM 1 FOR 10) BETWEEN ‘1582-10-05’ AND ‘1582-10-14’ THEN

    RETURN ‘Error, Date during Julian-to-Gregorian cutover’;

  END IF;

  RETURN ‘OK’;

END;
/*

  ocelot_date_datediff(date, date, J|G|O) Return number of days between two dates

  ——————–

  Results for positive Gregorian will be the same as MySQL/MariaDB datediff().

  This is an extension of datediff() which works with BC Gregorian and other calendars.

  Mostly it’s just to show how easily a routine can be written if there is a

  Julian-day function.

*/

CREATE FUNCTION ocelot_date_datediff(date_1 VARCHAR(25), date_2 VARCHAR(25), options CHAR(1)) RETURNS INT

LANGUAGE SQL DETERMINISTIC CONTAINS SQL

RETURN ocelot_date_to_julianday(date_1, options) – ocelot_date_to_julianday(date_2, options);
/*

  ocelot_date_test(J|G|O) Test that all legal dates have the correct Julian day

  —————-

  You only need to run this once. The Julian day routine looks bizarre so this

  test is here to give assurance that the ocelot_date_to_julianday function is okay.

  Start with a counter integer = 0 and a yyyy-mm-dd BC date = the minimum for the calendar.

  For each iteration of the loop, increment the counter and increment the date,

  call ocelot_date_to_julianday and check that it returns a value equal to the counter.

  Stop when date is 9999-12-31.

  For Oracle emulation we do not check dates which are invalid due to cutover or bugs.

  Bonus test: positive Gregorian dates must match MySQL|MariaDB datediff results.

  Bonus test: check validity of each incremented date.

*/

CREATE FUNCTION ocelot_date_test(options CHAR(1)) RETURNS CHAR(50)

LANGUAGE SQL DETERMINISTIC CONTAINS SQL

BEGIN

  DECLARE tmp VARCHAR(25);

  DECLARE tmp_validity VARCHAR(50);

  DECLARE year_as_char, month_as_char, day_as_char VARCHAR(25);

  DECLARE year_as_int, month_as_int, day_as_int DECIMAL(8);

  DECLARE ju, ju2 INT;

  DECLARE bc_as_char VARCHAR(3) DEFAULT ”;

  DECLARE is_leap INT DEFAULT 1;

  IF options = ‘J’ THEN

    SET ju = 0; SET tmp = ‘4713-01-01 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 1;

  END IF;

  IF options = ‘G’ THEN

    SET ju = 0; SET tmp = ‘4714-11-24 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 0;

  END IF;

  IF options = ‘O’ THEN

    SET ju = 0; SET tmp = ‘4712-01-01 BC’; SET bc_as_char = ‘ BC’; SET is_leap = 1;

  END IF;

  WHILE tmp <> ‘10000-01-01’ DO

    IF options <> ‘O’

    OR SUBSTRING(tmp FROM 1 FOR 4) <> ‘0000’

    OR bc_as_char <> ‘ BC’ THEN

      SET tmp_validity = ocelot_date_validate(tmp, options);

      IF tmp_validity <> ‘OK’ THEN RETURN tmp_validity; END IF;

    END IF;

    SET ju2 = ocelot_date_to_julianday(tmp, options);

    IF ju2 <> ju OR ju2 IS NULL THEN RETURN CONCAT(‘Fail ‘, tmp); END IF;
    IF options = ‘G’ and bc_as_char <> ‘ BC’ THEN

      IF ju2 – 1721426 <> DATEDIFF(tmp,’0001-01-01′) THEN

        RETURN CONCAT(‘Difference from datediff() ‘, tmp);

      END IF;

    END IF;

    SET year_as_char = SUBSTRING(tmp FROM 1 FOR 4);

    SET month_as_char = SUBSTRING(tmp FROM 6 FOR 2);

    SET day_as_char = SUBSTRING(tmp FROM 9 FOR 2);

    SET year_as_int = CAST(year_as_char AS DECIMAL(8));

    SET month_as_int = CAST(month_as_char AS DECIMAL(8));

    SET day_as_int = CAST(day_as_char AS DECIMAL(8));

    /* Increase day */

    SET day_as_int = day_as_int + 1;

    IF options = ‘O’ AND year_as_int = 1582 AND month_as_int = 10 AND day_as_int = 5 AND bc_as_char <> ‘ BC’ THEN

      SET day_as_int = day_as_int + 10;

    END IF;

    IF month_as_int = 1 AND day_as_int > 31

    OR month_as_int = 2 AND day_as_int – is_leap > 28

    OR month_as_int = 3 AND day_as_int > 31

    OR month_as_int = 4 AND day_as_int > 30

    OR month_as_int = 5 AND day_as_int > 31

    OR month_as_int = 6 AND day_as_int > 30

    OR month_as_int = 7 AND day_as_int > 31

    OR month_as_int = 8 AND day_as_int > 31

    OR month_as_int = 9 AND day_as_int > 30

    OR month_as_int = 10 AND day_as_int > 31

    OR month_as_int = 11 AND day_as_int > 30

    OR month_as_int = 12 AND day_as_int > 31 THEN

      /* Increase month */

      SET day_as_int = 1;

      SET month_as_int = month_as_int + 1;

      IF month_as_int > 12 THEN

        /* Increase year */

        SET month_as_int = 1;

        IF bc_as_char = ‘ BC’ THEN SET year_as_int = year_as_int – 1;

        ELSE SET year_as_int = year_as_int + 1; END IF;

        IF (year_as_int = 0 AND (options = ‘J’ OR options = ‘G’))

        OR (year_as_int =-1 AND options = ‘O’) THEN

          SET year_as_int = 1;

          SET bc_as_char = ”;

          SET is_leap = 0;

        END IF;

        /* Recalculate is_leap */

        BEGIN

          DECLARE divisible_year_as_int INT;

          SET divisible_year_as_int = year_as_int;

          IF bc_as_char <> ‘ BC’ OR options = ‘O’ THEN

            SET divisible_year_as_int = year_as_int;

          ELSE

            SET divisible_year_as_int = year_as_int – 1;

          END IF;

          SET is_leap = 0;

          IF divisible_year_as_int % 4 = 0 THEN

            SET is_leap = 1;

            IF options = ‘G’

            OR (options = ‘O’ AND bc_as_char <> ‘ BC’ AND year_as_int > 1582) THEN

              IF divisible_year_as_int % 100 = 0

              AND divisible_year_as_int % 400 <> 0 THEN

                SET is_leap = 0;

              END IF;

            END IF;

          END IF;

        END;

      END IF;

    END IF;

    SET day_as_char = CAST(day_as_int AS CHAR);

    IF LENGTH(day_as_char) = 1 THEN SET day_as_char = CONCAT(‘0’, day_as_char); END IF;

    SET month_as_char = CAST(month_as_int AS CHAR);

    IF LENGTH(month_as_char) = 1 THEN SET month_as_char = CONCAT(‘0’, month_as_char); END IF;

    SET year_as_char = CAST(year_as_int AS CHAR);

    WHILE LENGTH(year_as_char) < 4 DO
      SET year_as_char = CONCAT('0', year_as_char);
    END WHILE;
    SET tmp = CONCAT(year_as_char, '-', month_as_char, '-', day_as_char, bc_as_char);
    SET ju = ju + 1;
  END WHILE;
  RETURN CONCAT('OK ', tmp);
END;

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

sql_mode

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

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

Why?

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

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

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

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

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

The modes

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

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

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

Compare the SHOW CREATE TABLE results. Before:

and after:

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

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

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

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

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

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

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

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

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

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

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

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

The statement

There are many variants.

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

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

SET sql_mode=ORACLE;

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

ocelotgui 1.0.7

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

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

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

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

FOR … END FOR

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

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

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

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

FOR x IN (subquery) DO … END FOR

Example:

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

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

FOR x IN cursor DO … END FOR

Example:

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

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

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

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

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

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

Example:

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

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

SET @@sql_mode='oracle';

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

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

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

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

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

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

Differences from the standard

My first example looked like this:

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

In standard SQL it would have looked like this:

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

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

My second example looked like this:

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

In standard SQL it would have looked like this:

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

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

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

Shadowing, a Digression

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

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

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

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

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

But the solution depends on a third flaw.

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

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

Differences from the Standard, continued

My third example looked like this:

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

In standard SQL it would have looked like this:

  [empty prairie with crickets chirping in the darkness]

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

ocelotgui

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

pgulutzan, April 22, 2018. Category: MariaDB.

The simultaneous_assignment mode in MariaDB 10.3.5

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

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

I like it that this is standard SQL at last

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

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

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

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

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

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

I don’t like the name

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

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

A name like “evaluate_sources_first_during_updates” would have been better.

I don’t like that it’s a mode

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

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

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

Setup for examples

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

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

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

Assigning with swaps and variables

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

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

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

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

What about the SET statement?

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

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

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

SET v1 = v2;
SET v2 = v1;

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

What about the INSERT statement?

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

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

What about the INSERT ON DUPLICATE KEY UPDATE statement?

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

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

Triggers and constraints

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

Assigning twice to the same column

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

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

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

Multiple-table updates

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

There are other things

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

ocelotgui

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

pgulutzan, March 21, 2018. Category: MariaDB.

The Tarantool SQL Alpha

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

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

Example of SELECT

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.

pgulutzan, December 14, 2017. Category: Standard SQL.

Reserved Words

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

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

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

First here’s a screenshot of ocelotgui where I’m hovering over the word BEGIN.

What I’m illustrating is that you can’t depend on intuition and assume BEGIN is reserved, but a GUI client can tell you from context: it’s a declared variable.

20 words are reserved in MariaDB but not in MySQL:

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

36 words are reserved in MySQL but not in MariaDB:

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

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

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

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

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

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

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

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

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

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

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

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

No more mysql.proc in MySQL 8.0

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

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

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

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

Table definition differences

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

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

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

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

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

SELECT db, name FROM mysql.proc;

I could convert with ease to

SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM information_schema.routines;

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

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

param_list and returns

Let’s try

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

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

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

Result:

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

Now let’s look at it with

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

Result:

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

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

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

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

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

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

Result:

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

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

body

Let’s try:

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

Result:

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

Now let’s try:

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

Result:

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

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

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

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

Privilege differences

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

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

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

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

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

SHOW

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

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

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

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

GRANT SELECT ON *.* TO user_who_wants_to_show_create_procedure;

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

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

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

Other ways to get lists of routines

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

SELECT * FROM mysql.procs_priv;

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

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

Result:

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

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

Effect on ocelotgui debugger

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

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

pgulutzan, August 22, 2017. Category: MySQL.

The SQL Standard is SQL:2016

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

The LIST_AGG function

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

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

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

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

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

Polymorphic Table Functions

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

JSON

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

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

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

Advantage PostgreSQL.

Row Pattern Matching

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

Temporal Tables

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

pgulutzan, April 27, 2017. Category: Standard SQL.

MySQL, MariaDB, International Components for Unicode

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

That’s no longer always true, because ICU.

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

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

The support for ICU among DBMSs

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

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

PostgreSQL catches up?

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

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

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

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

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

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

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

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

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

The problems

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

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

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

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

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

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

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

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

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

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

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

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

← Previous page