I was glad to see the addition on union-subclass to hibernate and I am attempting to use it to map onto an established DB2 database. However, the SQL that hibernate generates for the UNION clause appears to use bad grammer when trying to return a NULL timestamp value.
What gets generated:
Code:
nullif(0,0) as propertyB,
what would work:
Code:
nullif('2002-11-14-12.12.23.00','2002-11-14-12.12.23.00') as propertyB,
I am thinking this is a bug. But it wouldn't be the first time I have misued/misunderstood the tools at hand. So I thought I'd pass it by the experts for your thoughts.
Bug ? user error ?
Hibernate version: 3.0.final
Mapping documents:Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class abstract="true" lazy="false" name="myAbstractSuperClass">
<id type="int" column="item_id" name="id">
<generator class="sequence"/>
</id>
<union-subclass name="mySubclassA" lazy="false" table="TST_A">
<property name="propertyA" not-null="true" type="int"/>
</union-subclass>
<union-subclass name="mySubclassB" lazy="false" table="TST_B">
<property name="propertyB" type="timestamp"/>
</union-subclass>
</class>
</hibernate-mapping>
Code:
Transaction tx = session.beginTransaction();
mySubclassA o= new mySubclassA();
session.save(o);
tx.commit();
List results = session.createQuery("from myAbstractSuperClass a where a.id != null").list();
Full stack trace of any exception that occurs:Code:
18:38:00,402 WARN JDBCExceptionReporter:57 - SQL Error: -415, SQLState: 42825
18:38:00,402 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -415, SQLSTATE: 42825, SQLERRMC: null
18:38:00,412 WARN JDBCExceptionReporter:57 - SQL Error: -727, SQLState: 56098
18:38:00,412 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-415;42825;
18:38:00,412 WARN JDBCExceptionReporter:57 - SQL Error: -727, SQLState: 56098
18:38:00,412 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-415;42825;
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:266)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:788)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at TestUnionSubclass.main(TestUnionSubclass.java:27)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -415, SQLSTATE: 42825, SQLERRMC: null
at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
at com.ibm.db2.jcc.a.hd.a(hd.java:1235)
at com.ibm.db2.jcc.c.jb.h(jb.java:149)
at com.ibm.db2.jcc.c.jb.a(jb.java:43)
at com.ibm.db2.jcc.c.w.a(w.java:30)
at com.ibm.db2.jcc.c.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.hd.n(hd.java:1215)
at com.ibm.db2.jcc.a.id.gb(id.java:1780)
at com.ibm.db2.jcc.a.id.d(id.java:2255)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1197)
at org.hibernate.loader.Loader.doQuery(Loader.java:366)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:206)
at org.hibernate.loader.Loader.doList(Loader.java:1515)
... 6 more
Name and version of the database you are using:DB2 Version 8 fixpack 10 on AIX
The generated SQL (show_sql=true):Code:
select
myabstract0_.item_id as item1_,
myabstract0_.propertyA as propertyA1_,
myabstract0_.propertyB as propertyB2_,
myabstract0_.clazz_ as clazz_
from
(
select
nullif(0,0) as propertyB,
propertyA,
item_id, 1 as clazz_
from TST_A
union
select
propertyB,
nullif(0,0) as propertyA,
item_id,
2 as clazz_
from TST_B
) myabstract0_
where
myabstract0_.item_id is not null
Debug level Hibernate log excerpt:
Login
FAQ
Search