NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/database/sybase/372

Text File | 1992-11-20 | 4.2 KB | 117 lines

Newsgroups: comp.databases.sybase
Path: sparky!uunet!cs.utexas.edu!uwm.edu!zaphod.mps.ohio-state.edu!wupost!gumby!destroyer!sol.ctr.columbia.edu!news.cs.columbia.edu!leland
From: [e-mail] (Lee Woodbury)
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Message-ID: <[e-mail]>
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Sender: [e-mail] (The Daily News)
Organization: Columbia University, Dept. of Computer Science, NYC
References: <[e-mail]> <[e-mail]>
Date: Fri, 20 Nov 1992 21:09:59 GMT
Lines: 104

In article <[e-mail]> [e-mail] (David Benua) writes:
>In <[e-mail]> [e-mail] (M Solda) writes:
>
>>does anyone know if there is a limitation in the number of EXIST subqueries
>>that can be in a single query?  i have a query with an EXISTS and a NOT EXISTS
>>joined by an OR, but only the rows that meet the EXISTS criteria are reported.

We found (using version 4.2) a similar error that may or may not be
independent of Solda's.  Specifically, any query that has three (and
presumably more, though we didn't check) NOT EXISTS conditions doesn't
work in general.  You *may* get the right response depending on the
order of the NOT EXISTS clauses and on the data in the database.

Interested parties will find under my signature below a short,
self-contained script demonstrating the error.  In the script, the
same query is given six different ways (representing the six possible
orderings of the three NOT EXISTS clauses).  You'll see that the
responses vary (for the data given below, the query should return
no tuples).

I'd be interested to know how the script behaves on versions later than
4.2.

We never found a workaround.  We're developing an OPS5-to-SQL compiler
that generates SQL queries, so rather than figure out some arcane mess
whereby the compiler would have to detect and generate alternatives,
we just modified the source program that caused the problem and moved
on.  (It doesn't come up much.)  But I'd be interested to hear anybody
else's new ideas on the matter.

Leland Woodbury
[e-mail]

----------------------------------------------------------------------
/* create the three relations */
create table room (number char(8), capacity int)
create table group_clear (something int)
create table group_move (anything int)

/* insert data */
insert room values ("H201", 1)
insert room values ("H213", 3)
insert room values ("D12", 2)
insert room values ("D101", 1)
insert room values ("M313", 3)
insert room values ("M104", 4)
insert room values ("M122", 2)
insert room values ("B104", 4)
insert group_clear values (0)

/* try query with various orderings of the 'not exists' conditions */

/* 1 and 2 and 3 */
select * from room room1
where
    not exists (select * from room room2
        where room2.capacity > room1.capacity)
    and not exists (select * from group_clear)
    and not exists (select * from group_move)

/* 1 and 3 and 2 */
select * from room room1
where
    not exists (select * from room room2
        where room2.capacity > room1.capacity)
    and not exists (select * from group_move)
    and not exists (select * from group_clear)

/* 2 and 1 and 3 */
select * from room room1
where
    not exists (select * from group_clear)
    and not exists (select * from room room2
        where room2.capacity > room1.capacity)
    and not exists (select * from group_move)

/* 2 and 3 and 1 */
select * from room room1
where
    not exists (select * from group_clear)
    and not exists (select * from group_move)
    and not exists (select * from room room2
        where room2.capacity > room1.capacity)

/* 3 and 1 and 2 */
select * from room room1
where
    not exists (select * from group_move)
    and not exists (select * from room room2
        where room2.capacity > room1.capacity)
    and not exists (select * from group_clear)

/* 3 and 2 and 1 */
select * from room room1
where
    not exists (select * from group_move)
    and not exists (select * from group_clear)
    and not exists (select * from room room2
        where room2.capacity > room1.capacity)
--
INTERNET: [e-mail]
  USENET: ...!columbia!cs.columbia.edu!leland
  BITNET: leland%cs.columbia.edu@cuvmb
  USMAIL: Columbia Univ., 457 CS, 500 W. 120 St., NYC 10027-6699