dynamobi/dynamobi — Issue #456: [FNL-52] common subexpression elimination combined with CASE short-circuiting spells trouble
Author: dynamobi-build
Created: 2011-12-30 03:27:40+00:00
State: open
Labels: Calc, FNL

[reporter="jvs", created="Wed, 23 Aug 2006 22:32:15 -0500 (GMT-05:00)"]
The following script illustrates the bug. Java calc produces correct result; Fennel calc produces incorrect result. EXPLAIN PLAN shows that common subexpression elimination is being performed for the expression SUBSTRING(val FROM 0 FOR 5). Notice that this appears within a branch of the first CASE that is NOT hit. I think the Fennel calc bug is that it short-circuits the first CASE, and then when it evaluates the second expression, it doesn't realize that it hasn't evaluated the common subexpression yet, so it returns garbage. (You can actually see the garbage if you replace the second CASE with just the substring expression to be returned directly):

create schema TEST;

set schema 'TEST';

create table source (val varchar(200) not null primary key);

insert into source values ('abcdef');

alter system set "calcVirtualMachine" = 'CALCVM_JAVA';

SELECT

CASE

WHEN SUBSTRING(val FROM 0 FOR 3) = 'ab' THEN 10000

WHEN SUBSTRING(val FROM 0 FOR 5) = 'acde' THEN 10001

ELSE 10 END AS "PROJECT_KEY",

CASE

WHEN SUBSTRING(val FROM 0 FOR 5) = 'abcd' THEN 20000

ELSE 20 END AS "FILE_SANS_BRANCH"

FROM source;

alter system set "calcVirtualMachine" = 'CALCVM_FENNEL';

SELECT

CASE

WHEN SUBSTRING(val FROM 0 FOR 3) = 'ab' THEN 10000

WHEN SUBSTRING(val FROM 0 FOR 5) = 'acde' THEN 10001

ELSE 10 END AS "PROJECT_KEY",

CASE

WHEN SUBSTRING(val FROM 0 FOR 5) = 'abcd' THEN 20000

ELSE 20 END AS "FILE_SANS_BRANCH"

FROM source;