help with structured fields in JDBC table

[James Anderson <ja...@finitestate.io>]

Hi,

I'm having trouble successfully validating a SQL query against a postgres
db where one of the selected fields is a postgres type (struct).

Stepping through the code, it appears that the column is successfully
interpreted as being of the type "STRUCTURED" (2002), but
SqlTypeFactoryImpl.createSqlType still always returns a BasicSqlType for
that column, rather than what I think should be returned which is a
RelRecordType instance.

I'm having trouble telling from the documentation, but it seems like
structured types should be supported, so I'm wondering if anyone has any
insight into what I might be doing incorrectly?

Any help would be greatly appreciated!

Example Postgres setup:

create type "my_type" as ("my_text_field" text);
create table "my_table"  (
    my_type my_type
);

Query to be validated/re-written

select (my_type).my_text_field from my_table t;


Parsing with:

private SqlNode parseAndValidate(final String query) throws
SqlParseException, ValidationException, RelConversionException {
    try (final Planner planner = Frameworks.getPlanner(frameworkConfig)) {
        final SqlNode parse = planner.parse(query);
        return planner.validate(parse);
    }
}


Result:

*org.apache.calcite.runtime.CalciteContextException: From line 1, column 9
to line 1, column 15: Incompatible types*


Thanks!
Jim

Re: help with structured fields in JDBC table

[Danny Chan <yu...@gmail.com>]

Calcite supports defining the user defined type (UDT) and also you can declare the type as a row type directly, for example:

create table "my_table" (
  my_field row(my_text_field varcher(2000))
);

Calcite does not support the “text” type, I’m not sure how you can define type like that.

Maybe you can have a test case in the JdbcTest so that the contributors interested in this can give some help.

Best,
Danny Chan
在 2020年8月26日 +0800 PM2:20，James Anderson <ja...@finitestate.io>，写道：
> Hi,
>
> I'm having trouble successfully validating a SQL query against a postgres
> db where one of the selected fields is a postgres type (struct).
>
> Stepping through the code, it appears that the column is successfully
> interpreted as being of the type "STRUCTURED" (2002), but
> SqlTypeFactoryImpl.createSqlType still always returns a BasicSqlType for
> that column, rather than what I think should be returned which is a
> RelRecordType instance.
>
> I'm having trouble telling from the documentation, but it seems like
> structured types should be supported, so I'm wondering if anyone has any
> insight into what I might be doing incorrectly?
>
> Any help would be greatly appreciated!
>
> Example Postgres setup:
>
> create type "my_type" as ("my_text_field" text);
> create table "my_table" (
> my_type my_type
> );
>
> Query to be validated/re-written
>
> select (my_type).my_text_field from my_table t;
>
>
> Parsing with:
>
> private SqlNode parseAndValidate(final String query) throws
> SqlParseException, ValidationException, RelConversionException {
> try (final Planner planner = Frameworks.getPlanner(frameworkConfig)) {
> final SqlNode parse = planner.parse(query);
> return planner.validate(parse);
> }
> }
>
>
> Result:
>
> *org.apache.calcite.runtime.CalciteContextException: From line 1, column 9
> to line 1, column 15: Incompatible types*
>
>
> Thanks!
> Jim