Klustron support for MySQL private DML syntax

foreword

In order to make it easier for MySQL applications to migrate to Klustron, we have done a lot of work to be compatible with MySQL.

This chapter mainly introduces the commonly used private DML syntax of MySQL currently supported by Klustron, and the differences between these syntax and native MySQL.

1. Compatible with MySQL's insert ignore syntax

Feature: Ignore new tuples that violate unique constraints.

Example:

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
# 违背唯一约束，不进行插入
postgres -> insert ignore into t1(a,b) values (4,4);
INSERT 0 1
postgres -> insert ignore into t1(a,b) values (4,4);
INSERT 0 0

Differences from native MySQL:

• Only unique constraints are ignored, and an error is reported if other constraints (such as partition constraints, non-null constraints) are violated.

For example:

postgres -> insert ignore into t1(a,b) values (4,NULL);
ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (4, null)

2. Compatible with MySQL's INSERT...ONDUPLICATE KEY UPDATE... syntax

Function: Insert data; if a unique constraint is violated, it will be transformed into an update operation to update one of the conflicting tuples.

Example:

postgres -> create table t1 (a int primary key, b int not null unique);
CREATE TABLE
postgres -> insert into t1 values(3,3), (4,4);
INSERT 0 2


# 插入的数据和已有的两个元组都冲突，但只更新了其中一个元组(3,3)
postgres -> insert into t1 values(3,4) on duplicate key update b=2;
INSERT 0 2
postgres -> select * from t1;
 a | b
---+---
 3 | 2
 4 | 4

Differences from native MySQL:

• The use of the VALUES() function to refer to new values in the ON DUPLICATE KEY UPDATE clause is not currently supported, and the excluded virtual table can be used instead.

For example:

postgres -> INSERT INTO t1 VALUES(3,0) ON DUPLICATE KEY UPDATE b = excluded.b;
INSERT 0 2
postgres -> select * from t1;
 a | b
---+---
 3 | 0
 4 | 4
(2rows)
postgres -> INSERT INTO t1 VALUES(3,0) ON DUPLICATE KEY UPDATE b=VALUES(b);
ERROR:  syntax error at or near "VALUES"

• When writing multiple new tuples to the temporary table in batches, if there is a uniqueness conflict between the new tuples, an error will be reported (the root cause is that the temporary table exists on the computing node, and the innodb engine is not used).

For example:

postgres -> create temp table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> INSERT INTO t1 VALUES(5,5), (5,6) ON DUPLICATE KEY UPDATE b = excluded.b;
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a secondtime
HINT:  Ensure that norows proposed for insertion within the same command have duplicate constrained values.
postgres ->

• The difference in the number of affected rows returned by the temporary table. Even if the values before and after the update are the same, the number of affected rows returned by the temporary table is still greater than 0.

For example:

postgres -> create temp table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> INSERT INTO t1 VALUES(5,5) ON DUPLICATE KEY UPDATE b=excluded.b;
INSERT 0 1
postgres -> INSERT INTO t1 VALUES(5,5) ON DUPLICATE KEY UPDATE b=excluded.b;
INSERT 0 1

3. Compatible with the replace into syntax of mysql

Function: Insert tuple; if there is a conflicting old tuple, delete all conflicting old tuples.

Example:

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> insert into t1 values(3,3),(4,4);
INSERT 0 2

postgres -> replace into t1 values(3,4);
INSERT 0 3
postgres -> select * from t1;
 a | b
---+---
 3 | 4
(1row)

Differences from native MySQL:

• When writing multiple new tuples to the temporary table in batches, if there is a uniqueness conflict between the new tuples, an error will be reported (the root cause is that the temporary table exists on the computing node, and the innodb engine is not used).

For example:

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> replace into t1 values(1,1),(1,2);
INSERT 0 3

