[ASTERIXDB-2964] UNNEST style join query with composite atomic-array index not recognized

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: IDX - Indexes
• Assignee: Glenn Justo Galvizo
• Reporter: Glenn Justo Galvizo
• Created: 14/Sep/21 20:17
• Updated: 14/Sep/21 20:17

Description

*Found with RQG.

UNNEST style join queries to use composite atomic-array indexes are not being accelerated. Queries can be rewritten to use a quantified expression style join to utilize the index. Example of query that is not accelerated is given below:

 

SET `compiler.arrayindex` "true";
DROP DATAVERSE TestDataverse1 IF EXISTS;
CREATE DATAVERSE TestDataverse1;
USE TestDataverse1;
CREATE TYPE TestType AS {
 _id: uuid
};
CREATE DATASET Dataset1 (TestType)
PRIMARY KEY _id AUTOGENERATED;
CREATE DATASET Dataset2 (TestType)
PRIMARY KEY _id AUTOGENERATED;
CREATE INDEX d1Idx
ON Dataset1 (
 field1 : bigint,
 ( UNNEST outer_items
 UNNEST inner_items
 SELECT field2 : bigint,
 field3 : bigint ),
 field4 : bigint
);
FROM Dataset2 D2,
 Dataset1 D1
 UNNEST D1.outer_items DOI
 UNNEST DOI.inner_items DII
WHERE D1.field1 /* +indexnl */ = TO_BIGINT(D2.field1) AND 
 DII.field2 /* +indexnl */ = TO_BIGINT(D2.field2) AND 
 DII.field3 /* +indexnl */ = TO_BIGINT(D2.field3) AND 
 D1.field4 /* +indexnl */ = TO_BIGINT(D2.field4)
SELECT *;