Andrew Liles created PHOENIX-1052:
-------------------------------------
Summary: 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:
====
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)
