[
https://issues.apache.org/jira/browse/CALCITE-7138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lino Rosa closed CALCITE-7138.
------------------------------
Resolution: Invalid
My mistake. The problem here was the dialect I was using (Snowflake) should
have been marked as {{hasImplicitTableAlias = false.}} Everything works as it
should once I flip that.
> RelToSqlConverterTest drops subquery boundary and inflates COUNT results
> ------------------------------------------------------------------------
>
> Key: CALCITE-7138
> URL: https://issues.apache.org/jira/browse/CALCITE-7138
> Project: Calcite
> Issue Type: Bug
> Reporter: Lino Rosa
> Priority: Major
>
> Take this input query:
> {code:java}
> SELECT
> COUNT(CASE WHEN has_salesman = 1 AND has_high_salary = 1 THEN 1 END) AS
> depts_with_salesman_and_high_salary,
> COUNT(CASE WHEN has_salesman = 1 THEN 1 END) AS depts_with_salesman,
> COUNT(CASE WHEN has_high_salary = 1 THEN 1 END) AS depts_with_high_salary
> FROM (
> SELECT
> MAX(CASE WHEN "position_title" = 'SALESMAN' THEN 1 ELSE 0 END) AS
> has_salesman,
> MAX(CASE WHEN "salary" > 1250 THEN 1 ELSE 0 END) AS has_high_salary
> FROM "foodmart"."employee"
> GROUP BY "department_id"
> ) AS dept_flags;{code}
> After going through `SqlToRelConverter`, the resulting SQL will have the
> GROUP BY from the subquery inlined into a single query:
> {code:java}
> SELECT
> COUNT(
> CASE
> WHEN MAX(CASE WHEN "position_title" = 'SALESMAN' THEN 1 ELSE 0
> END) = 1
> AND MAX(CASE WHEN CAST("salary" AS DECIMAL(14, 4)) > 1250.0000
> THEN 1 ELSE 0 END) = 1
> THEN 1
> ELSE NULL
> END
> ) AS "DEPTS_WITH_SALESMAN_AND_HIGH_SALARY",
> COUNT(
> CASE
> WHEN MAX(CASE WHEN "position_title" = 'SALESMAN' THEN 1 ELSE 0
> END) = 1
> THEN 1
> ELSE NULL
> END
> ) AS "DEPTS_WITH_SALESMAN",
> COUNT(
> CASE
> WHEN MAX(CASE WHEN CAST("salary" AS DECIMAL(14, 4)) > 1250.0000
> THEN 1 ELSE 0 END) = 1
> THEN 1
> ELSE NULL
> END
> ) AS "DEPTS_WITH_HIGH_SALARY"
> FROM "foodmart"."employee"
> GROUP BY "department_id";
> {code}
> This is not equivalent to the first query.
> The first query counts departments, while the second—due to the GROUP BY
> department_id and how COUNT(...) is used—counts rows (employees) inside each
> qualifying department.
> For example, for this dataset:
> ||department_id||position_title||salary||
> |10|SALESMAN|1000|
> |10|CLERK|3000|
> |20|MANAGER|2000|
> |30|SALESMAN|1100|
>
> The first query results in:
> ||depts_with_salesman_and_high_salary||depts_with_salesman||depts_with_high_salary||
> |1|2|2|
>
> While the second query returns {*}one row per department{*}, and the numbers
> are row counts, not department counts:
> ||depts_with_salesman_and_high_salary||depts_with_salesman||depts_with_high_salary||
> |2|2|2|
> |0|0|1|
> |0|1|0|
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
