Skip to main content

SQL Syntax Compatibility of Klustron (KunlunBase)

KlustronAbout 7 min

SQL Syntax Compatibility of Klustron (KunlunBase)

Overview of Klustron's SQL Compatibility

For application developers, working with Klustron is seamless as it shares full compatibility with PostgreSQL and MySQL. The majority of applications originally designed for PostgreSQL or MySQL can be connected to Klustron without any modifications or recompilation. This convenience greatly facilitates the migration of PostgreSQL and MySQL users to Klustron. This compatibility is achieved because Klustron supports all DML SQL syntax of PostgreSQL and MySQL, and it also supports many MySQL-specific features.

For DBAs, Klustron supports PostgreSQL's DDL syntax (excluding features like foreign keys and rules), and starting from Klustron version 1.2, it also supports common MySQL DDL syntax. Prior to Klustron version 1.2, the tool DDL2Kunlun was available to convert MySQL DDL SQL statements into PostgreSQL DDL SQL statements. Klustron's computing nodes support MySQL's SHOW commands and private data types.

Klustron adheres to the SQL standards, primarily due to PostgreSQL's excellent compatibility with SQL standards. It supports triggers and stored procedures with PL/SQL syntax, which facilitates the migration of applications from databases like PostgreSQL and Oracle to Klustron.

Knowledge of PostgreSQL's PL/SQL is only required when you intend to write stored procedures. Additionally, due to its adherence to standard SQL statements, various SQL ecosystem tools can automatically work with the Klustron system. Specifically, ORM tools like Hibernate can collaborate with Klustron, and the code and modules generated by these ORM tools can access Klustron without modification.

For developers accustomed to PostgreSQL, the private SQL extensions of PostgreSQL are well-supported in Klustron, except for certain disabled parts. See the following sections for more details:

Klustron supports JSON data management. Users can utilize PostgreSQL's JSON functions and operators for reading and writing JSON data, many of which are SQL standard functions and operators that MySQL also supports. However, MySQL's private JSON functions and operators are not supported. As of version 1.2, users cannot create indexes on JSON fields, though this feature will be supported in the future.

There are a few operators that have different meanings in MySQL and PostgreSQL, such as $$, ||, ^, #, etc. In Klustron's MySQL protocol connections, these operators follow their meanings in MySQL, while in PostgreSQL protocol connections, they follow their meanings in PostgreSQL.

