Julian Hyde created CALCITE-6564:
------------------------------------
Summary: Support queries with measures on top of a VALUES relation
Key: CALCITE-6564
URL: https://issues.apache.org/jira/browse/CALCITE-6564
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
A query with measures, where the underlying data set is a VALUES relational
literal rather than a table, cannot be planned. Same test case as CALCITE-6559:
{noformat}
!set outputformat mysql
!use scott
WITH tbl_with_null_dim AS (
SELECT e.deptno, e.grade, AVG(e.grade) AS MEASURE avg_grade
FROM (VALUES (1, 70),
(1, 50),
(NULL, 50),
( 3, 82)) AS e (deptno, grade))
SELECT deptno, avg_grade FROM tbl_with_null_dim
GROUP BY deptno;
+--------+-----------+
| DEPTNO | AVG_GRADE |
+--------+-----------+
| 1 | 60 |
| 3 | 82 |
| | 50 |
+--------+-----------+
(3 rows)
!ok
{noformat}
but different error message now that case has been fixed:
{noformat}
java.sql.SQLException: Error while executing SQL "WITH tbl_with_null_dim ...":
There are not enough rules to produce a node with desired properties:
convention=ENUMERABLE, sort=[].
Missing conversion is LogicalAggregate[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#2488:RelSubset#2.ENUMERABLE.[], the relevant part
of the original plan is as follows
2456:LogicalAggregate(group=[{0}], agg#0=[AGG_M2M($1)])
2454:LogicalProject(subset=[rel#2455:RelSubset#1.NONE.[]], DEPTNO=[$0],
AVG_GRADE=[V2M(CAST(/(SUM($1), COUNT($1))):INTEGER NOT NULL)])
2348:LogicalValues(subset=[rel#2453:RelSubset#0.NONE.[]], tuples=[[{ 1, 70
}, { 1, 50 }, { null, 50 }, { 3, 82 }]])
{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
