[jira] [Created] (PHOENIX-2618) Allow filters on LHS of join to be specified in ON clause

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

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)