[
https://issues.apache.org/jira/browse/CALCITE-7387?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-7387.
--------------------------------
Fix Version/s: 1.42.0
Assignee: Zhen Chen
Resolution: Duplicate
> 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
> Assignee: Zhen Chen
> Priority: Major
> Fix For: 1.42.0
>
>
> 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)
