This is the second time I've run into situations where one query works, then I add another and clause to the where, and suddenly, one of the column names gets munged?? Truly bizarre.


Hibernate version:
2.1.6

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping package="com.onassignment.search">
<class name="JobListing" table="joblistings">
<id
column="jobListingKey"
name="JobListingKey"
type="integer"
>
<generator class="vm" />
</id>
<property
column="listed"
length="10"
name="Listed"
not-null="true"
type="date"
/>
<property
column="startDate"
length="10"
name="StartDate"
not-null="false"
type="date"
/>
<property
column="jobtypekey"
length="10"
name="Jobtypekey"
not-null="true"
type="string"
/>
<property
column="jobtitle"
length="32"
name="Jobtitle"
not-null="true"
type="string"
/>
<property
column="hotjobcode"
length="11"
name="Hotjobcode"
not-null="false"
type="integer"
/>
<property
column="openings"
length="11"
name="Openings"
not-null="true"
type="integer"
/>
<property
column="shift"
length="11"
name="Shift"
not-null="false"
type="integer"
/>
<property
column="employer"
length="40"
name="Employer"
not-null="true"
type="string"
/>
<property
column="bonustype"
length="11"
name="Bonustype"
not-null="false"
type="integer"
/>
<property
column="divisionkey"
length="11"
name="Divisionkey"
not-null="true"
type="integer"
/>
<property
column="description"
name="Description"
not-null="false"
type="string"
/>
<property
column="state"
length="2"
name="State"
not-null="true"
type="string"
/>
<property
column="city"
length="40"
name="City"
not-null="true"
type="string"
/>
<property
column="duration"
length="11"
name="Duration"
not-null="false"
type="integer"
/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
public List getLatestResults() throws HibernateException{
Session session = HibernateUtil.currentSession();
return session.createQuery(getQuery() + " AND listed >= :lastEval")
.setParameter("lastEval", getLastEvaluated(), Hibernate.DATE)
.list();

}

Full stack trace of any exception that occurs:
01:30:12,660 ? DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
01:30:12,662 ? DEBUG SQL:226 - select joblisting0_.jobListingKey as jobListi1_, joblisting0_.listed as listed, joblisting0_.startDate as startDate, joblisting0_.jobtypekey as jobtypekey, joblisting0_.jobtitle as jobtitle, joblisting0_.hotjobcode as hotjobcode, joblisting0_.openings as openings, joblisting0_.shift as shift, joblisting0_.employer as employer, joblisting0_.bonustype as bonustype, joblisting0_.divisionkey as divisio11_, joblisting0_.description as descrip12_, joblisting0_.state as state, joblisting0_.city as city, joblisting0_.duration as duration from joblistings joblisting0_ where (state in('CA' , 'FL'))AND(jobtypekey in('PSYCH' , 'OR'))AND(listed>=? )
Hibernate: select joblisting0_.jobListingKey as jobListi1_, joblisting0_.listed as listed, joblisting0_.startDate as startDate, joblisting0_.jobtypekey as jobtypekey, joblisting0_.jobtitle as jobtitle, joblisting0_.hotjobcode as hotjobcode, joblisting0_.openings as openings, joblisting0_.shift as shift, joblisting0_.employer as employer, joblisting0_.bonustype as bonustype, joblisting0_.divisionkey as divisio11_, joblisting0_.description as descrip12_, joblisting0_.state as state, joblisting0_.city as city, joblisting0_.duration as duration from joblistings joblisting0_ where (state in('CA' , 'FL'))AND(jobtypekey in('PSYCH' , 'OR'))AND(listed>=? )
01:30:12,664 ? DEBUG BatcherImpl:249 - preparing statement
01:30:12,715 ? DEBUG DateType:46 - binding '22 September 2004' to parameter: 1
01:30:12,758 ? DEBUG Loader:277 - processing result set
01:30:12,797 ? DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: Column 'jobListi1_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2315)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1275)
at net.sf.hibernate.type.IntegerType.get(IntegerType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)

Name and version of the database you are using:

MySQL 4.1

The generated SQL (show_sql=true):

See above.

Debug level Hibernate log excerpt:

See above.

Turns out that if I don't try and do the parameter replace in the query (I just build the string), it works. I tried many different ways of doing the param replace. Hopefully 3.0 is going to take care of some of this freaky, specious behavior.

Clearly, Hibernate is doing the right thing here. The SQL looks like:



And then, Hibernate tries to find a column named "jobListi1_" in the ResultSet:



Which of course should be there.

So you should obviously not be blaming Hibernate for this.

Yeah, you are probably right: I spose the error is in the JDBC driver. I'm using the Connector/J driver from MySQL. I would have thought that more people would have seen problems though with basic prepared statements like this.