Re: ADQL - Careful what you wish for

From: Thomas McGlynn <tam-at-lheapop.gsfc.nasa.gov>
Date: Mon, 02 Feb 2004 14:23:50 -0500

Clive Page wrote:

> On Mon, 2 Feb 2004, Roy Williams wrote:
>
>

>>(1) Is there a "standard" SQL that all the databases understand, somthing
>>that is least common denominator, somthing not tied to a vendor? I seem to
>>recall SQL92, is that it? Is it ISO or ANSI approved? Is there a definition
>>document?

>
>
> Roy
>
> I'm sure Jim will give you a much more authoritative answer than I can on
> the question of standards for SQL and their implementation in practice,
> but just to give you an idea of the problems you might look at my
> analysis here
>
> http://wiki.astrogrid.org/bin/view/Astrogrid/DBMSmathFunctions
>
> of support for mathematical functions in:
> - our very own ADQL version 0.7
> - JDBC: a Sun-defined standard for connecting procedural languages to SQL
> - the SQL implementations of six relational DBMS in common use by
> astronomers.
>
> You will see that it's quite hard to write any mathematical function in
> SQL in a portable way. This site: http://troels.arvin.dk/db/rdbms/
> will give you a flavour of other practical problems.
>

Clearly there needs to be translation from the agreed upon standard to the internal query representation regardless of whether the standard is some particular version of SQL or some XML tree. For those of us with SQL based databases the question arises as to whether it is easier to modify an SQL string to accommodate changes in dialect, or to regenerate the entire SQL string from some preparsed XML.

Just to give a data point here... The HEASARC's Browse service runs on both Oracle and Sybase databases and the answer we have adopted is to use simple regular expression transformations of the query. So for us Sybase SQL is the 'standard' and Oracle is a dialect that we need to translate to.

Function names are relatively easy to isolate since they come immediately before '(' with no intervening operator. I'm sure that someone could deliberately craft a query that we'd have trouble
with, but in several years of operation I don't recall it ever happening. However, we don't support users entering SQL directly.

In practice the only issue we have is in the function to convert a number to a string (convert in Sybase and to_char in Oracle). We used to support Ingres and I believe there were rather more issues there, but there was no problem using the same approach. I see that 11 of the functions you list are identical in all systems and many of the one's that are not are easily mimicked using the standard set (e.g., cot = 1/tan).... So perhaps the glass is half full?

	Regards,
	Tom MCGlynn

Received on 2004-02-02Z20:24:20