[
https://issues.apache.org/jira/browse/CALCITE-4376?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin updated CALCITE-4376:
---------------------------------
Description:
When target project a different column sequence with group by, materialized
view recognition will fail, see the case below
{noformat}
@Test void testDifferentGroupBySequence() {
final String mv = "" +
"select \"deptno\", \"name\" from ("
+ "select \"name\", \"deptno\", \"commission\"\n"
+ "from \"emps\"\n"
+ " group by \"name\", \"deptno\", \"commission\") t";
final String query = ""
+ "select \"deptno\", \"name\"\n"
+ "from \"emps\"\n"
+ "group by \"deptno\", \"name\"";
sql(mv, query).withChecker(
resultContains(""
+ "EnumerableTableScan(table=[[hr, MV0]])")).ok();
}
{noformat}
was:
When target project a different column sequence with group by, materialized
view recognition will fail, see the case below
{noformat}
@Test void testDifferentGroupBySequence() {
final String mv = "" +
"select \"deptno\", \"name\" from ("
+ "select \"name\", \"deptno\", \"commission\"\n"
+ "from \"emps\"\n"
+ " group by \"name\", \"deptno\", \"commission\") t";
final String query = ""
+ "select \"deptno\", \"name\"\n"
+ "from \"emps\"\n"
+ "group by \"deptno\", \"name\"";
sql(mv, query).withChecker(
resultContains(""
+ "EnumerableTableScan(table=[[hr, MV0]])")).ok();
}
{noformat}
After the call of "AggregateOnCalcToAggregateUnifyRule", the result query is
{noformat}
Holder
Calc(program: (expr#0..1=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1}, groupSets: [{0, 1}], calls: [])
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
{noformat}
, and the target is
{noformat}
Calc(program: (expr#0..2=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
{noformat}
There is no match for
{noformat}
query descendant: Aggregate(groupSet: {1, 2}, groupSets: [{1, 2}], calls: [])
target descendant: Calc(program: (expr#0..2=[{inputs}], deptno=[$t1],
name=[$t0]))
{noformat}
and the recognition fails. Maybe always add a calc in the call result of
"AggregateOnCalcToAggregateUnifyRule", make the call result like this
{noformat}
Holder
Calc(program: (expr#0..1=[{inputs}], deptno=[$t1], name=[$t0]))
Aggregate(groupSet: {0, 1}, groupSets: [{0, 1}], calls: [])
Calc (xxxx) // always add a calc here
Aggregate(groupSet: {0, 1, 2}, groupSets: [{0, 1, 2}], calls: [])
Calc(program: (expr#0..4=[{inputs}], name=[$t2], deptno=[$t1],
commission=[$t4]))
Scan(table: [hr, emps])
{noformat}
> Materialized view recognition fails when target project different column
> sequence with GROUP BY
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-4376
> URL: https://issues.apache.org/jira/browse/CALCITE-4376
> Project: Calcite
> Issue Type: Bug
> Reporter: Wang Yanlin
> Assignee: Wang Yanlin
> Priority: Major
>
> When target project a different column sequence with group by, materialized
> view recognition will fail, see the case below
> {noformat}
> @Test void testDifferentGroupBySequence() {
> final String mv = "" +
> "select \"deptno\", \"name\" from ("
> + "select \"name\", \"deptno\", \"commission\"\n"
> + "from \"emps\"\n"
> + " group by \"name\", \"deptno\", \"commission\") t";
> final String query = ""
> + "select \"deptno\", \"name\"\n"
> + "from \"emps\"\n"
> + "group by \"deptno\", \"name\"";
> sql(mv, query).withChecker(
> resultContains(""
> + "EnumerableTableScan(table=[[hr, MV0]])")).ok();
> }
> {noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
