ADQL/XML non compliance with SQL?

[Martin Hill]

Hmm I haven't explained this very well and we seem to be getting diverted.

My point is that ADQL/XML pair-only UnionSearch/IntersectionSearch impose 
precedence on statements like this:

    SELECT * FROM table t WHERE t.i=t.z AND t.i=t.a AND t.b=t.e

when none is given in the original SQL, with implied performance loss at the SQL 
database.  The other stuff was just to point out that it's not simple to remove 
the implied brackets at the ADQL/XML->SQL stage.

I'm proposing that we give IntersectionSearch and UnionSearch from 2..unbound 
children so that ADQL/XML can in fact match SQL.

Cheers,

Martin

Ray Plante wrote:

> On Mon, 9 Feb 2004, Martin Hill wrote:
> 
>>I'm pretty sure this is correct (standard) SQL:
>>
>>SELECT * FROM table t WHERE t.i=t.z AND t.i=t.a AND t.b=t.e
>>
>>I would expect that an SQL database will look at those ANDs and work out which 
>>is the quickest set to solve first depending on internal indexing.
>>
>>However ADQL/XML (and SADQL v1.0!) only allow pairwise ANDs.  When transforming 
>>  ADQL/XML -> Database SQL, we must use brackets around each boolean construct, 
>>because we don't know what the original priority might have been, ie:
>>
>>    WHERE (f OR f) AND (t OR t) = f
>>
>>is different from
>>
>>    WHERE f OR (f AND t) OR t = t
>>
>>and as soon as we use brackets we lose multiple Unions/Intersections.
> 
> 
> As mentioned this is a question of precedence, so it's important to know 
> what the standard says.  The SQL-92 is a bit vague on the point of 
> precedence between AND and OR.  As best as I can follow the rules, AND & 
> OR have equal precedence, and therefore (in the absence of parentheses) 
> the precedence is left to right.  That is,
> 
>      WHERE a OR b AND c OR d
> 
> is equivalent to 
> 
>      WHERE ((a OR b) AND c) OR d
> 
> This is the precedence assumed by the JHU SQL->ADQL translator.  
> 
> (BTW, how it is actually evaluated is impl-dep, as long as the precedence 
> is preserved.)
> 
> It would be good if we could get a clarification from a standard-based 
> document (i.e. not just a vendor-specific manual).  I looked in Date & 
> Darwin's "Guide to the SQL Standard" but could not find a definitive 
> statement.  
> 
> Note that it is generally not a good idea, I believe, to insert 
> parentheses unless they are actually needed (or explicitly specified by 
> the user) as I think that this can degrade a database's ability to 
> optimize the query.  I could be wrong on this point.
> 
> 
>>On another point, isn't XOR a standard SQL boolean operator?
> 
> 
> It is *not* part of SQL-92.  
> 
> cheers,
> Ray
> 
> 

-- 
Martin Hill
Software Engineer
AstroGrid @ ROE
Tel: +44 7901 55 24 66
www.astrogrid.org