From: wfinnert@larry.shearson.com (Warren Finnerty)
Subject: Re: Sybase 4.9.1 Returns the Wrong Rows
Organization: Lehman Brothers
	<1jju7fINNdtl@gazette.bcm.tmc.edu>
	<1993Jan20.183702.14919@eplrx7.es.duPont.com>
Date: Thu, 21 Jan 1993 05:09:10 GMT

In article <1993Jan20.183702.14919@eplrx7.es.duPont.com> hanafey@eplrx7.es.dupont.com (Mike Hanafey) writes:
>
>   In article mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons) writes:
>   |> 
>   |> In article <1993Jan19.134419.27020@eplrx7.es.duPont.com>, hanafey@eplrx7.es.dupont.com (Mike Hanafey) writes:
>   |> > Sybase returns the wrong result for the following simple query:
>   |> > 
>   |> > 	1> select c.c1 from c where (select count(*) from b where b.c1 = c.c1) > 1
>   |> > 	2> go
>   |> > 	 c1          
>   |> > 	 ----------- 
>   |> > 	           2 
>   |> > 	           2 
>   |> > 	           3 
>   |> > 	           3 
>   |> > 	           3 
>   |> > 	(5 rows affected)
>   |> > 
>   |> > Given the tables below, the correct result should be zero rows selected:
>   |> > 
>   |> > 	1> select * from c
>   |> > 	2> go
>   |> > 	 c1          
>   |> > 	 ----------- 
>   |> > 	          1 
>   |> > 	          2 
>   |> >  	          2 
>   |> >  	          3 
>   |> >  	          3 
>   |> >  	          3 
>   |> > 	(6 rows affected)
>   |> > 
>   |> > 	1> select * from b
>   |> > 	2> go
>   |> >  	c1          c2          
>   |> > 		 ----------- ----------- 
>   |> >   	         1           0 
>   |> >   	         2           0 
>   |> >   	         3           0 
>   |> > 
>   |> > 	(3 rows affected)
>   |> > 
>   |> 
>   |> OOO! OOOO!  I can answer this one!!!
>   |> 
>   |> Your nested select does a test for b.c1=c.c1.  If we go through the tables
>   |> one step at a time we find that there's only one match for c1=1 so there's
>   |> no row in the results for c1=1.  In the case of c1=2 we get a count = 2, 
>   |> but notice that we'll get this count twice; why?  because SQL does not
>   |> distinguish between duplicate rows(not in the above select), i.e., there
>   |> are 2 times that b.c1 = c.c1 sooooooo, you get 2 results(the first time
>   |> the 2nd row of c matches against the 2nd row of b, the second time the
>   |> 3rd row of c mathces against the 2nd row of b).  Likewise on the
>   |> case of c1=3; you have 3 times that b.c1=c.c1, hence 3 resultant rows of
>   |> the same thing.
>   |> 
>   |> Therefore you get the 5 rows as shown above.  
>   |> 
>   |> If you want only one instance of each b.c1=c.c1 returned you could try:
>   |> 
>   |> 	select distinct c.c1 from c where . . . . 
>   |> 	       ^^^^^^^^
>   |> 
>   |> and this will give you
>   |> 
>   |> 	c1
>   |> 	--
>   |> 	2
>   |> 	3
>   |> 
>   |> As for why you think there should be 0 rows . . .. I'm confused . . 
>   |> could you explain why you think there should be 0 rows??  Just 
>   |> curious . . .
>   |> 
>   |> Did any of this make sense?? If not let me know . . . I'm not firing on
>   |> all pistons today!! ;-)
>   |> 
>   |> As for your other problem . . . still working on it . . . . Is
>   |> Friday here, yet!!!!!!!???????  ;-)
>   |> 
>   |> Mark
>   |> 
>   |> 	
>
>   The query is asking for rows in "c" that would join with more than one row in "b",
>   and there are no rows in "c" that meet this condition (in your response you swap
>   table "c" and "b", apparently this is what the Sybase optimizer does -- did you have
>   a hand in writing the Sybase optimizer? ;) ).
>
>   If in fact this is a Sybase error, I find it very disturbing that such a simple query
>   returns the wrong result.

Please ignore the first part of my previous post, I did not look close enough
at the data in "c" ( no more late night posts )
( I used "sysobjects c, sysindexes b" which missed the whole point ).

In 4.0.1 the results are even *WORSE* 


c1
---------
	2
	3

Yikes! This query should iterate once per outer row but the pess-imizer is
turning it into a join. It gets worse.......

select b.c1, count(*) from b 
where exists ( select * from c where c.c1 = b.c1 )
group by b.c1
having count(*) > 1

Bulletproof you think....
EXISTS should prevent it from being processed as a join....
No such luck.

c1
--------- -------
	1	1
	2	2
	3	3

Bummer. 
--
warren finnerty      | 388 Greenwich St.
Lehman Brothers      | NYC NY 10013
"Back off man!"      | wfinnert@shearson.com
 
Newsgroups: comp.databases.sybase