You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "James Taylor (JIRA)" <ji...@apache.org> on 2016/01/21 08:31:40 UTC
[jira] [Updated] (PHOENIX-2618) Allow filters on LHS of join to be
specified in ON clause
[ https://issues.apache.org/jira/browse/PHOENIX-2618?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Taylor updated PHOENIX-2618:
----------------------------------
Affects Version/s: 4.7.0
> 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
> Affects Versions: 4.7.0
> 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)