This is perfectly reasonable SQL. Oracle should support it.

well, at least Oracle 8 and 9 doesn't support such a queries, for Oracle 8 see for example: http://www.cs.bris.ac.uk/maintain/OracleDocs/server.816/a76989/express4.htm

"You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query."

and in docs for Oracle 9:

" ... Specify a different name (alias) for the column expression. Oracle will use this alias in the column heading. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause, but not other clauses in the query."

I suppose that subqueries should be used for such a constructions, see Oracle 9 documentation:

"Use subqueries for the following purposes:
...

• To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements"

Postgres does not allow using aliases too:
select id as i from a_table where i = 2;
causes error:
ERROR: column "i" does not exist

MySQL 3.2.x does not allow aliases in the where clause either:

mysql> select id as i from a_table where i = 2;
ERROR 1054: Unknown column 'i' in 'where clause'

Same problem on SQLServer 2000

FYI

The problem only occurs when adding a restriction on the entity ID (PK).

session.createCriteria( SynsetType.class )
.setProjection(
Projections.projectionList()
.add( Projections.property("id" )) )
.add( Restrictions.eq( "id", new Integer(1) ) )
.list();

This one works:
Hibernate: select this_.Synset_Type_Id as y0_ from Synset_Type this_ where this_.Synset_Type_Id=?

-

session.createCriteria( SynsetType.class )
.setProjection(
Projections.projectionList()
.add( Projections.property("id" ), "id") ) // <<<<< alias
.add( Restrictions.eq( "id", new Integer(1) ) )
.list();

This one fails:
Hibernate: select this_.Synset_Type_Id as y0_ from Synset_Type this_ where y0_=?

Passing an alias is useful to recognize the property in a ResultTransformer.

An interesting fact is that I don't get the problem when projecting/restricting a subcriteria on the ID, the same way.

Oh!

As mentionned by shanonvl (Posted: Wed Sep 21, 2005 1:52 pm) http://forum.hibernate.org/viewtopic.php?t=941669, this only happens when you give use an alias which is exactly the name of the property.

session.createCriteria( SynsetType.class )
.setProjection(
Projections.projectionList()
.add( Projections.property("id" ), "bozo") ) // <<<<< alias : bozo
.add( Restrictions.eq( "id", new Integer(1) ) )
.list();

This one works:

Hibernate: select this_.Synset_Type_Id as y0_ from Synset_Type this_ where this_.Synset_Type_Id=?

It sounds logical to me. I'll do as shanonvl suggest, I'll prefix my aliases (disambiguate them).

