Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!cs.utexas.edu!uunet!murtoa.cs.mu.oz.au!munnari.oz.au!otc!metro!kylie!christie
From: christie@kylie.oz (Chris Tham)
Newsgroups: comp.databases
Subject: Sybase bug?
Message-ID: <613@kylie.oz>
Date: 3 Jul 89 00:02:32 GMT
Reply-To: christie@kylie.oz ()
Organization: Optech Research Pty. Ltd.
Lines: 41

I found the following anomaly and wondered if it is a bug in Sybase or
normal in SQL:
(All lines beginning with '>' are typed by me)

> create table hello(a int)
> create table world(b int)
> insert into hello(a) values (1)
(1 row affected)
> select * from hello where a = 1
 a
 -----------
           1

(1 row affected)
> select * from hello where a = 1 or a in (select b from world)
 a
 -----------

(0 rows affected)
> insert into world(b) values (2)
(1 row affected)
> select * from hello where a = 1 or a in (select b from world)
 a
 -----------
           1

(1 row affected)
> drop table hello,world

As you can see, the "select" with a subselect in an "or" clause fails if the
subselect fails but succeeds if the subselect succeeds.

If this is in fact not a bug but proper behaviour, can anyone tell me
the proper way to phrase this query?

I await comments with anticipation.  Thank you.
-- 
Internet: christie@kylie.otr.oz		"I'm Pink, therefore I'm Spam"
JANET: christie%kylie.oz@uk.cc.ucl.cs	Phone: +612 235-0255
UUCP: {uunet,hplabs,mcvax,ukc,nttlab}!munnari!christie@kylie.otr.oz
Mail: Optech Research Pty Ltd, Level 60 MLC Centre Sydney NSW 2000 AUSTRALIA