Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Problem executing Joins on Phoenix Query Server

Highlighted

Problem executing Joins on Phoenix Query Server

New Contributor

I am trying to execute joins on Phoenix using query server with python library 'Phoenixdb'.

Below is the code:

cursor.execute('select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id where tbl1.id = ?',[1,])

I'm getting error as 'Parameter value unbound Parameter at index 1 is unbound'

This is the stack trace:

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:737)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:756)
        at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:127)
        at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:270)
        at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:254)
        at org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:43)
        at org.apache.calcite.avatica.server.AvaticaHandler.handle(AvaticaHandler.java:55)
        at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
        at org.eclipse.jetty.server.Server.handle(Server.java:497)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:245)
        at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
        at java.lang.Thread.run(Thread.java:745)
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:386)
        at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
        at org.apache.phoenix.jdbc.PhoenixParameterMetaData.getParam(PhoenixParameterMetaData.java:88)
        at org.apache.phoenix.jdbc.PhoenixParameterMetaData.isSigned(PhoenixParameterMetaData.java:138)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.parameters(JdbcMeta.java:235)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:246)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:748)

Can you tell me what is wrong?

If this is the bug in Calcite which is been fixed in new version, how to make query-server use latest Clacite jar?

I'm using HDP 2.3 Phoenix-version:4.4 Calcite-version:1.2

@Josh Elser

4 REPLIES 4

Re: Problem executing Joins on Phoenix Query Server

It is probably a bug with the PhoenixDB library, but I do not know what kind of testing exists for that library. The error is stating that the unbound value (the '?') did not have any value provided to bind. I'm guessing that you are trying to provide a value of "1"?

This should work fine in HDP-2.3 with the Java implementation.

Re: Problem executing Joins on Phoenix Query Server

New Contributor

On first sight it was exactly what I thought of however still if you can re-check with Hortonworks 2.3 Sandbox, because it seems Calcite bug to me also.

I have also went through one of the almost similar issue: CALCITE-1052

https://issues.apache.org/jira/browse/CALCITE-1052

Also can you help, how can I use newer calcite version over HDP2.3 hortonworks sandbox, just to ensure its being reproducible even with the newer version of calcite.

Re: Problem executing Joins on Phoenix Query Server

New Contributor

Also surprisingly, if we interchange the tables join sequence in SQL i.e. if execute tbl2 inner join tbl1, query just runs fine.

cursor.execute('select * from tbl2 inner join tbl1 on tbl1.id=tbl2.id where tbl1.id = ?',[1,])

That's the reason me and Japan Shah thinks its a Calcite bug.

@Josh Elser

Re: Problem executing Joins on Phoenix Query Server

I was doing some more digging on this one yesterday: it appears to be an issue with the Phoenix "thick" JDBC driver.

Connection cnxn = DriverManager.getConnection("jdbc:phoenix:localhost:2181:/hbase-unsecure");
PreparedStatement pstmt = cnxn.prepareStatement(“select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id where tbl1.id = ?”);
ParameterMetaData pmetadata = pstmt.getParameterMetaData();
for (int i = 1; i <= pmetadata.getParameterCount(); i++) {
  System.out.println("Type: " + pmetadata.getParameterType(i));
}

The above code snippet will exhibit the same exact error message you were seeing previously. Because of the calls PQS was making, it ran into this bug, but we can see trigger the same bug by reproducing what PQS was doing.

Will have to do some more digging, but it seems like the parameter on the relation we're joining is getting lost.