Re: [Tarantool-patches] [PATCH v3 1/2] sql: use unify pattern for column names

[Vladislav Shpilevoy <v.shpilevoy@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 (<AS> 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 <roman.habibov@tarantool.org>
> Date:   Thu Mar 5 12:48:58 2020 +0300
> 
>     sql: unify pattern for column names
>     
>     Name resulting columns generated by an expression or <VALUES>
>     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 <VALUES>
>     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 <ORDER BY> 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.

> +    })
> +