A query might return additional rows when it has an OR predicate
that meets the following conditions:
- The query has an OR predicate, called orPrd, that is either
present in a table inner join query block or simply applied to a
single table scan.
- The orPrd has an IN predicate, called inPrd.
- The inPrd predicate has pattern of <simple_base_columnX> IN
(<column_or_literal1>, <column_or_literal2>, ...), where one of
the <column_or_literal> is <simple_base_columnX> itself. An
example predicate is "col1 in (constant1, constant2, col1,
col2)", where col1 is not only IN predicate column but also an
INlist element.
- The inPrd column, <simple_base_columnX> as above for example,
is defined as nullable. There is table rows whose columnX is the
NULL value.
- The query has no predicate other than orPrd that can reject
the columnX Null value. For example, if query has relational
comparison predicate such as columnX = columnY, columnX NULL
values will be filtered out.
To verify if the query hits this problem, use the db2exfmt or
db2explin commands to generate db2exfmt a query explain plan.
Search the query explain plan to verify if the original query's
OR predicate is present in the section "Optimized Statement".
This problem might cause the orPrd predicate to be incorrectly
removed. This might result in the columX NULL value being
incorrectly returned to the consumer query block. This error
might make the final result inaccurate. |
There are two workarounds for this issue:
- Reduce query optimization to 0 or 1. A sample command is "db2
set current query optimization 0".
- Manually rewrite that OR predicate. Change it from
"<simple_base_columnX> IN (<column_or_literal1>,
<column_or_literal2>, ...)" to "<simple_base_columnX> IS NOT
NULL". |
