List of columns that are inserted to

[Viliam Durina <vi...@hazelcast.com>]

We have a query similar to this:

    INSERT INTO t(c1, c2) VALUES(1, 2);

However, the row type of table `t` has 3 columns: c1, c2 and c3.

When we convert the SqlNode to RelNode using
`SqlToRelConverter.convertQuery()`, the output is similar to this:

LogicalTableModify(table=[t], operation=[INSERT], flattened=[false])
  LogicalProject(c1=[$1], c2=[$2], c3=[null:OBJECT])
    LogicalValues(tuples=[[{1, 2}]])

Our issue is that the input of `TableModify` always matches the row type of
the target table. However, we'd like to know which columns were assigned to
in the original INSERT statement. We'd like to handle the `c3` differently.
In other words, the INSERT statement above and the following two statements
don't produce the same result:

    INSERT INTO t(c1, c2, c3) VALUES(1, 2, null);

Is there a way to know the list of columns? Or to produce a plan such as:

LogicalTableModify(table=[t], operation=[INSERT], flattened=[false],
columns=[c1, c2])
  LogicalValues(tuples=[[{1, 2}]])

Viliam

-- 
This message contains confidential information and is intended only for the 
individuals named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and 
delete this e-mail from your system. E-mail transmission cannot be 
guaranteed to be secure or error-free as information could be intercepted, 
corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. 
The sender therefore does not accept liability for any errors or omissions 
in the contents of this message, which arise as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. -Hazelcast

Re: List of columns that are inserted to

[Julian Hyde <jh...@apache.org>]

The mapping isn't as simple as you may think. Calcite checks the
catalog to see whether each column has a default expression. NULL is
just the default initializer expression.

By the way, can access the initializer expression functionality
without leaving columns out of the column list: you can write

  INSERT INTO t(c1, c3, c2) VALUES(1, DEFAULT, 2);

and the effect will be very similar to your statement.

All the information you want is of course in the SqlNode validated
parse tree. And I don't think we should carry extra information into
the RelNode tree. Therefore the place to fix this is by injecting
logic into the Sql-to-rel process. Initializer expressions are a
perfect way to do this.

Julian

On Mon, Oct 26, 2020 at 8:16 AM Viliam Durina <vi...@hazelcast.com> wrote:
>
> We have a query similar to this:
>
>     INSERT INTO t(c1, c2) VALUES(1, 2);
>
> However, the row type of table `t` has 3 columns: c1, c2 and c3.
>
> When we convert the SqlNode to RelNode using
> `SqlToRelConverter.convertQuery()`, the output is similar to this:
>
> LogicalTableModify(table=[t], operation=[INSERT], flattened=[false])
>   LogicalProject(c1=[$1], c2=[$2], c3=[null:OBJECT])
>     LogicalValues(tuples=[[{1, 2}]])
>
> Our issue is that the input of `TableModify` always matches the row type of
> the target table. However, we'd like to know which columns were assigned to
> in the original INSERT statement. We'd like to handle the `c3` differently.
> In other words, the INSERT statement above and the following two statements
> don't produce the same result:
>
>     INSERT INTO t(c1, c2, c3) VALUES(1, 2, null);
>
> Is there a way to know the list of columns? Or to produce a plan such as:
>
> LogicalTableModify(table=[t], operation=[INSERT], flattened=[false],
> columns=[c1, c2])
>   LogicalValues(tuples=[[{1, 2}]])
>
> Viliam
>
> --
> This message contains confidential information and is intended only for the
> individuals named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and
> delete this e-mail from your system. E-mail transmission cannot be
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
> The sender therefore does not accept liability for any errors or omissions
> in the contents of this message, which arise as a result of e-mail
> transmission. If verification is required, please request a hard-copy
> version. -Hazelcast