Summary of MySQL syntax and functions not supported by Klustron

1. Klustron-server (the computing node of Klustron) does not support MySQL's proprietary DDL syntax

1. Does not support user defined vars (user-defined variables) and functions that depend on it, such as using prepared statement in a text-type MySQL connection.

2. Does not support mysql stored procedures, foreign keys and triggers, does not support mysql user and authority management syntax, does not support binlog replication related functions and its SQL syntax.

3. Does not support any other proprietary DDL syntax of mysql, such as the alter table syntax of mysql, and mysql's private extension of the create table statement, such as table partition syntax, and engine=xxx, etc.

4. The system functions of mysql will continue to increase on demand in computing nodes, and we have added some at present, and users can also increase them as needed during use.

   The method is to use any system procedure language supported by PostgreSQL (such as PL/SQL, Python, perl, java, etc.) to implement the required MySQL system functions, and then execute the create function statement on any Klustron-server node in the cluster to create the stored procedure. Then other computing nodes in the cluster will quickly copy and execute this statement so that they also have this system function. The advantage of having MySQL system functions is that Klustron-server can recognize these system functions as functions supported by Klustron-storage, so that more SQL calculations can be pushed down.

2. Summary of special circumstances of Klustron-server's DML support for MySQL

1. keyword occupation

   Using PG-specific keywords as identifiers (table names, column names, etc.) may cause errors in the sql parsing of Klustron-server; using mysql-specific keywords as identifiers (table names, column names, etc.) may cause Causes an error in sql parsing in Klustron-storage.

   Example: create table t1(key int);-- This statement will cause Klustron-storage to report an error and cannot be executed. Because key is a keyword in mysql, it is not in postgreSQL.

2. SQL MODES

   Although there is no variable sql_mode, the behavior of Klustron-server is equivalent to the sql_mode of mysql is set to

   ONLY_FULL_GROUP_BY ｜ STRICT_ALL_TABLES ｜ NO_ZERO_IN_DATE ｜ NO_ZERO_DATE
   ERROR_FOR_DIVISION_BY_ZERO ｜ NO_ENGINE_SUBSTITUTION | IGNORE_SPACE
   

   Therefore, Klustron-server does not accept illegal date, time and timestamp values.

   Klustron-server does not have the IGNORE_SPACE limitation of mysql, that is to say, there can be spaces between the function name and the parentheses, and (this cannot be done by mysql) some keywords (reserved words) can be used as function names. For ANSI_QUOTES the situation is slightly more complicated:

   in mysql connections to Klustron, one can turn on/off mysql_ansi_quotes to produce exactly the same effect as if ANSI_QUOTES set/unset to sql_mode in mysql.
   in pg connections it's as if always set, so only quote string constants with ''.
   

3. Automatically rollback transaction after statement execution error

   If there is an error in the execution of a statement in a transaction, the transaction will be automatically rolled back by Klustron-server, and Klustron-server will ignore all subsequent statements after receiving the transaction until it receives the commit/rollback statement. will clear and rollback the transaction. This is the same behavior as PostgreSQL.

4. The set statement simplifies MySQL's set session/global var=value statement. The value can be any expression, but the value in Klustron-server can only be a constant (such as numbers 1, 2, 3, and strings 'abc', 'def ' wait).

3. Differences between Klustron and MySQL syntax

1. there must be a space around prepstmt parameter place holder (?), it should not be ajacent to any other operator letter

   eg =?, +?, ?+, are wrong for Klustron-server, but OK for mysql.

2. tinyint and tinyint unsigned is 2-bytes, so treat it as a alias of MYSQL_TYPE_SHORT and use a short/unsigned short var to hold in/out paramter value.

3. mysql-8.0.17 and later deprecated type modifiers are not supported in Klustron

   They are: numeric display width, float/double(M,D), andfloat/double unsigned

4. default const value must match type of its column

   For example, create table t1 (id1 int NOT NULL default '0')is NOT OK, should becreate table t1 (id1 int NOT NULL default 0)

