2529100 - Precedence of evaluation and behavior of NOT EXISTS & EXISTS

The following scenario: these kind of queries will take more time:

========

create proc p01 @param char(1)
as
if (@param = 'S' and not exists (select postalcode from authors where postalcode = "94704"))
begin
print "Berkeley author"
return
end
print "XXXXX"
return

create proc p02 @param char(1)
as
if (@param = 'S') and not exists (select postalcode from authors where postalcode = "94704")
begin
print "Berkeley author"
return
end
print "XXXXX"
return

========

Than this:

========

create proc p03 @param char(1)
as
if (@param = 'S' and exists (select postalcode from authors where postalcode = "94704"))
begin
print "Berkeley author"
return
end
print "XXXXX"
return

========

All procedures were executed passing "N" and the value "94704" doesn't exist on table.

When using "NOT EXISTS" the subquery is executed despite of variable “@param” is false (“N”) . In the developers's opinion this should not occur since the first part of is false.

They told the first operand should be completely evaluated and all side effects are completed before continuing evaluation of the logical AND expression. Then the second operand is evaluated only if the first operand evaluates to true (nonzero).

This doesn't occur when using "EXISTS".

• SAP Adaptive Server Enterprise (ASE) - All versions

performance NOT EXISTS precedence evaluation order operator parentheses expression , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem