[ASTERIXDB-3030] Unnecessary JOIN in plans

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.9.7
• Fix Version/s: 0.9.7
• Component/s: COMP - Compiler
• Assignee: Glenn Justo Galvizo
• Reporter: Glenn Justo Galvizo
• Created: 01/Apr/22 19:20
• Updated: 01/Apr/22 19:20

Description

Setup our example (user_id is the defined primary key here).

 

DROP DATAVERSE    Yelp IF EXISTS;
CREATE DATAVERSE  Yelp;
USE               Yelp;

CREATE TYPE       UsersType AS { user_id: bigint };
CREATE DATASET    Users (UsersType) PRIMARY KEY user_id;
INSERT INTO       Users [
    { "user_id": 1, "friends": [2, 3], "best_friend": 2 },
    { "user_id": 2, "friends": [1] },
    { "user_id": 3, "friends": [1], "best_friend": 1 },
    { "user_id": 4 }
    ]; 

 

 

The following query optimizes out the U1-U2 JOIN.

 

SELECT  U1
FROM    Users U1, Users U2
WHERE   U1.user_id = U2.user_id; 

 

 

The following query does not optimize out the U1-U2 JOIN, even though we know that U1 and U2 refer to the same document.

SELECT  U1, U2
FROM    Users U1, Users U2
WHERE   U1.user_id = U2.user_id; 

 

The following query also does not optimize a JOIN out, even though we know that U1 and the UU1 in U1F refer to the same document.

 

SELECT  U1, U2
FROM    Users U1,
        ( FROM Users UU1 UNNEST UU1.friends F SELECT UU1.user_id, F AS friend ) U1F,
        Users U2
WHERE   U1.user_id = U1F.user_id AND U1F.friend = U2.user_id; 

 

 

The `RemoveUnusedOneToOneEquiJoinRule` needs to be either extended or supplemented to handle these cases.