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

[Salvatore R (JIRA)]

Salvatore R created TEIID-4262:
----------------------------------

             Summary: Wrong result (empty or NULL) running a CTE
                 Key: TEIID-4262
                 URL: https://issues.jboss.org/browse/TEIID-4262
             Project: Teiid
          Issue Type: Bug
            Reporter: Salvatore R
            Assignee: Steven Hawkins


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}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)