[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-3032) On Sybase, a subquery is incorrectly generated, causing ''Incorrect syntax near the keyword 'as'.

Gail Badner (JIRA) noreply at atlassian.com
Mon Jan 5 23:06:38 EST 2009


     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner updated HHH-3032:
-----------------------------

         Assignee: Gail Badner
    Fix Version/s: 3.4
                   3.3.x
                   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.4
>
>
> 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