BUG DB2 V8 FP12存储过程参数 - NULL值区分大小写 [英] BUG DB2 V8 FP12 Stored Proc Parameters - NULL value is case sensitive
问题描述
我想我发现了一个处理空值的错误（vs NULL
值）作为参数传递给存储过程。
I一直认为数据库处理NULL并且空白
相同。以下语句返回预期结果：
选择NULL为空时的情况然后''相同''其他''不同''从
sysibm结束。 sysdummy1;
返回SAME。
但是，如果你调用一个小写null的proc作为参数，它将会
不被视为NULL。
这是一个示例proc：
CREATE PROCEDURE RG.NULLTEST（IN PARM1 VARCHAR（26））
SPECIFIC NULLTEST
修改SQL数据
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
P1：BEGIN
DECLARE c1 CURSOR返回给来电者
SELECT''PARM1 IS NULL''作为R1来自SYSIBM.SYSDUMMY1;
DECLARE c2 CURSOR返回给调用者
SELECT''PARM1 IS NOT NULL ''来自SYSIBM.SYSDUMMY1的R1;
如果PARM1为空，则为
OPEN c1;
ELSE
OPEN c2;
结束IF;
结束P1
;
CALL RG.NULLTEST（null）;
返回PARM1 IS NOT NULL
但是
CALL RG.NULLTEST（NULL）;
PARM1是空的
它很容易编码，但令人沮丧的是数据库
违反了一个基本假设。
还有其他人遇到过这个问题吗？
Bob
sy*****@gmail.com 写道：
我想我发现了一个处理空值的错误（vs NULL
值）存储过程的参数。
我一直认为数据库处理NULL并且空白
相同。以下语句返回预期结果：
选择NULL为空时的情况然后''相同''其他''不同''从
sysibm结束。 sysdummy1;
返回SAME。
但是，如果你调用一个小写null的proc作为参数，它将会
不被视为NULL。
这是一个示例proc：
CREATE PROCEDURE RG.NULLTEST（IN PARM1 VARCHAR（26））
SPECIFIC NULLTEST
修改SQL数据
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
P1：BEGIN
DECLARE c1 CURSOR返回给来电者
SELECT''PARM1 IS NULL''作为R1来自SYSIBM.SYSDUMMY1;
DECLARE c2 CURSOR返回给调用者
SELECT''PARM1 IS NOT NULL ''来自SYSIBM.SYSDUMMY1的R1;
如果PARM1为空，则为
OPEN c1;
ELSE
OPEN c2;
END IF ;
结束P1
;
CALL RG.NULLTEST（null）;
返回PARM1 IS NOT NULL
但是
CALL RG.NULLTEST（NULL）;
PARM1是空的
编码很容易，但数据库
违反基本假设时会感到沮丧。 br />
还有其他人遇到过这个问题吗？
我无法在DB2 9 GA上重现这一点。
如果您的观察结果证实它将是
$ CL $中的b $ ba bug，而不是引擎。
CLP对CALL语句进行了一些浅层解析来替换
文字？用于IN / OUT参数。也是在早期的
DB2 V7（！）CLP接受了没有引号的字符串。这里的NULL vs null
（''null''）是有意义的。
难道你是旧的DB2 V7客户端吗？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
< blockquote>我通过ODBC和CLP进行了测试，这是我发现的：
当我使用IBM DB2 ODBC驱动程序通过RapidSQL进行测试时
8.01 .12.99，proc返回错误。
R1
PARM1不为空
我试过命令行处理器和proc返回
正确。
（c）Copyright IBM Corporation 1993,2002
DB2命令行处理器SDK 8.2.5
db2 =调用rg.NUL LTEST（null）
结果集1
--------------
R1
-------------
PARM1 IS NULL
1条记录已选中。
返回状态= 0
所以看起来问题可能出在ODBC驱动程序上。
-Bob
Serge Rielau写道：
sy ***** @ gmail.com 写道：
我想我发现了处理空值的错误（vs NULL
值）作为参数传递给存储过程。
我一直认为数据库处理NULL并且null
相同。以下语句返回预期结果：
选择NULL为空时的情况然后''相同''其他''不同''从
sysibm结束。 sysdummy1;
返回SAME。
但是，如果你调用一个小写null的proc作为参数，它将会
不被视为NULL。
这是一个示例proc：
CREATE PROCEDURE RG.NULLTEST（IN PARM1 VARCHAR（26））
SPECIFIC NULLTEST
修改SQL数据
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
P1：BEGIN
DECLARE c1 CURSOR返回给来电者
SELECT''PARM1 IS NULL''作为R1来自SYSIBM.SYSDUMMY1;
DECLARE c2 CURSOR返回给调用者
SELECT''PARM1 IS NOT NULL ''来自SYSIBM.SYSDUMMY1的R1;
如果是PA，则为
RM1是空的那个
OPEN c1;
ELSE
OPEN c2;
结束IF;
结束P1
;
CALL RG.NULLTEST（null） ;
返回PARM1 IS NOT NULL
但是
CALL RG.NULLTEST（NULL） ;
PARM1是空的
这很容易编码，但数据库时令人沮丧
违反了一个基本假设。
还有其他人遇到过这个问题吗？
我无法在DB2 9 GA上重现这一点。
如果您的观察结果证实它将是
$ CL $中的b $ ba bug，而不是引擎。
CLP对CALL语句进行了一些浅层解析来替换
文字？用于IN / OUT参数。也是在早期的
DB2 V7（！）CLP接受了没有引号的字符串。这里的NULL vs null
（''null''）是有意义的。
难道你是旧的DB2 V7客户端吗？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
< blockquote>好的，开始有意义了。你能打开PMR吗？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
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
sy*****@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:sy*****@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/
这篇关于BUG DB2 V8 FP12存储过程参数 - NULL值区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
