Skip to content

INSERT with named columns errors with nested structs and arrays #3231

Open
@hatyo

Description

@hatyo

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingrelationalissues related to relational FDB

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions