You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Ganda Manurung <ga...@gmail.com> on 2020/05/29 07:11:29 UTC
Error Parameter value unbound. Parameter at index 1 is unbound on Subquery
Hello,
I have a query like this on my program
SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID =
'1' AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID = '1'
AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = '1'
AND STATUS = 'N' ) AS TEMP
And it runs smoothly when I tried it using SquirrelSQL with Phoenix JDBC
Thin Client.
However, I try the query in Java with a prepared statement, the query
changed like below:
SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID = ?
AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID = ? AND
STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = ? AND
STATUS = 'N' ) AS TEMP
And as the ID is a string, I set the parameter with code like this
PreparedStatement secondStatement = super.getConnection().prepareStatement(
sqlQuery);
secondStatement.setString(1, ID);
secondStatement.setString(2, ID);
secondStatement.setString(3, ID);
ResultSet secondResultset = secondStatement.executeQuery();
I expect it should be working, but I got this error
rg.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while
preparing SQL: SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM
TABLE_A WHERE ID = ? AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM
TABLE_B WHERE ID = ? AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM
TABLE_C WHERE ID = ? AND STATUS = 'N' ) AS TEMP
at org.apache.calcite.avatica.Helper.createException(Helper.java:53)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:314)
at
org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:153)
at
com.btpn.jdbc.interfacing.interfaces.impl.VerifyCIF.query(VerifyCIF.java:49)
at com.btpn.jdbc.interfacing.Fetcher.call(Fetcher.java:39)
at MainPhoenix.main(MainPhoenix.java:69)
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:651)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:677)
at
org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:177)
at
org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1113)
at
org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1091)
at
org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:102)
at
org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:38)
at
org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:68)
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: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:422)
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:231)
at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:244)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:669)
... 15 more
How do I fix this? Is there anything wrong?
I am using Apache Phoenix 4.7.0 and Hbase 1.1
Thank you and regards,
Ganda
--
Ganda Manurung
Re: Error Parameter value unbound. Parameter at index 1 is unbound on
Subquery
Posted by Josh Elser <el...@apache.org>.
Gentle reminder to always do a general search for similar issues
https://issues.apache.org/jira/browse/PHOENIX-5192
There have been some issues which have been similarly reported in the
past, but no one has been able to provide a reproduction. Perhaps you
can be the one to do that. As of now, your reported issue is not
actionable as we don't know where it is.
Finally, you're using an extremely old version of HBase and Phoenix. You
should upgrade.
On 5/29/20 3:11 AM, Ganda Manurung wrote:
>
> Hello,
>
> I have a query like this on my program
>
> SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID
> = '1' AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID
> = '1' AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID
> = '1' AND STATUS = 'N' ) AS TEMP
>
> And it runs smoothly when I tried it using SquirrelSQL with Phoenix JDBC
> Thin Client.
>
> However, I try the query in Java with a prepared statement, the query
> changed like below:
>
> SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID
> = ? AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID =
> ? AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = ?
> AND STATUS = 'N' ) AS TEMP
>
> And as the ID is a string, I set the parameter with code like this
>
> PreparedStatement secondStatement =
> super.getConnection().prepareStatement(sqlQuery);
>
> secondStatement.setString(1, ID);
>
> secondStatement.setString(2, ID);
>
> secondStatement.setString(3, ID);
>
>
> ResultSet secondResultset = secondStatement.executeQuery();
>
>
> I expect it should be working, but I got this error
>
>
>
> rg.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while
> preparing SQL: SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM
> TABLE_A WHERE ID = ? AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM
> TABLE_B WHERE ID = ? AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM
> TABLE_C WHERE ID = ? AND STATUS = 'N' ) AS TEMP
> at org.apache.calcite.avatica.Helper.createException(Helper.java:53)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:314)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:153)
> at
> com.btpn.jdbc.interfacing.interfaces.impl.VerifyCIF.query(VerifyCIF.java:49)
> at com.btpn.jdbc.interfacing.Fetcher.call(Fetcher.java:39)
> at MainPhoenix.main(MainPhoenix.java:69)
> 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:651)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:677)
> at
> org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:177)
> at
> org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1113)
> at
> org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1091)
> at
> org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:102)
> at
> org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:38)
> at
> org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:68)
> 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: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:422)
> 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:231)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:244)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:669)
> ... 15 more
>
>
>
> How do I fix this? Is there anything wrong?
>
> I am using Apache Phoenix 4.7.0 and Hbase 1.1
>
>
> Thank you and regards,
>
>
> Ganda
>
> --
> Ganda Manurung