You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Chin Wei Low <lo...@gmail.com> on 2015/01/16 09:31:11 UTC
Pushdown filter in prepared statement
Hi,
I am implementing a calcite adapter, and created a rule to pushdown filter.
When I execute a prepared statement, I can see the condition contains a
RexDynamicParam with value "?0" in the LogicalFilter.
How can I get the value of the bind variable, so I can push it down to the
underlying data source.
Regards,
Chin Wei
Re: Pushdown filter in prepared statement
Posted by Julian Hyde <ju...@gmail.com>.
Please try the latest. My fix for https://issues.apache.org/jira/browse/CALCITE-458 fixed all of these issues.
Julian
> On Jan 21, 2015, at 11:49 PM, Chin Wei Low <lo...@gmail.com> wrote:
>
> Hi,
>
> The new code can support logical expression. But, it still fail with the
> following:
>
> java.sql.SQLException: error while executing SQL "select "i","k" from
> "s"."beatles" where "k" = 4": 2
> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
> at
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> 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.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.ArrayIndexOutOfBoundsException: 2
> at Buzz.execute(Buzz.java:2)
> at Buzz.execute(Buzz.java:8)
> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:47)
> at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:99)
> at
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:66)
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> at
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> ... 24 more
>
>
> On Thu, Jan 22, 2015 at 1:02 PM, Julian Hyde <ju...@hydromatic.net> wrote:
>
>> If that other issue doesn't work on the latest, please log a bug. But I
>> think it will.
>>
>> Julian
>>
>>> On Jan 21, 2015, at 20:01, Chin Wei Low <lo...@gmail.com> wrote:
>>>
>>> Thanks. I will give it a try.
>>>
>>> Another issue is the Interpreter does not support logical expression,
>> using
>>> the sql:
>>> select k,i from beatles where i = 4 and k = 1940
>>>
>>> java.lang.AssertionError: unknown expression AND(=($0, 4), =($2, 1940))
>>> at
>>>
>> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:170)
>>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
>>> at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
>>> at
>>>
>> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
>>> at
>>>
>> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
>>> at
>>>
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
>>> at
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
>>> at
>>>
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
>>> at
>>>
>> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:191)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>> at java.lang.reflect.Method.invoke(Unknown Source)
>>> 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.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
>>>
>>>> On Thu, Jan 22, 2015 at 11:55 AM, Julian Hyde <ju...@gmail.com>
>> wrote:
>>>>
>>>> Yes, that issue looks similar. I made some changes in that area in
>> today’s
>>>> commit 66cfb12. It might just be fixed… worth a try…
>>>>
>>>> Julian
>>>>
>>>>
>>>>
>>>>> On Jan 21, 2015, at 7:50 PM, Chin Wei Low <lo...@gmail.com>
>> wrote:
>>>>>
>>>>> Browse through Jira, looks like it has been raised in
>>>>> https://issues.apache.org/jira/browse/CALCITE-458
>>>>>
>>>>>> On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Yes, this case should work. Can you log a jira case for this?
>>>>>>
>>>>>> Regarding column ordinals: the intent of the API is that filter is
>>>> applied
>>>>>> before the project, so the filter should be in terms of the column
>>>> ordinals
>>>>>> in the table, not the column ordinals output by the project. It makes
>>>> sense
>>>>>> to filter first, so that the project has fewer rows to deal with. Of
>>>> course
>>>>>> some implementations might filter and project simultaneously.
>>>>>>
>>>>>> Julian
>>>>>>
>>>>>>
>>>>>>> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com>
>>>> wrote:
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>> Basically, my requirements are:
>>>>>>> 1. pushdown projection
>>>>>>> 2. pushdown filters that are supported by the underlying data source,
>>>> and
>>>>>>> leave the unsupported one to Calcite.
>>>>>>>
>>>>>>> I found that I should be able to achieve that with
>>>>>>> ProjectableFilterableTable.
>>>>>>> But, there is issue using this interface.
>>>>>>> It throw exception with the projection column plus filter. For
>>>> example, I
>>>>>>> change one of the query of the ScannableTableTest to filter by column
>>>> k.
>>>>>>> This will not pushdown the filter, so Calcite will handle it.
>>>>>>>
>>>>>>> java.sql.SQLException: error while executing SQL "select "i","k" from
>>>>>>> "s"."beatles" where "k" = 1940": 2
>>>>>>> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
>>>>>>> at
>>>>
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
>>>>>>> at
>>>>
>> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
>>>>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>>>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>>>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>>>>>> at java.lang.reflect.Method.invoke(Unknown Source)
>>>>>>> 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.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.ArrayIndexOutOfBoundsException: 2
>>>>>>> at
>>>>
>> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
>>>>>>> at
>>>>>>
>> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
>>>>>>> at
>>>>
>> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
>>>>>>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
>>>>>>> at
>>>> org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
>>>>>>> at
>>>>
>> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
>>>>>>> at
>>>>
>> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>>>>>>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
>>>>>>> at
>>>>
>> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
>>>>>>> at
>>>>
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
>>>>>>> at
>>>>
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
>>>>>>> at
>>>>
>> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
>>>>>>> at
>>>>
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
>>>>>>> at
>>>>
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
>>>>>>> at
>>>>
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
>>>>>>> ... 24 more
>>>>>>>
>>>>>>> From the console:
>>>>>>> Interpreter: rewrite
>>>>
>> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
>>>>>>> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
>>>>>>> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
>>>>>>> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
>>>>>>>
>>>>>>> The filter/condition is [=($2, 1940)]
>>>>>>>
>>>>>>> I think the index of the condition/filter's column does not map to
>> the
>>>>>>> projected columns index. For this case the index of column 'K' after
>>>>>>> projection should be 1 instead of 2.
>>>>>>>
>>>>>>> Regards,
>>>>>>> Chin Wei
>>>>>>>
>>>>>>>> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> IIRC, bind variables are accessible via DataContext.get("$0"), or
>> some
>>>>>>>> similar variable name. Take a look at the contents of the
>>>>>>>> DataContextImpl.map field in a debugger. In generated java code,
>>>>>>>> DataContext is available as a field called "root".
>>>>>>>>
>>>>>>>> Julian
>>>>>>>>
>>>>>>>>
>>>>>>>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> I am implementing a calcite adapter, and created a rule to pushdown
>>>>>>>> filter.
>>>>>>>>> When I execute a prepared statement, I can see the condition
>>>> contains a
>>>>>>>>> RexDynamicParam with value "?0" in the LogicalFilter.
>>>>>>>>> How can I get the value of the bind variable, so I can push it down
>>>> to
>>>>>>>> the
>>>>>>>>> underlying data source.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> Chin Wei
>>>>
>>>>
>>
Re: Pushdown filter in prepared statement
Posted by Chin Wei Low <lo...@gmail.com>.
Hi,
The new code can support logical expression. But, it still fail with the
following:
java.sql.SQLException: error while executing SQL "select "i","k" from
"s"."beatles" where "k" = 4": 2
at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
at
org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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.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.ArrayIndexOutOfBoundsException: 2
at Buzz.execute(Buzz.java:2)
at Buzz.execute(Buzz.java:8)
at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:47)
at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:99)
at
org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:66)
at
org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
at
org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
... 24 more
On Thu, Jan 22, 2015 at 1:02 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> If that other issue doesn't work on the latest, please log a bug. But I
> think it will.
>
> Julian
>
> > On Jan 21, 2015, at 20:01, Chin Wei Low <lo...@gmail.com> wrote:
> >
> > Thanks. I will give it a try.
> >
> > Another issue is the Interpreter does not support logical expression,
> using
> > the sql:
> > select k,i from beatles where i = 4 and k = 1940
> >
> > java.lang.AssertionError: unknown expression AND(=($0, 4), =($2, 1940))
> > at
> >
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:170)
> > at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> > at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> > at
> >
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> > at
> >
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> > at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> > at
> >
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> > at
> >
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> > at
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> > at
> >
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> > at
> >
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> > at
> >
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> > at
> >
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> > at
> >
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:191)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> > at java.lang.reflect.Method.invoke(Unknown Source)
> > 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.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
> >
> >> On Thu, Jan 22, 2015 at 11:55 AM, Julian Hyde <ju...@gmail.com>
> wrote:
> >>
> >> Yes, that issue looks similar. I made some changes in that area in
> today’s
> >> commit 66cfb12. It might just be fixed… worth a try…
> >>
> >> Julian
> >>
> >>
> >>
> >>> On Jan 21, 2015, at 7:50 PM, Chin Wei Low <lo...@gmail.com>
> wrote:
> >>>
> >>> Browse through Jira, looks like it has been raised in
> >>> https://issues.apache.org/jira/browse/CALCITE-458
> >>>
> >>>> On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com>
> >>> wrote:
> >>>
> >>>> Yes, this case should work. Can you log a jira case for this?
> >>>>
> >>>> Regarding column ordinals: the intent of the API is that filter is
> >> applied
> >>>> before the project, so the filter should be in terms of the column
> >> ordinals
> >>>> in the table, not the column ordinals output by the project. It makes
> >> sense
> >>>> to filter first, so that the project has fewer rows to deal with. Of
> >> course
> >>>> some implementations might filter and project simultaneously.
> >>>>
> >>>> Julian
> >>>>
> >>>>
> >>>>> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com>
> >> wrote:
> >>>>>
> >>>>> Thanks.
> >>>>>
> >>>>> Basically, my requirements are:
> >>>>> 1. pushdown projection
> >>>>> 2. pushdown filters that are supported by the underlying data source,
> >> and
> >>>>> leave the unsupported one to Calcite.
> >>>>>
> >>>>> I found that I should be able to achieve that with
> >>>>> ProjectableFilterableTable.
> >>>>> But, there is issue using this interface.
> >>>>> It throw exception with the projection column plus filter. For
> >> example, I
> >>>>> change one of the query of the ScannableTableTest to filter by column
> >> k.
> >>>>> This will not pushdown the filter, so Calcite will handle it.
> >>>>>
> >>>>> java.sql.SQLException: error while executing SQL "select "i","k" from
> >>>>> "s"."beatles" where "k" = 1940": 2
> >>>>> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
> >>>>> at
> >>
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
> >>>>> at
> >>
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
> >>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >>>>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> >>>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> >>>>> at java.lang.reflect.Method.invoke(Unknown Source)
> >>>>> 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.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.ArrayIndexOutOfBoundsException: 2
> >>>>> at
> >>
> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
> >>>>> at
> >>>>
> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
> >>>>> at
> >>
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
> >>>>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> >>>>> at
> >> org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> >>>>> at
> >>
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> >>>>> at
> >>
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> >>>>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> >>>>> at
> >>
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> >>>>> at
> >>
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> >>>>> at
> >>
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> >>>>> at
> >>
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> >>>>> at
> >>
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> >>>>> at
> >>
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> >>>>> at
> >>
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> >>>>> ... 24 more
> >>>>>
> >>>>> From the console:
> >>>>> Interpreter: rewrite
> >>
> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
> >>>>> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
> >>>>> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
> >>>>> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
> >>>>>
> >>>>> The filter/condition is [=($2, 1940)]
> >>>>>
> >>>>> I think the index of the condition/filter's column does not map to
> the
> >>>>> projected columns index. For this case the index of column 'K' after
> >>>>> projection should be 1 instead of 2.
> >>>>>
> >>>>> Regards,
> >>>>> Chin Wei
> >>>>>
> >>>>>> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
> >>>>> wrote:
> >>>>>
> >>>>>> IIRC, bind variables are accessible via DataContext.get("$0"), or
> some
> >>>>>> similar variable name. Take a look at the contents of the
> >>>>>> DataContextImpl.map field in a debugger. In generated java code,
> >>>>>> DataContext is available as a field called "root".
> >>>>>>
> >>>>>> Julian
> >>>>>>
> >>>>>>
> >>>>>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
> >>>> wrote:
> >>>>>>>
> >>>>>>> Hi,
> >>>>>>>
> >>>>>>> I am implementing a calcite adapter, and created a rule to pushdown
> >>>>>> filter.
> >>>>>>> When I execute a prepared statement, I can see the condition
> >> contains a
> >>>>>>> RexDynamicParam with value "?0" in the LogicalFilter.
> >>>>>>> How can I get the value of the bind variable, so I can push it down
> >> to
> >>>>>> the
> >>>>>>> underlying data source.
> >>>>>>>
> >>>>>>> Regards,
> >>>>>>> Chin Wei
> >>
> >>
>
Re: Pushdown filter in prepared statement
Posted by Julian Hyde <ju...@hydromatic.net>.
If that other issue doesn't work on the latest, please log a bug. But I think it will.
Julian
> On Jan 21, 2015, at 20:01, Chin Wei Low <lo...@gmail.com> wrote:
>
> Thanks. I will give it a try.
>
> Another issue is the Interpreter does not support logical expression, using
> the sql:
> select k,i from beatles where i = 4 and k = 1940
>
> java.lang.AssertionError: unknown expression AND(=($0, 4), =($2, 1940))
> at
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:170)
> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> at
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> at
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> at
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:191)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> 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.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
>
>> On Thu, Jan 22, 2015 at 11:55 AM, Julian Hyde <ju...@gmail.com> wrote:
>>
>> Yes, that issue looks similar. I made some changes in that area in today’s
>> commit 66cfb12. It might just be fixed… worth a try…
>>
>> Julian
>>
>>
>>
>>> On Jan 21, 2015, at 7:50 PM, Chin Wei Low <lo...@gmail.com> wrote:
>>>
>>> Browse through Jira, looks like it has been raised in
>>> https://issues.apache.org/jira/browse/CALCITE-458
>>>
>>>> On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com>
>>> wrote:
>>>
>>>> Yes, this case should work. Can you log a jira case for this?
>>>>
>>>> Regarding column ordinals: the intent of the API is that filter is
>> applied
>>>> before the project, so the filter should be in terms of the column
>> ordinals
>>>> in the table, not the column ordinals output by the project. It makes
>> sense
>>>> to filter first, so that the project has fewer rows to deal with. Of
>> course
>>>> some implementations might filter and project simultaneously.
>>>>
>>>> Julian
>>>>
>>>>
>>>>> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com>
>> wrote:
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Basically, my requirements are:
>>>>> 1. pushdown projection
>>>>> 2. pushdown filters that are supported by the underlying data source,
>> and
>>>>> leave the unsupported one to Calcite.
>>>>>
>>>>> I found that I should be able to achieve that with
>>>>> ProjectableFilterableTable.
>>>>> But, there is issue using this interface.
>>>>> It throw exception with the projection column plus filter. For
>> example, I
>>>>> change one of the query of the ScannableTableTest to filter by column
>> k.
>>>>> This will not pushdown the filter, so Calcite will handle it.
>>>>>
>>>>> java.sql.SQLException: error while executing SQL "select "i","k" from
>>>>> "s"."beatles" where "k" = 1940": 2
>>>>> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
>>>>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
>>>>> at
>> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
>>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>>>> at java.lang.reflect.Method.invoke(Unknown Source)
>>>>> 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.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.ArrayIndexOutOfBoundsException: 2
>>>>> at
>> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
>>>>> at
>>>> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
>>>>> at
>> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
>>>>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
>>>>> at
>> org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
>>>>> at
>> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
>>>>> at
>> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>>>>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
>>>>> at
>> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
>>>>> at
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
>>>>> at
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
>>>>> at
>> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
>>>>> at
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
>>>>> at
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
>>>>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
>>>>> ... 24 more
>>>>>
>>>>> From the console:
>>>>> Interpreter: rewrite
>> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
>>>>> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
>>>>> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
>>>>> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
>>>>>
>>>>> The filter/condition is [=($2, 1940)]
>>>>>
>>>>> I think the index of the condition/filter's column does not map to the
>>>>> projected columns index. For this case the index of column 'K' after
>>>>> projection should be 1 instead of 2.
>>>>>
>>>>> Regards,
>>>>> Chin Wei
>>>>>
>>>>>> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> IIRC, bind variables are accessible via DataContext.get("$0"), or some
>>>>>> similar variable name. Take a look at the contents of the
>>>>>> DataContextImpl.map field in a debugger. In generated java code,
>>>>>> DataContext is available as a field called "root".
>>>>>>
>>>>>> Julian
>>>>>>
>>>>>>
>>>>>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
>>>> wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I am implementing a calcite adapter, and created a rule to pushdown
>>>>>> filter.
>>>>>>> When I execute a prepared statement, I can see the condition
>> contains a
>>>>>>> RexDynamicParam with value "?0" in the LogicalFilter.
>>>>>>> How can I get the value of the bind variable, so I can push it down
>> to
>>>>>> the
>>>>>>> underlying data source.
>>>>>>>
>>>>>>> Regards,
>>>>>>> Chin Wei
>>
>>
Re: Pushdown filter in prepared statement
Posted by Chin Wei Low <lo...@gmail.com>.
Thanks. I will give it a try.
Another issue is the Interpreter does not support logical expression, using
the sql:
select k,i from beatles where i = 4 and k = 1940
java.lang.AssertionError: unknown expression AND(=($0, 4), =($2, 1940))
at
org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:170)
at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
at
org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
at
org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
at
org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
at
org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:191)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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.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
On Thu, Jan 22, 2015 at 11:55 AM, Julian Hyde <ju...@gmail.com> wrote:
> Yes, that issue looks similar. I made some changes in that area in today’s
> commit 66cfb12. It might just be fixed… worth a try…
>
> Julian
>
>
>
> On Jan 21, 2015, at 7:50 PM, Chin Wei Low <lo...@gmail.com> wrote:
>
> > Browse through Jira, looks like it has been raised in
> > https://issues.apache.org/jira/browse/CALCITE-458
> >
> > On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com>
> wrote:
> >
> >> Yes, this case should work. Can you log a jira case for this?
> >>
> >> Regarding column ordinals: the intent of the API is that filter is
> applied
> >> before the project, so the filter should be in terms of the column
> ordinals
> >> in the table, not the column ordinals output by the project. It makes
> sense
> >> to filter first, so that the project has fewer rows to deal with. Of
> course
> >> some implementations might filter and project simultaneously.
> >>
> >> Julian
> >>
> >>
> >>> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com>
> wrote:
> >>>
> >>> Thanks.
> >>>
> >>> Basically, my requirements are:
> >>> 1. pushdown projection
> >>> 2. pushdown filters that are supported by the underlying data source,
> and
> >>> leave the unsupported one to Calcite.
> >>>
> >>> I found that I should be able to achieve that with
> >>> ProjectableFilterableTable.
> >>> But, there is issue using this interface.
> >>> It throw exception with the projection column plus filter. For
> example, I
> >>> change one of the query of the ScannableTableTest to filter by column
> k.
> >>> This will not pushdown the filter, so Calcite will handle it.
> >>>
> >>> java.sql.SQLException: error while executing SQL "select "i","k" from
> >>> "s"."beatles" where "k" = 1940": 2
> >>> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
> >>> at
> >>>
> >>
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
> >>> at
> >>>
> >>
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
> >>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> >>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> >>> at java.lang.reflect.Method.invoke(Unknown Source)
> >>> 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.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.ArrayIndexOutOfBoundsException: 2
> >>> at
> >>>
> >>
> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
> >>> at
> >> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
> >>> at
> >>>
> >>
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
> >>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> >>> at
> org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> >>> at
> >>>
> >>
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> >>> at
> >>>
> >>
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> >>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> >>> at
> >>>
> >>
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> >>> at
> >>>
> >>
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> >>> at
> >>
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> >>> at
> >>>
> >>
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> >>> at
> >>>
> >>
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> >>> at
> >>>
> >>
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> >>> at
> >>>
> >>
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> >>> ... 24 more
> >>>
> >>> From the console:
> >>> Interpreter: rewrite
> >>>
> >>
> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
> >>> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
> >>> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
> >>> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
> >>>
> >>> The filter/condition is [=($2, 1940)]
> >>>
> >>> I think the index of the condition/filter's column does not map to the
> >>> projected columns index. For this case the index of column 'K' after
> >>> projection should be 1 instead of 2.
> >>>
> >>> Regards,
> >>> Chin Wei
> >>>
> >>> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
> >> wrote:
> >>>
> >>>> IIRC, bind variables are accessible via DataContext.get("$0"), or some
> >>>> similar variable name. Take a look at the contents of the
> >>>> DataContextImpl.map field in a debugger. In generated java code,
> >>>> DataContext is available as a field called "root".
> >>>>
> >>>> Julian
> >>>>
> >>>>
> >>>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
> >> wrote:
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I am implementing a calcite adapter, and created a rule to pushdown
> >>>> filter.
> >>>>> When I execute a prepared statement, I can see the condition
> contains a
> >>>>> RexDynamicParam with value "?0" in the LogicalFilter.
> >>>>> How can I get the value of the bind variable, so I can push it down
> to
> >>>> the
> >>>>> underlying data source.
> >>>>>
> >>>>> Regards,
> >>>>> Chin Wei
> >>>>
> >>>>
> >>
> >>
>
>
Re: Pushdown filter in prepared statement
Posted by Julian Hyde <ju...@gmail.com>.
Yes, that issue looks similar. I made some changes in that area in today’s commit 66cfb12. It might just be fixed… worth a try…
Julian
On Jan 21, 2015, at 7:50 PM, Chin Wei Low <lo...@gmail.com> wrote:
> Browse through Jira, looks like it has been raised in
> https://issues.apache.org/jira/browse/CALCITE-458
>
> On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com> wrote:
>
>> Yes, this case should work. Can you log a jira case for this?
>>
>> Regarding column ordinals: the intent of the API is that filter is applied
>> before the project, so the filter should be in terms of the column ordinals
>> in the table, not the column ordinals output by the project. It makes sense
>> to filter first, so that the project has fewer rows to deal with. Of course
>> some implementations might filter and project simultaneously.
>>
>> Julian
>>
>>
>>> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com> wrote:
>>>
>>> Thanks.
>>>
>>> Basically, my requirements are:
>>> 1. pushdown projection
>>> 2. pushdown filters that are supported by the underlying data source, and
>>> leave the unsupported one to Calcite.
>>>
>>> I found that I should be able to achieve that with
>>> ProjectableFilterableTable.
>>> But, there is issue using this interface.
>>> It throw exception with the projection column plus filter. For example, I
>>> change one of the query of the ScannableTableTest to filter by column k.
>>> This will not pushdown the filter, so Calcite will handle it.
>>>
>>> java.sql.SQLException: error while executing SQL "select "i","k" from
>>> "s"."beatles" where "k" = 1940": 2
>>> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
>>> at
>>>
>> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>> at java.lang.reflect.Method.invoke(Unknown Source)
>>> 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.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.ArrayIndexOutOfBoundsException: 2
>>> at
>>>
>> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
>>> at
>> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
>>> at
>>>
>> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
>>> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
>>> at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
>>> at
>>>
>> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
>>> at
>>>
>> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>>> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
>>> at
>>>
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
>>> at
>> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
>>> at
>>>
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
>>> at
>>>
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
>>> ... 24 more
>>>
>>> From the console:
>>> Interpreter: rewrite
>>>
>> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
>>> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
>>> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
>>> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
>>>
>>> The filter/condition is [=($2, 1940)]
>>>
>>> I think the index of the condition/filter's column does not map to the
>>> projected columns index. For this case the index of column 'K' after
>>> projection should be 1 instead of 2.
>>>
>>> Regards,
>>> Chin Wei
>>>
>>> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
>> wrote:
>>>
>>>> IIRC, bind variables are accessible via DataContext.get("$0"), or some
>>>> similar variable name. Take a look at the contents of the
>>>> DataContextImpl.map field in a debugger. In generated java code,
>>>> DataContext is available as a field called "root".
>>>>
>>>> Julian
>>>>
>>>>
>>>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
>> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I am implementing a calcite adapter, and created a rule to pushdown
>>>> filter.
>>>>> When I execute a prepared statement, I can see the condition contains a
>>>>> RexDynamicParam with value "?0" in the LogicalFilter.
>>>>> How can I get the value of the bind variable, so I can push it down to
>>>> the
>>>>> underlying data source.
>>>>>
>>>>> Regards,
>>>>> Chin Wei
>>>>
>>>>
>>
>>
Re: Pushdown filter in prepared statement
Posted by Chin Wei Low <lo...@gmail.com>.
Browse through Jira, looks like it has been raised in
https://issues.apache.org/jira/browse/CALCITE-458
On Wed, Jan 21, 2015 at 1:37 AM, Julian Hyde <ju...@gmail.com> wrote:
> Yes, this case should work. Can you log a jira case for this?
>
> Regarding column ordinals: the intent of the API is that filter is applied
> before the project, so the filter should be in terms of the column ordinals
> in the table, not the column ordinals output by the project. It makes sense
> to filter first, so that the project has fewer rows to deal with. Of course
> some implementations might filter and project simultaneously.
>
> Julian
>
>
> > On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com> wrote:
> >
> > Thanks.
> >
> > Basically, my requirements are:
> > 1. pushdown projection
> > 2. pushdown filters that are supported by the underlying data source, and
> > leave the unsupported one to Calcite.
> >
> > I found that I should be able to achieve that with
> > ProjectableFilterableTable.
> > But, there is issue using this interface.
> > It throw exception with the projection column plus filter. For example, I
> > change one of the query of the ScannableTableTest to filter by column k.
> > This will not pushdown the filter, so Calcite will handle it.
> >
> > java.sql.SQLException: error while executing SQL "select "i","k" from
> > "s"."beatles" where "k" = 1940": 2
> > at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
> > at
> >
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
> > at
> >
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> > at java.lang.reflect.Method.invoke(Unknown Source)
> > 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.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.ArrayIndexOutOfBoundsException: 2
> > at
> >
> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
> > at
> org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
> > at
> >
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
> > at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> > at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> > at
> >
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> > at
> >
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> > at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> > at
> >
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> > at
> >
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> > at
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> > at
> >
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> > at
> >
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> > at
> >
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> > at
> >
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> > ... 24 more
> >
> > From the console:
> > Interpreter: rewrite
> >
> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
> > beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
> > $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
> > Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
> >
> > The filter/condition is [=($2, 1940)]
> >
> > I think the index of the condition/filter's column does not map to the
> > projected columns index. For this case the index of column 'K' after
> > projection should be 1 instead of 2.
> >
> > Regards,
> > Chin Wei
> >
> > On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com>
> wrote:
> >
> >> IIRC, bind variables are accessible via DataContext.get("$0"), or some
> >> similar variable name. Take a look at the contents of the
> >> DataContextImpl.map field in a debugger. In generated java code,
> >> DataContext is available as a field called "root".
> >>
> >> Julian
> >>
> >>
> >>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com>
> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I am implementing a calcite adapter, and created a rule to pushdown
> >> filter.
> >>> When I execute a prepared statement, I can see the condition contains a
> >>> RexDynamicParam with value "?0" in the LogicalFilter.
> >>> How can I get the value of the bind variable, so I can push it down to
> >> the
> >>> underlying data source.
> >>>
> >>> Regards,
> >>> Chin Wei
> >>
> >>
>
>
Re: Pushdown filter in prepared statement
Posted by Julian Hyde <ju...@gmail.com>.
Yes, this case should work. Can you log a jira case for this?
Regarding column ordinals: the intent of the API is that filter is applied before the project, so the filter should be in terms of the column ordinals in the table, not the column ordinals output by the project. It makes sense to filter first, so that the project has fewer rows to deal with. Of course some implementations might filter and project simultaneously.
Julian
> On Jan 19, 2015, at 11:33 PM, Chin Wei Low <lo...@gmail.com> wrote:
>
> Thanks.
>
> Basically, my requirements are:
> 1. pushdown projection
> 2. pushdown filters that are supported by the underlying data source, and
> leave the unsupported one to Calcite.
>
> I found that I should be able to achieve that with
> ProjectableFilterableTable.
> But, there is issue using this interface.
> It throw exception with the projection column plus filter. For example, I
> change one of the query of the ScannableTableTest to filter by column k.
> This will not pushdown the filter, so Calcite will handle it.
>
> java.sql.SQLException: error while executing SQL "select "i","k" from
> "s"."beatles" where "k" = 1940": 2
> at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
> at
> org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> 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.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.ArrayIndexOutOfBoundsException: 2
> at
> org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
> at org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
> at
> org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
> at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
> at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
> at
> org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
> at
> org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
> ... 24 more
>
> From the console:
> Interpreter: rewrite
> rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
> beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
> $t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
> Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
>
> The filter/condition is [=($2, 1940)]
>
> I think the index of the condition/filter's column does not map to the
> projected columns index. For this case the index of column 'K' after
> projection should be 1 instead of 2.
>
> Regards,
> Chin Wei
>
> On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com> wrote:
>
>> IIRC, bind variables are accessible via DataContext.get("$0"), or some
>> similar variable name. Take a look at the contents of the
>> DataContextImpl.map field in a debugger. In generated java code,
>> DataContext is available as a field called "root".
>>
>> Julian
>>
>>
>>> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> I am implementing a calcite adapter, and created a rule to pushdown
>> filter.
>>> When I execute a prepared statement, I can see the condition contains a
>>> RexDynamicParam with value "?0" in the LogicalFilter.
>>> How can I get the value of the bind variable, so I can push it down to
>> the
>>> underlying data source.
>>>
>>> Regards,
>>> Chin Wei
>>
>>
Re: Pushdown filter in prepared statement
Posted by Chin Wei Low <lo...@gmail.com>.
Thanks.
Basically, my requirements are:
1. pushdown projection
2. pushdown filters that are supported by the underlying data source, and
leave the unsupported one to Calcite.
I found that I should be able to achieve that with
ProjectableFilterableTable.
But, there is issue using this interface.
It throw exception with the projection column plus filter. For example, I
change one of the query of the ScannableTableTest to filter by column k.
This will not pushdown the filter, so Calcite will handle it.
java.sql.SQLException: error while executing SQL "select "i","k" from
"s"."beatles" where "k" = 1940": 2
at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:119)
at
org.apache.calcite.test.ScannableTableTest.testProjectableFilterable2WithProject(ScannableTableTest.java:193)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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.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.ArrayIndexOutOfBoundsException: 2
at
org.apache.calcite.interpreter.Interpreter$3.execute(Interpreter.java:193)
at org.apache.calcite.interpreter.Interpreter$2$1.get(Interpreter.java:177)
at
org.apache.calcite.interpreter.Interpreter$2.execute(Interpreter.java:130)
at org.apache.calcite.interpreter.FilterNode.run(FilterNode.java:42)
at org.apache.calcite.interpreter.Interpreter.start(Interpreter.java:97)
at
org.apache.calcite.interpreter.Interpreter.enumerator(Interpreter.java:64)
at
org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
at
org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:461)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:475)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:434)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:117)
... 24 more
>From the console:
Interpreter: rewrite
rel#75:EnumerableCalc.ENUMERABLE.[](input=rel#0:EnumerableTableScan.ENUMERABLE.[](table=[s,
beatles]),expr#0..2={inputs},expr#3=1940,expr#4==($t2,
$t3),$f0=$t0,$f1=$t2,$condition=$t4) to LogicalProject#78
Interpreter: rewrite LogicalProject#78 to LogicalFilter#80
The filter/condition is [=($2, 1940)]
I think the index of the condition/filter's column does not map to the
projected columns index. For this case the index of column 'K' after
projection should be 1 instead of 2.
Regards,
Chin Wei
On Fri, Jan 16, 2015 at 4:36 PM, Julian Hyde <ju...@gmail.com> wrote:
> IIRC, bind variables are accessible via DataContext.get("$0"), or some
> similar variable name. Take a look at the contents of the
> DataContextImpl.map field in a debugger. In generated java code,
> DataContext is available as a field called "root".
>
> Julian
>
>
> > On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com> wrote:
> >
> > Hi,
> >
> > I am implementing a calcite adapter, and created a rule to pushdown
> filter.
> > When I execute a prepared statement, I can see the condition contains a
> > RexDynamicParam with value "?0" in the LogicalFilter.
> > How can I get the value of the bind variable, so I can push it down to
> the
> > underlying data source.
> >
> > Regards,
> > Chin Wei
>
>
Re: Pushdown filter in prepared statement
Posted by Julian Hyde <ju...@gmail.com>.
IIRC, bind variables are accessible via DataContext.get("$0"), or some similar variable name. Take a look at the contents of the DataContextImpl.map field in a debugger. In generated java code, DataContext is available as a field called "root".
Julian
> On Jan 16, 2015, at 12:31 AM, Chin Wei Low <lo...@gmail.com> wrote:
>
> Hi,
>
> I am implementing a calcite adapter, and created a rule to pushdown filter.
> When I execute a prepared statement, I can see the condition contains a
> RexDynamicParam with value "?0" in the LogicalFilter.
> How can I get the value of the bind variable, so I can push it down to the
> underlying data source.
>
> Regards,
> Chin Wei