Issue Details (XML | Word | Printable)

Key: HHH-817
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Michal Jastak
Votes: 38
Watchers: 39
Operations

If you were logged in you would be able to see more operations.
Hibernate Core

Aggregate projection aliases should not be applied to where-clause

Created: 03/Aug/05 02:34 AM   Updated: 15/Nov/09 11:19 AM
Return to search
Component/s: core
Affects Version/s: 3.0.5
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. Text File HHH-817.patch (8 kB)
2. Text File HHH-817_3.3.2GA_17882.patch (9 kB)

Environment: Oracle 9.2.0.6, Hibernate 3.0.5, Spring Framework 1.2.2 based application working on Jakarta Tomcat 5.0.28
Issue Links:
Duplicate
 
Relates
 

Bug Testcase Reminder (view):
REMINDER: Bug reports should generally be accompanied by a test case
Participants: Aaron Braeckel, Bryan Stopp, Chris Federowicz, Danny Hurlburt, David, François Delisle, Gail Badner, Gavin King, guillaume mathias, Ittai Zeidman, jazir malik, Jose Luis Piedrahita, Justin Forder, Kevin Schmidt, Konstantin Ignatyev, Max Rydahl Andersen, Michal Jastak, Michal Palicka, Milosz Tylenda, Peter Wagner, Serge P. Nekoval, Vetlugin Yury and Waldemar Prabucki


 Description  « Hide

following java code:

protected Entity loadEntityLightweight(Serializable entityId) throws DataAccessException { Criteria criteria = getSession().createCriteria(Entity.class); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Property.forName(BaseEntity.PROP_ID), BaseEntity.PROP_ID); projectionList.add(Property.forName(BaseEntity.PROP_TYPE), BaseEntity.PROP_TYPE); criteria.setProjection(projectionList); criteria.add(Restrictions.eq(BaseEntity.PROP_ID, entityId)); criteria.setResultTransformer(new AliasToBeanResultTransformer(Entity.class)); return (Entity) criteria.uniqueResult(); }

generates following SQL query:

select this_.id as y0_, this_.type as y1_ from entities this_ left outer join facilities this_1_ on this_.id=this_1_.id left outer join users this_2_ on this_.id=this_2_.id left outer join addresses address2_ on this_.address_id=address2_.id left outer join entities entity3_ on this_2_.employer_id=entity3_.id left outer join facilities entity3_1_ on entity3_.id=entity3_1_.id left outer join users entity3_2_ on entity3_.id=entity3_2_.id where y0_=?

y0_ = ? expression in where clause is causing a 904 error on Oracle 9:

ORA-00904: "Y0_": invalid identifier

hibernate dialect: org.hibernate.dialect.Oracle9Dialect

mapping for Entity class:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="false" default-cascade="save-update">

<class name="Entity" table="entities" mutable="true">

<id name="id" type="java.lang.Long" unsaved-value="null">
<generator class="sequence">
<param name="sequence">entities_id_seq</param>
</generator>
</id>

<many-to-one name="address" class="Address" column="address_id" />

...

<!--

  • Facilities
    -->
    <joined-subclass name="Facility" table="facilities">
    <key column="id" />

...
<set name="users" inverse="true" lazy="true">
<key column="facility_id" />
<one-to-many class="User" />
</set>
</joined-subclass>

<!--

  • Users
    -->
    <joined-subclass name="User" table="users" dynamic-insert="true" dynamic-update="true">
    <key column="id" />

<many-to-one name="employer" class="Entity" column="employer_id" cascade="none" />

...
<set name="userAuthorities" inverse="true" cascade="all-delete-orphan">
<key column="user_id" />
<one-to-many class="Authority" />
</set>
</joined-subclass>

</class>
</hibernate-mapping>



Gavin King added a comment - 03/Aug/05 11:56 AM

This is perfectly reasonable SQL. Oracle should support it.


Michal Jastak added a comment - 03/Aug/05 12:23 PM

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"

Konstantin Ignatyev added a comment - 04/Aug/05 01:44 PM

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


Aaron Braeckel added a comment - 14/Sep/05 12:35 PM

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'


François Delisle added a comment - 06/Dec/05 10:45 AM

Same problem on SQLServer 2000


François Delisle added a comment - 06/Dec/05 11:30 AM

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.


François Delisle added a comment - 06/Dec/05 11:49 AM

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).


Vetlugin Yury added a comment - 21/Sep/06 08:00 AM

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


Max Rydahl Andersen added a comment - 21/Sep/06 08:05 AM

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


Michal Palicka added a comment - 28/Feb/07 03:03 AM

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.


Chris Federowicz added a comment - 05/Mar/07 01:56 PM

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} }

}


Chris Federowicz added a comment - 05/Mar/07 01:57 PM

I apologize for the lack of formatting in the above class


Milosz Tylenda added a comment - 06/Apr/07 12:45 PM

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


Bryan Stopp added a comment - 11/Jun/07 09:20 AM

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


Waldemar Prabucki added a comment - 13/Jun/07 05:21 AM

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?


Kevin Schmidt added a comment - 22/Aug/07 01:57 PM

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


Gail Badner added a comment - 24/Oct/07 06:23 PM

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


guillaume mathias added a comment - 29/Oct/07 01:43 PM

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...


Ittai Zeidman added a comment - 22/Nov/07 04:18 AM

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


Jose Luis Piedrahita added a comment - 11/Dec/07 06:25 AM

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.


jazir malik added a comment - 12/Aug/08 03:47 AM

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


Danny Hurlburt added a comment - 06/Jan/09 08:48 PM

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.


Waldemar Prabucki added a comment - 13/Feb/09 04:55 AM

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.


Gail Badner added a comment - 27/Feb/09 11:32 AM

Another fix is proposed in HHH-3371.


David added a comment - 13/Apr/09 04:12 PM

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.


Serge P. Nekoval added a comment - 14/Apr/09 03:27 AM

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?


David added a comment - 14/Apr/09 09:06 AM

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.


Peter Wagner added a comment - 30/Oct/09 04:02 AM

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


Peter Wagner added a comment - 30/Oct/09 04:21 AM

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


Peter Wagner added a comment - 30/Oct/09 09:25 AM

@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


Peter Wagner added a comment - 30/Oct/09 09:27 AM

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


Justin Forder added a comment - 15/Nov/09 10:06 AM

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


Justin Forder added a comment - 15/Nov/09 11:19 AM

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