I started out with the Hibernate 2.17c Criteria API, but couldn't obtain a subset of the properties of a class until Projections were incorporated in Hibernate 3.0 (hooray!)

In trying to use Projections, where one of the properties is a primary key in the database, the following message is observed:

ERROR JDBCExceptionReporter:58 - Column not found message from server: "Unknown column 'y1_' in 'where clause'"
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query


I checked for similar problems, and found a posting from Dec, 2003: "Problem with subquery since 3.0rc1"
There was a bug submitted , and per the suggestions from the reply, 3.0 beta versions were tried. This problem exists when using any of the Hibernate 3.0 beta versions. This might not be a related problem, or I'm making an egregious error somewhere.


Please excuse my messy files, I was using them to get familiar with Hibernate and never got
around to cleaning them up. I also avoided method chaining so that I could read and more readily debug.


thanks in advance for any assistance,
mw


Hibernate version: 3.0 final release
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="edu.ucar.rap.mantis.exts.datasource.metar.Metar" table="Metars">
<id name="icaoId" column="icaoId" type="string" >
<generator class="native"/>
</id>

<property name="obsTime" type="integer" not-null= "true" length = "10" column="obsTime"/>
<property name="reportTime" type="date" column="reportTime"/>
<property name="temp" type="int" column="temp" />
<property name="dewp" type="int" column="dewp"/>
<property name="wdir" type="int" column="wdir" not-null="false"/>
<property name="wspd" type="int" column="wspd" />
<property name="wgst" type="int" column="wgst"/>
<property name="visib" type="int" column="visib"/>
<property name="altim" type="int" column="altim"/>
<property name="slp" type="int" column="slp"/>
<property name="qcField" type="int" column="qcField"/>
<property name="wxString" type="string" column="wxString"/>
<property name="cldCvg1" type="string" column="cldCvg1"/>
<property name="cldCvg2" type="string" column="cldCvg2"/>
<property name="cldCvg3" type="string" column="cldCvg3"/>
<property name="cldCvg4" type="string" column="cldCvg4"/>
<property name="cldBas1" type="int" column="cldBas1"/>
<property name="cldBas2" type="int" column="cldBas2"/>
<property name="cldBas3" type="int" column="cldBas3"/>
<property name="cldBas4" type="int" column="cldBas4"/>
<property name="presTend" type="int" column="presTend"/>
<property name="maxT" type="int" column="maxT"/>
<property name="minT" type="int" column="minT"/>
<property name="maxT24" type="int" column="maxT24"/>
<property name="minT24" type="int" column="minT24"/>
<property name="visib" type="int" column="visib"/>
<property name="altim" type="int" column="altim"/>
<property name="slp" type="int" column="slp"/>
<property name="qcField" type="int" column="qcField"/>
<property name="wxString" type="string" column="wxString"/>
<property name="cldCvg1" type="string" column="cldCvg1"/>
<property name="cldCvg2" type="string" column="cldCvg2"/>
<property name="cldCvg3" type="string" column="cldCvg3"/>
<property name="cldCvg4" type="string" column="cldCvg4"/>
<property name="cldBas1" type="int" column="cldBas1"/>
<property name="cldBas2" type="int" column="cldBas2"/>
<property name="cldBas3" type="int" column="cldBas3"/>
<property name="cldBas4" type="int" column="cldBas4"/>
<property name="presTend" type="int" column="presTend"/>
<property name="maxT" type="int" column="maxT"/>
<property name="minT" type="int" column="minT"/>
<property name="maxT24" type="int" column="maxT24"/>
<property name="minT24" type="int" column="minT24"/>
<property name="precip" type="int" column="precip"/>
<property name="pcp3hr" type="int" column="pcp3hr"/>
<property name="pcp6hr" type="int" column="pcp6hr"/>
<property name="pcp24hr" type="int" column="pcp24hr"/>
<property name="snow" type="int" column="snow"/>
<property name="vertVis" type="int" column="vertVis" />
<property name="metarType" type="string" column="metarType"/>
<property name="rawOb" type="string" column="rawOb"/>

