Hibernate creates an undefined table alias(?) for the subclass table. I am unable to get native sql to work for my subclass mapping.
Activity is an abstract class with one abstract method. Basically no subclasses add properties. I just want the correct subclass returned.

Mapping worked in createQuery().

I don't want to use createQuery because that blows up trying to parse other complicated legacy select statements.

Hibernate version:
3.0


Mapping documents:
the most simplest case from my original mapping
<?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 package="activityjammer.src.model.activity">

<class name="Activity"
table="production.activity"
discriminator-value="N">

<id name="id" column="id" unsaved-value="0">
<generator class="native"/>
</id>
<discriminator column="discriminator" type="string"/>

<property name="type" not-null="true" column="type"/>
<property name="cost" not-null="false" column="cost"/>

<subclass name="activityjammer.src.model.activity.CommuteActivity" discriminator-value="COMMUTE">
<join table="production.activitycommute">
<key column="id"/>

<many-to-one name="destination"
class="activityjammer.src.model.Location"
column="id_destination"
not-null="true"/>
</join>
</subclass>

<subclass name="activityjammer.src.model.activity.GeneralActivity" discriminator-value="GENERAL">
</subclass>

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
The createQuery worked with mapping.

Session session = HibernateSession.currentSession();
//Query q = session.createQuery( "from Activity where id = :id" );
//q.setInteger( "id", key );
//String sql = "select {a.*} from production.activity as {a} where id = " + key;
String sql = "select {a.*} from production.activity as {a} where id = " + key;
SQLQuery query = session.createSQLQuery( sql );
query.addEntity( "a", Activity.class );
query.addEntity( "l", Location.class );
List list = query.list();

Full stack trace of any exception that occurs:
Caused by: java.sql.SQLException: General error, message from server: "Unknown table 'a_1_' in field list"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1876)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1098)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1192)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2051)


Name and version of the database you are using:
Mysql


The generated SQL (show_sql=true):
Hibernate: /* dynamic native SQL query */ select a.id as id0_, a.type as type6_0_, a.cost as cost6_0_, a_1_.id_destination as id2_7_0_, a.discriminator as discrimi2_0_ from production.activity as a where id = 2885
common.src.dao.DataAccessException: org.hibernate.exception.GenericJDBCException: could not execute query

Debug level Hibernate log excerpt:

one possible problem is that entity 'l' get's added but isn't used.
Remove the line -
query.addEntity( "l", Location.class );

if that's not it, perhaps you can post your mappings. Hard to tell without that.