ASE Traceflag 15316

Traceflag 15316:  If enabled, disables the fix made under CR 569947-1 and "opens up" the original fix 497066 to all ISNULL references. The CR 569947-1 fix was limiting the CR 497066 changes to only the ISNULLs under a SUBQOP transformed predicate under a subquery to avoid behavior regressions.

Traceflag 15336:  Allow the ISNULL() function to return a nullable datatype.

Prior to ASE 12.0, the convert() function did not have the option to specify the null-ability of the resulting datatype and by default results were not null.  When doing SELECT INTO, it was common practice to wrap values with ISNULL(col, null) to cause the resulting datatype to allow nulls, as ISNULL results always allowed nulls (which is sort of odd, because the intended purpose of the  isnull function is to change null values to a non-null value, so having it return a NULL was an unexpected usage). 

A change made in 15.0.2 ESD 6 under CR for a performance issue 497066 (“A NOT IN subquery transformed into NOT EXISTS might use table scans instead of available indexes when isnull() function is used on either side of the NOT IN.“) resulted in ISNULL() determining its output’s null-ability based on its input.  This broke code that was using ISNULL() to make non-nullable values nullable.  This caused applications to encounter error 233 “The column <name> in table <name> does not allow null values”.

CR 553556 in 15.0.3 ESD 1 introduced traceflag 15336 to revert the ISNULL() behavior back to returning nullable datatypes but also returned the performance problems with some NOT EXISTS subqueries.

CR 569947 in 15.0.3 ESD 3 limited the change made under CR 497066 to cases where isnull was used in a subquery (where the output null-ability is now determined by the input) while leaving the output nullable in all other uses.  Traceflag 15316 was introduced to turn this fix off resulting in isnull always returning a nullable result.

The expectation is that in 15.0.3 ESD 3 or later, neither traceflag should be needed.  The exception might be code that somehow relied on ISNULL() under a subquery to return a nullable result even when it’s input was not a null value.  In that case, turning these traceflags off might result in application code encountering error 233.  It should be possible to rewrite such code using convert() to specify the null-ability of the result.

Demonstration (compare  the "nulls" column in the sp_help output)
use tempdb
go
set switch on 15316 with override
go
select id, isnull(id,1) as id1, isnull(id,null) as idn into #withflag from sysobjects
go
set switch off 15316 with override
go
select id, isnull(id,1) as id1, isnull(id,null) as idn into #noflag from sysobjects
go
sp_help #noflag
go
sp_help #withflag
go