[PHOENIX-2274] Sort-merge join could not optimize out the sort on the right table

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 4.5.2
• Fix Version/s: 4.6.0
• Assignee: Wei Xue
• Reporter: Wei Xue
• Created: 18/Sep/15 15:20
• Updated: 21/Nov/15 02:17
• Resolved: 21/Sep/15 14:25

Description

CREATE TABLE t (
    mypk VARCHAR(100) NOT NULL PRIMARY KEY,
    mycol VARCHAR(200));

SELECT * FROM t t1 JOIN t t2 ON t1.mypk = t2.mypk;

would produce a plan like:

SORT-MERGE-JOIN (INNER) TABLES
    CLIENT PARALLEL 1-WAY FULL SCAN OVER T
AND
    CLIENT PARALLEL 1-WAY FULL SCAN OVER T
        SERVER SORTED BY [MYPK]
    CLIENT MERGE SORT

, while it should have generated a plan like:

SORT-MERGE-JOIN (INNER) TABLES
    CLIENT PARALLEL 1-WAY FULL SCAN OVER T
AND
    CLIENT PARALLEL 1-WAY FULL SCAN OVER T