[tarantool-patches] [PATCH v2 1/3] sql: remove unnecessary AUTOINCREMENT ID generation
imeevma at tarantool.org
imeevma at tarantool.org
Thu Jul 4 14:42:08 MSK 2019
Currently, if we perform something like
CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO t(a) VALUES (NULL);
we generate a new identifier in a special way. This was necessary
for the cases described in issue #2981. Here it is:
CREATE TABLE t1 (
s1 INTEGER PRIMARY KEY AUTOINCREMENT,
s2 INTEGER,
CHECK (s1 <> 19)
);
INSERT INTO t1 VALUES (18, NULL);
INSERT INTO t1 (s2) VALUES (NULL);
In this case, the CHECK did not work properly. This is not
necessary now, because CHECK was moved to BOX due to issue #3691.
This patch removes the mentioned special way.
Part of #4188
---
src/box/sql/insert.c | 5 +----
test/sql/gh-2981-check-autoinc.result | 32 ++++++++++++++++++++++++++++++++
test/sql/gh-2981-check-autoinc.test.lua | 11 ++++++++++-
3 files changed, 43 insertions(+), 5 deletions(-)
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index b353148..d2b4e17 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -628,10 +628,7 @@ sqlInsert(Parse * pParse, /* Parser context */
if (j < 0 || nColumn == 0
|| (pColumn && j >= pColumn->nId)) {
if (i == (int) autoinc_fieldno) {
- sqlVdbeAddOp2(v,
- OP_NextAutoincValue,
- space->def->id,
- iRegStore);
+ sqlVdbeAddOp2(v, OP_Null, 0, iRegStore);
continue;
}
struct Expr *dflt = NULL;
diff --git a/test/sql/gh-2981-check-autoinc.result b/test/sql/gh-2981-check-autoinc.result
index f03858a..8583115 100644
--- a/test/sql/gh-2981-check-autoinc.result
+++ b/test/sql/gh-2981-check-autoinc.result
@@ -23,6 +23,14 @@ box.execute("CREATE TABLE t3 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER,
---
- row_count: 1
...
+box.execute("CREATE TABLE t4 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 < 10));");
+---
+- row_count: 1
+...
+box.execute("CREATE TABLE t5 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));");
+---
+- row_count: 1
+...
box.execute("insert into t1 values (18, null);")
---
- row_count: 1
@@ -55,6 +63,22 @@ box.execute("insert into t3(s2) values (null)")
---
- error: 'Check constraint failed ''CK_CONSTRAINT_1_T3'': s1 < 10'
...
+box.execute("insert into t4 values (9, null)")
+---
+- row_count: 1
+...
+box.execute("insert into t4 values (null, null)")
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_1_T4'': s1 < 10'
+...
+box.execute("INSERT INTO t5 VALUES (18, NULL);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t5 SELECT NULL, NULL FROM t5;")
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_1_T5'': s1 <> 19'
+...
box.execute("DROP TABLE t1")
---
- row_count: 1
@@ -67,3 +91,11 @@ box.execute("DROP TABLE t3")
---
- row_count: 1
...
+box.execute("DROP TABLE t4")
+---
+- row_count: 1
+...
+box.execute("DROP TABLE t5")
+---
+- row_count: 1
+...
diff --git a/test/sql/gh-2981-check-autoinc.test.lua b/test/sql/gh-2981-check-autoinc.test.lua
index 0eb8f73..ac5624e 100644
--- a/test/sql/gh-2981-check-autoinc.test.lua
+++ b/test/sql/gh-2981-check-autoinc.test.lua
@@ -7,6 +7,8 @@ box.cfg{}
box.execute("CREATE TABLE t1 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));");
box.execute("CREATE TABLE t2 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19 AND s1 <> 25));");
box.execute("CREATE TABLE t3 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 < 10));");
+box.execute("CREATE TABLE t4 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 < 10));");
+box.execute("CREATE TABLE t5 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));");
box.execute("insert into t1 values (18, null);")
box.execute("insert into t1(s2) values (null);")
@@ -19,7 +21,14 @@ box.execute("insert into t2(s2) values (null);")
box.execute("insert into t3 values (9, null)")
box.execute("insert into t3(s2) values (null)")
+box.execute("insert into t4 values (9, null)")
+box.execute("insert into t4 values (null, null)")
+
+box.execute("INSERT INTO t5 VALUES (18, NULL);")
+box.execute("INSERT INTO t5 SELECT NULL, NULL FROM t5;")
+
box.execute("DROP TABLE t1")
box.execute("DROP TABLE t2")
box.execute("DROP TABLE t3")
-
+box.execute("DROP TABLE t4")
+box.execute("DROP TABLE t5")
--
2.7.4
More information about the Tarantool-patches
mailing list