<!-- SCALED DATA -->
<property name="ScaledTemp" formula="temp * 0.1" type="float"/>
<property name="ScaledDewp" formula="dewp * 0.1" type="float" />
<property name="ScaledVisib" formula="visib * 0.01" type="float"/>
<property name="ScaledAltim" formula="altim * 0.1" type="float"/>
<property name="ScaledSlp" formula="slp * 0.1" type="float"/>
<property name="ScaledCldBas1" formula="cldBas1 * 100" type="int"/>
<property name="ScaledCldBas2" formula="cldBas2 * 100" type="int"/>
<property name="ScaledCldBas3" formula="cldBas3 * 100" type="int"/>
<property name="ScaledCldBas4" formula="cldBas4 * 100" type="int"/>
<property name="ScaledPresTend" formula="presTend * 0.1" type="float"/>
<property name="ScaledMaxT" formula="maxT * 0.1" type="float"/>
<property name="ScaledMinT" formula="minT * 0.1" type="float"/>
<property name="ScaledMaxT24" formula="maxT24 * 0.1" type="float"/>
<property name="ScaledMinT24" formula="minT24 * 0.1" type="float"/>
<property name="ScaledPrecip" formula="precip * 0.001" type="float"/>
<property name="ScaledPcp3hr" formula="pcp3hr * 0.001" type="float"/>
<property name="ScaledPcp6hr" formula="pcp6hr * 0.001" type="float"/>
<property name="ScaledPcp24hr" formula="pcp24hr * 0.001" type="float"/>
<property name="ScaledSnow" formula="snow * 0.001" type="float"/>
<property name="ScaledVertVis" formula="vertVis * 100" type="integer"/>

<many-to-one name="StationInfo" class="edu.ucar.rap.mantis.exts.datasource.station.StationInfo"
column="icaoId" property-ref="icaoId" insert="false" update="false" cascade="all"/>


</class>
</hibernate-mapping>




Code between sessionFactory.openSession() and session.close():
package edu.ucar.rap.mantis.exts.datasource.metar;




import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;

import org.hibernate.transform.AliasToBeanResultTransformer;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;

public class MiniMetar
{
private SessionFactory _sessionFactory;
private Session _session;

public MiniMetar( )
{
try
{
Configuration config = new Configuration();
config.addResource( "hibernate.cfg.xml" );
_sessionFactory = config.configure( ).buildSessionFactory( );
_session = _sessionFactory.openSession();
}
catch (HibernateException ex)
{
ex.printStackTrace();
}

}



public Session getSession()
{
return _session;
}



public List doSimpleQuery()
{




Criteria crit = getSession().createCriteria( Metar.class );

//select rawOb icaoId obsTime from Metars where icaoId='KSEA'
ProjectionList pList = Projections.projectionList();
pList.add( Projections.property( "rawOb" ), "rawOb" );
pList.add( Projections.property( "icaoId" ), "icaoId" );
pList.add( Projections.property( "obsTime" ), "obsTime" );
crit.setMaxResults( 1000 );
crit.add( Restrictions.like( "icaoId", "KSEA" ) );
crit.setProjection( pList );

crit.setResultTransformer( new AliasToBeanResultTransformer( Metar.class ) );
List result = crit.list();

return result;




}

public void close()
{
getSession().close();
}

public static void main( String[] args )
{
MiniMetar mmet = new MiniMetar();
List results = mmet.doSimpleQuery();

Iterator iter = results.listIterator();

while( iter.hasNext() )
{
Metar m = (Metar) iter.next();
System.out.println( m.getRawOb() + " " + m.getIcaoId() + " " + m.getObsTime() );
}

}



}
/b]


[b]Full stack trace of any exception that occurs:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1420)
at org.hibernate.loader.Loader.list(Loader.java:1400)
at org.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:110)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1266)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:294)
at edu.ucar.rap.mantis.exts.datasource.metar.MiniMetar.doSimpleQuery(MiniMetar.java:75)
at edu.ucar.rap.mantis.exts.datasource.metar.MiniMetar.main(MiniMetar.java:92)
Caused by: java.sql.SQLException: Column not found message from server: "Unknown column 'y1_' in 'where clause'"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1158)
at org.hibernate.loader.Loader.doQuery(Loader.java:357)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:199)
at org.hibernate.loader.Loader.doList(Loader.java:1417)
... 6 more


