SEO

I think I have discovered a bug in the handling of null values (vs NULL
values) passed as parameters to a stored proc.

I have always believed that the database handled NULL and null the
same. The following statement returns the expected results:

select case when NULL is null then 'SAME' else 'DIFFERENT' end from
sysibm.sysdummy1;

returns SAME.

BUT, If you call a proc with lower case null as a parameter, it will
not be treated as a NULL.

Here's an example proc:

CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
SPECIFIC NULLTEST
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL

P1: BEGIN

DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;

DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;

if PARM1 IS null THEN

OPEN c1;

ELSE

OPEN c2;

END IF;

END P1
;


CALL RG.NULLTEST(null);

returns PARM1 IS NOT NULL

BUT

CALL RG.NULLTEST(NULL);

PARM1 IS NULL

It's easy enough to code around, but frustrating when the database
violates a basic assumption.

Has anyone else encountered this issue?

Bob

syntego@gmail.com wrote:
> I think I have discovered a bug in the handling of null values (vs NULL
> values) passed as parameters to a stored proc.
>
> I have always believed that the database handled NULL and null the
> same. The following statement returns the expected results:
>
> select case when NULL is null then 'SAME' else 'DIFFERENT' end from
> sysibm.sysdummy1;
>
> returns SAME.
>
> BUT, If you call a proc with lower case null as a parameter, it will
> not be treated as a NULL.
>
> Here's an example proc:
>
> CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
> SPECIFIC NULLTEST
> MODIFIES SQL DATA
> NOT DETERMINISTIC
> NULL CALL
> LANGUAGE SQL
>
> P1: BEGIN
>
> DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
>
> DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
> SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
>
> if PARM1 IS null THEN
>
> OPEN c1;
>
> ELSE
>
> OPEN c2;
>
> END IF;
>
> END P1
> ;
>
>
> CALL RG.NULLTEST(null);
>
> returns PARM1 IS NOT NULL
>
> BUT
>
> CALL RG.NULLTEST(NULL);
>
> PARM1 IS NULL
>
> It's easy enough to code around, but frustrating when the database
> violates a basic assumption.
>
> Has anyone else encountered this issue?
I cannot reproduce this on DB2 9 GA.
Nonetheleless if your observation turns out to be confirmed it would be
a bug in the CLP, not the engine.
CLP does some amount of shallow parsing of the CALL statement to replace
literals with ? for IN/OUT arguments. Also in the very early days of
DB2 V7(!) CLP accepted strings without quotes. Here the NULL vs null
('null') would make sense.
Could it be you are on an old DB2 V7 client?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

I tested through both ODBC and CLP and here's what I found:

When I tested through RapidSQL using the IBM DB2 ODBC Driver
8.01.12.99, the proc returned incorrectly.

R1
PARM1 IS NOT NULL

I tried through the Command Line Processor and the proc returned
correctly.

