[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steve Ebersole updated HHH-3032:
--------------------------------
Fix Version/s: (was: 3.2.7)
3.2.x
> 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