[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2967) Cast to date in Formula still doesn't work

[Frederic Leitenberger (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Frederic Leitenberger updated HHH-2967:
---------------------------------------

    Attachment: testcase.jar

Here is the testcase.
I added my .classpath-file (from eclipse) including a list of all used jar-files.

You need to create the schema like this:

create user testuser identified by test default tablespace users;
alter user testuser quota unlimited on users;
grant connect, resource to testuser;
create table testuser.TestEntity (id number(19,0) not null, created timestamp not null, version number(10,0), primary key (id));
create sequence testuser.hibernate_sequence;

Then simply start de.meinebasis.hibernate.test.OracleCastTest main().
I get this:

Hibernate: 
    select
        this_.id as id0_0_,
        this_.created as created0_0_,
        this_.version as version0_0_,
        trunc(cast(this_.created as this_.date)) as formula0_0_ 
    from
        testuser.TestEntity this_

11.12.2007 10:57:33 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNUNG: SQL Error: 905, SQLState: 42000
11.12.2007 10:57:33 org.hibernate.util.JDBCExceptionReporter logExceptions
SCHWERWIEGEND: ORA-00905: missing keyword

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:2223)
	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 de.meinebasis.hibernate.test.OracleCastTest.main(OracleCastTest.java:58)
Caused by: java.sql.SQLException: ORA-00905: missing keyword

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
	at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
	at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
	at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
	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:2220)
	... 6 more


The error is here:
        trunc(cast(this_.created as this_.date)) as formula0_0_ 
It should be:
        trunc(cast(this_.created as date)) as formula0_0_ 

> Cast to date in Formula still doesn't work
> ------------------------------------------
>
>                 Key: HHH-2967
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-criteria, query-hql, query-sql
>    Affects Versions: 3.2.5
>         Environment: Hibernate-Version: 3.2.5.ga
> Oracle 9i/10g
>            Reporter: Frederic Leitenberger
>            Priority: Minor
>         Attachments: testcase.jar
>
>
> Similar to HHH-473.
> Oracle 10g supports trunc(TS). (TS is a timestamp)
> Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
> Therefore i need to add the cast to the Forumla.
>     @Basic
>     @Column(nullable = false, updatable = false)
>     @Temporal(TemporalType.TIMESTAMP)
>     public Date getCreated() {
>         return created;
>     }
>     public void setCreated(Date created) {
>         this.created = created;
>     }
>     @Formula(value = "trunc(cast(created as date))")
>     public Date getDate() {
>         return date
>     }
>     public void setDate(Date date {
>         this.date= date
>     }
> This results in the following query:
> select
>         eventlog0_.id as id9_0_,
>         eventlog0_.created as created9_0_,
>         .........
>         trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
>     from
>         ICCS6.EventLog eventlog0_
>     where
>         eventlog0_.id=?
> The alias in front of "date" is obviously misplaced there.
> I also tried renaming the formula (since it was called "date"), but this didn't change the result.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira