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

["Chris Wu (JIRA)" <ji...@apache.org>]

Chris Wu created PHOENIX-3742:
---------------------------------

             Summary: 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


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)