Apache Phoenix Join Fails (Encountered exception in sub plan [0] execution)

Question

Here are the create table statements of the tables I'm testing, which was actually from Phoenix

CREATE TABLE Test.Employee(
  Region VARCHAR NOT NULL,
  LocalID VARCHAR NOT NULL,
  Name VARCHAR,
  StartDate DATE,
  CONSTRAINT pk PRIMARY KEY(Region, LocalID))
SALT_BUCKETS = 1;

CREATE TABLE Test.Patent (
    PatentID VARCHAR NOT NULL,
    Region VARCHAR,
    LocalID VARCHAR,
    Title VARCHAR,
    Category VARCHAR,
    FileDate DATE,
    CONSTRAINT pk PRIMARY KEY (PatentID))
SALT_BUCKETS=1;

And here is the simple join query:

SELECT E.Name, E.Region, P.PCount
FROM Test.Employee AS E
JOIN
    (SELECT Region, LocalID, count(*) AS PCount
     FROM Test.Patent
     WHERE FileDate >= to_date('2000-01-01')
     GROUP BY Region, LocalID) AS P
ON E.Region = P.Region AND E.LocalID = P.LocalID

Explain works fine and results to this:

PLAN
CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST.EMPLOYEE
    PARALLEL INNER-JOIN TABLE 0
    DYNAMIC SERVER FILTER BY (E.REGION, E.LOCALID) IN ((P.REGION, P.LOCALID))
        CLIENT MERGE SORT
        CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST.PATENT
            SERVER FILTER BY FILEDATE >= DATE '2000-01-01 00:00:00.000'
            SERVER AGGREGATE INTO DISTINCT ROWS BY [REGION, LOCALID]

But when I run it, I get this error:

Encountered exception in sub plan [0] execution.

Additional Details:

• I'm using phoenix-4.4.0-HBase-1.1
• Running against a stand-alone HBase-1.1.2 server
• Everything works fine except for queries involving JOINs
• Both tables contain 1 row each

Am currently stuck with this problem...

Thanks!

--------------UPDATE---------------

This works:

SELECT /*+ USE_SORT_MERGE_JOIN */ E.Name, E.Region, P.PCount
FROM Test.Employee AS E
JOIN
    (SELECT Region, LocalID, count(*) AS PCount
     FROM Test.Patent
     WHERE FileDate >= to_date('2000-01-01')
     GROUP BY Region, LocalID) AS P
ON E.Region = P.Region AND E.LocalID = P.LocalID

So when forced to use Sort-Merge Join, the query works, I assume there is a problem with Hash Join in my setup. Still don't have an answer.

Answer 1

This works:

SELECT /*+ USE_SORT_MERGE_JOIN / E.Name, E.Region, P.PCount FROM Test.Employee AS E JOIN (SELECT Region, LocalID, count() AS PCount FROM Test.Patent WHERE FileDate >= to_date('2000-01-01') GROUP BY Region, LocalID) AS P ON E.Region = P.Region AND E.LocalID = P.LocalID So when forced to use Sort-Merge Join, the query works, I assume there is a problem with Hash Join in my setup. Still don't have an answer.