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 <[email protected]> > Sent: Wednesday, August 8, 2018 11:29 AM > To: [email protected] > 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 <[email protected]> > 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<[email protected]> > > 日 期：2018年08月09日 00:07:58 > > 收件人：<[email protected]> > > 主 题：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 > > > > >
