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

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