[CALCITE-771] Use materialization for scan-project-sort query

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 25/Jun/15 17:19
• Updated: 27/Sep/21 09:10

Description

If a materialization is defined for scan-project-sort over another table, then scan-project and scan-project-sort queries should be able to use it.

For example, given the following schema (in pseudo-DDL):

CREATE TABLE Emp (empid, deptno, gender);
CREATE MATERIALIZATION I_Emp_Deptno AS
  SELECT deptno, empid FROM Emp ORDER BY deptno;

Calcite should rewrite the query

SELECT deptno FROM Emp ORDER BY deptno

to

Project(deptno)
  TableScan(table=[I_Emp_Deptno])

Note that no sort is necessary.

This rewrite is important for using secondary indexes.

Issue Links

• is duplicated by: CALCITE-4807 Materialized views do not support SQL with limit and order by well
• is duplicated by: CALCITE-3626 Can't apply materialized view with a simple sort
• links to: GitHub Pull Request #1274