[jira] [Commented] (TRAFODION-2044) CREATE TABLE LIKE with SALT USING clause uses source table partition count

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15323466#comment-15323466 ] 

David Wayne Birdsall commented on TRAFODION-2044:
-------------------------------------------------

Looking at the parser (parser/sqlparser.y), the CREATE command has a production for the LIKE stuff (like_definition, which is one of the alternatives for production table_definition_body), and included in that are a limited number of options of things that one can override from the base table. An example is WITHOUT SALT. So, you can optionally have your target table be not salted if you want. If you don't specify WITHOUT SALT, then you get whatever the source table has. Now, the SALT USING clause is in a completely different production which comes after the like attributes. (That is, SALT USING comes from production salt_by_clause, which is an alternative for create_table_attribute, which is an element of production create_table_attribute_list, which is an alternative for optional_create_table_attribute_list, which occurs as the non-terminal after table_definition_body in the table_definition production.) And whatever gets parsed from that seems to get ignored. So, for example, I can say, "CREATE TABLE T1A LIKE T1 WITHOUT SALT SALT USING 5 PARTITIONS". That creates an unsalted table.

My best guess is that in a CREATE TABLE LIKE, everything in the table_definition production after table_definition_body is effectively ignored. That suggests the following approach to fixing this JIRA:

1. Separate out like_definition from table_definition_body.
2. Create an alternative production for table_definition that replaces table_definition_body optional_create_table_attribute_list etc. with just like_definition.
3. Add a SALT USING n PARTITIONS syntax in like_option.
4. Change the CREATE TABLE LIKE code to use the number of partitions from this option if it is specified.

> CREATE TABLE LIKE with SALT USING  clause uses source table partition count
> ---------------------------------------------------------------------------
>
>                 Key: TRAFODION-2044
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2044
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmu
>    Affects Versions: 2.0-incubating
>         Environment: Trafodion 2.0 environment
>            Reporter: Carol Pearson
>            Assignee: David Wayne Birdsall
>             Fix For: 2.1-incubating
>
>
> Using CREATE TABLE LIKE to create a table, then changing the number of partitions. The resulting new table number of partitions matches the partitioncount for the source table in the CREATE TABLE LIKE statement, rather than the value specified in the command:
> See the following commands:
> >>>create table tsource (c1 int not null, c2 int not null, primary key (c1))
> +>salt using 4 partitions;
> --- SQL operation complete.
> >>create table tsource_like like tsource salt using 8 partitions;
> --- SQL operation complete.
> >>showddl tsource_like
> +>;
> CREATE TABLE TRAFODION.SEABASE.TSOURCE_LIKE
>   (
>     C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
>       SERIALIZED
>   , C2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
>       SERIALIZED
>   , PRIMARY KEY (C1 ASC)
>   )
>   SALT USING 4 PARTITIONS
> ;
> --- SQL operation complete.
> >>
> But table tsource_like was created using SALT USING 8 PARTITIONS



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)