[
https://issues.apache.org/jira/browse/CALCITE-5888?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750611#comment-17750611
]
Petr Masopust edited comment on CALCITE-5888 at 8/3/23 7:18 AM:
----------------------------------------------------------------
Here is SQL generated by version 1.34.0:
{{SELECT "a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f" AS
"a_label_d_opp_stage_id_foodmart_41e2413bb1a634",}}
{{TO_CHAR("a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{'YYYY-Q') AS "a_label_snapshot_timestamp_quarter_foodmart_7cbf8f9b996883",}}
{{CASE}}
{{WHEN "def_m_8e1ecf802d979518ddbe7edc88cebfac" THEN
"m_8e1ecf802d979518ddbe7edc88cebfac"}}
{{ELSE NULL END AS "m_1",}}
{{CASE}}
{{WHEN "def_m_caf6f7fe7cdb598f42a7160736195568" THEN
"m_caf6f7fe7cdb598f42a7160736195568"}}
{{ELSE NULL END AS "m_2"}}
{{FROM (SELECT "fact_opp_snapshot"."opp_stage_id" AS
"a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f",}}
{{DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") AS
"a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{COUNT(CASE}}
{{WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{"t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{THEN "fact_opp_snapshot"."id"}}
{{ELSE NULL END) AS "m_8e1ecf802d979518ddbe7edc88cebfac",}}
{{SUM(CASE}}
{{WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{"t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{THEN "fact_opp_snapshot"."f__amount"}}
{{ELSE NULL END) AS "m_caf6f7fe7cdb598f42a7160736195568",}}
{{MAX(CASE}}
{{WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{"t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number" THEN 0}}
{{ELSE NULL END) IS NOT NULL AS "def_m_8e1ecf802d979518ddbe7edc88cebfac",}}
{{MAX(CASE}}
{{WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{"t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number" THEN 0}}
{{ELSE NULL END) IS NOT NULL AS "def_m_caf6f7fe7cdb598f42a7160736195568"}}
{{FROM "foodmart_dceb1216814db263"."fact_opp_snapshot"}}
{{INNER JOIN (SELECT DATE_TRUNC('QUARTER', "snapshot_timestamp") AS
"a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{MIN("f__snapshot_number") AS "m_f847e358a546f6d69c5406ac373bc9c0",}}
{{TRUE AS "def_m_f847e358a546f6d69c5406ac373bc9c0"}}
{{FROM "foodmart_dceb1216814db263"."fact_opp_snapshot"}}
{{WHERE "opp_stage_id" = '5'}}
{{AND DATE_TRUNC('QUARTER', "snapshot_timestamp") =}}
{{CAST(TO_TIMESTAMP('2009-7', 'YYYY-MM') AS TIMESTAMP)}}
{{GROUP BY DATE_TRUNC('QUARTER', "snapshot_timestamp")) AS "t2"}}
{{ON DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") =}}
{{"t2"."a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9"}}
{{WHERE DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") =}}
{{CAST(TO_TIMESTAMP('2009-7', 'YYYY-MM') AS TIMESTAMP)}}
{{AND "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0"}}
{{AND "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{GROUP BY "fact_opp_snapshot"."opp_stage_id",}}
{{DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp")) AS "t6"}}
{{WHERE "t6"."a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f" = '5'}}
{{AND ("t6"."def_m_8e1ecf802d979518ddbe7edc88cebfac" OR
"t6"."def_m_caf6f7fe7cdb598f42a7160736195568");}}
Unfortunately formatting is broken but first of internal selects cannot be
generated in 1.35.0 version.
was (Author: pmsgd):
Here is SQL generated by version 1.34.0:
{{SELECT "a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f" AS
"a_label_d_opp_stage_id_foodmart_41e2413bb1a634",}}
{{ TO_CHAR("a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{ 'YYYY-Q') AS "a_label_snapshot_timestamp_quarter_foodmart_7cbf8f9b996883",}}
{{ CASE}}
{{ WHEN "def_m_8e1ecf802d979518ddbe7edc88cebfac" THEN
"m_8e1ecf802d979518ddbe7edc88cebfac"}}
{{ ELSE NULL END AS "m_1",}}
{{ CASE}}
{{ WHEN "def_m_caf6f7fe7cdb598f42a7160736195568" THEN
"m_caf6f7fe7cdb598f42a7160736195568"}}
{{ ELSE NULL END AS "m_2"}}
{{FROM (SELECT "fact_opp_snapshot"."opp_stage_id" AS
"a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f",}}
{{ DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") AS
"a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{ COUNT(CASE}}
{{ WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{ "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{ THEN "fact_opp_snapshot"."id"}}
{{ ELSE NULL END) AS "m_8e1ecf802d979518ddbe7edc88cebfac",}}
{{ SUM(CASE}}
{{ WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{ "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{ THEN "fact_opp_snapshot"."f__amount"}}
{{ ELSE NULL END) AS "m_caf6f7fe7cdb598f42a7160736195568",}}
{{ MAX(CASE}}
{{ WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{ "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number" THEN 0}}
{{ ELSE NULL END) IS NOT NULL AS "def_m_8e1ecf802d979518ddbe7edc88cebfac",}}
{{ MAX(CASE}}
{{ WHEN "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0" AND}}
{{ "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number" THEN 0}}
{{ ELSE NULL END) IS NOT NULL AS "def_m_caf6f7fe7cdb598f42a7160736195568"}}
{{ FROM "foodmart_dceb1216814db263"."fact_opp_snapshot"}}
{{ INNER JOIN (SELECT DATE_TRUNC('QUARTER', "snapshot_timestamp") AS
"a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9",}}
{{ MIN("f__snapshot_number") AS "m_f847e358a546f6d69c5406ac373bc9c0",}}
{{ TRUE AS "def_m_f847e358a546f6d69c5406ac373bc9c0"}}
{{ FROM "foodmart_dceb1216814db263"."fact_opp_snapshot"}}
{{ WHERE "opp_stage_id" = '5'}}
{{ AND DATE_TRUNC('QUARTER', "snapshot_timestamp") =}}
{{ CAST(TO_TIMESTAMP('2009-7', 'YYYY-MM') AS TIMESTAMP)}}
{{ GROUP BY DATE_TRUNC('QUARTER', "snapshot_timestamp")) AS "t2"}}
{{ ON DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") =}}
{{ "t2"."a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9"}}
{{ WHERE DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp") =}}
{{ CAST(TO_TIMESTAMP('2009-7', 'YYYY-MM') AS TIMESTAMP)}}
{{ AND "t2"."def_m_f847e358a546f6d69c5406ac373bc9c0"}}
{{ AND "t2"."m_f847e358a546f6d69c5406ac373bc9c0" =
"fact_opp_snapshot"."f__snapshot_number"}}
{{ GROUP BY "fact_opp_snapshot"."opp_stage_id",}}
{{ DATE_TRUNC('QUARTER', "fact_opp_snapshot"."snapshot_timestamp")) AS "t6"}}
{{WHERE "t6"."a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f" = '5'}}
{{ AND ("t6"."def_m_8e1ecf802d979518ddbe7edc88cebfac" OR
"t6"."def_m_caf6f7fe7cdb598f42a7160736195568");}}
Unfortunately formatting is broken but first of internal selects cannot be
generated in 1.35.0 version.
> Assertion error in aggregate
> ----------------------------
>
> Key: CALCITE-5888
> URL: https://issues.apache.org/jira/browse/CALCITE-5888
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Petr Masopust
> Priority: Major
>
> We have {{relBuilder.aggregate(groupKey, aggregateCalls)}} in our code with
> values {{[AS($31, 'a_label_d_opp_stage_id_foodmart_bd4c9c91212f9f'),
> AS(CAST(FLOOR($34, FLAG(QUARTER))):TIMESTAMP(0),
> 'a_label_snapshot_timestamp_quarter_foodmart_89fe710c8628d9')]}} and
> {{[COUNT(CASE(AND(COALESCE($37, false), =($36, $13)), $15, null:NULL)),
> SUM(CASE(AND(COALESCE($37, false), =($36, $13)), $2, null:NULL)),
> MAX(CASE(AND(COALESCE($37, false), =($36, $13)), 0, null:NULL)),
> MAX(CASE(AND(COALESCE($37, false), =($36, $13)), 0, null:NULL))].}}
> It works perfectly in version 1.34.0 but in 1.35.0 we got this exception:
> {{java.lang.AssertionError}}
> {{ at org.apache.calcite.rel.core.Aggregate.<init>(Aggregate.java:175)}}
> {{ at
> org.apache.calcite.rel.logical.LogicalAggregate.<init>(LogicalAggregate.java:72)}}
> {{ at
> org.apache.calcite.rel.logical.LogicalAggregate.create_(LogicalAggregate.java:144)}}
> {{ at
> org.apache.calcite.rel.logical.LogicalAggregate.create(LogicalAggregate.java:116)}}
> {{ at
> org.apache.calcite.rel.core.RelFactories$AggregateFactoryImpl.createAggregate(RelFactories.java:328)}}
> {{ at
> org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2564)}}
> {{ at
> org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2511)}}
> {{ at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2348)}}
>
> I think it is either missing {{permute}} or assert should compare
> {{cardinality}} instead of {{length.}} Because it compares field index? to
> number of fields which looks like nonsense to me.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
