2

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.

nardqueue
  • 31
  • 5
  • Upon using sqlline.py, was able to catch the error: `java.io.IOException: java.lang.NoClassDefFoundError: org/iq80/snappy/CorruptionException at org.apache.phoenix.coprocessor.ServerCachingEndpointImpl.addServerCache(ServerCachingEndpointImpl.java:78) at ` Just added https://mvnrepository.com/artifact/org.iq80.snappy/snappy/0.4 to the hbase lib directory and it solved the problem – nardqueue Oct 18 '16 at 07:25
  • Could you put the --------------UPDATE--------------- section as an answer, that way we could vote it up. – gersonZaragocin Nov 18 '21 at 20:25

1 Answers1

0

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.

nardqueue
  • 31
  • 5
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 22 '22 at 13:56