Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3742

GROUP BY and WHERE IN (SELECT...) in queries throw IllegalArgumentException

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.6.1
    • Fix Version/s: None
    • Environment:

      Linux Redhat

    • Flags:
      Important

      Description

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              christofu26 Chris Wu
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: