[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7302) HQL uses multiple column Types in a inconsitent manner between Where and Order By clauses

[Miek Dunn (JIRA)]

HQL uses multiple column Types in a inconsitent manner between Where and Order By clauses
-----------------------------------------------------------------------------------------

                 Key: HHH-7302
                 URL: https://hibernate.onjira.com/browse/HHH-7302
             Project: Hibernate ORM
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 4.1.0
         Environment: Java 1.7.0  Eclipse 3.7.2
            Reporter: Miek Dunn


When a type maps to multiple columns and that type is used in HQL the generated SQL will order the values in different manner in the Order By clause then it does in the Where clause.  The Order By clause assumes the fields are mapped in order of importance, while the where clause assumes not field is more important then any other.

For example assume a database stores a timestamp you using multiple fields (T2SCEN T2SYER T2SMTH T2SDAY T2STIM : century year month day time) and that there is a type that turns those fields into a Calendar.  Now that type is used in an entity class named WorkOrder with a field named scheduled.  The following HQL query:

{quote}
Select wo From WorkOrder wo WHERE wo.scheduled > :scheduled Order By wo.scheduled
{quote}

Will generate the following SQL

{quote}
    select
	...
    from
        SVDSPT workorder0_ 
    where
        workorder0_.T2SCEN>? 
        and workorder0_.T2SYER>? 
        and workorder0_.T2SMTH>? 
        and workorder0_.T2SDAY>? 
        and workorder0_.T2STIM>? 
    order by
        workorder0_.T2SCEN,
        workorder0_.T2SYER,
        workorder0_.T2SMTH,
        workorder0_.T2SDAY,
        workorder0_.T2STIM
{quote}

The Order By clause will order the values as one would expect for date, that is 2012-05-03T12:12:12 comes after 0001-12-01T00:00:00.
The Where clause on the other hand implies that 2012-05-03T12:12:12  does not come after 0001-12-01T00:00:00, since the month 5>12 will be false.



--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira