Should this query raise an exception?

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 - 08:08:04 CDT