osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Order of fields in select statement


Hi Aditya,

The example that you posted is not a simple SELECT statement since it has
the INSERT INTO clause.

I have the impression that the datatype from the output of the SELECT
statement should match the schema of the table that you are trying to add
the data (and that aliases do not really make a difference in this case). I
think this is also the behavior of various commercial databases e.g.,
Oracle.

Out of curiosity can you point out the name of the database that supports
this kind of queries.

Best,
Stamatis

2018-08-08 19:07 GMT+03:00 Aditya <adi285@xxxxxxxxx>:

> Hi,
>
> I'm trying out Calcite and realized that Calcite does not support aliases
> in select statement as well as it should be. Please let me know if I'm
> missing something here.
>
> Here is the scenario:
>
> {scenario}
> Let's say I have a destination with the following schema: { name: "field0",
> type: "INTEGER" } , { name: "field1", type: "VARCHAR" }
> and source with schema: { name: "field0", type: "INTEGER" } , { name:
> "field1", type: "VARCHAR" }, { name: "field2", type: "BOOL" }
>
> I have the following simple query: "INSERT INTO myDestination SELECT
> s.field1 as field1, s.field0 as field0 FROM mySource as s"
> {scenario}
>
> Planner validation fails with "Cannot assign to target field 'field0' of
> type INTEGER from source field 'field1' of type VARCHAR". It works if I
> reverse the order of fields in the select statement. So, it looks like the
> order of fields specified in the select statement should match the order of
> fields in the output schema even if I use aliases. Is that true ?
>
> Thanks,
> Aditya
>