James Taylor created PHOENIX-2618:
-------------------------------------
Summary: Allow filters on LHS of join to be specified in ON clause
Key: PHOENIX-2618
URL: https://issues.apache.org/jira/browse/PHOENIX-2618
Project: Phoenix
Issue Type: Bug
Reporter: James Taylor
Assignee: Maryann Xue
The query below sets index tables to be immutable if their associated data
table is also immutable (part of a fix for PHOENIX-2616). I tried to put all
the WHERE conditions into the ON clause, but I got an error that non equi-joins
aren't supported.
So this worked:
{code}
UPSERT INTO
SYSTEM.CATALOG(TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,IMMUTABLE_ROWS)
SELECT A.TENANT_ID,A.TABLE_SCHEM,B.COLUMN_FAMILY, NULL, NULL, TRUE
FROM SYSTEM.CATALOG A JOIN SYSTEM.CATALOG B ON (
A.TENANT_ID = B.TENANT_ID AND
A.TABLE_SCHEM = B.TABLE_SCHEM AND
A.TABLE_NAME = B.TABLE_NAME AND
A.COLUMN_NAME = B.COLUMN_NAME AND
B.LINK_TYPE = 1
)
WHERE A.COLUMN_FAMILY IS NULL AND
B.COLUMN_FAMILY IS NOT NULL AND
A.IMMUTABLE_ROWS = TRUE
{code}
But this didn't:
{code}
UPSERT INTO
SYSTEM.CATALOG(TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,IMMUTABLE_ROWS)
SELECT A.TENANT_ID,A.TABLE_SCHEM,B.COLUMN_FAMILY, NULL, NULL, TRUE
FROM SYSTEM.CATALOG A JOIN SYSTEM.CATALOG B ON (
A.TENANT_ID = B.TENANT_ID AND
A.TABLE_SCHEM = B.TABLE_SCHEM AND
A.TABLE_NAME = B.TABLE_NAME AND
A.COLUMN_NAME = B.COLUMN_NAME AND
B.LINK_TYPE = 1 AND
A.COLUMN_FAMILY IS NULL AND
B.COLUMN_FAMILY IS NOT NULL AND
A.IMMUTABLE_ROWS = TRUE
)
{code}
An IS NULL check is really the same as an equality expression and not sure why
the boolean comparison wasn't accepted.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
