You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Andrew O <ao...@gmail.com> on 2019/06/11 20:27:45 UTC
Select into Temporary Table
Does / should Calcite support select into expressions? E.g. I'm using v1.19
with queries of the style:
select * into temporary table "#myTempResults" from (select colA
from my Table where colA = 'abc')
but these queries fail to parse at the "into" words in the expression. (I
have a calcite connection setup with SqlDdlParserImpl)
Assuming this isn't currently supported, is this something that is in the
scope of the default Calcite code, or something that would need to be a
custom extension?
I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but
I have found where us this set / used.
Also, as additional context
- my target DB for the queries is Postgres (so supports this SQL query
directly)
- also in the future I would like to support parsing / processing Cursor
related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
Although I'm sure if / where these would belong in Calcite.
Thanks in advance
Andrew
Re: Select into Temporary Table
Posted by Andrew O <ao...@gmail.com>.
For "select into", I'm not sure on a semantic difference in the expression
sense (although obviously "create table as" is more flexible in terms of
expressing primary keys / etc.) but will look to see if I can find any
further details. However on Sybase (where it originated?) it has an
implementation difference. Generally it is only for temporary tables
(unless other db options are set) and it is a minimally logged transaction
so will not show up in transaction logs (which for permanent tables would
likely be an issue for replication)
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X89970.htm
For the cursor topic, appreciate the clarification questions and
confirming that it may belong in Calcite (with appropriate rational).
Probably it makes sense for me to start a fresh thread as I get closer to
needing / looking at that.
Regards
Andrew
On Thu, 13 Jun 2019, 18:32 Julian Hyde, <jh...@apache.org> wrote:
> I’d still love to hear how the semantics of SELECT INTO differ from CREATE
> TABLE AS SELECT.
>
> When proposing features to Calcite, there needs to be a rationale as well
> as code. I have a similar problem with a recent PR that implements ALTER
> VIEW and apparently does exactly the same as CREATE OR REPLACE VIEW.
>
> Can you explain the use case for cursor management? What is a cursor? Does
> it exist on the server side or the client? Is it shared among connections?
> What can a user accomplish with cursor support that they cannot without it?
>
> As cursor management is in the SQL Standard, and needs parser support,
> there is a good case for adding it to Babel. Or even to the core parser.
>
> Julian
>
>
> > On Jun 13, 2019, at 10:26 AM, Andrew O <ao...@gmail.com> wrote:
> >
> > Thanks for the reply. As a couple of follow on questions:
> >
> > By the looks of it, Babel currently only handles query style statements
> rather than an DDL statement. Would it ever extend to cover DDL
> statements for different dialects or is that the role of the server
> parser?
> >
> > Also, would cursor management (declare, fetch, etc.) statements fit in
> Babel or belong somewhere else?
> >
> > Thanks
> >
> > Andrew
> >
> > On Wed, 12 Jun 2019, 17:10 Julian Hyde, <jhyde.apache@gmail.com <mailto:
> jhyde.apache@gmail.com>> wrote:
> > I see this as functionality for the Babel parser, not the core parser,
> because it helps with compatibility but adds no expressive power.
> >
> > No one has yet explained why this syntax was introduced into those
> engines that have it, eg postgres. It wasn’t “historical reasons” for them.
> Just curious.
> >
> > On Jun 12, 2019, at 12:12 AM, Andrew O <ao2596599@gmail.com <mailto:
> ao2596599@gmail.com>> wrote:
> >
> >> Translating could work for my use case, although it may be counter to
> some of the other recent discussions where the bias was to keep the parser
> just doing parsing and no more (so translation would need to happen in
> another step?).
> >>
> >> (and Yes, as you suggest, essentially this trying to parse valid
> Postgres SQL)
> >>
> >> Thanks
> >>
> >> Andrew
> >>
> >> On Tue, 11 Jun 2019, 23:03 Haisheng Yuan, <h.yuan@alibaba-inc.com
> <ma...@alibaba-inc.com>> wrote:
> >> For historical reasons, perhaps. We need to parse and translate into
> CREATE TABLE AS SELECT... if we are going to support this syntax for
> Postgres and SQL Server.
> >>
> >> - Haisheng
> >>
> >> ------------------------------------------------------------------
> >> 发件人：Julian Hyde<jhyde.apache@gmail.com <ma...@gmail.com>>
> >> 日 期：2019年06月12日 05:41:38
> >> 收件人：<dev@calcite.apache.org <ma...@calcite.apache.org>>
> >> 主 题：Re: Select into Temporary Table
> >>
> >> I’ve never understood why some SQL dialects have “SELECT ... INTO
> table”. What’s wrong with “INSERT INTO table SELECT ...”?
> >>
> >> Julian
> >>
> >> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao2596599@gmail.com <mailto:
> ao2596599@gmail.com>> wrote:
> >> >
> >> > Does / should Calcite support select into expressions? E.g. I'm using
> v1.19
> >> > with queries of the style:
> >> >
> >> > select * into temporary table "#myTempResults" from (select colA
> >> > from my Table where colA = 'abc')
> >> >
> >> > but these queries fail to parse at the "into" words in the
> expression. (I
> >> > have a calcite connection setup with SqlDdlParserImpl)
> >> >
> >> > Assuming this isn't currently supported, is this something that is in
> the
> >> > scope of the default Calcite code, or something that would need to
> be a
> >> > custom extension?
> >> >
> >> > I do see in Schema.TableType there is an enum value of
> TEMPORARY_TABLE, but
> >> > I have found where us this set / used.
> >> >
> >> > Also, as additional context
> >> > - my target DB for the queries is Postgres (so supports this SQL query
> >> > directly)
> >> > - also in the future I would like to support parsing / processing
> Cursor
> >> > related operations (e.g. Declare Cursor, fetch next, fetch next,
> etc.).
> >> > Although I'm sure if / where these would belong in Calcite.
> >> >
> >> > Thanks in advance
> >> >
> >> > Andrew
>
>
Re: Select into Temporary Table
Posted by Julian Hyde <jh...@apache.org>.
I’d still love to hear how the semantics of SELECT INTO differ from CREATE TABLE AS SELECT.
When proposing features to Calcite, there needs to be a rationale as well as code. I have a similar problem with a recent PR that implements ALTER VIEW and apparently does exactly the same as CREATE OR REPLACE VIEW.
Can you explain the use case for cursor management? What is a cursor? Does it exist on the server side or the client? Is it shared among connections? What can a user accomplish with cursor support that they cannot without it?
As cursor management is in the SQL Standard, and needs parser support, there is a good case for adding it to Babel. Or even to the core parser.
Julian
> On Jun 13, 2019, at 10:26 AM, Andrew O <ao...@gmail.com> wrote:
>
> Thanks for the reply. As a couple of follow on questions:
>
> By the looks of it, Babel currently only handles query style statements rather than an DDL statement. Would it ever extend to cover DDL statements for different dialects or is that the role of the server parser?
>
> Also, would cursor management (declare, fetch, etc.) statements fit in Babel or belong somewhere else?
>
> Thanks
>
> Andrew
>
> On Wed, 12 Jun 2019, 17:10 Julian Hyde, <jhyde.apache@gmail.com <ma...@gmail.com>> wrote:
> I see this as functionality for the Babel parser, not the core parser, because it helps with compatibility but adds no expressive power.
>
> No one has yet explained why this syntax was introduced into those engines that have it, eg postgres. It wasn’t “historical reasons” for them. Just curious.
>
> On Jun 12, 2019, at 12:12 AM, Andrew O <ao2596599@gmail.com <ma...@gmail.com>> wrote:
>
>> Translating could work for my use case, although it may be counter to some of the other recent discussions where the bias was to keep the parser just doing parsing and no more (so translation would need to happen in another step?).
>>
>> (and Yes, as you suggest, essentially this trying to parse valid Postgres SQL)
>>
>> Thanks
>>
>> Andrew
>>
>> On Tue, 11 Jun 2019, 23:03 Haisheng Yuan, <h.yuan@alibaba-inc.com <ma...@alibaba-inc.com>> wrote:
>> For historical reasons, perhaps. We need to parse and translate into CREATE TABLE AS SELECT... if we are going to support this syntax for Postgres and SQL Server.
>>
>> - Haisheng
>>
>> ------------------------------------------------------------------
>> 发件人：Julian Hyde<jhyde.apache@gmail.com <ma...@gmail.com>>
>> 日 期：2019年06月12日 05:41:38
>> 收件人：<dev@calcite.apache.org <ma...@calcite.apache.org>>
>> 主 题：Re: Select into Temporary Table
>>
>> I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”?
>>
>> Julian
>>
>> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao2596599@gmail.com <ma...@gmail.com>> wrote:
>> >
>> > Does / should Calcite support select into expressions? E.g. I'm using v1.19
>> > with queries of the style:
>> >
>> > select * into temporary table "#myTempResults" from (select colA
>> > from my Table where colA = 'abc')
>> >
>> > but these queries fail to parse at the "into" words in the expression. (I
>> > have a calcite connection setup with SqlDdlParserImpl)
>> >
>> > Assuming this isn't currently supported, is this something that is in the
>> > scope of the default Calcite code, or something that would need to be a
>> > custom extension?
>> >
>> > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but
>> > I have found where us this set / used.
>> >
>> > Also, as additional context
>> > - my target DB for the queries is Postgres (so supports this SQL query
>> > directly)
>> > - also in the future I would like to support parsing / processing Cursor
>> > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
>> > Although I'm sure if / where these would belong in Calcite.
>> >
>> > Thanks in advance
>> >
>> > Andrew
Re: Select into Temporary Table
Posted by Andrew O <ao...@gmail.com>.
Thanks for the reply. As a couple of follow on questions:
By the looks of it, Babel currently only handles query style statements
rather than an DDL statement. Would it ever extend to cover DDL
statements for different dialects or is that the role of the server
parser?
Also, would cursor management (declare, fetch, etc.) statements fit in
Babel or belong somewhere else?
Thanks
Andrew
On Wed, 12 Jun 2019, 17:10 Julian Hyde, <jh...@gmail.com> wrote:
> I see this as functionality for the Babel parser, not the core parser,
> because it helps with compatibility but adds no expressive power.
>
> No one has yet explained why this syntax was introduced into those engines
> that have it, eg postgres. It wasn’t “historical reasons” for them. Just
> curious.
>
> On Jun 12, 2019, at 12:12 AM, Andrew O <ao...@gmail.com> wrote:
>
> Translating could work for my use case, although it may be counter to
> some of the other recent discussions where the bias was to keep the parser
> just doing parsing and no more (so translation would need to happen in
> another step?).
>
> (and Yes, as you suggest, essentially this trying to parse valid
> Postgres SQL)
>
> Thanks
>
> Andrew
>
> On Tue, 11 Jun 2019, 23:03 Haisheng Yuan, <h....@alibaba-inc.com> wrote:
>
>> For historical reasons, perhaps. We need to parse and translate into
>> CREATE TABLE AS SELECT... if we are going to support this syntax for
>> Postgres and SQL Server.
>>
>> - Haisheng
>>
>> ------------------------------------------------------------------
>> 发件人：Julian Hyde<jh...@gmail.com>
>> 日 期：2019年06月12日 05:41:38
>> 收件人：<de...@calcite.apache.org>
>> 主 题：Re: Select into Temporary Table
>>
>> I’ve never understood why some SQL dialects have “SELECT ... INTO table”.
>> What’s wrong with “INSERT INTO table SELECT ...”?
>>
>> Julian
>>
>> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
>> >
>> > Does / should Calcite support select into expressions? E.g. I'm using
>> v1.19
>> > with queries of the style:
>> >
>> > select * into temporary table "#myTempResults" from (select colA
>> > from my Table where colA = 'abc')
>> >
>> > but these queries fail to parse at the "into" words in the expression.
>> (I
>> > have a calcite connection setup with SqlDdlParserImpl)
>> >
>> > Assuming this isn't currently supported, is this something that is in
>> the
>> > scope of the default Calcite code, or something that would need to be a
>> > custom extension?
>> >
>> > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE,
>> but
>> > I have found where us this set / used.
>> >
>> > Also, as additional context
>> > - my target DB for the queries is Postgres (so supports this SQL query
>> > directly)
>> > - also in the future I would like to support parsing / processing Cursor
>> > related operations (e.g. Declare Cursor, fetch next, fetch next,
>> etc.).
>> > Although I'm sure if / where these would belong in Calcite.
>> >
>> > Thanks in advance
>> >
>> > Andrew
>>
>
Re: Select into Temporary Table
Posted by Julian Hyde <jh...@gmail.com>.
I see this as functionality for the Babel parser, not the core parser, because it helps with compatibility but adds no expressive power.
No one has yet explained why this syntax was introduced into those engines that have it, eg postgres. It wasn’t “historical reasons” for them. Just curious.
> On Jun 12, 2019, at 12:12 AM, Andrew O <ao...@gmail.com> wrote:
>
> Translating could work for my use case, although it may be counter to some of the other recent discussions where the bias was to keep the parser just doing parsing and no more (so translation would need to happen in another step?).
>
> (and Yes, as you suggest, essentially this trying to parse valid Postgres SQL)
>
> Thanks
>
> Andrew
>
>> On Tue, 11 Jun 2019, 23:03 Haisheng Yuan, <h....@alibaba-inc.com> wrote:
>> For historical reasons, perhaps. We need to parse and translate into CREATE TABLE AS SELECT... if we are going to support this syntax for Postgres and SQL Server.
>>
>> - Haisheng
>>
>> ------------------------------------------------------------------
>> 发件人：Julian Hyde<jh...@gmail.com>
>> 日 期：2019年06月12日 05:41:38
>> 收件人：<de...@calcite.apache.org>
>> 主 题：Re: Select into Temporary Table
>>
>> I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”?
>>
>> Julian
>>
>> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
>> >
>> > Does / should Calcite support select into expressions? E.g. I'm using v1.19
>> > with queries of the style:
>> >
>> > select * into temporary table "#myTempResults" from (select colA
>> > from my Table where colA = 'abc')
>> >
>> > but these queries fail to parse at the "into" words in the expression. (I
>> > have a calcite connection setup with SqlDdlParserImpl)
>> >
>> > Assuming this isn't currently supported, is this something that is in the
>> > scope of the default Calcite code, or something that would need to be a
>> > custom extension?
>> >
>> > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but
>> > I have found where us this set / used.
>> >
>> > Also, as additional context
>> > - my target DB for the queries is Postgres (so supports this SQL query
>> > directly)
>> > - also in the future I would like to support parsing / processing Cursor
>> > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
>> > Although I'm sure if / where these would belong in Calcite.
>> >
>> > Thanks in advance
>> >
>> > Andrew
Re: Re: Select into Temporary Table
Posted by Andrew O <ao...@gmail.com>.
Translating could work for my use case, although it may be counter to some
of the other recent discussions where the bias was to keep the parser just
doing parsing and no more (so translation would need to happen in another
step?).
(and Yes, as you suggest, essentially this trying to parse valid Postgres
SQL)
Thanks
Andrew
On Tue, 11 Jun 2019, 23:03 Haisheng Yuan, <h....@alibaba-inc.com> wrote:
> For historical reasons, perhaps. We need to parse and translate into
> CREATE TABLE AS SELECT... if we are going to support this syntax for
> Postgres and SQL Server.
>
> - Haisheng
>
> ------------------------------------------------------------------
> 发件人：Julian Hyde<jh...@gmail.com>
> 日 期：2019年06月12日 05:41:38
> 收件人：<de...@calcite.apache.org>
> 主 题：Re: Select into Temporary Table
>
> I’ve never understood why some SQL dialects have “SELECT ... INTO table”.
> What’s wrong with “INSERT INTO table SELECT ...”?
>
> Julian
>
> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
> >
> > Does / should Calcite support select into expressions? E.g. I'm using
> v1.19
> > with queries of the style:
> >
> > select * into temporary table "#myTempResults" from (select colA
> > from my Table where colA = 'abc')
> >
> > but these queries fail to parse at the "into" words in the expression. (I
> > have a calcite connection setup with SqlDdlParserImpl)
> >
> > Assuming this isn't currently supported, is this something that is in the
> > scope of the default Calcite code, or something that would need to be a
> > custom extension?
> >
> > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE,
> but
> > I have found where us this set / used.
> >
> > Also, as additional context
> > - my target DB for the queries is Postgres (so supports this SQL query
> > directly)
> > - also in the future I would like to support parsing / processing Cursor
> > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
> > Although I'm sure if / where these would belong in Calcite.
> >
> > Thanks in advance
> >
> > Andrew
>
Re: Re: Select into Temporary Table
Posted by Haisheng Yuan <h....@alibaba-inc.com>.
For historical reasons, perhaps. We need to parse and translate into CREATE TABLE AS SELECT... if we are going to support this syntax for Postgres and SQL Server.
- Haisheng
------------------------------------------------------------------
发件人：Julian Hyde<jh...@gmail.com>
日 期：2019年06月12日 05:41:38
收件人：<de...@calcite.apache.org>
主 题：Re: Select into Temporary Table
I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”?
Julian
> On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
>
> Does / should Calcite support select into expressions? E.g. I'm using v1.19
> with queries of the style:
>
> select * into temporary table "#myTempResults" from (select colA
> from my Table where colA = 'abc')
>
> but these queries fail to parse at the "into" words in the expression. (I
> have a calcite connection setup with SqlDdlParserImpl)
>
> Assuming this isn't currently supported, is this something that is in the
> scope of the default Calcite code, or something that would need to be a
> custom extension?
>
> I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but
> I have found where us this set / used.
>
> Also, as additional context
> - my target DB for the queries is Postgres (so supports this SQL query
> directly)
> - also in the future I would like to support parsing / processing Cursor
> related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
> Although I'm sure if / where these would belong in Calcite.
>
> Thanks in advance
>
> Andrew
Re: Select into Temporary Table
Posted by Julian Hyde <jh...@apache.org>.
In answer to your question, no I don't think Calcite should support SELECT INTO.
On Tue, Jun 11, 2019 at 2:53 PM Andrew O <ao...@gmail.com> wrote:
>
> Indeed, unfortunately in this case it's pre-generated SQL so unfortunately
> is a fixed input style.
>
> On Tue, 11 Jun 2019, 22:41 Julian Hyde, <jh...@gmail.com> wrote:
>
> > I’ve never understood why some SQL dialects have “SELECT ... INTO table”.
> > What’s wrong with “INSERT INTO table SELECT ...”?
> >
> > Julian
> >
> > > On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
> > >
> > > Does / should Calcite support select into expressions? E.g. I'm using
> > v1.19
> > > with queries of the style:
> > >
> > > select * into temporary table "#myTempResults" from (select colA
> > > from my Table where colA = 'abc')
> > >
> > > but these queries fail to parse at the "into" words in the expression. (I
> > > have a calcite connection setup with SqlDdlParserImpl)
> > >
> > > Assuming this isn't currently supported, is this something that is in the
> > > scope of the default Calcite code, or something that would need to be a
> > > custom extension?
> > >
> > > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE,
> > but
> > > I have found where us this set / used.
> > >
> > > Also, as additional context
> > > - my target DB for the queries is Postgres (so supports this SQL query
> > > directly)
> > > - also in the future I would like to support parsing / processing Cursor
> > > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
> > > Although I'm sure if / where these would belong in Calcite.
> > >
> > > Thanks in advance
> > >
> > > Andrew
> >
Re: Select into Temporary Table
Posted by Andrew O <ao...@gmail.com>.
Indeed, unfortunately in this case it's pre-generated SQL so unfortunately
is a fixed input style.
On Tue, 11 Jun 2019, 22:41 Julian Hyde, <jh...@gmail.com> wrote:
> I’ve never understood why some SQL dialects have “SELECT ... INTO table”.
> What’s wrong with “INSERT INTO table SELECT ...”?
>
> Julian
>
> > On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
> >
> > Does / should Calcite support select into expressions? E.g. I'm using
> v1.19
> > with queries of the style:
> >
> > select * into temporary table "#myTempResults" from (select colA
> > from my Table where colA = 'abc')
> >
> > but these queries fail to parse at the "into" words in the expression. (I
> > have a calcite connection setup with SqlDdlParserImpl)
> >
> > Assuming this isn't currently supported, is this something that is in the
> > scope of the default Calcite code, or something that would need to be a
> > custom extension?
> >
> > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE,
> but
> > I have found where us this set / used.
> >
> > Also, as additional context
> > - my target DB for the queries is Postgres (so supports this SQL query
> > directly)
> > - also in the future I would like to support parsing / processing Cursor
> > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
> > Although I'm sure if / where these would belong in Calcite.
> >
> > Thanks in advance
> >
> > Andrew
>
Re: Select into Temporary Table
Posted by Julian Hyde <jh...@gmail.com>.
I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”?
Julian
> On Jun 11, 2019, at 1:27 PM, Andrew O <ao...@gmail.com> wrote:
>
> Does / should Calcite support select into expressions? E.g. I'm using v1.19
> with queries of the style:
>
> select * into temporary table "#myTempResults" from (select colA
> from my Table where colA = 'abc')
>
> but these queries fail to parse at the "into" words in the expression. (I
> have a calcite connection setup with SqlDdlParserImpl)
>
> Assuming this isn't currently supported, is this something that is in the
> scope of the default Calcite code, or something that would need to be a
> custom extension?
>
> I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but
> I have found where us this set / used.
>
> Also, as additional context
> - my target DB for the queries is Postgres (so supports this SQL query
> directly)
> - also in the future I would like to support parsing / processing Cursor
> related operations (e.g. Declare Cursor, fetch next, fetch next, etc.).
> Although I'm sure if / where these would belong in Calcite.
>
> Thanks in advance
>
> Andrew