[PHOENIX-4871] Query parser throws exception on parameterized join

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.14.0
• Fix Version/s: 5.1.0

Environment

This issue exists on version 4 and I could reproduce it on current git repo version 

• Assignee: Miles Spielberg
• Reporter: Mehdi Salarkia
• Created: 28/Aug/18 00:08
• Updated: 10/Feb/21 10:01
• Resolved: 21/Sep/19 20:33

Description

When a join select statement has a parameter, Phoenix query parser fails to create query metadata and fails this query :

SELECT "A"."a2" FROM "A" JOIN "B" ON ("A"."a1" = "B"."b1" ) WHERE "B"."b2" = ? 

with the following exception: 

 

org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT "A"."a2" FROM "A" JOIN "B" ON ("A"."a1" = "B"."b1") WHERE ("B"."b2" = ?) 

at org.apache.calcite.avatica.Helper.createException(Helper.java:54)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:358)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at org.apache.phoenix.end2end.QueryServerBasicsIT.testParameterizedJoin(QueryServerBasicsIT.java:377)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
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.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
java.lang.RuntimeException: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound
at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:700)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:726)
at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:195)
at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1215)
at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1186)
at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:127)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:534)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)
at org.apache.phoenix.jdbc.PhoenixParameterMetaData.getParam(PhoenixParameterMetaData.java:89)
at org.apache.phoenix.jdbc.PhoenixParameterMetaData.isSigned(PhoenixParameterMetaData.java:138)
at org.apache.calcite.avatica.jdbc.JdbcMeta.parameters(JdbcMeta.java:276)
at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:288)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:721)
... 20 more

 As a workaround you can change the order of tables. Meaning that the table that has the condition in the where clause must appear first in the query. For the example above this will work

SELECT "A"."a2" FROM "B" JOIN "A" ON ("A"."a1" = "B"."b1" ) WHERE "B"."b2" = ? 

After debugging this it looks like the Phoenix server splits the join in two separated queries and constructs two java.sql.Statement. The first of the two is referenced for reading the query metadata down steam and since the column in the condition in the example above is in table `B` rather than `A` it won't exists in the first query and later it fails with the error above.
That's why changing the order of tables in the query fixes the issue.
This issue has also been reported here: https://community.hortonworks.com/questions/45896/problem-executing-joins-on-phoenix-query-server.html
  See org.apache.phoenix.compile.QueryCompiler#compileJoinQuery for details

Issue Links

• links to: GitHub Pull Request #511