[
https://issues.apache.org/jira/browse/CALCITE-7387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18058575#comment-18058575
]
Zhen Chen commented on CALCITE-7387:
------------------------------------
This Jira is same as CALCITE-7411. Fix via
[{{869fe15}}|https://github.com/apache/calcite/commit/869fe15f36fa5579f2a1cf289958fa763af36a9b]
> Query after expansion of measure Correlation variable $cor0 should be defined
> -----------------------------------------------------------------------------
>
> Key: CALCITE-7387
> URL: https://issues.apache.org/jira/browse/CALCITE-7387
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Zhen Chen
> Priority: Major
>
> An error will occur when executing the following SQL in CoreQuidemTest2.
> {code:java}
> # Listing 5: Query after expansion of measure
> !use scott
> # Table 1: Customers table
> CREATE VIEW "Customers" AS
> SELECT * FROM (VALUES
> ('Alice', 23),
> ('Bob', 41),
> ('Celia', 17))
> AS t ("custName", "age");
> (0 rows modified)
> !update
> # Table 2: Orders table
> CREATE VIEW "Orders" AS
> SELECT * FROM (VALUES
> ('Happy', 'Alice', DATE '2023-11-28', 6, 4),
> ('Acme', 'Bob', DATE '2023-11-27', 5, 2),
> ('Happy', 'Alice', DATE '2024-11-28', 7, 4),
> ('Whizz', 'Celia', DATE '2023-11-25', 3, 1),
> ('Happy', 'Bob', DATE '2022-11-27', 4, 1))
> AS t ("prodName", "custName", "orderDate", "revenue", "cost");
> (0 rows modified)
> !update
> SELECT "prodName",
> (SELECT
> CAST(SUM(i."revenue") - SUM(i."cost") AS DECIMAL(4, 2)) /
> SUM(i."revenue")
> FROM "Orders" AS i
> WHERE i."prodName" = o."prodName") AS "profitMargin",
> COUNT(*) AS "count"
> FROM "Orders" AS o
> GROUP BY "prodName";
> +----------+--------------+-------+
> | prodName | profitMargin | count |
> +----------+--------------+-------+
> | Acme | 0.600000 | 1 |
> | Happy | 0.470588 | 3 |
> | Whizz | 0.666666 | 1 |
> +----------+--------------+-------+
> (3 rows)
> !ok {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
