osdir.com


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

Re: Fw: Order of fields in select statement


Thanks for your responses. I'm not sure if the example that I gave conforms
to any standard.
This is in the context of Samza SQL
<https://samza.apache.org/learn/tutorials/0.14/samza-sql.html> (stream
processing) where we largely conform to standard
SQL and the source and destinations in my example are kafka topics. We will
document the
behavior according to what Calcite supports (IOW, conform to the SQL
standards).

Aditya

________________________________________
> From: Ankit Singhal <ankitsinghal59@xxxxxxxxx>
> Sent: Wednesday, August 8, 2018 11:29 AM
> To: dev@xxxxxxxxxxxxxxxxxx
> Subject: Re: Order of fields in select statement
>
> 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@xxxxxxxxxxxxxxx>
> 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@xxxxxxxxx>
> > 日 期:2018年08月09日 00:07:58
> > 收件人:<dev@xxxxxxxxxxxxxxxxxx>
> > 主 题: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
> >
> >
>