DB2 - Problembeschreibung
Problem IC70667 | Status: Geschlossen |
A DB2 SQL query might return SQL0440N error. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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 ===================================== | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows | |
Lösung | |
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 ===================================== | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 20.08.2010 25.10.2010 25.10.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5. | |
Problem behoben lt. FixList in der Version | |
9.1.0.7 | |
9.5.0.7 |