phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-3742) GROUP BY and WHERE IN (SELECT...) in queries throw IllegalArgumentException
Date Thu, 16 Mar 2017 21:09:42 GMT

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

James Taylor commented on PHOENIX-3742:
---------------------------------------

[~maryannxue]

> GROUP BY and WHERE IN (SELECT...) in queries throw IllegalArgumentException
> ---------------------------------------------------------------------------
>
>                 Key: PHOENIX-3742
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3742
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.6.1
>         Environment: Linux Redhat
>            Reporter: Chris Wu
>              Labels: apache, groupby, hbase, joins, phoenix
>
> It looks like Phoenix SQL is unable to handle SQL joins where one subquery has a WHERE
column IN (SELECT ...) and the other one has a GROUP BY. To demonstrate, consider the following
example:
> CREATE TABLE temptable1(
>   TRACT_GEOID integer not null,
>   COUNTY_GEOID integer
>   CONSTRAINT PK PRIMARY KEY(TRACT_GEOID)
> );
> upsert into temptable1 values(11,1);
> upsert into temptable1 values(12,1);
> upsert into temptable1 values(23,2);
> upsert into temptable1 values(24,2);
> upsert into temptable1 values(35,3);
> CREATE TABLE temptable2(
>   TRACT_GEOID integer,
>   THINGS integer
>   CONSTRAINT PK PRIMARY KEY(TRACT_GEOID)
> );
> upsert into temptable1 values(11,10);
> upsert into temptable1 values(12,20);
> upsert into temptable1 values(23,30);
> upsert into temptable1 values(44,22);
> upsert into temptable1 values(55,33);
> SELECT
>   G.COUNTY_GEOID,
>   SUM(M.THINGS) AS THINGS
> FROM(
>   SELECT
>     TRACT_GEOID,
>     THINGS
>   FROM
>     temptable2
>   WHERE
>     TRACT_GEOID IN (
>       SELECT
>         DISTINCT TRACT_GEOID
>       FROM
>         GEOCROSSWALK
>       WHERE
>         COUNTY_GEOID IN (1,2)
>     )) AS M
>   INNER JOIN(
>     SELECT
>       COUNTY_GEOID,
>       TRACT_GEOID
>     FROM
>       GEOCROSSWALK
>     GROUP BY
>       COUNTY_GEOID,
>       TRACT_GEOID
>   ) AS G
>   ON
>     G.TRACT_GEOID = M.TRACT_GEOID
> GROUP BY
>   G.COUNTY_GEOID;
> If you remove group by on the right table or the where clause in the left table, the
query will work. But having the two together in the join will cause an Illegal Argument Exception



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message