5. Unresonable and irrational type conversions are not supported

   type conversion between a date/time type and a numeric type are not supported;

   and type conversion between any pair of date/time types except between datetime and timestamp, are not supported.

6. CONVERT() to convert string encodingisn't supported.

7. strlen(blob): blob values are longer than actual length

   because a blob value is hex encoded and length() is pushed down to storage node which returns the encoded length

8. SET time_zone = '+00:00';not supported:do SET TimeZone = 'GMT';

   All available time zone names can be found via sql:select*from pg_timezone_names;, use the 'name' column.

9. timestamp constant must end with time zone

   eg 2011-02-02 15:31:06+00is a valid Klustron timestamp constant, but 2011-02-02 15:31:06isn't.

10. column alias names should not be single-quoted(), can be back quoted() or double quoted()

    eg select abcdefg as 'a' from t1is invalid, do select abcdefg as a from t1or select abcdefg as a from t1orselect abcdefg as "a" from t1

11. Representation of rows of all default values

    given table t1(a int default 0, b int default 0), insert into t1 values(), ()mysql syntax should be written asinsert into t1 values(default, default), (default, default)

12. fulltext match operator isn't same as mysql

    MATCH str AGAINST patternexpression should be written tostr ~ pattern

13. CREATE TABLE ... SELECT not supported

    should be split and written asCREATE TABLE ... LIKE ...; INSERT INTO ...SELECT*FROM ...

14. group_concat()not exist for now

15. don't drop objects which are dependent upon:

    eg given view v1 depends on table t1, don't drop table t1,v1 in one stmt, or drop v1 before t1 in 2 stmts, this is OK in mysql but not in Klustron, dependency check will reject this. simply do drop table t1 cascadewill drop v1 and anything dependent on t1 (eg sequences).

16. NOT supported: table level collation and charset setting at end of a CREATE TABLE stmt, andENGINE=XX setting

17. can't create non-unique index inside CREATE TABLE stmt with KEY() clause, and UNIQUE KEY()isn't OK, UNIQUE()is OK.

    eg create table(a int primary key, b int, c int, KEY(b), UNIQUE KEY(c))isn't OK, and should be written as:create table t1(a int primary key, b int, c int, UNIQUE (c)); create index on t1(b);

18. INSERT INTO t1 VALUES ... ON CONFLICT UPDATE conflict value reference syntax NOTsame as mysql In INSERT INTO t1 VALUES ... ON CONFLICT UPDATE col=valexprhere in the value expr, refer to the current field in the row with t1.col, and refer to the proposed new field value with EXCLUDED.col.

    e.g.

     INSERT INTO t1 VALUES(1,2) ON CONFLICT UPDATE col1=t1.col1 +3, col2=t1.col2+5;
     INSERT INTO t1 VALUES(1,2) ON CONFLICT UPDATE col1=EXCLUDED.col1 + 3, col2=EXCLUDED.col2+5;
    

    this is different from mysql. For mysql, when referring to current field value in row, simply colis OK, no need for t1.col, and to refer to the new(proposed) value, latest mysql approach is to define a row alias and use it, rather than using a fixed row alias EXCLUDED. So equivalent mysql syntax is:

      INSERT INTO t1 VALUES(1,2) ON CONFLICT UPDATE col1=col1 +3, col2=col2+5;
     INSERT INTO t1 VALUES(1,2) AS newrow ON CONFLICT UPDATE col1=newrow.col1 + 3, col2=newrow.col2+5;
    

    and the VALUES() approach for this purpose is deprecated.

19. Klustron enforces STRICT_ALL_TABLES, so it doesn't allow varchar value longer than maxlen

