[ASTERIXDB-2924] Ternary joins with same probe using INLJ

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.9.7
• Component/s: IDX - Indexes
• Assignee: Glenn Justo Galvizo
• Reporter: Glenn Justo Galvizo
• Created: 08/Jul/21 19:11
• Updated: 02/Aug/21 23:25
• Resolved: 29/Jul/21 20:39

Description

Given the DDLs:

DROP DATAVERSE    TestDataverse IF EXISTS;
CREATE DATAVERSE  TestDataverse;
USE               TestDataverse;

CREATE TYPE       GenericType AS { _id: bigint, c : bigint };
CREATE DATASET    IndexDatasetA (GenericType)
PRIMARY KEY       _id;
CREATE DATASET    IndexDatasetB (GenericType)
PRIMARY KEY       _id;
CREATE DATASET    ProbeDataset (GenericType)
PRIMARY KEY       _id;

CREATE INDEX      indexA
ON                IndexDatasetA (k : int);
CREATE INDEX      indexB
ON                IndexDatasetB (k : int);

The following ternary join query produces an INLJ plan for both indexed datasets. The join field from the probe is the probe dataset's primary key.

--                Query 1, ternary join w/ primary key on probe.
FROM              ProbeDataset P,
                  IndexDatasetA A,
                  IndexDatasetB B
WHERE             P._id /* +indexnl */ = A.k AND
                  P._id /* +indexnl */ = B.k
SELECT            COUNT(*);

The following ternary join query produces an INLJ plan for only the left-most indexed dataset. The join field from the probe is a closed field from the probe dataset.

--                Query 2, ternary join w/ closed field on probe.
FROM              ProbeDataset P,
                  IndexDatasetA A,
                  IndexDatasetB B
WHERE             P.c /* +indexnl */ = A.k AND
                  P.c /* +indexnl */ = B.k
SELECT            COUNT(*);

Both queries should produce two INLJs.