postgres -> create temp table t2(a int primary key,b int not null unique);
CREATE TABLE
postgres -> replace into t2 values(1,1),(1,2);
ERROR:  REPLACEINTO command cannot affect row a secondtime
HINT:  Ensure that norows proposed for insertion within the same command have duplicate constrained values.

4. Compatible with MySQL's update/delete...order by...limit.. syntax

Function: Specify the order and number of updated/deleted tuples.

Example:

postgres -> create table t1 (a int primary key, b int);
CREATE TABLE
postgres -> insert into t1 select generate_series(1,100),generate_series(1,100);
INSERT 0 100

# 对非分区表的有序更新
postgres -> update t1 set b=b+1 order by a desc limit 4 returning*;
  a  |  b
-----+-----
 100 | 101
  99 | 100
  98 |  99
  97 |  98
(4rows)

UPDATE 4

postgres -> drop table t1;
DROP TABLE
postgres -> CREATE TABLE t1 (A INT PRIMARY KEY,B INT) PARTITION BY RANGE(a);
CREATE TABLE
postgres -> CREATE TABLE t1p1 PARTITION OF t1 FOR VALUES FROM (0) TO (100);
CREATE TABLE
postgres -> CREATE TABLE t1p2 PARTITION OF t1 FOR VALUES FROM (100) TO (200);
CREATE TABLE
postgres -> insert into t1 select generate_series(0,199);
INSERT 0 200

# 指定分区表删除的总量
postgres -> delete from t1  limit 4 returning *;
 a | b
---+---
 0 |
 1 |
 2 |
 3 |
(4rows)

DELETE 4

Differences from native MySQL:

• The update/delete order of the specified partition table is not currently supported (note: the partition table of the temporary table is already supported). Of course, there are very few scenarios in which the update/delete sequence needs to be strictly specified in actual use, and this limitation will not cause trouble to Klustron users.

For example:

postgres -> CREATE TABLE t1 (A INT PRIMARY KEY, B INT) PARTITION BY RANGE(a);
CREATE TABLE
postgres -> CREATE TABLE t1p1 PARTITION OF t1 FOR VALUESFROM (0) TO (100);
CREATE TABLE
postgres -> CREATE TABLE t1p2 PARTITION OF t1 FORVALUESFROM (100) TO (200);
CREATE TABLE

# 不能指定分区表删除顺序
postgres -> delete from t1 order by a limit 4 returning *;
ERROR:  Kunlun-db: Cannot push down plan
postgres ->

5. INSERT/REPLACE SET syntax compatible with MYSQL

Function: Specify the specific value of each column in an update-like syntax

Example:

Sql
postgres=# create table t1(a int primary key, b int);
CREATE TABLE
postgres=# insert into t1 set a=1;
INSERT 0 1
postgres=# replace into t1 set a=1, b=3;

INSERT 0 2

6. Compatible with MySQL's INSERT/REPLACE other unique syntax

Function: The INTO keyword is optional; INSERT can also use the VALUE keyword in addition to the VALUES keyword.

Example:

Sql
postgres=# create table t1(a int primary key, b int);
CREATE TABLEpostgres=# insert t1 values(1,1);
INSERT 0 1
postgres=# insert t1 value(2,2),(3,3);
INSERT 0 2
postgres=# replace t1 values(1,2);

INSERT 0 2
postgres=# replace t1 value(2,3),(3,4);
INSERT 0 4

7. UPDATE IGNORE syntax compatible with MYSQL

Function: Ignore constraint conflict errors during update

Example:

Sql
postgres=# create table t1(a int primary key, b int);
CREATE TABLE
postgres=# insert into t1 values(1,1),(2,2);
INSERT 0 2
postgres=# update t1 set a=+1;
ERROR: Kunlun-db: MySQL storage node (1, 1) returned error: 1062, Duplicate entry '2' for key 't1.PRIMARY'.
postgres=# update ignore t1 set a=a+1;
UPDATE 1
postgres=# select * from t1;
a | b
---+---
1 | 1
3 | 2
(2 rows)

END