Name and version of the database you are using:mySQL 3.23.49

The generated SQL (show_sql=true):
/usr/local/j2sdk/bin/java -classpath "/home/minnawin/cvs/java/build:/home/minnawin/mysql-connector-java-3.0.16-ga/mysql-connector-java-3.0.16-ga-bin.jar:/home/minnawin/xdoclet-1.2.2/lib:/home/minnawin/xdoclet-1.2.2/lib/commons-collections-2.0.jar:/home/minnawin/xdoclet-1.2.2/lib/commons-logging.jar:/home/minnawin/xdoclet-1.2.2/lib/log4j.jar:/home/minnawin/xdoclet-1.2.2/lib/maven-xdoclet-plugin-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-apache-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-bea-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-borland-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-caucho-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-de-locale-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-ejb-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-exolab-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-fr_FR-locale-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-hibernate-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-hp-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-ibm-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-java-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-jboss-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-jdo-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-jmx-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-jsf-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-libelis-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-macromedia-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-mockobjects-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-mvcsoft-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-mx4j-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-objectweb-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-openejb-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-oracle-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-orion-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-portlet-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-pramati-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-pt_BR-locale-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-solarmetric-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-spring-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-sun-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-sybase-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-tjdo-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-web-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-webwork-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-wsee-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xdoclet-xdoclet-module-1.2.2.jar:/home/minnawin/xdoclet-1.2.2/lib/xjavadoc-1.1.jar:/d1/minnawin/JBuilder7/lib/junit.jar:/d1/minnawin/JBuilder7/lib/unittest.jar:/home/minnawin/cvs/java/ext_lib/jdom/jdom_b9/jdom.jar:/d1/minnawin/JBuilder7/jakarta-tomcat-4.0.3/common/lib/servlet.jar:/home/minnawin/cvs/java/ext_lib/jakarta-commons-pool/commons-pool-1.2/commons-pool-1.2.jar:/home/minnawin/ServletUnit/servletunit13/servletunit.jar:/home/minnawin/ServletUnit/servletunit13/src.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/httpunit.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/junit.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/nekohtml.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/servlet.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/xercesImpl.jar:/home/minnawin/cvs/java/ext_lib/httpunit/httpunit-1.6/xmlParserAPIs.jar:/home/minnawin/cvs/java/ext_lib/hibernate/dbcp-connection-provider.jar:/home/minnawin/cvs/java/ext_lib/jakarta-commons-dbcp/commons-dbcp-1.2.1/commons-dbcp-1.2.1.jar:/home/minnawin/cvs/java/ext_lib/jakarta-commons-httpclient/httpclient-3.0-alpha2/commons-httpclient-3.0-alpha2-all.jar:/home/minnawin/cvs/java/ext_lib/jakarta-commons-httpclient/httpclient-3.0-alpha2/commons-httpclient-3.0-alpha2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/hibernate3.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/cglib-full-2.0.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/dom4j-1.5.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ehcache-1.1.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jta.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/log4j-1.2.9.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/commons-collections-2.1.1.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/commons-logging-1.0.4.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ant-1.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ant-antlr-1.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ant-junit-1.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ant-launcher-1.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/ant-swing-1.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/antlr-2.7.4.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/c3p0-0.8.4.5.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/cleanimports.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/concurrent-1.3.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/connector.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jaas.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jacc-1_0-fr.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jaxen-1.1-beta-4.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jboss-cache.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jboss-common.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jboss-jmx.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jboss-remoting.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jboss-system.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jdbc2_0-stdext.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/jgroups-2.2.7.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/junit-3.8.1.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/oscache-2.1.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/proxool-0.8.3.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/swarmcache-1.0rc2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/versioncheck.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/xalan-2.6.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/xerces-2.6.2.jar:/home/minnawin/cvs/java/ext_lib/hibernate/hibernate-3.0beta4/lib/xml-apis.jar:/usr/local/j2sdk/jre/lib/ext/sunjce_provider.jar:/usr/local/j2sdk/jre/lib/ext/dnsns.jar:/usr/local/j2sdk/jre/lib/ext/j3dutils.jar:/usr/local/j2sdk/jre/lib/ext/ldapsec.jar:/usr/local/j2sdk/jre/lib/ext/localedata.jar:/usr/local/j2sdk/jre/lib/ext/vecmath.jar:/usr/local/j2sdk/jre/lib/ext/j3daudio.jar:/usr/local/j2sdk/jre/lib/ext/j3dcore.jar:/usr/local/j2sdk/jre/lib/sunrsasign.jar:/usr/local/j2sdk/jre/lib/jce.jar:/usr/local/j2sdk/jre/lib/im/indicim.jar:/usr/local/j2sdk/jre/lib/im/thaiim.jar:/usr/local/j2sdk/jre/lib/jsse.jar:/usr/local/j2sdk/jre/lib/charsets.jar:/usr/local/j2sdk/jre/lib/plugin.jar:/usr/local/j2sdk/jre/lib/rt.jar:/usr/local/j2sdk/jre/javaws/javaws.jar:/usr/local/j2sdk/lib/dt.jar:/usr/local/j2sdk/lib/htmlconverter.jar:/usr/local/j2sdk/lib/tools.jar:/usr/local/j2sdk/demo/jfc/Java2D/Java2Demo.jar:/usr/local/j2sdk/demo/plugin/jfc/Java2D/Java2Demo.jar" edu.ucar.rap.mantis.exts.datasource.metar.MiniMetar
Hibernate: select this_.rawOb as y0_, this_.icaoId as y1_, this_.obsTime as y2_ from Metars this_ where y1_ like ? limit ?


