Hibernate version:
2.1

Elemento.hbm.xml relevant part
<many-to-one name="norma" access="field" column="NORMA_ID" cascade="save-update"/>

Norma.hbm.xml relevant part
<list name="collectionSets" table="NORMA_COLLECTIONSET" lazy="true" inverse="false" cascade="save-update">
<key column="NORMA_ID"/>
<index column="normacollectionset_index"/>
<many-to-many class="com.sae.arquitectura.model.tipo.Tipo" column="COLLECTION_SET_ID" outer-join="auto"/>
</list>
<property name="tcNorma" type="com.sae.arquitectura.dao.common.converter.BigStringConverter"/>


Full stack trace of any exception that occurs:

java.sql.SQLException: ORA-00904: "NORMA4_"."ID": invalid identifier

Could not execute query
net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at com.sae.arquitectura.dao.impl.HibernateQueryStatement.count(HibernateQueryStatement.java:130)
at com.sae.arquitectura.dao.impl.GenericDAOHibernate.count(GenericDAOHibernate.java:151)
at com.sae.service.impl.LegislacionConsolidadaServiceImpl.count(LegislacionConsolidadaServiceImpl.java:78)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.framework.AopProxyUtils.invokeJoinpointUsingReflection(AopProxyUtils.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:116)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:138)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:152)
at $Proxy10.count(Unknown Source)
at com.laley.sae.controller.parsers.TestTraductorConsultaUsuarioLegislacionConsolidadaCombinadas.testCombinadaConVariosOR(TestTraductorConsultaUsuarioLegislacionConsolidadaCombinadas.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
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.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: ORA-00904: "NORMA4_"."ID": invalid identifier

Name and version of the database you are using:
Oracle 9i

The HQL
select count (*) from Elemento this where (( this in (SELECT this from this.norma.collectionSets col where upper(col.nombre) like 'ACROF' ) ) or ('ACROF'='*' and this.norma.collectionSets.size > 0)) OR ((upper(this.norma.tcNorma) like 'TCNORMA LALALA') or ('TCNORMA LALALA'='*' and this.norma.tcNorma is not null))

The generated SQL (show_sql=true):
select count(*) as x0_0_ from sae.ELEMENTO elemento0_ where (((elemento0_.id in(select elemento0_.id from sae.NORMA norma1_, sae.NORMA_COLLECTIONSET collection2_, sae.TIPO tipo3_ where elemento0_.NORMA_ID=norma1_.id and norma1_.id=collection2_.NORMA_ID and collection2_.COLLECTION_SET_ID=tipo3_.id and ((upper(tipo3_.nombre)like 'ACROF' )))))or(('ACROF'='*' )and((select count(*) from sae.NORMA norma4_, sae.NORMA_COLLECTIONSET collection5_ where elemento0_.NORMA_ID=norma4_.id and norma4_.id=collection5_.NORMA_ID)>0 )))OR(((upper(norma4_.tcNorma)like 'TCNORMA LALALA' and elemento0_.NORMA_ID=norma4_.id))or(('TCNORMA LALALA'='*' )and(norma4_.tcNorma is not null and elemento0_.NORMA_ID=norma4_.id)))

Hello, I had tried to find a post related to this, but I didn´t find anything.
As you can see, I have the HQL above and the generate SQL have a problem with the condition after the OR. Basically the norma4_ couldn´t be reconized because it is out from the correct scope.
We use in the user interface the '*' as a wild card to the user.
Is very difficult to me explain the error very well because it is very very specific. I hope anybody can help me!

Thanks

I could reduce the scope of the error:

if I execute this hql with count:
select count(*) from Elemento this where ( ( this in (SELECT this from this.norma.collectionSets col where col.nombre like 'LALA' )) or ('LALA'='*' and this.norma.collectionSets.size > 0) )

The generate sql is:
select count(*) as x0_0_ from sae.ELEMENTO elemento0_ where (((elemento0_.id in(select elemento0_.id from sae.NORMA norma1_, sae.NORMA_COLLECTIONSET collection2_, sae.TIPO tipo3_ where elemento0_.NORMA_ID=norma1_.id and norma1_.id=collection2_.NORMA_ID and collection2_.COLLECTION_SET_ID=tipo3_.id and ((tipo3_.nombre like 'LALA' )))))or(('LALA'='*' )and((select count(*) from sae.NORMA norma4_, sae.NORMA_COLLECTIONSET collection5_ where elemento0_.NORMA_ID=norma4_.id and norma4_.id=collection5_.NORMA_ID)>0 )))

...and It's execute successful

if I execute this hql:
select new com.laley.sae.view.dto.DocumentoDTOLegislacionConsolidada(this.documentoLegislacionConsolidada.id, this.documentoLegislacionConsolidada.guid, this.fechaVigenciaInicial, this.fechaVigenciaFinal, this.tipoElemento.nombre, this.descripcion,this.norma.jurisdiccion.sigla, this.fechaOrdenamiento, this.norma)from Elemento this where ( ( this in (SELECT this from this.norma.collectionSets col where col.nombre like 'LALA' )) or ('LALA'='*' and this.norma.collectionSets.size > 0) ) order by this.norma.jurisdiccion.descripcion, this.norma.emisiones.elements.emisor.descripcion, this.norma.emisiones.elements.tipoDeNorma.sigla, this.norma.emisiones.elements.numeroNorma, this.fechaOrdenamiento DESC, this.ordenInterno, this.descripcion, this.fechaVigenciaFinal DESC

The generate sql is:
select norma4_.id as id, norma4_.version as version, norma4_.tcNorma as tcNorma, norma4_.EDICION_ANTERIOR_ID as EDICION_4_, norma4_.EDICION_POSTERIOR_ID as EDICION_5_, norma4_.fechaCarga as fechaCarga, norma4_.fechaPublicacion as fechaPub7_, norma4_.fechaVigenciaInicial as fechaVig8_, norma4_.GUIDnormaGeneradora as GUIDnorm9_, norma4_.JURISDICCION_ID as JURISDI10_, norma4_.notasEspeciales as notasEs11_, norma4_.notasInternas as notasIn12_, norma4_.notasHistoria as notasHi13_, norma4_.notasVigencia as notasVi14_, norma4_.publicacion as publica15_, norma4_.BASE_ORIGEN_ID as BASE_OR16_, norma4_.fechaCreacion as fechaCr17_, norma4_.fechaModificacion as fechaMo18_, elemento0_.DOCUMENTO_LEG_CONS_ID as x0_0_, documentol11__1_.GUID as x1_0_, elemento0_.fechaVigenciaInicial as x2_0_, elemento0_.fechaVigenciaFinal as x3_0_, tipo12_.nombre as x4_0_, elemento0_.descripcion as x5_0_, jurisdicci6_.SIGLA as x6_0_, elemento0_.fechaOrdenamiento as x7_0_, norma4_.id as x8_0_ from sae.ELEMENTO elemento0_, sae.JURISDICCION jurisdicci6_, sae.NORMA_EMISIONES emisiones7_, sae.EMISION emision8_, sae.EMISORLEGISLACION emisorlegi9_, sae.TIPODENORMA tipodenorm10_, sae.DOCUMENTO_LEGISLACION_CONS documentol11_, sae.DOCUMENTO documentol11__1_, sae.TIPO tipo12_ where elemento0_.NORMA_ID=norma4_.id and norma4_.JURISDICCION_ID=jurisdicci6_.JURISDICCIONID and elemento0_.NORMA_ID=norma4_.id and norma4_.id=emisiones7_.NORMA_ID and emisiones7_.EMISION_ID=emision8_.id and emision8_.EMISOR_LEGISLACION_ID=emisorlegi9_.EMISORLEGISLACIONID and elemento0_.NORMA_ID=norma4_.id and norma4_.id=emisiones7_.NORMA_ID and emisiones7_.EMISION_ID=emision8_.id and emision8_.TIPO_NORMA_ID=tipodenorm10_.TIPODENORMAID and elemento0_.NORMA_ID=norma4_.id and norma4_.id=emisiones7_.NORMA_ID and emisiones7_.EMISION_ID=emision8_.id and elemento0_.DOCUMENTO_LEG_CONS_ID=documentol11_.PADRE_ID and documentol11_.PADRE_ID=documentol11__1_.DOCUMENTOID(+) and elemento0_.TIPO_ELEMENTO_ID=tipo12_.id and elemento0_.NORMA_ID=norma4_.id and ((((elemento0_.id in(select elemento0_.id from sae.NORMA norma1_, sae.NORMA_COLLECTIONSET collection2_, sae.TIPO tipo3_ where elemento0_.NORMA_ID=norma1_.id and norma1_.id=collection2_.NORMA_ID and collection2_.COLLECTION_SET_ID=tipo3_.id and ((tipo3_.nombre like 'LALA' )))))or(('LALA'='*' )and((select count(*) from sae.NORMA norma4_, sae.NORMA_COLLECTIONSET collection5_ where elemento0_.NORMA_ID=norma4_.id and norma4_.id=collection5_.NORMA_ID)>0 )))) order by jurisdicci6_.DESCRIPCION , emisorlegi9_.DESCRIPCION , tipodenorm10_.SIGLA , emision8_.numeroNorma , elemento0_.fechaOrdenamiento DESC , elemento0_.ordenInterno , elemento0_.descripcion , elemento0_.fechaVigenciaFinal DESC

and the result It's
25-oct-2006 18:12:39 ERROR JDBCExceptionReporter:58 - ORA-00904: "NORMA4_"."ID": invalid identifier

SUMMARY: with count pass and with a select "property"... + and some "order" ==> fails