Still no support from Oracle or MySQL (Oracle 10g, mysql 5.0.13rc, hibernate 3.0.5). It`s real pain in ass.

workaround have been posted (by francois) and feel free to submit a patch that fixes it.

Hello!

The query generated by Hibernate is definitely an invalid SQL, since column aliases are not allowed in WHERE clauses.

SQL evaluates the statements in the following order:
(1) the FROM clause
(2) the WHERE clause
(3) the SELECT clause

When the WHERE clause is being evaluated, the aliases from the SELECT clause are not yet known.

There are several links that describe the problem:

http://www.sqlhacks.com/index.php/Retrieve/Alias-in-where
http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

I do not have the SQL standard specification at hand, but the third document (at mysql.com) states
that the mentioned behaviour is part of the SQL standard (so it is not a bug in Oracle, Postgres or MySQL).

Please, change the priority of this issue to "Critical".
Thank you.

I have a workaround that has worked for me in basic examples.

Basically i'm using projections and the alias to bean transformer fo only load selected properties of an entity.

// Set up selected columns to load of the entity
ProjectionList projectionList = Projections.projectionList();
projectionList.add(new CustomPropertyAliasProjection("property1", "property1"));
projectionList.add(new CustomPropertyAliasProjection("property2", "property2"));
criteria.setProjection(projectionList);
criteria.setResultTransformer(new PrefixAliasToBeanResultTransformer(entityClass));

// Now I can use this without getting the ORA-00904 error
criteria.add(Restrictions.eq("property1", new Integer(1)));

Here's the class I wrote to override AliasedProjection. I can't guarantee it works in all cases. It would be great if someone from the Hibernate team can take a quick look and see if there are any potential problems that I've overlooked.

/**

• Customized projection to map a property with an alias that is intended to work-around HHH-817.
• This works in a basic situation that I've tried, but I can't guarantee it works in complex queries.
• This causes the alias to be bypassed in the where clause so the original column name is used.
• Some DB's don't support aliases in the where clause.
• http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
• @author Chris Federowicz
  */
  public class CustomPropertyAliasProjection extends AliasedProjection {
  /** Stores the property name being aliased. */
  private String propertyName;
  /** Stores the column name for the property in the PropertyProjection. */
  private String propertyColumn;

/**

• @param propertyName the property name
• @param alias alias of the property
  */
  public CustomPropertyAliasProjection(String propertyName, String alias) { super(Projections.property(propertyName), alias); this.propertyName = propertyName; }

/** Default serialization ID. */
private static final long serialVersionUID = 1L;

/**

• @see org.hibernate.criterion.AliasedProjection#toSqlString(org.hibernate.Criteria, int, org.hibernate.criterion.CriteriaQuery)
  */
  public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException { String ret = super.toSqlString(criteria, position, criteriaQuery); // Store the property's real column for use in getColumnAliases propertyColumn = criteriaQuery.getColumn(criteria, propertyName); return ret; }

/**

• Override to use the property's column if we have it (which we should).
• Hack alert: This assumes that toSqlString gets called beforehand, which seems to be the case.
• @see org.hibernate.criterion.AliasedProjection#getColumnAliases(java.lang.String, int)
  */
  public String[] getColumnAliases(String alias, int loc)
  Unknown macro: { return propertyColumn != null ? new String[] {propertyColumn} }

}

I apologize for the lack of formatting in the above class

The patch is against the current 3.2 snapshot. It makes expressions in WHERE clause use column names instead of aliases.

Michal, sorry for the delay

I would like to add that DB2 doesn't support it either.

Also, a note on the workaround. You should only prefix the alias for those columns that are associated with the main table in the query. I found that when adding a prefix to a child table along an association path, an exception was thrown stating that it could not find the associated property.

Removing the alias on child table elements allowed me to prevent the use of the alias in the WHERE clause, as well as properly utilize a ResultTransformer.

My 2 cents.

-B

I have simmilar problem with following query (@Hibernate 3.2.4sp1):
"delete SupplierBO libo1 where libo.oid not in (select abbo.supplier.oid from PartBO abbo) and libo.oid not in (select pobo.supplier.oid from PotentialanalyseSourceBO pobo)".

Everything is ok if there is only one condition either
" libo.oid not in (select pobo.lieferwerk.oid from PotenzialanalyseQuelleBO pobo)"
or
"ibo.oid not in (select abbo.lieferwerk.oid from AbrufteilBO abbo)"
but with both of them i run into ORA-00904: "SUPPLIER0_"."OID" from the second condition.

I am new in Hibernate and I will be very thankful if anyone could suggets me some efficient and safe workaround to this.

Is this published patch safe in this case? If yes could it have some negative effects on other hibernate components?

Chris,

I appreciate your help with this issue. I am using the Hibernate AliasToBean results transformer to effectivly lazy load specific properties of my Criteria and Queryies. Essentially adding aliases with the same names as the properties to the criteria and then using the result transformer to only load the properties that are mentioned in the aliases. As such this issue was causing me problems, I made a minor change to your class that fixed an issue where the wrong column was being returned.

Here is the fix.

/**

• Override to use the property's column if we have it (which we should).
• Hack alert: This assumes that toSqlString gets called beforehand, which seems to be the case.
• @author kevinrs
• Note, I had to hack his hack so that this worked correctly. It first checks if the given alias is backed by this Object, and only then
• returna the column property value.
  *
• @see org.hibernate.criterion.AliasedProjection#getColumnAliases(java.lang.String, int)
  */
  public String[] getColumnAliases(String alias, int loc) {
  String[] returnValue = null;
  if(this.getAliases()[0].equals(alias) && propertyColumn != null)
  Unknown macro: { returnValue = new String[] {propertyColumn}; }
  else { returnValue = super.getColumnAliases(alias, loc); }
  return returnValue;
  }

Thanks so much for your help

Please attach a runnable test case (Java + mapping).

I had similar problem but I resolved it using keyword "this" behind the property name in the where clause :

Restrictions.like("this.name", value, MatchMode.EXACT)

I don't know if it works in all cases...

Hi,
this bug needs to be changed to 'Critical' as when using mapped entities you need to use the criteria.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) method to recieve the pojo's data back in a hashmap. the 'CriteriaSpecification.ALIAS_TO_ENTITY_MAP' implementation only converts the properties with aliases to the hashmap, a behavior i think is a different bug. Seeing as this is the only way to get into the hash the required property you have to 'choose' between using the property in the select clause or in the where clause. this is definitely a critical problem.
Thanks

This bug must be marked as critical, if not, what is the reason for not doing it? maybe the "this. prefix in where"? this works for me but not to others.

Please we need a answer.

I agree, I can't see how this issue can be minor and still untouched after such a long time.

I am having the same problem with SQL Server 2005. Aliases in the where clause can't be used. I am using criteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP) so my aliases are the map's keys. I want the map's keys to be the same as the property names and therefore I have aliases that are the same as the property names. Of course when I add restrictions, I am using property names and this is causing aliases in the where clause.

This bug was reported about 3 1/2 YEARS ago. Why hasn't Hibernate fixed this bug?

If some databases can handle aliases in the where clause and some can't then the dialects should be updated and the where clause should be created accordingly.

Is anyone going to get this thing done?
I am forced to use more and more native sqls becaue of this Bug.
IMHO this Bug severtity should be changed at least to normal.

Another fix is proposed in HHH-3371.

this bug is especially potent because it's the recommended technique for avoiding lazy-loaded properties!

http://www.hibernate.org/hib_docs/reference/en/html/performance.html
" A different (better?) way to avoid unnecessary column reads, at least for read-only transactions is to use the projection features of HQL or Criteria queries. This avoids the need for buildtime bytecode processing and is certainly a preferred solution. "

If lazy-loading is the most requested feature, like the documentation says, and if people are expected to use the ProjectionList behavior in criteria searches to avoid loading their blobs, etc... It's crazy this hasn't been fixed. For everything that hibernate does, I'm really surprised someone hasn't addressed this massively basic use case. I shouldn't have to create two tables and two classes because of one simple column.

I agree. Its almost 4 years open and still Minor. We've added lots of weird workarounds like SQL groupings, but it seems that Hibernate team keeps ignoring anything related to Criterias. Are criterias dead?

Okay, so maybe this won't ever get fixed, the correct workaround to lazy load a property using projections:

Create a criteria,

build your projection list...
ProjectionList cols = Projections.projectionList();

Add the columns you want to be loaded on your object, to your projection list.
Make sure you include your identity property in your projection list, but don't include any column you want to query against
(see why this stinks?)

This means I can't ever lazy-load an object using its identity, unless I want to track / set it manually.

Will this ever be fixed or is there any reason why this issue will not be fixed? I am wondering, cause this bug was posted over 4 years ago and it is still unassigned. Through this bug QBE in combination with Projection and AliasToBean is not usable. (And i think QBE i one of the greatest features in Hibernate...).

Best regards

Peter

What is the patch about? The changes aren't present in Hibernate core 3.3.2.

@Milosz Tylenda
i have to apologize, your patch works great. I have created same patch for 3.3.2GA.
But i ask myself, why this patch was never added to Hibernate Core... Any comments on this?

Best regards

peter

patch for Hibernate 3.3.2GA 17882; based on the patch of Milosz Tylenda

I ran into this today, using Java DB. I tested interactively against Java DB and MySQL to confirm that aliases didn't work in the WHERE clause, then found the MySQL documentation explaining that the SQL standard does not permit the use of aliases here.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

I found this discussed in various threads on the Hibernate User forum.

Initially I worked around the problem using an SQL restriction, but then I found the advice here:
http://stackoverflow.com/questions/84644/hibernate-query-by-example-and-projections/960278#960278

that using "this.property" works. It does, indeed, work for me, but leaves the worry that this (undocumented, so far as I know) behaviour may change in future.

I've added a comment to this forum thread:
https://forum.hibernate.org/viewtopic.php?f=1&t=988049&p=2421106#p2421106

– Justin Forder

I said that the "this.property" approach was "(undocumented, so far as I know)". The Hibernate Reference Documentation doesn't mention it (and makes the somewhat misleading statement that "An alias may optionally be assigned to a projection, so that the projected value may be referred to in restrictions or orderings").

However, both "Hibernate in Action" and "Java Persistence with Hibernate" do document the use of this.property as an option. (Section 7.3.3 in "Hibernate in Action", and section 15.1.2 in "Java Persistence with Hibernate".)

– Justin Forder