Debug level Hibernate log excerpt:
18:56:39,614 WARN JDBCExceptionReporter:57 - SQL Error: 1054, SQLState: S0022
18:56:39,635 ERROR JDBCExceptionReporter:58 - Column not found message from server: "Unknown column 'y1_' in 'where clause'"

I have the same kind of problem.

Hibernate adds suffixes like 0_ to the column name, and therefor when it is trying to get a value from a result set it is asking for the columname eg. name0_ instead of name.

I do not know what to do in this case. May be it is JDBC driver dependend.

Can anyone help with this problem
Erwin

Erwin,

Perhaps my problem might be due to the following:

The column which I'm getting the exception corresponds to a database key that is not unique, and the legacy database I'm working with (and cannot change) has two primary keys defined, this one and another. I switched to a different mapping where i'm using composite-id :( . Fortunately, someone in my group knows how to use Middlegen and generated the mapping file, persistent object class and composite identifier class for me. I haven't tried this out with projections yet, but will get around to it sometime next week. I have other things that have a deadline in a week so I won't have a chance to try it out.

I hope that helps you in your search for a solution.

thanks,
m.

Hi

If you remove Projections it will probably work.

Criteria criteria = hibernateSession.createCriteria(PWVUser.class);
criteria.add(Restrictions.ilike(INewSearch.USER_EMAIL_ADDRESS_COLLUMN, searchPairList.getSearchString(), MatchMode.ANYWHERE));
//ProjectionList pList = Projections.projectionList();
//pList.add(Projections.property("id"), "id");
//pList.add(Projections.property(USER_ID_COLLUMN), USER_ID_COLLUMN);
//criteria.setProjection(pList);

With projections I get this sql

select this_.id as y0_, this_.userId as y1_ from
wvUser this_ left outer join impsSession sessions2_ on this_.id=sessions2_.userIden
where lower(this_.email) like ? and lower(y1_) like ?

The right SQL shoud have been

select this_.id as y0_, this_.userId as y1_ from
wvUser this_ left outer join impsSession sessions2_ on this_.id=sessions2_.userIden
where lower(this_.email) like ? and lower(this_.userId) like ?


Lars

thanks, but I really need to use projections so that users can dynamically request specific columns in a query.

I was originally using a primary key that wasn't unique, so I used a composite-key. The problem still persists.

See http://opensource2.atlassian.com/projec ... se/HHH-817. To me it looks like Hibernate is generating SQL that reuses the alias in the where clause, and several DBs don't like it. SQLServer, Oracle, Postgres, and MySQL all seem to take issue with SQL similar to:

select this_.id as y0_ from mytable as this_ where y0_=?

So it seems to me that this could be a Hibernate bug similar to the one filed.