* [tarantool-patches] [PATCH] sql: disallow id duplicate in insert & update
[not found] <DF3A58E7-87E9-4809-95D4-200923CD88CD@tarantool.org>
@ 2018-04-09 4:29 ` AKhatskevich
2018-04-09 20:30 ` [tarantool-patches] " Vladislav Shpilevoy
0 siblings, 1 reply; 3+ messages in thread
From: AKhatskevich @ 2018-04-09 4:29 UTC (permalink / raw)
To: v.shpilevoy; +Cc: tarantool-patches
Before this patch one was able to exec query like this:
`insert into t(col1, col1) values(1, 1)`
`update t set col1 = 1, col1 = 2;`
which is nonsense.
New policy is to throw an error in case of duplicates in columns
description.
Closes #2358
---
src/box/sql/insert.c | 15 +++++++++++++++
src/box/sql/sqliteLimit.h | 9 +++++++++
src/box/sql/update.c | 7 +++++++
test/sql-tap/insert1.test.lua | 30 +++++++++++++++++++++++++++++-
test/sql-tap/update.test.lua | 23 ++++++++++++++++++++++-
5 files changed, 82 insertions(+), 2 deletions(-)
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 42254ddf4..33175e54c 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -35,6 +35,7 @@
*/
#include "sqliteInt.h"
#include "box/session.h"
+#include "bit/bit.h"
/*
* Generate code that will open pTab as cursor iCur.
@@ -450,6 +451,12 @@ sqlite3Insert(Parse * pParse, /* Parser context */
* is appears in the original table. (The index of the INTEGER
* PRIMARY KEY in the original table is pTab->iPKey.)
*/
+ /* Create bitmask to mark used columns of the table. */
+ void *used_columns = tt_static_buf();
+ /* The size of used_columns buffer is checked during compilation time
+ * using SQLITE_MAX_COLUMN constant.
+ */
+ memset(used_columns, 0, (pTab->nCol + 7) / 8);
bIdListInOrder = 1;
if (pColumn) {
for (i = 0; i < pColumn->nId; i++) {
@@ -477,6 +484,14 @@ sqlite3Insert(Parse * pParse, /* Parser context */
pParse->checkSchema = 1;
goto insert_cleanup;
}
+ if (bit_test(used_columns, j)) {
+ const char *err;
+ err = "table id list: duplicate column name %s";
+ sqlite3ErrorMsg(pParse,
+ err, pColumn->a[i].zName);
+ goto insert_cleanup;
+ }
+ bit_set(used_columns, j);
}
}
diff --git a/src/box/sql/sqliteLimit.h b/src/box/sql/sqliteLimit.h
index 138268259..f6b580665 100644
--- a/src/box/sql/sqliteLimit.h
+++ b/src/box/sql/sqliteLimit.h
@@ -35,6 +35,7 @@
*
* This file defines various limits of what SQLite can process.
*/
+#include "trivia/util.h"
enum {
/*
@@ -74,6 +75,14 @@ enum {
#ifndef SQLITE_MAX_COLUMN
#define SQLITE_MAX_COLUMN 2000
#endif
+/*
+ * tt_static_buf() is used to store bitmask for used columns in a table during
+ * SQL parsing stage. The following statement checks if static buffer is big
+ * enough to store the bitmask.
+ */
+#if SQLITE_MAX_COLUMN > TT_STATIC_BUF_LEN * 8
+#error "Bitmask for used table columns cannot fit into static buffer"
+#endif
/*
* The maximum length of a single SQL statement in bytes.
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index bf413252d..91717d7bd 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -233,6 +233,13 @@ sqlite3Update(Parse * pParse, /* The parser context */
if (pPk && table_column_is_in_pk(pTab, j)) {
chngPk = 1;
}
+ if (aXRef[j] != -1) {
+ sqlite3ErrorMsg(pParse,
+ "set id list: duplicate"
+ " column name %s",
+ pChanges->a[i].zName);
+ goto update_cleanup;
+ }
aXRef[j] = i;
break;
}
diff --git a/test/sql-tap/insert1.test.lua b/test/sql-tap/insert1.test.lua
index 847b25ad2..750732d37 100755
--- a/test/sql-tap/insert1.test.lua
+++ b/test/sql-tap/insert1.test.lua
@@ -1,7 +1,7 @@
#!/usr/bin/env tarantool
test = require("sqltester")
yaml = require('yaml')
-test:plan(24)
+test:plan(28)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -516,5 +516,33 @@ test:do_execsql_test("insert-4.7", [[
-- SELECT * FROM t12c;
-- } {one xyzzy two}
-- integrity_check insert-99.0
+
+
+test:do_execsql_test(
+ "insert-13.0",
+ [[
+ create table test(a primary key, b)
+ ]])
+
+test:do_catchsql_test(
+ "insert-13.1",
+ [[
+ insert into test(a, a, b) values(1, 1, 1)
+ ]],
+ {1, "table id list: duplicate column name A"})
+
+test:do_catchsql_test(
+ "insert-13.2",
+ [[
+ insert into test(a, b, b) values(1, 1, 1)
+ ]],
+ {1, "table id list: duplicate column name B"})
+
+test:do_execsql_test(
+ "insert-13.3",
+ [[
+ drop table test;
+ ]])
+
test:finish_test()
diff --git a/test/sql-tap/update.test.lua b/test/sql-tap/update.test.lua
index f6e756731..a4efe4015 100755
--- a/test/sql-tap/update.test.lua
+++ b/test/sql-tap/update.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(108)
+test:plan(111)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -1109,5 +1109,26 @@ test:do_catchsql_test("update-10.10", [[
-- UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL;
-- SELECT a,b,c,'|' FROM t15 ORDER BY a;
-- } {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |}
+
+test:do_execsql_test(
+ "insert-15.0",
+ [[
+ create table test(a primary key);
+ insert into test(a) values(1);
+ ]])
+
+test:do_catchsql_test(
+ "insert-15.1",
+ [[
+ update test set a = 2, a = 3;
+ ]],
+ {1, "set id list: duplicate column name A"})
+
+test:do_execsql_test(
+ "insert-15.2",
+ [[
+ drop table test;
+ ]])
+
test:finish_test()
--
2.14.1
^ permalink raw reply [flat|nested] 3+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: disallow id duplicate in insert & update
2018-04-09 4:29 ` [tarantool-patches] [PATCH] sql: disallow id duplicate in insert & update AKhatskevich
@ 2018-04-09 20:30 ` Vladislav Shpilevoy
2018-04-16 13:35 ` Kirill Yukhin
0 siblings, 1 reply; 3+ messages in thread
From: Vladislav Shpilevoy @ 2018-04-09 20:30 UTC (permalink / raw)
To: tarantool-patches, AKhatskevich
LGTM.
On 09/04/2018 07:29, AKhatskevich wrote:
> Before this patch one was able to exec query like this:
> `insert into t(col1, col1) values(1, 1)`
> `update t set col1 = 1, col1 = 2;`
> which is nonsense.
>
> New policy is to throw an error in case of duplicates in columns
> description.
>
> Closes #2358
> ---
> src/box/sql/insert.c | 15 +++++++++++++++
> src/box/sql/sqliteLimit.h | 9 +++++++++
> src/box/sql/update.c | 7 +++++++
> test/sql-tap/insert1.test.lua | 30 +++++++++++++++++++++++++++++-
> test/sql-tap/update.test.lua | 23 ++++++++++++++++++++++-
> 5 files changed, 82 insertions(+), 2 deletions(-)
>
> diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
> index 42254ddf4..33175e54c 100644
> --- a/src/box/sql/insert.c
> +++ b/src/box/sql/insert.c
> @@ -35,6 +35,7 @@
> */
> #include "sqliteInt.h"
> #include "box/session.h"
> +#include "bit/bit.h"
>
> /*
> * Generate code that will open pTab as cursor iCur.
> @@ -450,6 +451,12 @@ sqlite3Insert(Parse * pParse, /* Parser context */
> * is appears in the original table. (The index of the INTEGER
> * PRIMARY KEY in the original table is pTab->iPKey.)
> */
> + /* Create bitmask to mark used columns of the table. */
> + void *used_columns = tt_static_buf();
> + /* The size of used_columns buffer is checked during compilation time
> + * using SQLITE_MAX_COLUMN constant.
> + */
> + memset(used_columns, 0, (pTab->nCol + 7) / 8);
> bIdListInOrder = 1;
> if (pColumn) {
> for (i = 0; i < pColumn->nId; i++) {
> @@ -477,6 +484,14 @@ sqlite3Insert(Parse * pParse, /* Parser context */
> pParse->checkSchema = 1;
> goto insert_cleanup;
> }
> + if (bit_test(used_columns, j)) {
> + const char *err;
> + err = "table id list: duplicate column name %s";
> + sqlite3ErrorMsg(pParse,
> + err, pColumn->a[i].zName);
> + goto insert_cleanup;
> + }
> + bit_set(used_columns, j);
> }
> }
>
> diff --git a/src/box/sql/sqliteLimit.h b/src/box/sql/sqliteLimit.h
> index 138268259..f6b580665 100644
> --- a/src/box/sql/sqliteLimit.h
> +++ b/src/box/sql/sqliteLimit.h
> @@ -35,6 +35,7 @@
> *
> * This file defines various limits of what SQLite can process.
> */
> +#include "trivia/util.h"
>
> enum {
> /*
> @@ -74,6 +75,14 @@ enum {
> #ifndef SQLITE_MAX_COLUMN
> #define SQLITE_MAX_COLUMN 2000
> #endif
> +/*
> + * tt_static_buf() is used to store bitmask for used columns in a table during
> + * SQL parsing stage. The following statement checks if static buffer is big
> + * enough to store the bitmask.
> + */
> +#if SQLITE_MAX_COLUMN > TT_STATIC_BUF_LEN * 8
> +#error "Bitmask for used table columns cannot fit into static buffer"
> +#endif
>
> /*
> * The maximum length of a single SQL statement in bytes.
> diff --git a/src/box/sql/update.c b/src/box/sql/update.c
> index bf413252d..91717d7bd 100644
> --- a/src/box/sql/update.c
> +++ b/src/box/sql/update.c
> @@ -233,6 +233,13 @@ sqlite3Update(Parse * pParse, /* The parser context */
> if (pPk && table_column_is_in_pk(pTab, j)) {
> chngPk = 1;
> }
> + if (aXRef[j] != -1) {
> + sqlite3ErrorMsg(pParse,
> + "set id list: duplicate"
> + " column name %s",
> + pChanges->a[i].zName);
> + goto update_cleanup;
> + }
> aXRef[j] = i;
> break;
> }
> diff --git a/test/sql-tap/insert1.test.lua b/test/sql-tap/insert1.test.lua
> index 847b25ad2..750732d37 100755
> --- a/test/sql-tap/insert1.test.lua
> +++ b/test/sql-tap/insert1.test.lua
> @@ -1,7 +1,7 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> yaml = require('yaml')
> -test:plan(24)
> +test:plan(28)
>
> --!./tcltestrunner.lua
> -- 2001 September 15
> @@ -516,5 +516,33 @@ test:do_execsql_test("insert-4.7", [[
> -- SELECT * FROM t12c;
> -- } {one xyzzy two}
> -- integrity_check insert-99.0
> +
> +
> +test:do_execsql_test(
> + "insert-13.0",
> + [[
> + create table test(a primary key, b)
> + ]])
> +
> +test:do_catchsql_test(
> + "insert-13.1",
> + [[
> + insert into test(a, a, b) values(1, 1, 1)
> + ]],
> + {1, "table id list: duplicate column name A"})
> +
> +test:do_catchsql_test(
> + "insert-13.2",
> + [[
> + insert into test(a, b, b) values(1, 1, 1)
> + ]],
> + {1, "table id list: duplicate column name B"})
> +
> +test:do_execsql_test(
> + "insert-13.3",
> + [[
> + drop table test;
> + ]])
> +
> test:finish_test()
>
> diff --git a/test/sql-tap/update.test.lua b/test/sql-tap/update.test.lua
> index f6e756731..a4efe4015 100755
> --- a/test/sql-tap/update.test.lua
> +++ b/test/sql-tap/update.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(108)
> +test:plan(111)
>
> --!./tcltestrunner.lua
> -- 2001 September 15
> @@ -1109,5 +1109,26 @@ test:do_catchsql_test("update-10.10", [[
> -- UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL;
> -- SELECT a,b,c,'|' FROM t15 ORDER BY a;
> -- } {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |}
> +
> +test:do_execsql_test(
> + "insert-15.0",
> + [[
> + create table test(a primary key);
> + insert into test(a) values(1);
> + ]])
> +
> +test:do_catchsql_test(
> + "insert-15.1",
> + [[
> + update test set a = 2, a = 3;
> + ]],
> + {1, "set id list: duplicate column name A"})
> +
> +test:do_execsql_test(
> + "insert-15.2",
> + [[
> + drop table test;
> + ]])
> +
> test:finish_test()
>
>
^ permalink raw reply [flat|nested] 3+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: disallow id duplicate in insert & update
2018-04-09 20:30 ` [tarantool-patches] " Vladislav Shpilevoy
@ 2018-04-16 13:35 ` Kirill Yukhin
0 siblings, 0 replies; 3+ messages in thread
From: Kirill Yukhin @ 2018-04-16 13:35 UTC (permalink / raw)
To: tarantool-patches; +Cc: AKhatskevich
Hello,
On 09 апр 23:30, Vladislav Shpilevoy wrote:
> LGTM.
I've checked the patch into 2.x branch.
--
Regards, Kirill Yukhin
^ permalink raw reply [flat|nested] 3+ messages in thread
end of thread, other threads:[~2018-04-16 13:35 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
[not found] <DF3A58E7-87E9-4809-95D4-200923CD88CD@tarantool.org>
2018-04-09 4:29 ` [tarantool-patches] [PATCH] sql: disallow id duplicate in insert & update AKhatskevich
2018-04-09 20:30 ` [tarantool-patches] " Vladislav Shpilevoy
2018-04-16 13:35 ` Kirill Yukhin
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox