[ASTERIXDB-2960] LOJ on subquery with UNNEST for array indexes throwing type error

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: IDX - Indexes
• Assignee: Glenn Justo Galvizo
• Reporter: Glenn Justo Galvizo
• Created: 11/Sep/21 21:26
• Updated: 17/Sep/21 17:04
• Resolved: 17/Sep/21 17:04

Description

A query like this currently throws an error when trying to optimize for an LOJ INLJ:

 

SET               `compiler.arrayindex` "true";
DROP DATAVERSE    TestDataverse IF EXISTS;
CREATE DATAVERSE  TestDataverse;
USE               TestDataverse;
CREATE TYPE       GenericType AS { _id: uuid  };
CREATE DATASET    IndexedDataset (GenericType)
PRIMARY KEY       _id AUTOGENERATED;
CREATE DATASET    ProbeDataset (GenericType)
PRIMARY KEY       _id AUTOGENERATED;
CREATE INDEX      TestIdx
ON                IndexedDataset (
    UNNEST items    
    SELECT val : bigint
);

FROM             ProbeDataset P
LEFT OUTER JOIN  ( 
    FROM IndexedDataset I   
    UNNEST I.items II    
    SELECT VALUE II.val
) AS IIV
ON                IIV /* +indexnl */ = TO_BIGINT(P.val)
SELECT            DISTINCT P;