Criteria Query Issue with Projection and restriction

Michael Kuhinica · **Joined:** Tue Jun 24, 2008 8:35 am **Posts:** 2

I'm getting this odd error whenever I try to add a restriction to a property with a projection set on it. Hibernate tries to query using the property alias and not with its name. Properties without a projection works fine.
I had to use a sqlRestriction with the collumn name hardcoded on it as a workaround...
Does anyone know what could it be?

Hibernate version: 3.2.6ga

Code between sessionFactory.openSession() and session.close():

Code:

      Criteria c = session.createCriteria(Customer.class).setProjection(
      Projections.projectionList()
         .add(Projections.property(Customer.PROP_ID), "id")
         .add(Projections.property(Customer.PROP_NAME), "name")
      )
      .add(Restrictions.like(Customer.PROP_EMAIL, email))
      .add(Restrictions.like(Customer.PROP_NAME, name));

The generated SQL (show_sql=true):

Code:

    select
        this_.ctm_id as y0_,
        this_.ctm_name as y1_ 
    from
        customers this_ 
    where
        this_.ctm_mail like ? 
        and y1_ like ?

Full stack trace of any exception that occurs:

Code:

09:47:21,920  WARN JDBCExceptionReporter:77 - SQL Error: 1054, SQLState: 42S22
09:47:21,923 ERROR JDBCExceptionReporter:78 - Unknown column 'y1_' in 'where clause'
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2216)
   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 br.com.webtraffic.bidmanagement.dao.CustomerListDAOImpl.main(CustomerListDAOImpl.java:43)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'y1_' in 'where clause'
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
   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:2213)
   ... 6 more

Name and version of the database you are using:MySQL 5

Cameron McKenzie · **Posted:** Tue Jun 24, 2008 9:56 am

I'd wonder if the name field in the POJO is properly mapped to the corresponding column in the database.

As a test, I'd try to do the query without the projections. See if it works, and see what is returned for the name attribute.

Michael Kuhinica · **Joined:** Tue Jun 24, 2008 8:35 am **Posts:** 2

Cameron McKenzie wrote:

I'd wonder if the name field in the POJO is properly mapped to the corresponding column in the database.

As a test, I'd try to do the query without the projections. See if it works, and see what is returned for the name attribute.

Hello Cameron,
I tried it already and everything worked fine. In the example I posted I query two properties, one with a projection and another without it, the projection-less property is queried in the right way but the one with a projection is not.
I made a bunch of tests and it looks like a Hibernate bug for me.

aswinnair · **Joined:** Tue Oct 11, 2005 11:03 am **Posts:** 4

I have the same issue. If I specify column alias with projection and then have restriction the wrong alias gets used in the query and it fails. Is it a bug or am I missing something?

Quamtar · **Joined:** Sat Jun 06, 2009 2:49 pm **Posts:** 1

I had a very similar problem and was able to get past it by using "this.property-name" in my restriction.

I wrote up the details here: http://stackoverflow.com/questions/84644/hibernate-query-by-example-and-projections/960278#960278

justinforder · **Joined:** Sun Nov 15, 2009 10:33 am **Posts:** 2

I ran into the same problem today.

The MySQL reference is clear that the use of aliases in the WHERE clause is not permitted in standard SQL.
http://dev.mysql.com/doc/refman/5.0/en/ ... alias.html

There is a bug report from 2005 here:
http://opensource.atlassian.com/project ... se/HHH-817

and patches have been submitted, but the problem has never been fixed - the bug is classified as 'minor', and is unassigned. Gavin King's only contribution was his initial comment that "This is perfectly reasonable SQL. Oracle should support it."

This is terrible.

My initial workaround was to use an SQL restriction, but now I've heard about "this.property" as an alternative (and checked that it works in my case), I'll favour that.

-- Justin Forder

justinforder · **Joined:** Sun Nov 15, 2009 10:33 am **Posts:** 2

By the way, even though the "this.property" approach is not documented in the Hibernate Reference Manual, it is mentioned in "Hibernate in Action"

Section 7.3.3:

Quote:

Properties of the root entity (Item) may be referred to without the qualifying alias or by using the alias "this".

and in "Java Persistence with Hibernate"

Section 15.1.2:

Quote:

Properties of the root entity of the criteria query (Item) may be referred to without the qualifying alias, or with the alias "this":

So I think it is safe to assume it won't break in future.

-- Justin Forder

zephtao · **Posted:** Fri Jun 17, 2016 10:35 am

Quamtar wrote:

I had a very similar problem and was able to get past it by using "this.property-name" in my restriction.

I wrote up the details here: http://stackoverflow.com/questions/84644/hibernate-query-by-example-and-projections/960278#960278

Nice solution. It worked just fine for me. Tks.