From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E596F214D6 for ; Thu, 4 Jul 2019 07:42:10 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yBUIstQOA_OA for ; Thu, 4 Jul 2019 07:42:10 -0400 (EDT) Received: from smtp62.i.mail.ru (smtp62.i.mail.ru [217.69.128.42]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id A7891214A5 for ; Thu, 4 Jul 2019 07:42:10 -0400 (EDT) From: imeevma@tarantool.org Subject: [tarantool-patches] [PATCH v2 1/3] sql: remove unnecessary AUTOINCREMENT ID generation Date: Thu, 4 Jul 2019 14:42:08 +0300 Message-Id: <96975db4f246bc09a3801dd247f5ab65d7709a6f.1562239051.git.imeevma@gmail.com> In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: korablev@tarantool.org Cc: tarantool-patches@freelists.org 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