A derived predicate based on a generated column that includes
the concatenation of a VARCHAR or VARGRAPHIC column to some
other column or expression, could result in incorrect results if
some of the data in the VARCHAR or VARGRAPHIC column has
trailing blanks.
For example:
drop table t1;
create table t1 (
c1 varchar(5) not null,
c2 varchar(5) not null,
c3 int,
mc1 varchar(10) generated always as (c1||c2));
create index t1_i1 on t1 (mc1);
insert into t1 (c1,c2,c3) values
('AAA','BBB',1),
('AAA ','BBB',2);
select * from t1 where c1 = 'AAA' and c2 = 'BBB';
Query results:
db2 => select * from t1 where c1 = 'AAA' and c2 = 'BBB';
C1 C2 C3 MC1
----- ----- ----------- ----------
AAA BBB 1 AAABBB
1 record(s) selected.
db2 => drop index t1_i1;
DB20000I The SQL command completed successfully.
db2 => select * from t1 where c1 = 'AAA' and c2 = 'BBB';
C1 C2 C3 MC1
----- ----- ----------- ----------
AAA BBB 1 AAABBB
AAA BBB 2 AAA BBB
2 record(s) selected.
The problem is that the derived predicate is: MC1='AAABBB' which
misses the C1 values with 'AAA' that have trailing blanks.
The problem occurs for an index scan using an index the includes
the generated column, where the derived predicate is applied as
a start or stop key. A table scan doesn't have this issue
because the original predicates are applied instead of the
derived predicate. However, it is possible for the problem to
occur for a table scan of a range partition table because
partitions could be incorrectly eliminated based on the derived
predicate, which could also lead to incorrectly missing rows. |
For tables that aren't range partitioned, drop the index that
includes the generated column. For range partitioned tables,
change the definition of the generated column to avoid
concatenating a VARCHAR or VARGRAPHIC column that might contain
duplicate values that differ only by the number of trailing
blanks. |
