[jira] [Created] (IMPALA-10493) Using JOIN ON syntax to join two full ACID collections produces wrong results

["Zoltán Borók-Nagy (Jira)" <ji...@apache.org>]

Zoltán Borók-Nagy created IMPALA-10493:
------------------------------------------

             Summary: Using JOIN ON syntax to join two full ACID collections produces wrong results
                 Key: IMPALA-10493
                 URL: https://issues.apache.org/jira/browse/IMPALA-10493
             Project: IMPALA
          Issue Type: Bug
            Reporter: Zoltán Borók-Nagy


The following query produces wrong results:
{noformat}
use functional_orc_def; // use full ACID tables
select a1.item, a2.item
from complextypestbl.int_array a1 join complextypestbl.int_array a2
on a1.item=a2.item
where a1.item<2;{noformat}
It creates a CROSS JOIN without the predicate "a1.item = a2.item", generating too many rows. The expected plan node would be an INNER JOIN on "a1.item = a2.item".

If we put the JOIN condition to the WHERE clause we get the correct plan:
{noformat}
select a1.item, a2.item
from complextypestbl.int_array a1 join complextypestbl.int_array a2
where a1.item=a2.item and a1.item<2{noformat}
We also get a correct plan if the right table is non-ACID:
{noformat}
select a1.item, a2.item
from complextypestbl.int_array a1 join functional_parquet.complextypestbl.int_array a2
on a1.item=a2.item
where a1.item<2;{noformat}
Or ACID table but the column is non-collection:
{noformat}
select c.id, a1.item
from complextypestbl.int_array a1 join complextypestbl c
on c.id=a1.item
where c.id<2;{noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)