[
https://issues.apache.org/jira/browse/ASTERIXDB-3625?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18003884#comment-18003884
]
ASF subversion and git services commented on ASTERIXDB-3625:
------------------------------------------------------------
Commit f232e2d8ec19032df7cfa68edfdfb0587f72bd75 in asterixdb's branch
refs/heads/master from Shahrzad Shirazi
[ https://gitbox.apache.org/repos/asf?p=asterixdb.git;h=f232e2d8ec ]
[ASTERIXDB-3625][COMP] Correcting the result of LEFT OUTER JOIN using
index-nested-loop join
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
When an index-nested-loop join finds no matching entries, it may
discard all tuples from the left table, resulting in incorrect results.
This fix ensures that tuples from the left table are preserved even
when no matching entries exist for the join.
Ext-ref: MB-67347
Change-Id: Ica97998d7fb006230977034152a35e1d6386ef21
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20003
Tested-by: Ali Alsuliman <[email protected]>
Reviewed-by: Ali Alsuliman <[email protected]>
> Incorrect result with LEFT OUTER JOIN using index-nested-loop join
> ------------------------------------------------------------------
>
> Key: ASTERIXDB-3625
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-3625
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: *DB - AsterixDB
> Reporter: shahrzad shirazi
> Priority: Major
>
> The LEFT OUTER JOIN using index-nested-loop join does not return correct
> result for (left) tuples that do not join.
> Example :
> If the following datatypes and datasets are created:
> {code:java}
> CREATE TYPE col1Type AS {
> id:int};
> CREATE TYPE col2Type AS {
> id:int};
> CREATE DATASET col1(col1Type) PRIMARY KEY id;
> CREATE DATASET col2(col2Type) PRIMARY KEY id;
> UPSERT INTO col1 {"id": 1, "f": 8};
> CREATE INDEX idx_id ON col2(f);{code}
> and we run the following query, we get an empty result:
> {code:java}
> FROM col1 LEFT JOIN col2 ON col1.f /*+ indexnl */ = col2.f
> SELECT col1, col2;
> {code}
> When the correct result should contain 1 record:
> {code:java}
> FROM col1 LEFT JOIN col2 ON col1.f = col2.f
> SELECT col1, col2;
> {code}
> But if we have matching tuples the result will be correct as an example if we
> insert following data:
> UPSERT INTO col1 \{"id": 2, "f": 28};
> UPSERT INTO col2 \{"id": 1, "f": 8};
> The result will be correct.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
