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