If a query contains an access plan that features a SORT over
UNION, like this for example:
12324.2
SORT
( 4)
22774.9
|
12324.2
UNION
( 5)
22754.1
+------+------+-----+---------+
leg0 leg2 leg3 leg4
Then for performance reasons, the DB2 query compiler might
decide to have the query insert rows into the SORT in each of
the union legs rather than after the union has output rows (this
is called a "pushdown").
Further, suppose that the union is outputting a row that
features a variable length column such as a varchar(), call this
column A.
If there is a nullability mismatch of this column A from the
different union legs, for example, if UNION leg 0 produces
column A that is nullable, but UNION leg 1 produces column A
that is not nullable, then the overall nullability that the
UNION will produce will be nullable.
As a result of this nullability difference, the query compiler
injects some logic that will ensure that the columns are
correctly converted to the appropriate nullability.
If these conditions are all true then:
- SORT over UNION being pushed down into union legs
- nullability difference between union legs for a varying length
column
..then there is a chance that this problem can occur.
Specifically, the query will return data that has unused bytes
following the column's data. Typically, this would be the NULL
character byte 0x00, however it's also possible that it could be
various other stale bytes of memory. Further wrong results
might also result if this output is used in a predicate, where
results are not qualifying when really they should have (missing
result rows). |
