Re: Should this query raise an exception?

I *think* I get it now. Basically, if the inner query fails when you run it alone (it does), then why should it succeed if you merely filter the results by the outer query?

"Steve Kass" <skass_at_drew.edu> wrote in message news:3D5D361B.C4E72171_at_drew.edu...
> Alan,
>
> If the derived table
>
> select
> 1/a as R,
> b
> from t
>
> is materialized, there will be an exception. While ANSI defines the
> correct result of the full query in terms of this derived table, it
doesn't
> require it to be materialized. Is it an error to materialize it and throw
> an exception? Is it an error not to?
>
> There's no ambiguity regarding the rows that should be returned if the
> query succeeds, but should it?
>
> Steve
>
> Alan wrote:
>
> > I'm not sure I fully understand your question and how it relates to your
> > example, but from what I can see, the query will divide by 1, not 0, so
of
> > course there would not be an exception.
> >
> > "Steve Kass" <skass_at_drew.edu> wrote in message
> > news:3D5CF934.7079644E_at_drew.edu...
> > > The following returns a result set and no exception
> > > in Microsoft SQL Server, IBM DB2, or Ocelot, and I can't
> > > find anything in the SQL-92 standard to help me decide
> > > whether it should succeed according to ANSI.
> > >
> > > If the derived table were fully materialized, a divide-by-zero
> > > exception should occur, but the search condition excludes
> > > the row where the exception occurs.
> > >
> > > Since the usual explanation of how a query works logically
> > > refers to the entire derived query as the table source, should
> > > the query fail?
> > >
> > > create table t (
> > > a integer,
> > > b integer
> > > )
> > >
> > > insert into t values (0,0)
> > > insert into t values (1,1)
> > >
> > > select * from (
> > > select
> > > 1/a as R,
> > > b
> > > from t
> > > ) v where b<>0
> > >
> > > drop table t
> > >
> > > Steve Kass
> > > Drew University
> > >
>
Received on Fri Aug 16 2002 - 22:18:14 CEST