[ https://issues.apache.org/jira/browse/PHOENIX-3510?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15711239#comment-15711239 ]
Bartlomiej Niemienionek edited comment on PHOENIX-3510 at 12/1/16 8:01 AM: --------------------------------------------------------------------------- I have found another area with (I guess) similar issue: SELECT 1 WHERE CAST(:abc AS VARCHAR(100)) = '1'; SELECT 1 WHERE :abc = '1'; in both cases at execution state :abc is replaced by ? Second is working fine for the first one same exception is thrown. was (Author: bjn): I have found another area with (I guess) similar issue: SELECT 1 WHERE CAST(:abc AS VARCHAR(100)) = '1'; SELECT 1 WHERE :abc = '1'; in both cases at execution state :abc is replaced by ? > Phoenix LIKE operator with bind parameters - Error -1 (00000) : while > preparing SQL; Binded parameter in the SELECT clause > -------------------------------------------------------------------------------------------------------------------------- > > Key: PHOENIX-3510 > URL: https://issues.apache.org/jira/browse/PHOENIX-3510 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.8.0 > Reporter: Bartlomiej Niemienionek > Assignee: Kevin Liew > > 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: > {code:sql} > CREATE TABLE TEST_TABLE ( > NAME VARCHAR(100) NOT NULL PRIMARY KEY, > VAL VARCHAR(100) > ); > {code} > > These are working fine: > {code:sql} > 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'; > {code} > > Here I get the exception: > {code:sql} > SELECT 1 FROM TEST_TABLE WHERE NAME LIKE :abc; > SELECT 1 FROM TEST_TABLE WHERE NAME LIKE :1; > {code} > Accordign to the error message this queries before execution are changed to: > {code:sql} > SELECT 1 FROM TEST_TABLE WHERE NAME LIKE ? > {code} > Similar exception is thrown when executing this query: > {code:sql} > SELECT :abc AS PARAM_VALUE FROM TEST_TABLE; > {code} > Error occurs while trying to execute SQL prepared statements form my Java > application or from DBeaver. > Exception stack trace: > {code:java} > 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 > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)