Insert with DEAFULT fails when an IDENTITY column is used as a salt key

[Weishiun Tsai]

DEFAULT is often used to insert an automatically generated value into an IDENTITY column. But such an insert fails if the IDETNITY column is also used as a salt key for the table.

In the following example, c1 is an IDENTITY column which is also used as a salt key. 2 types of inserts are used: One uses column list with c2 only. The other specifies DEAFULT for c1. As shown in the execution output, the first kind of inserts work fine, but specifying DEFAULT returns error 3428.

This is seen on the v1.1.0rc1 (v0417) build.

---------------------------------------------------------------------

Here is the entire script to reproduce this problem:

create schema mytest;
set schema mytest;

create table t (
c1 largeint GENERATED ALWAYS AS IDENTITY not null not droppable,
c2 int not null not droppable
) store by (c1) salt using 2 partitions on (c1);

insert into t (c2) values (1);
insert into t (c2) values (2);
insert into t values (DEFAULT,3);
insert into t values (DEFAULT,4);

select * from t;

drop schema mytest cascade;

---------------------------------------------------------------------

Here is the execution output:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table t (
+>c1 largeint GENERATED ALWAYS AS IDENTITY not null not droppable,
+>c2 int not null not droppable
+>) store by (c1) salt using 2 partitions on (c1);

--- SQL operation complete.
>>
>>insert into t (c2) values (1);

--- 1 row(s) inserted.
>>insert into t (c2) values (2);

--- 1 row(s) inserted.
>>insert into t values (DEFAULT,3);

*** ERROR[3428] IDENTITY column C1 defined as GENERATED ALWAYS cannot accept values specified by the user.

*** ERROR[8822] The statement was not prepared.

>>insert into t values (DEFAULT,4);

*** ERROR[3428] IDENTITY column C1 defined as GENERATED ALWAYS cannot accept values specified by the user.

*** ERROR[8822] The statement was not prepared.

>>
>>select * from t;

C1 C2
-------------------- -----------

                   1 1
                   2 2

--- 2 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0519 build installed on a cluster. This problem is now fixed:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table t (
+>c1 largeint GENERATED ALWAYS AS IDENTITY not null not droppable,
+>c2 int not null not droppable
+>) store by (c1) salt using 2 partitions on (c1);

--- SQL operation complete.
>>
>>insert into t (c2) values (1);

--- 1 row(s) inserted.
>>insert into t (c2) values (2);

--- 1 row(s) inserted.
>>insert into t values (DEFAULT,3);

--- 1 row(s) inserted.
>>insert into t values (DEFAULT,4);

--- 1 row(s) inserted.
>>
>>select * from t;

C1 C2
-------------------- -----------

                   1 1
                   2 2
                   4 4
                   3 3

--- 4 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.