[TRAFODION-3051] Results of string functions may change slightly if argument is in a join predicate

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 30/Apr/18 21:11
• Updated: 30/Apr/18 21:16

Description

The following script demonstrates the problem.

?section setup

drop table if exists t1;

drop table if exists t2;

create table t1 (a int not null, b varchar(10), primary key (a));

create table t2 (x int not null, y varchar(8), primary key ( x ));

insert into t1 values (1,'ab');

insert into t2 values (2,'ab ');

?section testit

select a,b,x,y,char_length(b),char_length( c ) from t1 join t2 on b = y;

cqd comp_bool_158 'off';

select a,b,x,y,char_length(b),char_length( c ) from t1 join t2 on b = y;

With the fix for Jira TRAFODION-3050, the first query returns 2 for each of char_length(b) and char_length( c ). This is because the compiler thinks it can freely substitute b for y because of the equi-join predicate. When we turn comp_bool_158 'off', however, the compiler no longer does this and we get 2 and 3 for char_length(b) and char_length( c ) respectively. So, the workaround for such examples is to set cqd comp_bool_158 'off'.