[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