[jira] [Comment Edited] (ASTERIXDB-3033) LEFT OUTER JOIN does not produce correct output

["Dmitry Lychagin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/ASTERIXDB-3033?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17543122#comment-17543122 ] 

Dmitry Lychagin edited comment on ASTERIXDB-3033 at 5/27/22 8:53 PM:
---------------------------------------------------------------------

the result seems correct to me:
{noformat}
SELECT count(*) from customer c --> 60000
{noformat}
when running the outer-join query I get 60,000 tuples in the result. One of these is \{ "o_id": 570 }, the remaining 59,999 are \{  } . Which is expected:
{noformat}
SELECT o.o_id from customer c LEFT OUTER JOIN orders o ON ....
{noformat}
is supposed to produce objects each with one o_id field. However o_id is coming from the right branch of the left outer join, therefore will be set to MISSING for 59,999 tuples this didn't join. \{ o_id : MISSING } is equivalent to an empty object and is printed as \{ } 


was (Author: dlychagin-cb):
the result seems correct to me:
SELECT count(*) from customer c --> 60000
when running the outer-join query I get 60,000 tuples in the result. One of these is \{ "o_id": 570 }, the remaining 59,999 are \{  } . Which is expected:
SELECT o.o_id from customer c LEFT OUTER JOIN orders o ON ....
is supposed to produce objects each with one o_id field. However o_id is coming from the right branch of the left outer join, therefore will be set to MISSING for 59,999 tuples this didn't join. \{ o_id : MISSING } is equivalent to an empty object and is printed as \{ } 

> LEFT OUTER JOIN does not produce correct output
> -----------------------------------------------
>
>                 Key: ASTERIXDB-3033
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-3033
>             Project: Apache AsterixDB
>          Issue Type: Bug
>    Affects Versions: 0.9.7
>            Reporter: Xean Nguyenla
>            Priority: Major
>         Attachments: ch2_test_data.zip, inner_join_plan.txt, inner_join_query.txt, left_outer_join_plan.txt, left_outer_join_query.txt
>
>
> I am currently working with the CH2 benchmark data and ran into an issue regarding inner join vs. left outer join. When doing an inner join between customers & orders, there is a successful join (outputs 1 when finding how many counts of o_id are present). but when running the left outer join, it outputs 0 indicating no successful joins when it should output 1 as well. Queries, plans, and test data are attached below.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)