You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2018/04/30 21:17:00 UTC

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

     [ https://issues.apache.org/jira/browse/TRAFODION-3051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Wayne Birdsall updated TRAFODION-3051:
--------------------------------------------
    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'.

 

  was:
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(y) from t1 join t2 on b = y;

cqd comp_bool_158 'off';

select a,b,x,y,char_length(b),char_length(y) 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(y). 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(y) respectively. So, the workaround for such examples is to set cqd comp_bool_158 'off'.

 


> Results of string functions may change slightly if argument is in a join predicate
> ----------------------------------------------------------------------------------
>
>                 Key: TRAFODION-3051
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3051
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.3
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>
> 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'.
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)