[teiid-issues] [JBoss JIRA] (TEIID-4262) Wrong result (empty or NULL) running a CTE

Wrong result (empty or NULL) running a CTE ------------------------------------------ Key: TEIID-4262 URL: https://issues.jboss.org/browse/TEIID-4262 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 9.0 Reporter: Salvatore R Assignee: Steven Hawkins Priority: Blocker Fix For: 9.0 The following query returns an empty result instead of 1: {code:sql} WITH alias as (SELECT 1 as a), alias2 as (select t2.a as a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a), alias3 as (select t2.a as a1, t1.a from alias t1 join alias2 t2 on t1.a=t2.a) SELECT alias3.a1 FROM alias2 join alias3 on alias3.a=alias2.a or alias3.a>alias2.a {code} This similar query (using LEFT instead of INNER join) returns NULL instead of 1: {code:sql} WITH alias as (SELECT 1 as a), alias2 as (select t2.a as a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a), alias3 as (select t2.a as a1, t1.a from alias t1 join alias2 t2 on t1.a=t2.a) SELECT alias3.a1 FROM alias2 left join alias3 on alias3.a=alias2.a or alias3.a>alias2.a {code}