phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Maryann Xue (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-2618) Allow filters on LHS of join to be specified in ON clause
Date Thu, 04 Feb 2016 21:09:40 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-2618?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15133035#comment-15133035
] 

Maryann Xue commented on PHOENIX-2618:
--------------------------------------

[~jamestaylor], Could you please help review the patch?

> 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
>         Attachments: PHOENIX-2618.patch
>
>
> 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)

Mime
View raw message