[Tarantool-patches] [PATCH v2 2/2] sql: don't modify column names
Roman Khabibov
roman.habibov at tarantool.org
Mon Apr 27 02:09:00 MSK 2020
Don't print "_1" to the end of column name, if this name is
duplicated within the expression list during <FROM> expanding.
Colses #4545
---
src/box/sql/select.c | 30 +---
test/sql-tap/colname.test.lua | 137 +------------------
test/sql-tap/view.test.lua | 2 +-
test/sql/boolean.result | 6 +-
test/sql/gh-4104-view-access-check.result | 2 +-
test/sql/gh-4104-view-access-check.test.lua | 2 +-
test/sql/gh-4545-no-col-name-modify.result | 33 +++++
test/sql/gh-4545-no-col-name-modify.test.sql | 10 ++
8 files changed, 51 insertions(+), 171 deletions(-)
create mode 100644 test/sql/gh-4545-no-col-name-modify.result
create mode 100755 test/sql/gh-4545-no-col-name-modify.test.sql
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index d65266586..e6e5d3cd4 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1895,13 +1895,9 @@ sqlColumnsFromExprList(Parse * parse, ExprList * expr_list,
{
/* Database connection */
sql *db = parse->db;
- u32 cnt; /* Index added to make the name unique */
Expr *p; /* Expression for a single result column */
char *zName; /* Column name */
- int nName; /* Size of name in zName[] */
- Hash ht; /* Hash table of column names */
- sqlHashInit(&ht);
uint32_t column_count =
expr_list != NULL ? (uint32_t)expr_list->nExpr : 0;
/*
@@ -1953,32 +1949,9 @@ sqlColumnsFromExprList(Parse * parse, ExprList * expr_list,
}
if (zName == NULL) {
uint32_t idx = ++parse->autoname_i;
- zName = sqlDbStrDup(db, sql_generate_column_name(idx));
- } else {
- zName = sqlDbStrDup(db, zName);
- }
-
- /* Make sure the column name is unique. If the name is not unique,
- * append an integer to the name so that it becomes unique.
- */
- cnt = 0;
- while (zName && sqlHashFind(&ht, zName) != 0) {
- nName = sqlStrlen30(zName);
- if (nName > 0) {
- int j;
- for (j = nName - 1;
- j > 0 && sqlIsdigit(zName[j]); j--);
- if (zName[j] == '_')
- nName = j;
- }
- zName =
- sqlMPrintf(db, "%.*z_%u", nName, zName, ++cnt);
+ zName = (char *) sql_generate_column_name(idx);
}
size_t name_len = strlen(zName);
- void *field = &space_def->fields[i];
- if (zName != NULL &&
- sqlHashInsert(&ht, zName, field) == field)
- sqlOomFault(db);
space_def->fields[i].name = region_alloc(region, name_len + 1);
if (space_def->fields[i].name == NULL) {
sqlOomFault(db);
@@ -1989,7 +1962,6 @@ sqlColumnsFromExprList(Parse * parse, ExprList * expr_list,
}
}
cleanup:
- sqlHashClear(&ht);
if (db->mallocFailed) {
/*
* pTable->def could be not temporal in
diff --git a/test/sql-tap/colname.test.lua b/test/sql-tap/colname.test.lua
index e895c040b..748394405 100755
--- a/test/sql-tap/colname.test.lua
+++ b/test/sql-tap/colname.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(69)
+test:plan(57)
--!./tcltestrunner.lua
-- 2008 July 15
@@ -64,10 +64,6 @@ test:do_test(
INSERT INTO txyz VALUES(4,5,6);
CREATE TABLE tboth(a INT PRIMARY KEY,b INT,c INT,x INT,y INT,z INT);
INSERT INTO tboth VALUES(11,12,13,14,15,16);
- CREATE VIEW v1 AS SELECT tabC.a, txyZ.x, *
- FROM tabc, txyz ORDER BY 1 LIMIT 1;
- CREATE VIEW v2 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, *
- FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;
]]
return test:execsql2 [[
SELECT * FROM tabc;
@@ -138,37 +134,10 @@ test:do_execsql2_test(
-- </colname-2.7>
})
-test:do_execsql2_test(
- "colname-2.8",
- [[
- SELECT * FROM v1 ORDER BY 2;
- ]], {
- -- <colname-2.8>
- "A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6
- -- </colname-2.8>
- })
-
-test:do_execsql2_test(
- "colname-2.9",
- [[
- SELECT * FROM v2 ORDER BY 2;
- ]], {
- -- <colname-2.9>
- "A",1,"X",4,"A_1",11,"X_1",14,"A_2",1,"B",2,"C",3,"X_2",4,"Y",5,"Z",6,"A_3",11,"B_1",12,"C_1",13,"X_3",14,"Y_1",15,"Z_1",16
- -- </colname-2.9>
- })
-
-- Tests for full=OFF
test:do_test(
"colname-3.1",
function()
- test:execsql [[
- UPDATE "_session_settings" SET "value" = false WHERE "name" = 'sql_full_column_names';
- CREATE VIEW v3 AS SELECT tabC.a, txyZ.x, *
- FROM tabc, txyz ORDER BY 1 LIMIT 1;
- CREATE VIEW v4 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, *
- FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;
- ]]
return test:execsql2 [[
SELECT * FROM tabc;
]]
@@ -238,56 +207,12 @@ test:do_execsql2_test(
-- </colname-3.7>
})
-test:do_execsql2_test(
- "colname-3.8",
- [[
- SELECT v1.a, * FROM v1 ORDER BY 2;
- ]], {
- -- <colname-3.8>
- "A",1,"A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6
- -- </colname-3.8>
- })
-
-test:do_execsql2_test(
- "colname-3.9",
- [[
- SELECT * FROM v2 ORDER BY 2;
- ]], {
- -- <colname-3.9>
- "A",1,"X",4,"A_1",11,"X_1",14,"A_2",1,"B",2,"C",3,"X_2",4,"Y",5,"Z",6,"A_3",11,"B_1",12,"C_1",13,"X_3",14,"Y_1",15,"Z_1",16
- -- </colname-3.9>
- })
-
-test:do_execsql2_test(
- "colname-3.10",
- [[
- SELECT * FROM v3 ORDER BY 2;
- ]], {
- -- <colname-3.10>
- "A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6
- -- </colname-3.10>
- })
-
-test:do_execsql2_test(
- "colname-3.11",
- [[
- SELECT * FROM v4 ORDER BY 2;
- ]], {
- -- <colname-3.11>
- "A",1,"X",4,"A_1",11,"X_1",14,"A_2",1,"B",2,"C",3,"X_2",4,"Y",5,"Z",6,"A_3",11,"B_1",12,"C_1",13,"X_3",14,"Y_1",15,"Z_1",16
- -- </colname-3.11>
- })
-
-- Test for full=ON
test:do_test(
"colname-4.1",
function()
test:execsql [[
UPDATE "_session_settings" SET "value" = true WHERE "name" = 'sql_full_column_names';
- CREATE VIEW v5 AS SELECT tabC.a, txyZ.x, *
- FROM tabc, txyz ORDER BY 1 LIMIT 1;
- CREATE VIEW v6 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, *
- FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;
]]
return test:execsql2 [[
SELECT * FROM tabc;
@@ -358,66 +283,6 @@ test:do_execsql2_test(
-- </colname-4.7>
})
-test:do_execsql2_test(
- "colname-4.8",
- [[
- SELECT * FROM v1 ORDER BY 2;
- ]], {
- -- <colname-4.8>
- "V1.A",1,"V1.X",4,"V1.A_1",1,"V1.B",2,"V1.C",3,"V1.X_1",4,"V1.Y",5,"V1.Z",6
- -- </colname-4.8>
- })
-
-test:do_execsql2_test(
- "colname-4.9",
- [[
- SELECT * FROM v2 ORDER BY 2;
- ]], {
- -- <colname-4.9>
- "V2.A",1,"V2.X",4,"V2.A_1",11,"V2.X_1",14,"V2.A_2",1,"V2.B",2,"V2.C",3,"V2.X_2",4,"V2.Y",5,"V2.Z",6,"V2.A_3",11,"V2.B_1",12,"V2.C_1",13,"V2.X_3",14,"V2.Y_1",15,"V2.Z_1",16
- -- </colname-4.9>
- })
-
-test:do_execsql2_test(
- "colname-4.10",
- [[
- SELECT * FROM v3 ORDER BY 2;
- ]], {
- -- <colname-4.10>
- "V3.A",1,"V3.X",4,"V3.A_1",1,"V3.B",2,"V3.C",3,"V3.X_1",4,"V3.Y",5,"V3.Z",6
- -- </colname-4.10>
- })
-
-test:do_execsql2_test(
- "colname-4.11",
- [[
- SELECT * FROM v4 ORDER BY 2;
- ]], {
- -- <colname-4.11>
- "V4.A",1,"V4.X",4,"V4.A_1",11,"V4.X_1",14,"V4.A_2",1,"V4.B",2,"V4.C",3,"V4.X_2",4,"V4.Y",5,"V4.Z",6,"V4.A_3",11,"V4.B_1",12,"V4.C_1",13,"V4.X_3",14,"V4.Y_1",15,"V4.Z_1",16
- -- </colname-4.11>
- })
-
-test:do_execsql2_test(
- "colname-4.12",
- [[
- SELECT * FROM v5 ORDER BY 2;
- ]], {
- -- <colname-4.12>
- "V5.A",1,"V5.X",4,"V5.A_1",1,"V5.B",2,"V5.C",3,"V5.X_1",4,"V5.Y",5,"V5.Z",6
- -- </colname-4.12>
- })
-
-test:do_execsql2_test(
- "colname-4.13",
- [[
- SELECT * FROM v6 ORDER BY 2;
- ]], {
- -- <colname-4.13>
- "V6.A",1,"V6.X",4,"V6.A_1",11,"V6.X_1",14,"V6.A_2",1,"V6.B",2,"V6.C",3,"V6.X_2",4,"V6.Y",5,"V6.Z",6,"V6.A_3",11,"V6.B_1",12,"V6.C_1",13,"V6.X_3",14,"V6.Y_1",15,"V6.Z_1",16
- -- </colname-4.13>
- })
-
-- MUST_WORK_TEST avoid using sql_master
-- ticket #3229
-- test:do_test(
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index 66374e374..68e783758 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -1156,7 +1156,7 @@ test:do_catchsql_test(
CREATE TABLE t13(c INT PRIMARY KEY);
CREATE VIEW v11 AS SELECT * FROM
(SELECT a FROM (SELECT a, b FROM t11, t12)),
- (SELECT * FROM (SELECT a, c FROM t11, t13));
+ (SELECT * FROM (SELECT a AS a_1, c FROM t11, t13));
]], {
-- <view-23.1>
0
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 51ec5820b..b92ba5bf2 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -690,16 +690,16 @@ SELECT * FROM (SELECT t4.i, t5.i, a, b FROM t4, t5 WHERE a = false OR b = true);
| - metadata:
| - name: I
| type: integer
- | - name: I_1
+ | - name: I
| type: integer
| - name: A
| type: boolean
| - name: B
| type: boolean
| rows:
- | - [100, 1, false, true]
+ | - [100, 100, false, true]
+ | - [100, 100, false, false]
| - [100, 100, false, false]
- | - [100, 101, false, false]
| ...
-- Check VIEW.
diff --git a/test/sql/gh-4104-view-access-check.result b/test/sql/gh-4104-view-access-check.result
index d38b633c3..675b94a7b 100644
--- a/test/sql/gh-4104-view-access-check.result
+++ b/test/sql/gh-4104-view-access-check.result
@@ -2,7 +2,7 @@ box.execute("CREATE TABLE supersecret(id INT PRIMARY KEY, data TEXT);")
---
- row_count: 1
...
-box.execute("CREATE TABLE supersecret2(id INT PRIMARY KEY, data TEXT);")
+box.execute("CREATE TABLE supersecret2(id_2 INT PRIMARY KEY, data_2 TEXT);")
---
- row_count: 1
...
diff --git a/test/sql/gh-4104-view-access-check.test.lua b/test/sql/gh-4104-view-access-check.test.lua
index d1d19fc28..86dabebaf 100644
--- a/test/sql/gh-4104-view-access-check.test.lua
+++ b/test/sql/gh-4104-view-access-check.test.lua
@@ -1,5 +1,5 @@
box.execute("CREATE TABLE supersecret(id INT PRIMARY KEY, data TEXT);")
-box.execute("CREATE TABLE supersecret2(id INT PRIMARY KEY, data TEXT);")
+box.execute("CREATE TABLE supersecret2(id_2 INT PRIMARY KEY, data_2 TEXT);")
box.execute("INSERT INTO supersecret VALUES(1, 'very very big secret');")
box.execute("INSERT INTO supersecret2 VALUES(1, 'very big secret 2');")
box.execute("CREATE VIEW supersecret_leak AS SELECT * FROM supersecret, supersecret2;")
diff --git a/test/sql/gh-4545-no-col-name-modify.result b/test/sql/gh-4545-no-col-name-modify.result
new file mode 100644
index 000000000..be452488b
--- /dev/null
+++ b/test/sql/gh-4545-no-col-name-modify.result
@@ -0,0 +1,33 @@
+-- test-run result file version 2
+CREATE TABLE t1 (a INT PRIMARY KEY)
+ | ---
+ | - row_count: 1
+ | ...
+CREATE TABLE t2 (a INT PRIMARY KEY)
+ | ---
+ | - row_count: 1
+ | ...
+
+-- Names of columns can be duplicated.
+SELECT * FROM (SELECT * FROM t1, t2)
+ | ---
+ | - metadata:
+ | - name: A
+ | type: integer
+ | - name: A
+ | type: integer
+ | rows: []
+ | ...
+
+-- Make sure that a view with duplicated column names
+-- can't be created.
+CREATE VIEW v AS SELECT * FROM t1, t2
+ | ---
+ | - null
+ | - Space field 'A' is duplicate
+ | ...
+CREATE VIEW v AS SELECT * FROM t1, (SELECT * FROM t2)
+ | ---
+ | - null
+ | - Space field 'A' is duplicate
+ | ...
diff --git a/test/sql/gh-4545-no-col-name-modify.test.sql b/test/sql/gh-4545-no-col-name-modify.test.sql
new file mode 100755
index 000000000..d5f5e5a9d
--- /dev/null
+++ b/test/sql/gh-4545-no-col-name-modify.test.sql
@@ -0,0 +1,10 @@
+CREATE TABLE t1 (a INT PRIMARY KEY)
+CREATE TABLE t2 (a INT PRIMARY KEY)
+
+-- Names of columns can be duplicated.
+SELECT * FROM (SELECT * FROM t1, t2)
+
+-- Make sure that a view with duplicated column names
+-- can't be created.
+CREATE VIEW v AS SELECT * FROM t1, t2
+CREATE VIEW v AS SELECT * FROM t1, (SELECT * FROM t2)
\ No newline at end of file
--
2.21.0 (Apple Git-122)
More information about the Tarantool-patches
mailing list