Support for Other PostgreSQL and MySQL Features

  • Support for PostgreSQL and MySQL quoting rules (/'/")
  • Support for PostgreSQL and MySQL data types and operators
  • Prepared statements (both PostgreSQL and MySQL syntax)
  • PostgreSQL and MySQL character sets and collation
  • PostgreSQL and MySQL system functions and syntax for reading/writing system variables

MySQL Compatibility

For detailed information about Klustron's support for MySQL private DML syntax, refer to Klustron's Support for MySQL Private DML Syntax.

For details about Klustron's MySQL connection protocol, refer to Introduction to Klustron's MySQL Connection Protocol.

For a comprehensive list of MySQL syntax and features that Klustron does not support, refer to Unsupported MySQL Syntax and Features in Klustron.

  1. Identifier Length Limitation
  • PostgreSQL requires identifier names to be less than 64 bytes, which means that if an identifier is in Chinese, the maximum character count M varies within a range, with the smallest M being less than 20 characters. Here, identifiers include names of databases, tables, columns, indexes, sequences, procedures, users, etc.
  1. Klustron Supports Common MySQL Private DML Syntax and Variable Read/Write Syntax.

  • Whether using a PostgreSQL protocol connection or a MySQL protocol connection, you can execute supported PostgreSQL and MySQL syntax in Klustron. This includes MySQL private DML syntax, standard SQL syntax, and any other PostgreSQL-specific syntax supported by Klustron.

  • Starting from Klustron version 1.2, Klustron also supports some common MySQL DDL syntax.

  1. Klustron supports the "autoincrement" keyword to define columns as auto-increment columns, and it also supports the "last_insert_id()" function, which is used in the same way as in MySQL.
  • More powerful than MySQL, in Klustron, you can define multiple columns as auto-increment, not limited to just one column.
  • PostgreSQL's CREATE TABLE syntax can be used to utilize sequences more flexibly, such as using the same sequence to generate ID column values for multiple tables.

  1. Klustron supports all MySQL private data types, including:

    • TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, VARBINARY, BINARY

    • These are uniformly stored and processed as the "bytea" type in Klustron

    • TINYTEXT, MEDIUMTEXT, LONGTEXT

    • These are uniformly stored and processed as the "text" type in Klustron

    • DOUBLE [PRECISION]

    • byte, tinyint, mediumint, middleint

    • All integer types with the UNSIGNED modifier (though the unsigned modifier has no constraint and is directly ignored).

    • DATETIME: Treated as a timestamp without time zone information.

    • TIMESTAMP: Treated as a timestamp with time zone information.

    • TIMESTAMP type has different meanings in MySQL and PostgreSQL: in MySQL, it represents a timestamp with time zone information, while in PostgreSQL, it represents a timestamp without time zone information.

      • To remain consistent with both, in Klustron, it follows MySQL's definition in MySQL connections and PostgreSQL's definition in PostgreSQL connections.

  2. Klustron does not support MySQL stored procedures or any MySQL-specific DDL syntax, "load data infile" command, replication-related commands, or any other MySQL-specific functional syntax.

  3. MySQL-specific system databases "performance_schema," "sys," and "mysql" do not exist in Klustron's computing nodes and cannot be accessed in MySQL connections in the computing nodes.

  • The "information_schema" system library is an SQL standard, but the system tables and views within MySQL's "information_schema" library mostly do not exist in Klustron, and even if there are tables with the same name, their definitions might not be equivalent.
  • We can add such system tables and views as needed, and some have already been added.

  1. Supports MySQL's common "show" series commands.

  2. Supports MySQL transaction processing functionality.

  • Autocommit statements.
  • Implicit transaction start and commit.
  • Optionally (when "enable_stmt_subxact=true"), supports MySQL's error handling mechanism, where statement errors only roll back the statement without rolling back the transaction. The application logic determines whether to commit or roll back the transaction, and so on.

Insert Statement: Supports Most MySQL Private Extension Syntax

Supports the "on conflict do update" clause while preserving MySQL's syntax (which differs slightly from PostgreSQL).

Supports "insert ignore" to ignore unique key/primary key conflicts but not other errors.

For example: insert ignore into t1 values(1,2);

Support for REPLACE Statement to Override Duplicate Rows

For example:

replace into t1 set a=1,b=2;

replace t1 values(1,2);

Support for SET Syntax in INSERT Statement:

For example: insert into t1 set a=1,b=2;

Support for INSERT INTO... SELECT FROM

Prohibit Other Modifying Keywords in INSERT and REPLACE Statements, i.e., DELAYED, LOW_PRIORITY, HIGH_PRIORITY

Cannot Specify Partitions, i.e., Does Not Support insert into t2 partition(p0,p1) values(1,2),(3,4);

UPDATE & DELETE Statements

  1. Support for Updating/Deleting Rows of Multiple Tables in One Statement;

  2. Support for 'ORDER BY' Clause and 'LIMIT' Clause of UPDATE/DELETE Statement;

  3. Do Not Support MySQL Private Modifiers, Including IGNORE, DELAYED, LOW_PRIORITY, HIGH_PRIORITY, QUICK;

  4. Do Not Allow Specifying Partition in UPDATE and DELETE Syntax;

  5. Use PostgreSQL Syntax for Common Table Expressions (CTE) Used in UPDATE and DELETE Statements;

  6. Support for Updating Partition Columns.

SELECT Statement: Prohibiting MySQL Private Extensions

  1. Do Not Support MySQL Private Modifiers, Including:

    [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

  2. Do Not Support 'WITH ROLLUP' Clause, as PostgreSQL's OLAP Functionality Is More Powerful and Doesn't Require 'WITH ROLLUP';

  3. Do Not Allow Specifying Partitions in Queries;

  4. Do Not Support Data Export Syntax:

    [INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]

export_options
| INTO DUMP-FILE 'file_name' | INTO var_name [, var_name]]

  5. Support SELECT Locking Clauses:

    Support Common Syntax: [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] | LOCK IN SHARE MODE] But Do Not Support MySQL Private Options: [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]

  6. CTE Should Be in PostgreSQL Syntax.

  7. Window Functions: Window Definition and Usage Should Be in PostgreSQL Syntax.

Compatibility of MySQL System Functions

With the exception of GIS and JSON functions, most MySQL system functions are standard SQL functions. All standard SQL functions are supported by Klustron, and the remaining few MySQL-specific functions (excluding GIS and JSON functions) are supported based on user needs. Currently, Klustron supports all mainstream client libraries and a wide range of application software that rely on these system functions. Additionally, users can find alternative functions from PostgreSQL.

  1. Klustron supports the following MySQL functions:

    postgres=# select distinct t1.*from MySQL_funcs t1, pg_proc t2 where lower(t1.fname)=t2.proname;

    fname

    ABS ACOS ASIN ATAN ATAN2 BIT_LENGTH CEIL CEILING CHARACTER_LENGTH CHAR_LENGTH CONCAT CONCAT_WS COS COT DEGREES EXP FLOOR JSON_OBJECT LENGTH LN LOG LOWER LPAD LTRIM MD5 OCTET_LENGTH PI POW POWER RADIANS REGEXP_REPLACE REVERSE ROUND RPAD RTRIM SIGN SIN SQRT TAN UPPER VERSION

All MySQL window functions are supported in PostgreSQL and KunlunBase, but they are not listed in the above list. Therefore, they are listed separately here.

ROW_NUMBER
RANK
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
LEAD
LAG
FIRST_VALUE
LAST_VALUE
NTH_VALUE
  1. List of MySQL system functions not supported by Klustron

Since we continuously merge code from the upstream Percona MySQL main version to continually upgrade Kunlun Storage, and each version of Klustron will also continue to add support for more MySQL system functions, the following list will change with each version of Klustron. For the version of Klustron you are using, you can connect to any computing node and execute the following statement to obtain a list of unsupported MySQL system functions.

Here is the result obtained from a specific version of Klustron. You can see that most of the unsupported functions are GIS and JSON functions. For the remaining functions, equivalent functions with different names can be found in Klustron.

postgres=# select * from  MySQL_funcs t1 where lower(t1.fname) not in (select distinct proname from pg_proc);

