Should this query raise an exception?
From: Steve Kass <skass_at_drew.edu>
Date: Fri, 16 Aug 2002 09:08:04 -0400
Message-ID: <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.
) v where b<>0
Date: Fri, 16 Aug 2002 09:08:04 -0400
Message-ID: <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
) v where b<>0
drop table t
Steve Kass
Drew University
Received on Fri Aug 16 2002 - 15:08:04 CEST