(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.5

db2 => call rg.NULLTEST(null)


Result set 1
--------------

R1
-------------
PARM1 IS NULL

1 record(s) selected.

Return Status = 0

so it appears the problem may be with the ODBC driver.

-Bob

Serge Rielau wrote:
> syntego@gmail.com wrote:
> > I think I have discovered a bug in the handling of null values (vs NULL
> > values) passed as parameters to a stored proc.
> >
> > I have always believed that the database handled NULL and null the
> > same. The following statement returns the expected results:
> >
> > select case when NULL is null then 'SAME' else 'DIFFERENT' end from
> > sysibm.sysdummy1;
> >
> > returns SAME.
> >
> > BUT, If you call a proc with lower case null as a parameter, it will
> > not be treated as a NULL.
> >
> > Here's an example proc:
> >
> > CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
> > SPECIFIC NULLTEST
> > MODIFIES SQL DATA
> > NOT DETERMINISTIC
> > NULL CALL
> > LANGUAGE SQL
> >
> > P1: BEGIN
> >
> > DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > if PARM1 IS null THEN
> >
> > OPEN c1;
> >
> > ELSE
> >
> > OPEN c2;
> >
> > END IF;
> >
> > END P1
> > ;
> >
> >
> > CALL RG.NULLTEST(null);
> >
> > returns PARM1 IS NOT NULL
> >
> > BUT
> >
> > CALL RG.NULLTEST(NULL);
> >
> > PARM1 IS NULL
> >
> > It's easy enough to code around, but frustrating when the database
> > violates a basic assumption.
> >
> > Has anyone else encountered this issue?
> I cannot reproduce this on DB2 9 GA.
> Nonetheleless if your observation turns out to be confirmed it would be
> a bug in the CLP, not the engine.
> CLP does some amount of shallow parsing of the CALL statement to replace
> literals with ? for IN/OUT arguments. Also in the very early days of
> DB2 V7(!) CLP accepted strings without quotes. Here the NULL vs null
> ('null') would make sense.
> Could it be you are on an old DB2 V7 client?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> IOD Conference
> http://www.ibm.com/software/data/ond...ness/conf2006/

OK, that starts to make sense. Can you open a PMR?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Will do. Thanks.

Serge Rielau wrote:
> OK, that starts to make sense. Can you open a PMR?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> IOD Conference
> http://www.ibm.com/software/data/ond...ness/conf2006/

syntego@gmail.com wrote:
> Will do. Thanks.
Thanks, I asked backstage and they tried to repro. Without success.
So we are looking forward to your PMR to get to the bottom of this.

Cheers
Serge

Result:

> sqlprepare 1 "call RG.NULLTEST(null)" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
> fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS NULL
FetchAll: 1 rows fetched.
> sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
> sqlprepare 1 "call RG.NULLTEST(NULL)" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
> fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS NULL
FetchAll: 1 rows fetched.
> sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
> sqlprepare 1 "call RG.NULLTEST('null')" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
> fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS not NULL
FetchAll: 1 rows fetched.
> sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
> sqlprepare 1 "call RG.NULLTEST('NULL')" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
> fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS not NULL
FetchAll: 1 rows fetched.



--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

syntego@gmail.com wrote:

> Will do. Thanks.

Let me know when you open PMR (PMR number will be handy ...)

Jan M. Nelken

syntego@gmail.com wrote:
> I think I have discovered a bug in the handling of null values (vs NULL
> values) passed as parameters to a stored proc.
>
> I have always believed that the database handled NULL and null the
> same. The following statement returns the expected results:
>
> select case when NULL is null then 'SAME' else 'DIFFERENT' end from
> sysibm.sysdummy1;
>
> returns SAME.
>
> BUT, If you call a proc with lower case null as a parameter, it will
> not be treated as a NULL.
>
> Here's an example proc:
>
> CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
> SPECIFIC NULLTEST
> MODIFIES SQL DATA
> NOT DETERMINISTIC
> NULL CALL
> LANGUAGE SQL
>
> P1: BEGIN
>
> DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
>
> DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
> SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
>
> if PARM1 IS null THEN
>
> OPEN c1;
>
> ELSE
>
> OPEN c2;
>
> END IF;
>
> END P1
> ;
>
>
> CALL RG.NULLTEST(null);
>
> returns PARM1 IS NOT NULL
>
> BUT
>
> CALL RG.NULLTEST(NULL);
>
> PARM1 IS NULL
>
> It's easy enough to code around, but frustrating when the database
> violates a basic assumption.
>
> Has anyone else encountered this issue?
>
> Bob

Works properly for me, on Solaris (UDB 8.1.6).

Two points.

1) Is the version of the client and the server the same? (We had issues
here with the server crashing, when they were not.)

2) Have you tried casting the NULL as a VARCHAR to match the IN
parameter?

B.

Brian,

The server and the client are both at V8 FP 12. I'm running the Admin
Client on my workstation.

Here's the results of casting the null to a varchar(26) using RapidSQL
7.4 over ODBC:

call rg.nulltest(cast(null as varchar(26)))
R1
PARM1 IS NOT NULL

call rg.nulltest(cast(NULL as varchar(26)))

R1
PARM1 IS NOT NULL

The error seems to be ODBC related. Whenever we have used the DB2
Command Line Processor, the results return correctly.

FYI - I've submitted the case to our DBA team who will be creating the
PMR and will update the thread with the number when it gets created.

Thanks,

Bob

Brian Tkatch wrote:
> syntego@gmail.com wrote:
> > I think I have discovered a bug in the handling of null values (vs NULL
> > values) passed as parameters to a stored proc.
> >
> > I have always believed that the database handled NULL and null the
> > same. The following statement returns the expected results:
> >
> > select case when NULL is null then 'SAME' else 'DIFFERENT' end from
> > sysibm.sysdummy1;
> >
> > returns SAME.
> >
> > BUT, If you call a proc with lower case null as a parameter, it will
> > not be treated as a NULL.
> >
> > Here's an example proc:
> >
> > CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
> > SPECIFIC NULLTEST
> > MODIFIES SQL DATA
> > NOT DETERMINISTIC
> > NULL CALL
> > LANGUAGE SQL
> >
> > P1: BEGIN
> >
> > DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > if PARM1 IS null THEN
> >
> > OPEN c1;
> >
> > ELSE
> >
> > OPEN c2;
> >
> > END IF;
> >
> > END P1
> > ;
> >
> >
> > CALL RG.NULLTEST(null);
> >
> > returns PARM1 IS NOT NULL
> >
> > BUT
> >
> > CALL RG.NULLTEST(NULL);
> >
> > PARM1 IS NULL
> >
> > It's easy enough to code around, but frustrating when the database
> > violates a basic assumption.
> >
> > Has anyone else encountered this issue?
> >
> > Bob
>
> Works properly for me, on Solaris (UDB 8.1.6).
>
> Two points.
>
> 1) Is the version of the client and the server the same? (We had issues
> here with the server crashing, when they were not.)
>
> 2) Have you tried casting the NULL as a VARCHAR to match the IN
> parameter?
>
> B.

Here's the PMR:

PMR27545

Thanks All.

Jan M. Nelken wrote:
> syntego@gmail.com wrote:
>
> > Will do. Thanks.
>
> Let me know when you open PMR (PMR number will be handy ...)
>
> Jan M. Nelken