fname
-----------------------------------
ADDTIME
AES_DECRYPT
AES_ENCRYPT
ANY_VALUE
BENCHMARK
BIN
BIN_TO_UUID
BIT_COUNT
COERCIBILITY
COMPRESS
CONNECTION_ID
CONV
CONVERT_TZ
CRC32
CURRENT_ROLE
DATEDIFF
DATE_FORMAT
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
ELT
EXPORT_SET
EXTRACTVALUE
FIELD
FIND_IN_SET
FOUND_ROWS
FROM_BASE64
FROM_DAYS
FROM_UNIXTIME
GET_LOCK
GREATEST
GTID_SUBSET
GTID_SUBTRACT
HEX
IFNULL
INET6_ATON
INET6_NTOA
INET_ATON
INET_NTOA
INSTR
IS_FREE_LOCK
IS_IPV4
IS_IPV4_COMPAT
IS_IPV4_MAPPED
IS_IPV6
ISNULL
IS_USED_LOCK
IS_UUID
JSON_ARRAY
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_DEPTH
JSON_EXTRACT
JSON_INSERT
JSON_KEYS
JSON_LENGTH
JSON_MERGE
JSON_MERGE_PATCH
JSON_MERGE_PRESERVE
JSON_PRETTY
JSON_QUOTE
JSON_REMOVE
JSON_REPLACE
JSON_SEARCH
JSON_SET
JSON_STORAGE_FREE
JSON_STORAGE_SIZE
JSON_TYPE
JSON_UNQUOTE
JSON_VALID
LAST_DAY
LAST_INSERT_ID
LCASE
LEAST
LOAD_FILE
LOCATE
LOG10
LOG2
MAKEDATE
MAKE_SET
MAKETIME
MASTER_POS_WAIT
MBRCONTAINS
MBRCOVEREDBY
MBRCOVERS
MBRDISJOINT
MBREQUALS
MBRINTERSECTS
MBROVERLAPS
MBRTOUCHES
MBRWITHIN
MONTHNAME
NAME_CONST
NULLIF
OCT
ORD
PERIOD_ADD
PERIOD_DIFF
QUOTE
RAND
RANDOM_BYTES
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
RELEASE_ALL_LOCKS
RELEASE_LOCK
ROLES_GRAPHML
ROTATE_SYSTEM_KEY
SEC_TO_TIME
SHA
SHA1
SHA2
SLEEP
SOUNDEX
SPACE
ST_AREA
ST_ASBINARY
ST_ASGEOJSON
ST_ASTEXT
ST_ASWKB
ST_ASWKT
STATEMENT_DIGEST
STATEMENT_DIGEST_TEXT
ST_BUFFER
ST_BUFFER_STRATEGY
ST_CENTROID
ST_CONTAINS
ST_CONVEXHULL
ST_CROSSES
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_DISTANCE_SPHERE
ST_ENDPOINT
ST_ENVELOPE
ST_EQUALS
ST_EXTERIORRING
ST_GEOHASH
ST_GEOMCOLLFROMTEXT
ST_GEOMCOLLFROMTXT
ST_GEOMCOLLFROMWKB
ST_GEOMETRYCOLLECTIONFROMTEXT
ST_GEOMETRYCOLLECTIONFROMWKB
ST_GEOMETRYFROMTEXT
ST_GEOMETRYFROMWKB
ST_GEOMETRYN
ST_GEOMETRYTYPE
ST_GEOMFROMGEOJSON
ST_GEOMFROMTEXT
ST_GEOMFROMWKB
ST_INTERIORRINGN
ST_INTERSECTION
ST_INTERSECTS
ST_ISCLOSED
ST_ISEMPTY
ST_ISSIMPLE
ST_ISVALID
ST_LATFROMGEOHASH
ST_LATITUDE
ST_LENGTH
ST_LINEFROMTEXT
ST_LINEFROMWKB
ST_LINESTRINGFROMTEXT
ST_LINESTRINGFROMWKB
ST_LONGFROMGEOHASH
ST_LONGITUDE
ST_MAKEENVELOPE
ST_MLINEFROMTEXT
ST_MLINEFROMWKB
ST_MPOINTFROMTEXT
ST_MPOINTFROMWKB
ST_MPOLYFROMTEXT
ST_MPOLYFROMWKB
ST_MULTILINESTRINGFROMTEXT
ST_MULTILINESTRINGFROMWKB
ST_MULTIPOINTFROMTEXT
ST_MULTIPOINTFROMWKB
ST_MULTIPOLYGONFROMTEXT
ST_MULTIPOLYGONFROMWKB
ST_NUMGEOMETRIES
ST_NUMINTERIORRING
ST_NUMINTERIORRINGS
ST_NUMPOINTS
ST_OVERLAPS
ST_POINTFROMGEOHASH
ST_POINTFROMTEXT
ST_POINTFROMWKB
ST_POINTN
ST_POLYFROMTEXT
ST_POLYFROMWKB
ST_POLYGONFROMTEXT
ST_POLYGONFROMWKB
STRCMP
STR_TO_DATE
ST_SIMPLIFY
ST_SRID
ST_STARTPOINT
ST_SWAPXY
ST_SYMDIFFERENCE
ST_TOUCHES
ST_TRANSFORM
ST_UNION
ST_VALIDATE
ST_WITHIN
ST_X
ST_Y
SUBSTRING_INDEX
SUBTIME
TIMEDIFF
TIME_FORMAT
TIME_TO_SEC
TO_BASE64
TO_DAYS
TO_SECONDS
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UNIX_TIMESTAMP
UPDATEXML
UUID
UUID_SHORT
UUID_TO_BIN
VALIDATE_PASSWORD_STRENGTH
WAIT_FOR_EXECUTED_GTID_SET
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
WEEKDAY
WEEKOFYEAR
YEARWEEK

Compatibility of MySQL Operators

All standard SQL operators in MySQL are fully supported in Klustron. The specific MySQL private extension operators that are supported include:

  • Logical operators: &&, ||, !, XOR
  • Comparison operator: <=>
  • Bitwise operator: ^
  • Arithmetic operators: DIV and MOD
  • Assignment operator: := (This is rarely used, and = is commonly used instead)

Compatibility with Other RDBMS

If you were using other relational databases such as SQL Server or Oracle Server, you can connect to the Klustron system using JDBC and ODBC protocols and use standard SQL syntax to operate and access Klustron. All SQL standard features of SQL Server and Oracle Server can work normally. For SQL Server and Oracle Server's private extension features, you would need to modify your application's SQL statements to use standard SQL features or Klustron's supported PostgreSQL features.

Current mainstream RDBMS systems have good compatibility with standard SQL. These standard SQL syntaxes are used by most applications to access databases. Therefore, if your application or website originally used SQL Server or Oracle Server, you would only need minimal modifications or possibly no modifications to your SQL statements to use and access Klustron.

END

Copyright © Klustron
Copyright © 2025 Klustron