Path: utzoo!attcan!uunet!mcvax!ukc!stl!stc!datlog!ajs
From: ajs@datlog.co.uk ( Andy Simms )
Newsgroups: comp.databases
Subject: Re: Sybase bug?
Summary: It is a bug
Message-ID: <2143@dlvax2.datlog.co.uk>
Date: 10 Jul 89 09:37:18 GMT
References: <637@cullsj.UUCP> <4982@sybase.sybase.com>
Reply-To: ajs@datlog.co.uk ( Andy Simms )
Organization: Data Logic Ltd, Queens House, Greenhill Way, Harrow, London.
Lines: 36

In article <4982@sybase.sybase.com> jeffl@sybase.Sybase.COM (Jeff Lichtman) writes:
>>> > select * from hello where a = 1 or a in (select b from world)
>> 
>
>The result of a SQL query is supposed to be as if the following operations
>were performed (in this order):
>
>	1) Cartesian product
>	2) Restriction (where clause)
>	3) Projection (select list)
>
>In this case, the Cartesian product of the tables contains no rows, so the
>answer contains no rows.
>
>A previous posting had it right: 'or' does not mean 'union'.
>---
>Jeff Lichtman at Sybase

You're wrong, because there is NO cartesian product involved here. The
(conceptual) cartesian product only applies when there is more than one
table named in the FROM clause; here we have a predicate based on a single
table with two conditions joined by a disjunction, the second condition
being a subquery. Now, given that the predicate "a = 1" is TRUE for the
single row in table "hello" and that "TRUE OR x" is TRUE for all three truth
values of x, the result of the subquery is immaterial: the predicate is
satisfied. In fact, irrelevant though it may be, the value of x is treated as
FALSE (rather then MAYBE) by ANSI SQL (see under <quantified predicate> 
case 2(d)).

If the Sybase query optimiser is transforming subqueries into joins, then
I suspect you have a nice little problem to solve here. Furthermore, lazy
evaluation suggests the subquery does not need to be executed at all (so
you certainly don't need to do any transformation).

Andy Simms
Data Logic (ajs@datlog.co.uk).