DB2 - Problembeschreibung
Problem IC94476 | Status: Geschlossen |
SQL0901 ERROR IN DPF WHEN COMPILING A QUERY WITH AN EQUALITY PREDICATE REFERENCING A NULL CONSTANT | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
You might encounter an SQL0901 error with reason "getting canonical type length" and producing the following stack trace sqlnn_cmpl sqlng_main sqlng_main sqlng_build_thread sqlng_process_return_op sqlng_process_TQ_op sqlng_build_thread sqlng_process_pipe_op sqlng_process_mate_op sqlng_process_hsjn_op sqlng_build_thread sqlng_process_TQ_op sqlng_build_thread sqlng_build_TQB_op sqlng_populate_COLBLOCK_ob when compiling a query consisting of a non-collocated join between two or more tables that are hash distributed across two or more database partitions, and the join columns also equate to the NULL constant. The following is an example that might produce the SQL0901 error: create table i1 (a int, b int) distribute by hash (a); create table i2 (a int, b int) distribute by hash (a); select * from i1,i2 where i1.b=i2.b and i2.b = null ; In this example, the join is not collocated, so the optimizer can consider both a broadcast join or a repartition join. The decision is cost-based, and the problem only occurs if the repartition join is chosen. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * The problem can occur in a DPF environment. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Problem first fixed in DB2 version v9.7 fp 9 * **************************************************************** | |
Local-Fix: | |
You might be able to avoid the error by re-ordering the predicates in the WHERE clause such that the equality predicate referencing the NULL constant is included before the join predicate referencing the same column. Using the example in the error description, re-ordering the predicates as follows could avoid the error: "where i2.b = null and i1.b=i2.b". | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows | |
Lösung | |
Problem first fixed in DB2 version v9.7 fp 9 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 30.07.2013 16.12.2013 16.12.2013 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP9 | |
Problem behoben lt. FixList in der Version | |
9.7.0.9 | |
9.7.0.9 |