[jira] [Commented] (PHOENIX-3742) GROUP BY and WHERE IN (SELECT...) in queries throw IllegalArgumentException

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

    [ 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)