From: camidei@Ingres.COM (Chris Amidei)
Newsgroups: comp.databases
Subject: Re: Proposed Bug in Sybase 4.2
Date: 20 Aug 92 19:09:52 GMT
Organization: Ingres, an ASK Company, Alameda CA 94501
X-Newsreader: Tin 1.1 PL3

albert@HPLWK.HPL.HP.COM (Joseph Albert) writes:
: References: <1992Aug19.180433.16976@infonode.ingr.com>
: Sender: 
: Followup-To: 
: Distribution: 
: Organization: Hewlett-Packard Laboratories -- Database Technology Dept
: Keywords: 
: 
: In article <1992Aug19.180433.16976@infonode.ingr.com> srb@barakam.b17d.ingr.com (Sreedhar Barakam) writes:
: >I have a Sybase 4.2 server(on SunOS 4.1.2) which has two tables
: >t1,t2. They are populated like this:
: >
: >  t1  t2
: >  c1  c1
: >  1   1
: >      2
: >      3
: >      4
: >      5
: >      6
: >      7
: >      8
: >      9
: >     10
: >
: >For both of the following queries:
: >
: > select t1.c1 from t1,t2 where t1.c1 < t2.c1  
: >select * from t1 where c1 <any (select c1 from t2)
: >

: >The results are:
: > c1
: > -----------
: >           1
: >           1
: >           1
: >           1
: >           1
: >           1
: >           1
: >           1
: >           1
: 
: This is NOT a bug, but the correct answer to the query according to the
: specified semantics of SQL. (If you don't agree with the semantics of
: SQL, that is a different issue.)
: 
: When you write a query, 
: 
: SELECT t1.c1 FROM t1,t2 WHERE t1.c1 < t2.c1  
: 
: you are asking for a result which could be defined by:
: 
: 1. take the cross product of t1 and t2
: 2. select those tuples from this cross product in which t1.c1 < t2.c2
: 3. project the result of step 2 onto the column t1.c1
: 
: of course, the dbms does not have to compute according to this 3-step
: procedure, i'm just using it to define the correct result.
: 
: the query you seem to be interested in is either:
: 
: a. select all t1 tuples for which there exists a t2 tuple with t1.c1 < t2.c1
: 
: -or-
: 
: b. select all t1 tuples for which t1.c1 is smaller than the c1 field of
:    every t2 tuple.
: 
: such existential and universal quantification is most naturally expressed
: in the domain calculus, but, alas, for better or worse we are stuck with
: SQL as a standard. in SQL, one uses the EXISTS function, which is really
: a test for emptiness of a correlated subquery (which can be used to construct
: queries semantically equivalent to any query requiring a quantifier in
: relational calculus):
: 
: a. SELECT c1 FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.c1 < t2.c1);
: 
: b. SELECT c1 FROM t1 WHERE NOT EXISTS 
:                 (SELECT * FROM t2 WHERE ((t1.c1 > t2.c2) OR (t1.c1 = t2.c2)));
: 
: 
: I'm surprised that the Sybase customer support people thought that
: this was a bug.
: 
: Joseph Albert
: albert@hplabs.hp.com

Take a look at page 41 of the current Ansi Standard (X3.135-1989) under
General Rules. 

Processing a flat join is not the same as processing a subquery.  Yes, the
answer would be correct for a flat join.  But, the query in question is using
a subquery.  For the subquery version, I believe the result should be a single
value of '1', at least the way I read the standard.

This still does not mean it's a bug.  It still depends on what the behavior is
according to SYBASE documentation.

--
The last thing the bungee jumper heard: Gee, I thought I was next?
------------------------------------------------------------------------
Chris Amidei		| E-mail: camidei@ingres.com
Instructor        	|  Phone: (510)748-3261 - my desk
Ingres, an Ask Company	|       : (510)769-1400 - main line