Generate column names instead of labels

[mlkersten]

Is your feature request related to a problem? Please describe.
Consider the following statements:

sql> create table table_3a as select count(*) from sys.tables;
CREATE TABLE: generated labels not allowed in column names, use an alias instead
sql> create table table_3a as select count(*) as x from sys.tables;
operation successful

Describe the solution you'd like
I would expect that proper columns names are automatically generated. e.g. col_1, col_2,...
such that the resulting table can be easily re-used.

It is a programming convenience. Especially if you can easily retrieve the
number of columns in table_3a. (which we can from the catalogue)

[kutsurak]

I believe that the idea behind the generated labels is to precisely be disallowed as column names so that there is no overlap with user provided names for columns.

In my opinion adding as x clauses in these kinds of queries is a reasonable inconvenience to the user, compared to the engineering effort it would take to avoid having conflicts.

[mlkersten]

It is highly convenient for a tool that wishes to preserve the result set as a real table and only sees the user entering a SELECT
statement without further analysis.
Column confusion is not a problem, because the SQL optimizer pipeline can in the final stage replace the labels with permissible names before it creates the table from the result set. And simply check for a clash of an alias/colname with the generated one

[PedroTadim]

I'm going to close this issue as a duplicate of issue 7032.

[mlkersten]

No, it is not the same, as 7032 calls for better handling of duplicate names in error handling. I wish to let the system generate meaningful, distinct default column names. One can not depend on the user in all cases. It is needed in the next feature release.
However, a proper solution will help both issues.

[njnes]

just add the as 'alias', gives the user full control.

[mlkersten]

The user is an application that generates them!! it has no control over the expressions.
Futhermore, from a design point of view the relational model stresses the fact that the result of a relational query is again a relation. Not being able to address the attributes subsequently is a gross misstake.

[mlkersten]

Niels :
i) If any two columns in the table specified by the QUERY EXPRESSION have equivalent COLUMN NAMEs, or if any column of that table has an implementation-dependent name, then a VIEWCOLUMNLIST shall be specified.

Martin Kersten :
We are talking here about implementation-dependent names, and I ask you to make sure that these names are disjoint to begin with. I could say not doing so is a bug in system-dependent name generation.

[PedroTadim]

"Not being able to address the attributes subsequently is a gross misstake." This is a user mistake, not a database one.

[mlkersten]

Though shall never generate identifiers in a substructure that makes the complete structure invalid.
This means that the valid expression

CREATE TABLE pair AS SELECT 1, 2

becomes invalid while this can be easily avoided using a proper system-dependent name generation.
it is not the user at fault, but the system not doing its work properly in name generation. It re-uses the same label,

If you take the SQL standard literally, then the following expression should also be at fault
CREATE TABLE one AS SELECT 1
because it has a single system generated name "v". This, however, is accepted by our implementation.

[PedroTadim]

We are doing exactly like postgres, ie it has a default name for column without labels. If there is more than one column without a label then a duplicate name is generated, thus error.