[jira] [Created] (ASTERIXDB-3625) Incorrect result with LEFT OUTER JOIN using index-nested-loop join

[shahrzad shirazi (Jira)]

shahrzad shirazi created ASTERIXDB-3625:
-------------------------------------------

             Summary: 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


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.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)