[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3032) On Sybase, a subquery is incorrectly generated, causing ''Incorrect syntax near the keyword 'as'.
strong liu (JIRA)
noreply at atlassian.com
Wed Aug 12 15:03:12 EDT 2009
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33749#action_33749 ]
strong liu commented on HHH-3032:
---------------------------------
from Dhimant Chokshi:
as mentioned in the notes, alias is not suppoted in subqueries(although it is supported in select list in normal query). So the query is needed to be changed so that subquery does not have alias(AS y0_). Query should look like this:
select [ommitted field names] from dtb_trd_resultado.resu.tbl_posicao_produto_trd this_ where this_.ind_situa_regis=? and exists (select distinct this0__.tip_dolar as
from dtb_trd_resultado.resu.tbl_det_posicao_produto_trd this0__ where this0__.ind_situa_regis=? and this0__.dat_posic=?)
> On Sybase, a subquery is incorrectly generated, causing ''Incorrect syntax near the keyword 'as'.
> -------------------------------------------------------------------------------------------------
>
> Key: HHH-3032
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3032
> Project: Hibernate Core
> Issue Type: Bug
> Affects Versions: 3.2.5
> Environment: Hibernate 3.2.5, JDK 1.6, IDE Eclipse 3.3, running on Windows XP
> Reporter: Fernando Galdino
> Assignee: Gail Badner
> Fix For: 3.2.x, 3.3.x, 3.5
>
>
> I am using Spring 2.5 and Hibernate. I created a method to find a list of ProductPositionData based on an existence of its details represented by class DetalhePosicaoProdutoData. So, there is a relationship 1:n between tables ProductPosition and DetalhePosicaoProduto.
> public List<ProductPositionData> findAllBy(Date date, String viewCode, String status)
> {
> DetachedCriteria subquery = DetachedCriteria.forClass(DetalhePosicaoProdutoData.class);
> subquery.add(Expression.eq("indSituaRegis", status));
> subquery.add(Expression.eq("compositeId.datPosic", date));
> subquery.setProjection(Projections.distinct(Property.forName("tipDolar")));
>
> DetachedCriteria criteria = DetachedCriteria.forClass(ProductPositionData.class);
> criteria.add(Expression.eq("indSituaRegis", status));
> criteria.add(Subqueries.exists(subquery));
> List<ProductPositionData> list = this.hibernateTemplate.findByCriteria(criteria);
> return list;
> }
> It should generate a query on the format SELECT blablabla FROM xyz WHERE exists (SELECT 1 FROM wyz). In really, running this method, I got a similar query.
> select [ommitted field names]
> from dtb_trd_resultado.resu.tbl_posicao_produto_trd this_
> where this_.ind_situa_regis=? and exists (select distinct this0__.tip_dolar as y0_
> from dtb_trd_resultado.resu.tbl_det_posicao_produto_trd this0__ where this0__.ind_situa_regis=? and this0__.dat_posic=?)
> It causes the following error running under Sybase:
> Incorrect syntax near the keyword 'as'.
> ; nested exception is com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword 'as'.
> It happens because on the subquery is generated this0__.tip_dolar as y0_ but "as y0_" is not valid in Sybase because using alias is not allowed for Sybase subqueries.
> I saw similar problems at:
> http://forum.hibernate.org/viewtopic.php?t=949233
> http://opensource.atlassian.com/projects/hibernate/browse/HHH-2905
> -----------------------------------
> Stacktrace:
> org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select this_.tip_orige_opera as tip1_4_0_, this_.num_opera as num2_4_0_, this_.ind_ativo_passi as ind3_4_0_, this_.tip_posic_opera as tip4_4_0_, this_.num_book as num5_4_0_, this_.num_regra_produ as num6_4_0_, this_.tip_objet_opera as tip7_4_0_, this_.tip_opera as tip8_4_0_, this_.num_empre as num9_4_0_, this_.num_clien as num10_4_0_, this_.ind_tradi as ind11_4_0_, this_.dat_inici_opera as dat12_4_0_, this_.dat_termi_opera as dat13_4_0_, this_.dat_termi_opera_me as dat14_4_0_, this_.dat_venci_risco as dat15_4_0_, this_.val_parid_moeda as val16_4_0_, this_.pcl_taxa_opera as pcl17_4_0_, this_.pcl_sobre_index as pcl18_4_0_, this_.val_cotac_indic_abert as val19_4_0_, this_.cod_risco_index as cod20_4_0_, this_.num_confi_calcu_produ as num21_4_0_, this_.cod_indic_econo_indic as cod22_4_0_, this_.tip_indic_econo_indic as tip23_4_0_, this_.nat_indic_econo_indic as nat24_4_0_, this_.tip_merca_indic_indic as tip25_4_0_, this_.cod_indic_econo_taxa as cod26_4_0_, this_.tip_indic_econo_taxa as tip27_4_0_, this_.tip_merca_indic_taxa as tip28_4_0_, this_.nat_indic_econo_taxa as nat29_4_0_, this_.ind_situa_regis as ind30_4_0_, this_.dat_situa_regis as dat31_4_0_, this_.cod_user as cod32_4_0_, this_.num_carte as num33_4_0_, this_.dat_liqui_opera as dat34_4_0_, this_.cod_indic_econo_taxa_fwd as cod35_4_0_, this_.tip_indic_econo_taxa_fwd as tip36_4_0_, this_.tip_merca_indic_taxa_fwd as tip37_4_0_, this_.nat_indic_econo_taxa_fwd as nat38_4_0_, this_.dat_limit_varia_indic as dat39_4_0_, this_.tip_metod_preci as tip40_4_0_, this_.tip_estru_sinte as tip41_4_0_, this_.dat_entra_opera as dat42_4_0_ from dtb_trd_resultado.resu.tbl_posicao_produto_trd this_ where this_.ind_situa_regis=? and exists (select distinct this0__.tip_dolar as y0_ from dtb_trd_resultado.resu.tbl_det_posicao_produto_trd this0__ where this0__.ind_situa_regis=? and this0__.dat_posic=?)]; SQL state [ZZZZZ]; error code [156]; Incorrect syntax near the keyword 'as'.
> ; nested exception is com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword 'as'.
> at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
> at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
> at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
> at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
> at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
> at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:981)
> at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:974)
> at com.jpmorgan.br.databroker.control.productposition.ProductPositionControlImpl.findAllByx(ProductPositionControlImpl.java:45)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
> at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
> at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
> at $Proxy5.findAllByx(Unknown Source)
> at com.jpmorgan.br.databroker.control.productposition.ProductPositionDataProvider.getData(ProductPositionDataProvider.java:32)
> at com.jpmorgan.br.databroker.service.OptPriceProcessTest.runProcess(OptPriceProcessTest.java:109)
> at com.jpmorgan.br.databroker.service.OptPriceProcessTest.testProcess(OptPriceProcessTest.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
> at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword 'as'.
> at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:3178)
> at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2481)
> at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
> at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1611)
> at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1596)
> at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:96)
> at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
> at org.hibernate.loader.Loader.doQuery(Loader.java:674)
> at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
> at org.hibernate.loader.Loader.doList(Loader.java:2220)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
> at org.hibernate.loader.Loader.list(Loader.java:2099)
> at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
> at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
> at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:991)
> at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
> ... 35 more
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list