From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp39.i.mail.ru (smtp39.i.mail.ru [94.100.177.99]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id C14E64696C5 for ; Mon, 27 Apr 2020 02:09:02 +0300 (MSK) From: Roman Khabibov Date: Mon, 27 Apr 2020 02:09:00 +0300 Message-Id: <20200426230900.23258-3-roman.habibov@tarantool.org> In-Reply-To: <20200426230900.23258-1-roman.habibov@tarantool.org> References: <20200426230900.23258-1-roman.habibov@tarantool.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v2 2/2] sql: don't modify column names List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org Don't print "_1" to the end of column name, if this name is duplicated within the expression list during 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( -- }) -test:do_execsql2_test( - "colname-2.8", - [[ - SELECT * FROM v1 ORDER BY 2; - ]], { - -- - "A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6 - -- - }) - -test:do_execsql2_test( - "colname-2.9", - [[ - SELECT * FROM v2 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -- 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( -- }) -test:do_execsql2_test( - "colname-3.8", - [[ - SELECT v1.a, * FROM v1 ORDER BY 2; - ]], { - -- - "A",1,"A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6 - -- - }) - -test:do_execsql2_test( - "colname-3.9", - [[ - SELECT * FROM v2 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-3.10", - [[ - SELECT * FROM v3 ORDER BY 2; - ]], { - -- - "A",1,"X",4,"A_1",1,"B",2,"C",3,"X_1",4,"Y",5,"Z",6 - -- - }) - -test:do_execsql2_test( - "colname-3.11", - [[ - SELECT * FROM v4 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -- 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( -- }) -test:do_execsql2_test( - "colname-4.8", - [[ - SELECT * FROM v1 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-4.9", - [[ - SELECT * FROM v2 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-4.10", - [[ - SELECT * FROM v3 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-4.11", - [[ - SELECT * FROM v4 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-4.12", - [[ - SELECT * FROM v5 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -test:do_execsql2_test( - "colname-4.13", - [[ - SELECT * FROM v6 ORDER BY 2; - ]], { - -- - "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 - -- - }) - -- 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)); ]], { -- 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)