PreparedStatement throw exception "cannot translate expression ?0"

[Jiang Xu]

Hi Guys,

I'm building a olap query engine based optiq. But when I try to use PreparedStatement, I got the following exception. I have 2 questions for PreparedStatement:

1. Does optiq JDBC support question mark "?" in PreparedStatement? 

2. Is there any reference implementation of optiq PreparedStatement? I can't find it in optiq-cvs, optiq-mongo and optiq-splunk?

3. How can I plugin the logic of value setting in relational expression? Which method of RelNode should be override?

Thanks

Xu

java.sql.SQLException: Error while preparing statement [select calendar.cal_year, site.site, sum(usd_amt) as sum_usd_amt, sum(listing_cnt) as sum_listing_cnt from item inner join calendar on calendar.id = item.calendar_id  inner join site on item.site_id = site.id where calendar.cal_year = ? and site.site = ? group by calendar.cal_year, site.site]

at net.hydromatic.optiq.jdbc.Helper.createException(Helper.java:40)

at net.hydromatic.optiq.jdbc.OptiqConnectionImpl.prepareStatement(OptiqConnectionImpl.java:341)

at net.hydromatic.optiq.jdbc.OptiqConnectionImpl.prepareStatement(OptiqConnectionImpl.java:171)

at com.abc.olap.optiq.test.StarSchemaTest.test10(StarSchemaTest.java:124)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: java.lang.RuntimeException: cannot translate expression ?0

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:318)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:293)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexImpTable.implementNullSemantics(RexImpTable.java:537)

at net.hydromatic.optiq.rules.java.RexImpTable.implementNullSemantics0(RexImpTable.java:492)

at net.hydromatic.optiq.rules.java.RexImpTable.access$800(RexImpTable.java:47)

at net.hydromatic.optiq.rules.java.RexImpTable$1.implement(RexImpTable.java:230)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:313)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:293)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translateList(RexToLixTranslator.java:424)

at net.hydromatic.optiq.rules.java.RexImpTable$2.implement(RexImpTable.java:252)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:313)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:293)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:128)

at net.hydromatic.optiq.rules.java.RexToLixTranslator.translateCondition(RexToLixTranslator.java:453)

at net.hydromatic.optiq.rules.java.JavaRules$EnumerableCalcRel.implement(JavaRules.java:570)

at com.abc.olap.optiq.rel.OLAPCalcRel.implement(OLAPCalcRel.java:101)

at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:61)

at net.hydromatic.optiq.rules.java.JavaRules$EnumerableAggregateRel.implement(JavaRules.java:763)

at com.abc.olap.optiq.rel.OLAPAggregateRel.implement(OLAPAggregateRel.java:118)

at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:61)

at com.abc.olap.optiq.rel.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:69)

at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:66)

at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:637)

at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:227)

at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:131)

at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:309)

at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:220)

at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:190)

at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:403)

at net.hydromatic.optiq.jdbc.OptiqPreparedStatement.<init>(OptiqPreparedStatement.java:61)

at net.hydromatic.optiq.jdbc.FactoryJdbc41$OptiqPreparedStatementJdbc41.<init>(FactoryJdbc41.java:152)

at net.hydromatic.optiq.jdbc.FactoryJdbc41.newPreparedStatement(FactoryJdbc41.java:91)

at net.hydromatic.optiq.jdbc.FactoryJdbc4Impl.newPreparedStatement(FactoryJdbc4Impl.java:23)

at net.hydromatic.optiq.jdbc.OptiqConnectionImpl.prepareStatement(OptiqConnectionImpl.java:331)

... 27 more

[Julian Hyde]

On Nov 19, 2013, at 1:20 AM, Jiang Xu <jiangx...@gmail.com> wrote:

> Hi Guys,
>
> I'm building a olap query engine based optiq. But when I try to use PreparedStatement, I got the following exception. I have 2 questions for PreparedStatement:
>
> 1. Does optiq JDBC support question mark "?" in PreparedStatement?

We intend to support it, but we've not fully tested it. I believe that parsing and validation works. From your call stack, it looks like RexToLixTranslator is not handling it. Please log an issue in github.

> 2. Is there any reference implementation of optiq PreparedStatement? I can't find it in optiq-cvs, optiq-mongo and optiq-splunk?

PreparedStatement (without bind variables) works. JdbcTest.testCurrentTimestamp uses it.

You'll see that net.hydromatic.optiq.jdbc.OptiqPreparedStatement implements PreparedStatement. Factory creates an appropriate sub-class depending on the JDBC version.

> 3. How can I plugin the logic of value setting in relational expression? Which method of RelNode should be override?

PreparedStatement is the right way to do it. After planning, the parameter values will be available via RexDynamicParam expressions. Those would be implemented differently for each convention. In enumerable convention, for instance, we'd probably generate

((Integer) root.get("_0"))

(where "root" is the variable of type DataContext available in every statement) to get the value of the first parameter, whose type is int.

Julian

[Jiang Xu]

Hi Julian,

Thanks a lot for your quick fix! I still have some questions about DataContext. 

Since I hope to pushdown the prediction to our storage engine, I need to get the binding value of RexDynamicParam in my plugin code (i.e. Enumerator & RelNode & RelOptRule & Schema). 

1. Is it possible to get DataContext variable (i.e. root) in RelNode or Enumerator? Then I can run  root.get("_0")) to get binding variable.

2. Otherwise, how can I get root variable in my plugin code?

Thanks

Xu

[Julian Hyde]

On Nov 21, 2013, at 1:25 AM, Jiang Xu <jiangx...@gmail.com> wrote:

> Since I hope to pushdown the prediction to our storage engine, I need to get the binding value of RexDynamicParam in my plugin code (i.e. Enumerator & RelNode & RelOptRule & Schema).
>
> 1. Is it possible to get DataContext variable (i.e. root) in RelNode or Enumerator? Then I can run root.get("_0")) to get binding variable.

When you are generating code in your RelNode's implement method, call getRootExpression(). This will return the variable called "root".

Actually the variable is called "?0". (We should probably add a method to JavaRelImplementor to generate the expression for getting each bind variable.)

Julian

[Jiang Xu]

Thanks a lot! It works. I use getRootExpression() to pass "root" variable to Enumerator.