Comment on ADQL PR
patrick.dowler at nrc-cnrc.gc.ca
Wed Jun 8 11:49:04 PDT 2005
Another data point:
As far as I can tell, DB2 supports only "create table as ( select ... )" and not
"select into table ...". "select into" is only used to put values into variables.
We are running DB2 EEE 8.1.
On 8.6.2005 05:26, Francois Ochsenbein wrote:
> You are right Clive, the SQL92 standard effectively indicates that
> the "into" clause represents a set of variables. The SQL standardisation
> came only late, when it turned out that each data-base vendor implemented
> his own SQL dialect... Postgres accepts SELECT INTO TABLE table_name,
> MS-SQL and Sybase don't need the "TABLE" keyword, Oracle accepts
> it as a "record" cursor...
> Is the concept of variables and cursors of any use to ADQL ?
> >On Mon, 6 Jun 2005, Francois Ochsenbein wrote:
> >> Well, I'm surprised -- and doubt that Postgres really represents THE SQL
> >> standard ... SELECT INTO exists since the first versions of SQL,
> >> and does generates a table (or a relation if you prefer the "purist"
> >> relational language)
> >I hate to disagree with someone with as much experience as Francois, but
> >my reading of the SQL1992 Standard (the latest one I can find online) is
> >that SELECT INTO only refers to a [list of] variables, not a table. The
> >syntax is pretty clear, but the semantics in the Standard are pretty
> >hard to understand, so my interpretation could be wrong.
> >The PostgreSQL reference manual is quite helpful about describing any
> >differences between its syntax and that of Standard SQL. It has this to
> >say about SELECT INTO:
> >SQL92 uses SELECT ... INTO to represent selecting values into scalar
> >variables of a host program, rather than creating a new table. [snip] The
> >PostgreSQL usage of SELECT INTO to represent table creation is historical.
> >It is best to use CREATE TABLE AS for this purpose.
> >There is an excellent compilation of differences in the implementation of
> >SQL among popular DBMS at http://troels.arvin.dk/db/rdbms/ although
> >unfortunately it does not deal with this particular issue. But the only
> >conclusion that can be drawn from reading this compilation is that the
> >whole subject is a can of worms: *none* of the main DMBS vendors takes
> >*any* serious notice of *any* SQL Standards, so I don't see why our ADQL
> >should be constrained by them.
> >My suggestion is that we should continue to use SELECT INTO <newtable> in
> >the way that we originally planned, rather than have to get involvind in
> >adding a whole new syntax tree for CREATE TABLE AS, when we don't at
> >present support anything like CREATE TABLE.
> Francois Ochsenbein ------ Observatoire Astronomique de Strasbourg
> 11, rue de l'Universite F-67000 STRASBOURG Phone: +33-(0)390 24 24 29
> Email: francois at astro.u-strasbg.fr (France) Fax: +33-(0)390 24 24 32
Tel/Tél: (250) 363-6914 | fax/télécopieur: (250) 363-0045
Canadian Astronomy Data Centre | Centre canadien de donnees astronomiques
National Research Council Canada | Conseil national de recherches Canada
Government of Canada | Gouvernement du Canada
5071 West Saanich Road | 5071, chemin West Saanich
Victoria, BC | Victoria (C.-B.)
More information about the voql