[ https://issues.apache.org/jira/browse/TRAFODION-3237?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16715781#comment-16715781 ]
ASF GitHub Bot commented on TRAFODION-3237: ------------------------------------------- GitHub user DaveBirdsall opened a pull request: https://github.com/apache/trafodion/pull/1759 [TRAFODION-3237] Fix incorrect PCode optimization When an interval literal was referenced twice or more in an INSERT/SELECT in expressions with different INTERVAL types, the PCode optimizer was incorrectly treating the code to produce the interval literal value as a common subexpression, so the INSERT/SELECT would insert incorrect values. This has been fixed. The PCode optimizer common subexpression elimination logic now checks for commonality in datatype, scale and precision for INTERVAL conversions. A test case that demonstrates the problem (with correct behavior with the fix) has been added to core/TEST038. You can merge this pull request into a Git repository by running: $ git pull https://github.com/DaveBirdsall/trafodion Trafodion3237 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/trafodion/pull/1759.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #1759 ---- commit 8b4e533d3082379dc090e7efeefab80741334d00 Author: Dave Birdsall <dbirdsall@...> Date: 2018-12-10T23:32:00Z [TRAFODION-3237] Fix incorrect PCode optimization ---- > Incorrect results for INTERVAL addition in an uncommon case > ----------------------------------------------------------- > > Key: TRAFODION-3237 > URL: https://issues.apache.org/jira/browse/TRAFODION-3237 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.4 > Reporter: David Wayne Birdsall > Assignee: David Wayne Birdsall > Priority: Major > > The following script illustrates the problem: > {quote}?section ddl > drop table if exists mytableF_base; > create table mytableF_base ( > val int, > "c9" interval day(7) to second default NULL, > "c10" interval day(13) to second(0) default NULL, > "key c10" interval day(13) to second(0) not null default interval > '1111111111111:01:11:11' day(13) to second(0), > unique ("key c10") > ); > ?section populateit > insert into mytableF_base values ( > 0, > interval '0 00:00:00' day(7) to second, > interval '0 00:00:00' day(13) to second(0), > interval '0 00:00:00' day(13) to second(0)); > insert into mytableF_base > select 1, > "c9" + interval '1' day, > "c10" + interval '1' day, -- gives incorrect result > "key c10" + interval '1' day -- gives incorrect result > from mytableF_base > where val = 0; > insert into mytableF_base > select 23, > "c9" + interval '1' day, > "c10" + interval '2' day, -- gives correct result > "key c10" + interval '3' day -- gives correct result > from mytableF_base > where val = 0; > select "c10","key c10" from mytableF_base; > {quote} > When run, the last SELECT gives: > {quote}>>select "c10","key c10" from mytableF_base; > c10 key c10 > ----------------------- ----------------------- > 0 00:00:00 0 00:00:00 > 1000000 00:00:00 1000000 00:00:00 > 2 00:00:00 3 00:00:00 > --- 3 row(s) selected. > >>exit; > {quote} > The second line is incorrect; the values are off by a factor of one million. -- This message was sent by Atlassian JIRA (v7.6.3#76005)