20. All symbol names are stored and compared as lower-case in Klustron-server, whether they are quoted or not.

    • For original PostgreSQL these DDLs are valid (although insane):

    CREATE TABLE t1("A" int, a int);
    CREATE TABLE "T1"("Aa" int, "aA" int);
    

    but for Klustron, this will be rejected by Klustron-server, and it was rejected before by Klustron-storage anyway, so there is no backward compatibility issue for Klustron.

    Furthermore, Klustron-storage has been set lower_case_table_names=1 so we never allowed two tables with names different only in lettercase, but original PostgreSQL allows a table named "T1" at creation and a table t1 to co-exist.

    • returned column name in result metadata will be lower case even if it was upper case in CREATE TABLE stmt.

    This might slightly affect app code which were using mysql, and has no impacts to those using postgres before. For example, some app code which were using MySQL may refer to result rows' fields using column name like this:

    print(row["A"], row["Aa"])

    this code now will fail with Klustron --- the code must be modified as:

    print(row["a"], row["aa"])

21. The 'unsigned' type modifier is NOT effective CREATE TABLEstatements like this: CREATE TABLE t1(a int unsigned)parses and executes successfully, but users can actually insert rows with negative values into table t1. That is, the 'unsigned' type modifier isn't an effective constraint in Klustron, they are only parsed and then ignored.

22. Fetching results from execution of prepared statements using cursor type CURSOR_TYPE_READ_ONLYbehaves differently

    If CURSOR_TYPE_READ_ONLYis specified as an option for a prepared statement, a new type of server side cursor(Sliced_cursor) is created in server to execute the query incrementally and send result rows by N(specified in PREFETCH_ROWSoption) rows a time. No rows are cached at server side temp table, bringing better performance given large set of result rows. Client side can NOT fetch across transaction boundary otherwise the COM_STMT_FETCHwould return inconsistent results because the FETCH'es in following transactions would use a different read view than the originally uncertain one. -storage will close all server side READ_ONLY cursors at end of a transaction.

    This restriction is newly introduced, and may cause errors if violated. community mysql given CURSOR_TYPE_READ_ONLYworks the same way as CURSOR_TYPE_SCROLLABLEor CURSOR_TYPE_FOR_UPDATEdescribed below.

    If working with Klustron-storage directly without Klustron-server, applications doing FETCH'es to the same prepared stmt across transaction boundary using a cursor of type CURSOR_TYPE_READ_VIEWmay work wrong and need to be fixed.

    Between two FETCH'es of the same prepared stmt, any other SQL stmts or mysql_stmt_prepare(), mysql_stmt_execute(), mysql_stmt_fetch() with other prepared statements can be executed. This is the same as community mysql.

23. In MySQL connections transform CREATE DATABASE to CREATE SCHEMA by default

    Klustron-server doesn't allow switching to another database like mysql, but mysql users assume and may rely on this capability.

    So in MySQL connections, by default transform all CREATE DATABASE stmts to CREATE SCHEMA stmts to allow that. If transform_create_db_to_schema is off, the CREATE DATABASE will be executed as is and the current connection can't access the created database.

    When migrating and loading data from a mysql db instance, choose an existing database of Klustron to hold all the mysql instance's 'databases' and create them as schemas now.

    MySQL clients connect to a real Klustron database(not a schema), at initially its at 'public' schema, and then the client can use 'USE' stmt to switch between its original mysql databases(now schemas).

    If a connection breaks and the client reconnects, it will reconnect with its cached 'current database', so at server side executing a USE or COM_INIT_DB, if we can't find the specified name as a schema name, we look for it as a db name. And if it's currently connected db, we switch to its 'public' schema(which may be different from before the disconnection, a difference from mysql); if it's another valid db or a non-existent db, we reject the request .

    Similarly in MySQL connections, by default a DROP DATABASE stmt is transformed to DROP SCHEMA ... CASCADE, unless transform_create_db_to_schema is false.

    Users must be clear they are dropping a database or schema really, to drop a real database in a mysql connection, set transform_create_db_to_schema to false before issuing the DROP DATABASE stmt.

4. Summary of MySQL private syntax supported by Klustron-server

In general, Klustron supports almost all MySQL private DML syntax, see Klustron support for MySQL private DML syntax for details

5. MySQL connection protocol of Klustron and list of supported MySQL syntax and functions

Introduction to Klustron & MySQL Connection Protocol

END