Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!rutgers!cs.utexas.edu!uunet!daitc!daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: Sybase bug?
Message-ID: <571@daitc.daitc.mil>
Date: 4 Jul 89 13:35:47 GMT
References: <613@kylie.oz>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: DTIC Special Projects Office (DTIC-SPO), Alexandria VA
Lines: 38
In-reply-to: christie@kylie.oz (Chris Tham)

In article <613@kylie.oz>, christie@kylie (Chris Tham) writes:
>I found the following anomaly and wondered if it is a bug in Sybase or
>normal in SQL:  [test case looks like:

	+-------+			+-------+
	| hello |    a          	| world |    b
	+-------+------+		+-------+------+
		|    1 |			+------+
		+------+

	select * from hello where a = 1 or a in (select b from world);

			+--------+
			| result |    a
			+--------+------+
				 +------+

It's normal.  The semantics are usually unintended, however.  The
subquery implies a join to an empty table.  This means a cartesian
product where one matrix has size zero.  Or if you prefer, in the
relational model intersection is undefined on two relations if either
is empty.

To force the semantics you intend, use UNION:

	select a from hello where a = 1
	UNION
	select a from hello where a in (select b from world);

			+--------+
			| result |    a
			+--------+------+
				 |    1 |
				 +------+

-- Jon
-- 
--