Zhen Chen created CALCITE-7387:
----------------------------------
Summary: 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
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)
