[
https://issues.apache.org/jira/browse/PHOENIX-1052?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14049422#comment-14049422
]
Andrew Liles commented on PHOENIX-1052:
---------------------------------------
I have tried using the HEAD of the 4.0 and find that this issue is resolved
there.
I think the defect is in the QueryOptimizer; I think the two commits made by
maryannzue around 1/14 April may have fixed it
(https://github.com/apache/phoenix/commit/a7d3311bdf7f954b372111b3cf99c729e379886e#diff-d2b77e303722ffcafc12a0062635103c)
but I didn't rollback to that particular point to check.
> Secondary indices can cause "ERROR 504 (42703): Undefined column" in queries
> involving joins
> --------------------------------------------------------------------------------------------
>
> Key: PHOENIX-1052
> URL: https://issues.apache.org/jira/browse/PHOENIX-1052
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.0.0
> Environment: HBase 0.96.1.1+cdh5.0.1+68
> Reporter: Andrew Liles
>
> The following SQL will execute correctly and produce an execution plan:
> ====
> DROP TABLE IF EXISTS I;
> DROP TABLE IF EXISTS P;
> CREATE TABLE I (
> ID BIGINT NOT NULL,
> ID2 BIGINT NOT NULL,
> CONSTRAINT FB_PK PRIMARY KEY (ID, ID2)
> );
> CREATE TABLE P (
> ID BIGINT NOT NULL,
> PG.C2 VARCHAR(100),
> PG.C3 VARCHAR(100)
> CONSTRAINT FB_PK PRIMARY KEY (ID)
> );
> EXPLAIN SELECT I.ID2
> FROM P INNER JOIN I ON P.ID = I.ID
> WHERE P.C3 = 'X';
> ====
> Now add an index & re-run the execution plan:
> ====
> CREATE INDEX P_IDX1 ON P (PG.C2);
> EXPLAIN SELECT I.ID2
> FROM P INNER JOIN I ON P.ID = I.ID
> WHERE P.C3 = 'X';
> ====
> You will get the error:
> Error: ERROR 504 (42703): Undefined column. columnName=P.C3
> (state=42703,code=504
> ====
> Adding an index that INCLUDEs that missing column works around the issue:
> ====
> CREATE INDEX P_IDX2 ON P (PG.C2) INCLUDE (PG.C3);
> EXPLAIN SELECT I.ID2
> FROM P INNER JOIN I ON P.ID = I.ID
> WHERE P.C3 = 'X';
> ====
> This fault is related also to the compound key in table I.
> Having a workaround with an anti-index hint would be helpful
--
This message was sent by Atlassian JIRA
(v6.2#6252)
