calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ankit Singhal <ankitsingha...@gmail.com>
Subject Re: Order of fields in select statement
Date Wed, 08 Aug 2018 18:29:38 GMT
Yes, what Stamatis said, INSERT will happen in an order as per the schema
of destination(irrespective of alias), if you want to alter the order, you
can specify INSERT statement like this.

"INSERT INTO myDestination*(field1,field0)* SELECT
s.field1 as field1, s.field0 as field0 FROM mySource as s"

And on the other hand, please share references to any database or SQL
standard(if you find any) which support the behaviour you are looking at.
(because if my memory serves me correctly, I remember Hive used to support
the alias mapping with destination schema but not sure if it's even true
today in latest releases)

Regards,
Ankit Singhal

On Wed, Aug 8, 2018 at 10:49 AM, Haisheng Yuan <h.yuan@alibaba-inc.com>
wrote:

> It has nothing to do with aliases. The type should match. As the message
> said, you are trying to insert varchar type into integer field, which
> doesn’t pass Calcite’s validation.
>
> Thanks ~
> Haisheng Yuan
> ------------------------------------------------------------------
> 发件人:Aditya<adi285@gmail.com>
> 日 期:2018年08月09日 00:07:58
> 收件人:<dev@calcite.apache.org>
> 主 题:Order of fields in select statement
>
> 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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message