[CALCITE-6564] Support queries with measures on top of a VALUES relation

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 05/Sep/24 16:42
• Updated: 05/Sep/24 16:42

Description

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:

!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

but different error message now that case has been fixed:

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 }]])

Issue Links

• relates to: CALCITE-6559 Query with measure that applies AVG to SMALLINT throws AssertionError "Cannot add expression of different type to set"