From: leland@cs.columbia.edu (Lee Woodbury)
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Organization: Columbia University, Dept. of Computer Science, NYC
Date: Fri, 20 Nov 1992 21:09:59 GMT

In article <1992Nov15.144258.18635@panix.com> dbenua@panix.com (David Benua) writes:
>In <1992Nov14.174847.29576@lamont.ldgo.columbia.edu> msolda@lamont.ldgo.columbia.edu (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
leland@cs.columbia.edu

----------------------------------------------------------------------
/* 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: leland@cs.columbia.edu
  USENET: ...!columbia!cs.columbia.edu!leland
  BITNET: leland%cs.columbia.edu@cuvmb
  USMAIL: Columbia Univ., 457 CS, 500 W. 120 St., NYC 10027-6699