Little problem using formulas with discriminators over foreign keys (maybe isolated to MySQL but the generated SQL look pretty faulty for any db manager). Have seen a couple times on the forum people ask about using subselects on ID foreign keys and using a returned non-keyed string column as the discriminator-value but not gotten a definate answer if I can use the formula listed below to filter out "versions":

Hibernate version: 3.1 beta 3

Mapping documents:
<class name="Resurs" table="RESURS">

<id name="id" column="RESURSID">
<generator class="identity">
</generator>
</id>

<discriminator formula="(SELECT t1.RESURSTYP FROM RESURSTYP t1 WHERE t1.RESURSTYPID = RESURSTYPID)"/>

<many-to-one name="typ" column="RESURSTYPID" class="Resurstyp" lazy="false" foreign-key="FK_RESURSTYP" not-null="true"/>

....

<subclass name="Version" discriminator-value="version"/>

</class>

....

<class name="Resurstyp" table="RESURSTYP">

<id name="id" column="RESURTYPID">
<generator class="identity">
</generator>
</id>

<property name="typ">
<column name="RESURSTYP" length="50" not-null="true"/>
</property>

<property name="beteckning">
<column name="BETECKNING" length="50" not-null="true"/>
</property>

</class>


Full stack trace of any exception that occurs:
[java] Hibernate: select version0_.RESURSID as RESURSID0_, version0_.RESURSTYPID as RESURSTY2_0_, version0_.BETECKNING as BETECKNING0_, version0_.FRANDATUM as FRANDATUM0_, version0_.TOMDATUM as TOMDATUM0_, version0_.TSTAMP as TSTAMP0_ from RESURS version0_ where (SELECT t1.RESURSTYP FROM RESURSTYP t1 WHERE t1.RESURSTYPID = version0_.RESURSTYPID)='version'
[java] 09:22:30,651 WARN JDBCExceptionReporter:71 - SQL Error: 1054, SQLState: 42S22
[java] 09:22:30,654 ERROR JDBCExceptionReporter:72 - Unknown column 't1.RESURSTYPID' in 'where clause'
[java] Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query


Name and version of the database you are using: MySQL, version: 5.0.13-rc-standard


Main problem is that the resulting SQL (from the formula) is concatenated as a where clause rather than as a join between RESURS/RESURSTYP tables. Any ideas? Otherwise I have to reference RESURSTYP in my RESURS table via the RESURSTYP column in the RESURSTYP table (i.e. property-ref) instead of going against the primary key.

Maybe a work-around would be to extend the Resurs class and map the extended subclass (in this class Version) to the same table (resurs table) adding a where attribute/clause which constraints the resurstypid column to certain records in the RESURSTYP table. For example,

<class name="Version" table="RESURS" where="RESURSTYPID IN (SELECT RESURSTYPID FROM RESURSTYP WHERE RESURSTYP = 'version')">

....

</class>


Unless somebody has a solution to my original question or suggests on the above (which I haven't tested).


Thanks / Matthew

Tried just adding an additional class mapping to the RESURS table and having the corresponding POJO java extend the Resurs class but the HibernateToolTask does seem to like that construction. And even the latter solution would work with HSQL I don't dig not being able to generate POJO.

So back to cleaning up the formula. Any ideas? Somebody has done this before.

The original problem was just due to a spelling error. The class definition of Resurstyp had the ID column set to RESURTYPID instead of RESURSTYPID. And the SQL generated works fine with MySQL.

/ Matthew