[CALCITE-1987] EXTRACT is not implemented for JDBC

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.14.0
• Fix Version/s: 1.15.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Pavel Gubin
• Created: 14/Sep/17 12:50
• Updated: 09/Dec/17 18:21
• Resolved: 02/Oct/17 21:08

Description

The following test fails with exception being added to JdbcTest:

  @Test public void testExtractMonthFromTimestamp() {
    CalciteAssert.that()
        .with(CalciteAssert.Config.JDBC_FOODMART)
        .query("select extract(month from \"birth_date\") as c \n"
            + "from \"foodmart\".\"employee\" where \"employee_id\"=1")
        .returns("C=8\n");
  }

Note, that it is important to make select from real table and not using limit so that planner tries to push EXTRACT to JdbcProject. But then it throws the following exception ():

Caused by: java.sql.SQLException: Error while executing SQL "select extract(month from "birth_date") as c 
from "foodmart"."employee" where "employee_id"=1": class org.apache.calcite.sql.SqlSyntax$6: SPECIAL
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)
	at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:564)
	... 26 more
Caused by: java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlSyntax$6: SPECIAL
	at org.apache.calcite.util.Util.needToImplement(Util.java:923)
	at org.apache.calcite.sql.SqlSyntax$6.unparse(SqlSyntax.java:116)
	at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:332)
	at org.apache.calcite.sql.SqlDialect$BaseHandler.unparseCall(SqlDialect.java:733)
...

Issue Links

• is duplicated by: CALCITE-1175 extract() from PostgreSQL timestamp column throws org.apache.calcite.sql.SqlSyntax$6: SPECIAL
• is duplicated by: CALCITE-1992 RelNode to Query conversion
• is related to: CALCITE-1793 Allow RelBuilder to make calls to EXTRACT
• links to: PR 539