[jira] [Resolved] (PHOENIX-1049) Secondary indices can cause "ERROR 203 (22005): Type mismatch" in queries involving joins

["James Taylor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-1049?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

James Taylor resolved PHOENIX-1049.
-----------------------------------

       Resolution: Fixed
    Fix Version/s: 4.1
                   3.1
                   5.0.0

> 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
>             Fix For: 5.0.0, 3.1, 4.1
>
>
> 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)