Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

comparing literal values to a query returning NULL returning TRUE (should be FALSE)

1 view
Skip to first unread message

mark87544

unread,
Apr 5, 2011, 2:24:33 PM4/5/11
to
Hi,

when i compare a literal or constant integer value to a query
returning a NULL value, the results are not valid. In other words, if
i have TSQL statement as:

if 1 = (select returns NULL) then the next TSQL statement is executed
as if the conditional returned as TRUE, but 1 does not equal a NULL.
The results are different when i execute same TSQL on ASE125.

please let me know if you need more information.

Thank you,
Mark

ASE version: Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/
RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/

----- RESULTS OF TSQL -----

USING constant[1]
1 !=[]yes
1 = []NO!!
1 = []NO!!
USING @l_int[1]
1 !=[]yes
1 !=[]yes
1 !=[]yes

----- COMPLETE TSQL -----

SET NOCOUNT ON
CREATE TABLE #singleint (anint int)
INSERT #singleint SELECT 1

-- sanity PRINTs
SELECT SUBSTRING(@@version,1,100)
SELECT anint "anint WHERE 1>0 " FROM #singleint WHERE 1>0
SELECT anint "anint WHERE 1=0 " FROM #singleint WHERE 1=0
SELECT anint "anint WHERE anint=0" FROM #singleint WHERE anint=0

PRINT "(SELECT anint FROM #singleint WHERE 1=0)"
IF (SELECT anint FROM #singleint WHERE 1=0) IS NULL PRINT "QUERY
ISNULL" ELSE PRINT "QUERY NOTNULL"

PRINT "(SELECT anint FROM #singleint WHERE anint=0)"
IF (SELECT anint FROM #singleint WHERE anint=0) IS NULL PRINT "QUERY
ISNULL" ELSE PRINT "QUERY NOTNULL"

DECLARE @l_int int
,@l_qry1 int
,@l_qry2 int

SELECT @l_int=1
SELECT @l_qry1=anint FROM #singleint WHERE 1=0
SELECT @l_qry2=anint FROM #singleint WHERE anint=0

-- begin tests
PRINT ""
PRINT "TEST: IS EQUAL -> SHOULD PRINT SIX 'it !='"
PRINT "USING constant[1]"
if 1 = NULL PRINT "1 = [%1!]NO!!",NULL else PRINT "1 !=
[%1!]yes",NULL
if 1 = (SELECT anint FROM #singleint WHERE 1=0) PRINT "1 =
[%1!]NO!!",@l_qry1 else PRINT "1 != [%1!]yes",@l_qry1
if 1 = (SELECT anint FROM #singleint WHERE anint=0) PRINT "1 =
[%1!]NO!!",@l_qry2 else PRINT "1 != [%1!]yes",@l_qry2

PRINT "USING @l_int[%1!]",@l_int
if @l_int = NULL PRINT "%1! =
[%1!]NO!!",@l_int,NULL else PRINT "%1! != [%2!]yes",@l_int,NULL
if @l_int = (SELECT anint FROM #singleint WHERE 1=0) PRINT "%1! =
[%2!]NO!!",@l_int,@l_qry1 else PRINT "%1! != [%2!]yes",@l_int,@l_qry1
if @l_int = (SELECT anint FROM #singleint WHERE anint=0) PRINT "%1! =
[%2!]NO!!",@l_int,@l_qry2 else PRINT "%1! != [%2!]yes",@l_int,@l_qry2

PRINT ""
PRINT "TEST: NOT EQUAL-> SHOULD PRINT SIX 'it !='",@l_int
PRINT "USING constant[1]"
if 1 != NULL PRINT "1 !=[%1!]yes",NULL else PRINT "1 =
[%1!]NO!!",NULL
if 1 != (SELECT anint FROM #singleint WHERE 1=0) PRINT "1 !
=[%1!]yes",@l_qry1 else PRINT "1 = [%1!]NO!!",@l_qry1
if 1 != (SELECT anint FROM #singleint WHERE anint=0) PRINT "1 !
=[%1!]yes",@l_qry2 else PRINT "1 = [%1!]NO!!",@l_qry2

PRINT "USING @l_int[%1!]",@l_int
if @l_int != NULL PRINT "%1! !=[%2!]yes",@l_int,NULL else
PRINT "%1! = [%2!]NO!!",@l_int,NULL
if @l_int != (SELECT anint FROM #singleint WHERE 1=0) PRINT "%1! !
=[%2!]yes",@l_int,@l_qry1 else PRINT "%1! = [%2!]NO!!",@l_int,@l_qry1
if @l_int != (SELECT anint FROM #singleint WHERE anint=0) PRINT "%1! !
=[%2!]yes",@l_int,@l_qry2 else PRINT "%1! = [%2!]NO!!",@l_int,@l_qry2

0 new messages