|
Problem IC70667
|
Status: Closed |
A DB2 SQL query might return SQL0440N error. |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
A DB2 SQL query might return following error message:
SQL0440N No authorized routine named "=" of type "FUNCTION"
having compatible
arguments was found. SQLSTATE=42884
The problems happens to SQL queries with SELECT clause such that
it has 2 or more equiv JOIN predicates whose (1)one side
operands are NULL values and (2)the other side are of different
types that can't be compared.
Here is an example of such queries:
=====================================
create table t1 (c1 int, c2 char(10));
create table t2 like t1;
create table t3 like t1;
with tmp as (
select cast(NULL as int) x,
cast(NULL as char(10)) y
from t1
)
select 1
from tmp, t2, t3
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y;
=====================================
The JOIN predicates "t2.c1=x" and "t2.c2=y" result in the
problem. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of version 9.5 on Linux, Unix and *
* Windowsplatforms. *
****************************************************************
* PROBLEM DESCRIPTION: *
* A DB2 SQL query might return following error *
* message:SQL0440N No authorized routine named "=" of type *
* "FUNCTION"having compatiblearguments was found. *
* SQLSTATE=42884The problems happens to SQL queries with *
* SELECT clause suchthat it has 2 or more equiv JOIN *
* predicates whose (1)oneside operands are NULL values and *
* (2)the other side are ofdifferent types that can't be *
* compared.Here is an example of such *
* queries:=====================================create table t1 *
* (c1 int, c2 char(10));create table t2 like t1;create table *
* t3 like t1;with tmp as (select cast(NULL as int) x,cast(NULL *
* as char(10)) yfrom t1)select 1from tmp, t2, t3where *
* t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and *
* t2.c2=y;=====================================The JOIN *
* predicates "t2.c1=x" and "t2.c2=y" result in theproblem. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 UDB Version Version 9.5 FixPak 7 or *
* higherlevels. *
**************************************************************** |
| Local Fix: |
Change the NULL operands into Non-NULL types.
For the example shown in Problem Description, modify the SQL
query to:
=====================================
with tmp as (
select nullif(0,0)+0 x,
cast(NULL as char(10)) y
from t1
)
select 1
from tmp, t2, t3
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y;
=====================================
or
=====================================
with tmp as (
select cast(NULL as int) x,
nullif('','')||'' y
from t1
)
select 1
from tmp, t2, t3
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y
===================================== |
| available fix packs: |
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 UDB Version 9.5 FixPak 7. |
| Workaround |
Change the NULL operands into Non-NULL types.
For the example shown in Problem Description, modify the SQL
query to:
=====================================
with tmp as (
select nullif(0,0)+0 x,
cast(NULL as char(10)) y
from t1
)
select 1
from tmp, t2, t3
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y;
=====================================
or
=====================================
with tmp as (
select cast(NULL as int) x,
nullif('','')||'' y
from t1
)
select 1
from tmp, t2, t3
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y
===================================== |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.08.2010
25.10.2010
25.10.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
