[
https://issues.apache.org/jira/browse/ASTERIXDB-3625?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ali Alsuliman updated ASTERIXDB-3625:
-------------------------------------
Description:
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.
was:
The LEFT OUTER JOIN using index-nested-loop join will not return correct result
if there are no matching tuples.
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 :
{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.
> 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)
