[ASTERIXDB-2962] Query w/ extra UNNESTs does not use array index

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

Description

*Found from the RQG.

The following query does not utilize the index, even though it should. This is due to the presence of a redundant UNNEST operator (explicit UNNEST + quantified expression) on the indexed field.

USE TestDataverse;
DROP DATASET IndexedDataset IF EXISTS;
CREATE DATASET IndexedDataset (TestType)
PRIMARY KEY _id AUTOGENERATED;

CREATE INDEX testIndex ON IndexedDataset ( integer_rand_4.contained_object_2: BIGINT, ( UNNEST string_rand_26_c.contained_object_2 : STRING ), container_object_1.double_rand_20: DOUBLE );

SET `compiler.arrayindex` "true";
FROM IndexedDataset AS D1
UNNEST D1.string_rand_26_c.contained_object_2 AS G1
WHERE ( D1.integer_rand_4.contained_object_2 BETWEEN 0 AND 3 ) AND 
      ( D1.container_object_1.double_rand_20 <= 19 ) AND 
      ( SOME V1 IN D1.string_rand_26_c.contained_object_2 SATISFIES 
         ( V1 BETWEEN "A" AND "Z" ) )
SELECT D1._id AS PK1
ORDER BY D1._id
LIMIT 100;