[teiid-issues] [JBoss JIRA] (TEIID-4262) Wrong result (empty or NULL) with an view and a single disjunction for join criteria

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4262?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-4262:
----------------------------------
          Description: 
The following query returns an empty result instead of 1:
{code:sql}
SELECT alias3.a1 FROM (select 1 as a) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a FROM (SELECT 1 AS a) AS t1 INNER JOIN (select 1 as a) as t2 ON t1.a = t2.a)  AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a))
{code}

when updating the parent join on removing the alias3 view layer, the OR condition is being converted to AND - which can never be true.

  was:
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}

        Fix Version/s: 8.12.5
                       8.13.6
              Summary: Wrong result (empty or NULL) with an view and a single disjunction for join criteria  (was: Wrong result (empty or NULL) running a CTE)
    Affects Version/s: 7.0
                           (was: 9.0)


> Wrong result (empty or NULL) with an view and a single disjunction for join criteria
> ------------------------------------------------------------------------------------
>
>                 Key: TEIID-4262
>                 URL: https://issues.jboss.org/browse/TEIID-4262
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.0
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>            Priority: Blocker
>             Fix For: 9.0, 8.12.5, 8.13.6
>
>
> The following query returns an empty result instead of 1:
> {code:sql}
> SELECT alias3.a1 FROM (select 1 as a) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a FROM (SELECT 1 AS a) AS t1 INNER JOIN (select 1 as a) as t2 ON t1.a = t2.a)  AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a))
> {code}
> when updating the parent join on removing the alias3 view layer, the OR condition is being converted to AND - which can never be true.



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