From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp37.i.mail.ru (smtp37.i.mail.ru [94.100.177.97]) (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 815EA4429E1 for ; Mon, 29 Jun 2020 23:08:41 +0300 (MSK) References: <20200611151853.24398-1-roman.habibov@tarantool.org> <20200611151853.24398-2-roman.habibov@tarantool.org> <81127D96-BB22-4F99-A788-047F9327A4AE@tarantool.org> <3e040934-d6dc-200b-5925-04879fea3e83@tarantool.org> From: Vladislav Shpilevoy Message-ID: Date: Mon, 29 Jun 2020 22:08:39 +0200 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8" Content-Language: en-US Content-Transfer-Encoding: 8bit Subject: Re: [Tarantool-patches] [PATCH v3 1/2] sql: use unify pattern for column names List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Roman Khabibov Cc: tarantool-patches@dev.tarantool.org Hi! Thanks for the patch! > In my patch, I used the zName field of the struct ExprList_item, > which is also used for aliases. But I did not find words in the > code that this field is only for aliases ( clause), therefore > I considered it legitimate to put auto names in it. > > PostgreSQL would throw an error in the case above: "ambiguous names”. > But it works for us. Is it a bug or not? Perhaps yes. In any case, > this does not apply to the patch and requires a separate discussion, > so I just delete this test. I wouldn't consider it a bug. And it is totally not related to this patchset. So I would leave it as is now. Please, don't delete any tests unless they are duplicate, or useless. See 2 comments below. > commit ed196a4446177eb14aa1b86a382c32416edb5794 > Author: Roman Khabibov > Date: Thu Mar 5 12:48:58 2020 +0300 > > sql: unify pattern for column names > > 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 COLLATE "unicode_ci" FROM test; 1. In my only comment to the previous patch I asked not to use COLLATE for numbers, and make the example more representative. Here if I remove 'COLLATE "unicode_ci"', the result won't change. That makes the example close to useless. Please, reconsider my comment from the previous email. > --- > - metadata: > - name: COLUMN_1 > type: scalar > - name: COLUMN_1 > type: scalar > rows: > - [1, 1] > ... > ``` > > diff --git a/test/sql-tap/colname.test.lua b/test/sql-tap/colname.test.lua > index caa61a07a..f27ffe413 100755 > --- a/test/sql-tap/colname.test.lua > +++ b/test/sql-tap/colname.test.lua > @@ -635,4 +635,133 @@ test:do_catchsql_test(> + > +test:do_execsql2_test( > + "colname-12.14", > + [[ > + CREATE TABLE j_1 (column_1 SCALAR PRIMARY KEY, column_2 SCALAR); > + INSERT INTO j_1 VALUES(1, 1); > + ]], {}) > + > +test:do_execsql2_test( > + "colname-12.15", > + [[ > + SELECT column_1, column_1 COLLATE "unicode_ci", column_2, 1 FROM j_1; > + ]], { > + "COLUMN_1",1,"COLUMN_1",1,"COLUMN_2",1,"COLUMN_2",1 2. Ditto. > + }) > +