Glenn Justo Galvizo created ASTERIXDB-3030:
----------------------------------------------
Summary: Unnecessary JOIN in plans
Key: ASTERIXDB-3030
URL: https://issues.apache.org/jira/browse/ASTERIXDB-3030
Project: Apache AsterixDB
Issue Type: Bug
Components: COMP - Compiler
Affects Versions: 0.9.7
Reporter: Glenn Justo Galvizo
Assignee: Glenn Justo Galvizo
Fix For: 0.9.7
Setup our example (user_id is the defined primary key here).
{code:java}
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 }
]; {code}
The following query optimizes out the U1-U2 JOIN.
{code:java}
SELECT U1
FROM Users U1, Users U2
WHERE U1.user_id = U2.user_id; {code}
The following query does not optimize out the U1-U2 JOIN, even though we know
that U1 and U2 refer to the same document.
{code:java}
SELECT U1, U2
FROM Users U1, Users U2
WHERE U1.user_id = U2.user_id; {code}
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.
{code:java}
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; {code}
The `RemoveUnusedOneToOneEquiJoinRule` needs to be either extended or
supplemented to handle these cases.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
