[tarantool-patches] Re: [PATCH v3 1/2] sql: move autoincrement in vdbe

[n.pettik]

> This patch expands changes made in issue #2981. Now NULL
> in primary key always replaced by generated value inside
> of vdbe.

Nitpicking: during VDBE execution.

> It allows us to get last_insert_id with easy.

Nitpicking: with ease.

Actually I can’t understand purpose of this patch.
These manipulations with NULL are quite tricky.
Could you please explain in details what the bug was
about and how you fix it. Thx.

> --- a/test/sql/gh-2981-check-autoinc.test.lua
> +++ b/test/sql/gh-2981-check-autoinc.test.lua
> @@ -7,6 +7,7 @@ box.cfg{}
> box.sql.execute("CREATE TABLE t1 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));");
> box.sql.execute("CREATE TABLE t2 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19 AND s1 <> 25));");
> box.sql.execute("CREATE TABLE t3 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 < 10));");
> +box.sql.execute("CREATE TABLE t4 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));");
> 
> box.sql.execute("insert into t1 values (18, null);")
> box.sql.execute("insert into t1(s2) values (null);")
> @@ -19,7 +20,10 @@ box.sql.execute("insert into t2(s2) values (null);")
> box.sql.execute("insert into t3 values (9, null)")
> box.sql.execute("insert into t3(s2) values (null)")
> 
> +box.sql.execute("insert into t4 values (18, null);")
> +box.sql.execute("insert into t4 values (null, null);”)

I have doubts concerning this test. Firstly, AFAIK sequence
doesn’t guarantee that new values == prev_value + 1.
Thus, basically this test can fail. Moreover, why next
INSERT(NULL, NULL) doesn’t fail? For me it seems counterintuitive.
I mean I understand that sequence generator increased but insertion
failed and so forth. But does user expect such behaviour?
For instance, in MySQL this doesn’t result in error at all:

CREATE TABLE t4 (s1 int PRIMARY KEY AUTO_INCREMENT, s2 INTEGER, CHECK (s1 <> 19)) \\

insert into t4 values (18, null) \\
insert into t4 values (null, null) \\
insert into t4 values (null, null) \\
select * from t4\\

  	mysql version
1	5.7.12-log

  	s1	s2
1	18	NULL
2	19	NULL
3	20	NULL