title: Apache Phoenix Join Fails (Encountered exception in sub plan [0] execution)
body: <p>Here are the create table statements of the tables I'm testing, which was actually from <a href="https://phoenix.apache.org/joins.html" rel="nofollow">Phoenix</a></p>
  
  <pre><code>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;
  </code></pre>
  
  <p>And here is the simple join query:</p>
  
  <pre><code>SELECT E.Name, E.Region, P.PCount
  FROM Test.Employee AS E
  JOIN
      (SELECT Region, LocalID, count(*) AS PCount
       FROM Test.Patent
       WHERE FileDate &gt;= to_date('2000-01-01')
       GROUP BY Region, LocalID) AS P
  ON E.Region = P.Region AND E.LocalID = P.LocalID
  </code></pre>
  
  <p>Explain works fine and results to this:</p>
  
  <pre><code>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 &gt;= DATE '2000-01-01 00:00:00.000'
              SERVER AGGREGATE INTO DISTINCT ROWS BY [REGION, LOCALID]
  </code></pre>
  
  <p>But when I run it, I get this error:</p>
  
  <pre><code>Encountered exception in sub plan [0] execution.
  </code></pre>
  
  <p>Additional Details:</p>
  
  <ul>
  <li>I'm using phoenix-4.4.0-HBase-1.1</li>
  <li>Running against a stand-alone HBase-1.1.2 server</li>
  <li>Everything works fine except for queries involving JOINs</li>
  <li>Both tables contain 1 row each</li>
  </ul>
  
  <p>Am currently stuck with this problem...</p>
  
  <p>Thanks!</p>
  
  <p>--------------UPDATE---------------</p>
  
  <p>This works:</p>
  
  <pre><code>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 &gt;= to_date('2000-01-01')
       GROUP BY Region, LocalID) AS P
  ON E.Region = P.Region AND E.LocalID = P.LocalID
  </code></pre>
  
  <p>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.</p>