[CALCITE-1992] RelNode to Query conversion

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.12.0
• Fix Version/s: 1.15.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Sanjai Verma
• Created: 23/Sep/17 12:28
• Updated: 09/Dec/17 18:24
• Resolved: 02/Oct/17 18:52

Description

I convert a query

select rpt_unit_num,month(acct_dt),sum(balance) as bal_year from transaction_bal where year(acct_dt)=2017 group by rpt_unit_num,month(acct_dt)  

to RelNode rel:

LogicalAggregate(group=[{0, 1}], bal_year=[SUM($2)])
  LogicalProject(rpt_unit_num=[$4], EXPR$1=[EXTRACT_DATE(FLAG(MONTH), Reinterpret($23))], balance=[$24])
    LogicalFilter(condition=[=(EXTRACT_DATE(FLAG(YEAR), Reinterpret($23)), 2017)])
      JdbcTableScan(table=[[svayam_data, transaction_bal]])

but when I convert RelNode rel to Query again using the code:

SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect();
		 RelToSqlConverter rlToSql=new RelToSqlConverter(dialect);
                      if(rel instanceof Aggregate){
                      Aggregate p=(Aggregate)rel;
			  Result rs= rlToSql.visit(p);
			  SqlSelect sqSelect=rs.asSelect();
			String  sql=  sqSelect.toSqlString(dialect).getSql();
}

It gives the exception:

java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlSyntax$6: SPECIAL
	at org.apache.calcite.util.Util.needToImplement(Util.java:925)
	at org.apache.calcite.sql.SqlSyntax$6.unparse(SqlSyntax.java:115)
	at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:332)
	at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:99)
	at org.apache.calcite.sql.SqlNodeList.commaList(SqlNodeList.java:121)
	at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:349)
	at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:338)
	at org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:152)
	at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:234)
	at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:141)
	at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:147)
	at changeQuery.BuilRelFinal.printNodeInstance(BuilRelFinal.java:469)

Issue Links

• duplicates: CALCITE-1987 EXTRACT is not implemented for JDBC