[PHOENIX-3510] Phoenix LIKE operator with bind parameters - Error -1 (00000) : while preparing SQL; Binded parameter in the SELECT clause - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Duplicate
Affects Version/s: 4.8.0
Fix Version/s: None
Component/s: None
Labels:
None

Description

I am using phoenix-4.9.0-HBase-1.2-thin-client.jar.

When trying to execute SQL query with LIKE operator with bind parameter I got the AvaticaSqlException.

Example of queries to reproduce the issue:

CREATE TABLE TEST_TABLE (
       NAME VARCHAR(100) NOT NULL PRIMARY KEY,
       VAL VARCHAR(100)
    );

These are working fine:

SELECT 1 FROM TEST_TABLE WHERE NAME = :abc;
SELECT 1 FROM TEST_TABLE WHERE NAME = 'abc';
SELECT 1 FROM TEST_TABLE WHERE NAME LIKE 'abc';

Here I get the exception:

SELECT 1 FROM TEST_TABLE WHERE NAME LIKE :abc;
SELECT 1 FROM TEST_TABLE WHERE NAME LIKE :1;

Accordign to the error message this queries before execution are changed to:

SELECT 1 FROM TEST_TABLE WHERE NAME LIKE ?

Similar exception is thrown when executing this query:

SELECT :abc AS PARAM_VALUE FROM TEST_TABLE;

Error occurs while trying to execute SQL prepared statements form my Java application or from DBeaver.

Exception stack trace:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [....]; nested exception is org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: ...
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
        at com.norkom.configurator.business.dataactions.impl.PreparedDatabaseAction.getRecords(PreparedDatabaseAction.java:790)
        at com.norkom.configurator.business.dataactions.impl.BufferedDatabaseMasterQuery.getRecords(BufferedDatabaseMasterQuery.java:106)
        at com.norkom.configurator.business.facades.impl.ScreenConfiguratorLogicFacadeImpl.executePagedListQueryAction(ScreenConfiguratorLogicFacadeImpl.java:998)
        at com.norkom.configurator.business.facades.wrappers.impl.ScreenConfiguratorLogicFacadeWrapperImpl.executePagedListQueryAction(ScreenConfiguratorLogicFacadeWrapperImpl.java:216)
        at sun.reflect.GeneratedMethodAccessor2539.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy264.executePagedListQueryAction(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor2539.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy265.executePagedListQueryAction(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor2539.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy265.executePagedListQueryAction(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor2539.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy265.executePagedListQueryAction(Unknown Source)
        at com.norkom.configurator.services.ejb.ScreenConfiguratorLogicEjb.executePagedListQueryAction(ScreenConfiguratorLogicEjb.java:239)
        at sun.reflect.GeneratedMethodAccessor2583.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)
        at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)
        at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
        at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInNoTx(CMTTxInterceptor.java:267)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.notSupported(CMTTxInterceptor.java:331)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:241)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.remote.EJBRemoteTransactionPropagatingInterceptor.processInvocation(EJBRemoteTransactionPropagatingInterceptor.java:79)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:55)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.as.ee.component.TCCLInterceptor.processInvocation(TCCLInterceptor.java:45)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288)
        at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
        at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:185)
        at org.jboss.as.ejb3.remote.LocalEjbReceiver.processInvocation(LocalEjbReceiver.java:249)
        at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:184)
        at org.jboss.ejb.client.EJBObjectInterceptor.handleInvocation(EJBObjectInterceptor.java:58)
        at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:186)
        at org.jboss.ejb.client.EJBHomeInterceptor.handleInvocation(EJBHomeInterceptor.java:83)
        at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:186)
        at org.jboss.ejb.client.TransactionInterceptor.handleInvocation(TransactionInterceptor.java:42)
        at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:186)
        at org.jboss.ejb.client.ReceiverInterceptor.handleInvocation(ReceiverInterceptor.java:125)
        at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:186)
        at org.jboss.ejb.client.EJBInvocationHandler.sendRequestWithPossibleRetries(EJBInvocationHandler.java:255)
        at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:200)
        at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:183)
        at org.jboss.ejb.client.EJBInvocationHandler.invoke(EJBInvocationHandler.java:146)
        at com.sun.proxy.$Proxy183.executePagedListQueryAction(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor2582.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.remoting.rmi.RmiClientInterceptorUtils.invokeRemoteMethod(RmiClientInterceptorUtils.java:111)
        at org.springframework.ejb.access.SimpleRemoteSlsbInvokerInterceptor.doInvoke(SimpleRemoteSlsbInvokerInterceptor.java:99)
        at org.springframework.ejb.access.AbstractRemoteSlsbInvokerInterceptor.invokeInContext(AbstractRemoteSlsbInvokerInterceptor.java:141)
        at org.springframework.ejb.access.AbstractSlsbInvokerInterceptor.invoke(AbstractSlsbInvokerInterceptor.java:189)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy184.executePagedListQueryAction(Unknown Source)
        at com.norkom.configurator.web.screens.sections.impl.ListSectionController.getValueMapsListWithQueryActionResult(ListSectionController.java:1088)
        at com.norkom.configurator.web.screens.sections.impl.ListSectionController.populateSectionStateMapWithQueryResult(ListSectionController.java:1039)
        at com.norkom.configurator.web.screens.sections.impl.ListSectionController.performListQueryAction(ListSectionController.java:1019)
        at com.norkom.configurator.web.screens.sections.impl.InteractiveListSectionController.getJSONData(InteractiveListSectionController.java:234)
        at com.norkom.configurator.web.servlets.InteractiveListServlet.returnTableDataResponse(InteractiveListServlet.java:218)
        at com.norkom.configurator.web.servlets.InteractiveListServlet.executeRequestFunction(InteractiveListServlet.java:166)
        at com.norkom.configurator.web.servlets.InteractiveListServlet.getSectionData(InteractiveListServlet.java:120)
        at com.norkom.configurator.web.servlets.InteractiveListServlet.doPost(InteractiveListServlet.java:53)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
        at com.norkom.base.web.filters.SecurityFilter.doFilter(SecurityFilter.java:403)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:420)
        at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: [....]
        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:329)
        at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:264)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:748)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:734)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:405)
        at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1446)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:583)
        ... 143 more

Attachments

Issue Links

duplicates

PHOENIX-2314 Cannot prepare parameterized statement with a 'like' predicate

Resolved

Activity

People

Assignee:: Kevin Liew

Reporter:: Bartlomiej Niemienionek

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 29/Nov/16 11:18

Updated:: 09/Mar/17 15:47

Resolved:: 06/Feb/17 07:31