Open
Description
Our SQL supports setting only a specific set of columns of a (nested) record, provided this is not prohibited e.g. due to causing constraint violation such as missing (parts of) the primary key constituents, i.e. this works as expected:
create type as struct S(S1 bigint, S2 bigint)
create table B(B1 bigint, B2 bigint, B3 S, primary key(B1))
insert into B (B1, B3(S2)) values (20, (5))
select * from B -- returns (20, null, (null, 5))
However, it is not possible to do this when we have nested arrays and structs:
create type as struct R(R1 bigint, R2 bigint)
create type as struct S(S1 bigint, S2 bigint)
create type as struct T(T1 R, T2 bigint)
create type as struct U(U1 T, U2 S array)
create table C(A1 bigint, A2 S array, primary key (A1))
create table D(A1 bigint, A2 U array, primary key (A1))
insert into C(A1, A2(S1)) values (1, [(10), (30)]), (2, [(100)]) -- error
insert into D(A1, A2(U2(S1))) values (1, [[(100), (200)]]) -- error
As it throws the following error:
Caused by: com.apple.foundationdb.relational.api.exceptions.ContextualSQLException: expected Record but got Array
at com.apple.foundationdb.relational.api.exceptions.RelationalException.toSqlException(RelationalException.java:74)
at com.apple.foundationdb.relational.recordlayer.EmbeddedRelationalPreparedStatement.execute(EmbeddedRelationalPreparedStatement.java:80)
at com.apple.foundationdb.relational.yamltests.command.QueryExecutor.executeStatement(QueryExecutor.java:294)
Arguably, the above syntax can be ambiguous, or at least, it does not align exactly with the row we're trying to set. Perhaps we need to change the aliasing syntax to reflect the fact that A2
is actually an array, but I am not sure.
insert into C(A1, A2[(S1)]) values (1, [(10), (30)]), (2, [(100)]) -- indicate A2 is an array