Re: Select into Temporary Table
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, 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 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.apa...@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 ao2596...@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, <mailto:h.y...@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 Hydemailto:jhyde.apa...@gmail.com>> > >> 日 期:2019年06月12日 05:41:38 > >> 收件人:mailto:dev@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 ao2596...@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
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 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, <mailto:jhyde.apa...@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 <mailto:ao2596...@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, > <mailto:h.y...@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 Hydemailto:jhyde.apa...@gmail.com>> >> 日 期:2019年06月12日 05:41:38 >> 收件人:mailto:dev@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 > > <mailto:ao2596...@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
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, 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 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, 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 >> 日 期:2019年06月12日 05:41:38 >> 收件人: >> 主 题: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 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
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 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, 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 >> 日 期:2019年06月12日 05:41:38 >> 收件人: >> 主 题: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 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
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, 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 > 日 期:2019年06月12日 05:41:38 > 收件人: > 主 题: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 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
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 日 期:2019年06月12日 05:41:38 收件人: 主 题: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 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
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 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, 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 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
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, 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 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
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 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