Re: Puzzle: What should this query return?

Ivan,

The 1=0 alone doesn't always exclude results, since

  select count(*) from T where 1=0

should return one row, and the puzzle is that the case expression includes a count() expression as one alternative (of course not one that happens here).

Steve

Ivan Demkovitch wrote:

> I think it shouldn't return anything since we have Where 1=0
>
> X
> --
>
> (0) Rows affected
>
> "Steve Kass" <skass_at_drew.edu> wrote in message
> news:3D1CC3BC.203279AC_at_drew.edu...
> > What should this query return, assuming T contains one row?
> >
> > select case when 1=1 then 12 else count(0) end as X from T where 1=0
> >
> > SQL Server says:
> >
> > X
> > --
> > 12
> >
> > Ocelot says:
> > [OCELOT][OCELOT]CLI specific: Overflow while allocating RC, table size =
> <768> stmt count = <51>
> >
> > Similar query:
> >
> > select case when 1=0 then 12 else 1+count(0) end as X from T where 1=0
> >
> > SQL Server says:
> >
> > X
> > --
> > 1
> >
> > Ocelot says:
> >
> > X
> > --
> > 0
> >
> > What does the standard say?
> >
> > Steve Kass
> > Drew University
> >
Received on Fri Jun 28 2002 - 23:05:07 CEST