Andrew Liles created PHOENIX-1049:
-------------------------------------
Summary: Secondary indices can cause "ERROR 203 (22005): Type
mismatch" in queries involving joins
Key: PHOENIX-1049
URL: https://issues.apache.org/jira/browse/PHOENIX-1049
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.0.0
Reporter: Andrew Liles
The following SQL will execute correctly and produce an execution plan:
====
DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
CREATE TABLE A (
K BIGINT NOT NULL,
CF2.C1 VARCHAR(100),
CF2.C2 VARCHAR(100),
CONSTRAINT FB_PK PRIMARY KEY (K)
);
CREATE TABLE B (
K BIGINT NOT NULL
CONSTRAINT FB_PK PRIMARY KEY (K)
);
EXPLAIN SELECT * FROM A INNER JOIN B ON A.K = B.K;
====
Now add an index & re-run the execution plan:
====
CREATE INDEX IDXA2 ON A (CF2.C1);
EXPLAIN SELECT * FROM A INNER JOIN B ON A.K = B.K;
====
You get the error:
Error: ERROR 203 (22005): Type mismatch. expected: LONG but was: VARCHAR at
column: A.K (state=22005,code=203)
This seems related to PHOENIX-61. Following James' insight in the first
comment I have tried changing the BIGINT > DECIMAL. But no difference.
What is curious is that if you remove column A.C2 the issue goes away.
I have tried a work around using hints to avoid the "bad" index:
EXPLAIN SELECT /* NO_INDEX */ * FROM A INNER JOIN B ON A.K = B.K;
but that too doesn't avoid the fault. (Is my use of the hint syntax correct?)
I'm using Phoenix 4.0.0 on HBase 0.96.1.1+cdh5.0.1+68
--
This message was sent by Atlassian JIRA
(v6.2#6252)
