From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 4F677445320 for ; Wed, 8 Jul 2020 17:52:37 +0300 (MSK) From: Roman Khabibov Date: Wed, 8 Jul 2020 17:52:32 +0300 Message-Id: <20200708145233.68952-2-roman.habibov@tarantool.org> In-Reply-To: <20200708145233.68952-1-roman.habibov@tarantool.org> References: <20200708145233.68952-1-roman.habibov@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v4 1/2] sql: unify pattern for column names List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Name resulting columns generated by an expression or construction by the "COLUMN_N" pattern. Closes #3962 @TarantoolBot document Title: Column naming in SQL Now, every auto generated column is named by the "COLUMN_N" pattern, where N is the number of generated column in a query (starting from 1). Auto generated column is a column in a query result generated by an expression or a column from construction. Examples: ``` box.execute("VALUES(1, 2, 3);") --- - metadata: - name: COLUMN_1 type: integer - name: COLUMN_2 type: integer - name: COLUMN_3 type: integer rows: - [1, 2, 3] ... box.execute("SELECT * FROM (VALUES (1+1, 1+1));") --- - metadata: - name: COLUMN_1 type: integer - name: COLUMN_2 type: integer rows: - [2, 2] ... box.execute("SELECT 1+1, 1+1;") --- - metadata: - name: COLUMN_1 type: integer - name: COLUMN_2 type: integer rows: - [2, 2] ... ``` Here, the expression "mycol + 1" generates a new column, so that it is the first auto generated resulting column will be named as "COLUMN_1". ``` tarantool> CREATE TABLE test (mycol INT PRIMARY KEY); --- - row_count: 1 ... tarantool> SELECT mycol, mycol + 1 FROM test; --- - metadata: - name: MYCOL type: integer - name: COLUMN_1 type: integer rows: [] ... ``` Note that you can use generated names already within the query, e.g. in clause. ``` tarantool> SELECT mycol, mycol + 1 FROM test ORDER BY column_1; --- - metadata: - name: MYCOL type: integer - name: COLUMN_1 type: integer rows: [] ... ``` It should also be noted that if you use column names similar to the "COLUMN_N" pattern, you can get the same names as a result: ``` tarantool> CREATE TABLE test (column_1 SCALAR PRIMARY KEY); --- - row_count: 1 ... tarantool> INSERT INTO test VALUES(1); --- - row_count: 1 ... tarantool> SELECT column_1, column_1 + 1 FROM test; --- - metadata: - name: COLUMN_1 type: scalar - name: COLUMN_1 type: scalar rows: - [1, 2] ... ``` --- src/box/sql/select.c | 69 ++-- src/box/sql/sqlInt.h | 15 + test/box/function1.result | 12 +- test/sql-tap/colname.test.lua | 161 ++++++++- test/sql-tap/select1.test.lua | 21 +- test/sql-tap/select6.test.lua | 22 +- test/sql-tap/view.test.lua | 4 +- test/sql/bind.result | 42 +-- test/sql/boolean.result | 354 +++++++++---------- test/sql/collation.result | 14 +- test/sql/errinj.result | 2 +- test/sql/foreign-keys.result | 4 +- test/sql/full_metadata.result | 10 +- test/sql/func-recreate.result | 2 +- test/sql/gh-3199-no-mem-leaks.result | 24 +- test/sql/gh-3888-values-blob-assert.result | 8 +- test/sql/gh-4697-scalar-bool-sort-cmp.result | 4 +- test/sql/icu-upper-lower.result | 82 ++--- test/sql/integer-overflow.result | 14 +- test/sql/iproto.result | 18 +- test/sql/max-on-index.result | 6 +- test/sql/misc.result | 12 +- test/sql/persistency.result | 66 ++-- test/sql/prepared.result | 36 +- test/sql/row-count.result | 40 +-- test/sql/transition.result | 66 ++-- test/sql/types.result | 224 ++++++------ 27 files changed, 753 insertions(+), 579 deletions(-) diff --git a/src/box/sql/select.c b/src/box/sql/select.c index 4b069addb..26c735ed7 100644 --- a/src/box/sql/select.c +++ b/src/box/sql/select.c @@ -1854,14 +1854,14 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList, } } else { const char *z = NULL; - if (colname != NULL) + if (colname != NULL) { z = colname; - else if (span != NULL) - z = span; - else - z = tt_sprintf("column%d", i + 1); + } else { + uint32_t idx = ++pParse->autoname_i; + z = sql_generate_column_name(idx); + } vdbe_metadata_set_col_name(v, i, z); - if (is_full_meta && colname != NULL) + if (is_full_meta) vdbe_metadata_set_col_span(v, i, span); } } @@ -1897,7 +1897,6 @@ 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 */ @@ -1929,13 +1928,12 @@ sqlColumnsFromExprList(Parse * parse, ExprList * expr_list, space_def->field_count = column_count; for (uint32_t i = 0; i < column_count; i++) { - /* Get an appropriate name for the column + /* + * Check if the column contains an "AS " + * phrase. */ - p = sqlExprSkipCollate(expr_list->a[i].pExpr); - if ((zName = expr_list->a[i].zName) != 0) { - /* If the column contains an "AS " phrase, use as the name */ - } else { - Expr *pColExpr = p; /* The expression that is the result column name */ + if ((zName = expr_list->a[i].zName) == 0) { + struct Expr *pColExpr = expr_list->a[i].pExpr; struct space_def *space_def = NULL; while (pColExpr->op == TK_DOT) { pColExpr = pColExpr->pRight; @@ -1951,14 +1949,14 @@ sqlColumnsFromExprList(Parse * parse, ExprList * expr_list, } else if (pColExpr->op == TK_ID) { assert(!ExprHasProperty(pColExpr, EP_IntValue)); zName = pColExpr->u.zToken; - } else { - /* Use the original text of the column expression as its name */ - zName = expr_list->a[i].zSpan; } } - if (zName == NULL) - zName = "_auto_field_"; - zName = sqlMPrintf(db, "%s", zName); + 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. @@ -4792,6 +4790,24 @@ selectPopWith(Walker * pWalker, Select * p) } } +/** + * Determine whether to generate a name for @a expr or not. + * + * Auto generated names is needed for every item in a