[Hibernate-issues] [Hibernate-JIRA] Closed: (HB-165) Where clause not recognising booleans.

[<leg...@at...>]

Message:

   The following issue has been closed.

   Resolver: Gavin King
       Date: Tue, 8 Jul 2003 6:47 PM

On some databases you need to set hibernate.query.substitutions, as per the example hibernate.properties.

Perhaps this should be a dialect default property. Resubmit a feature request if you think so...
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-165


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-165
    Summary: Where clause not recognising booleans.
       Type: Bug

     Status: Closed
   Priority: Major
 Resolution: REJECTED

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.0.1

   Assignee: 
   Reporter: Christian Bauer

    Created: Tue, 8 Jul 2003 3:47 PM
    Updated: Tue, 8 Jul 2003 6:47 PM
Environment: jdk1.4.1 on win32(XP) with a MySQL (v3.23.55) back-end.

Description:
SQLException (Column not found) thrown when using 'where blah = false' in a query. Work
around is to change where clause to 'where blah = 0'.
 
 
 Query (broken version):
 
 
 "select booking from " +
   "from au.com.octarine.bookingengine.model.Booking as booking " +
 "where " +
   "booking.sheet.courseId = ? and " +
   "booking.sheet.year = ? and " +
   "booking.sheet.month = ? and " +
   "booking.sheet.day = ? and " +
   "booking.booked = false and " +
   "booking.time between ? and ? "
 
 
 Query (working version):
 
 
 "select booking from " +
   "from au.com.octarine.bookingengine.model.Booking as booking " +
 "where " +
   "booking.sheet.courseId = ? and " +
   "booking.sheet.year = ? and " +
   "booking.sheet.month = ? and " +
   "booking.sheet.day = ? and " +
   "booking.booked = 0 and " +
   "booking.time between ? and ? "
 
 
 Booking.java:
 
 
 /**
  * Booking.java
  *
  * Created on Jan 30, 2003, 4:15:10 PM
  */
 
 package au.com.octarine.bookingengine.model;
 
 import java.sql.Time;
 
 /**
  * The <code>Booking</code> instance captures the booking information.
  *
  * @author Andrew
  * @version $Revision: $
  */
 
 public class Booking {
 
     // Booking unique id
     private int id;
 
     // Owning sheet.
     private Sheet sheet;
 
     // Tee
     private int teeNumber;
 
     // Time of booking.
     private Time time;
 
     // Booked? (yes/no)
     private boolean booked;
 
     // Temporary booking? (yes/no)
     private boolean temporary;
 
     // Booking userid
     private String bookedBy;
 
     // When the booking was made.
     private Time bookedWhen;
 
     // Playing userid
     private String playerId;
 
     // Player name (if not person who booked).
     private String publicName;
 
     /** Default constructor. */
     public Booking() {
     }
 
     public Booking( Sheet sheet, int teeNumber, Time time ) {
         this.sheet = sheet;
         this.teeNumber = teeNumber;
         this.time = time;
     }
 
     public int getId() {
         return id;
     }
 
     public void setId( int id ) {
         this.id = id;
     }
 
     public Sheet getSheet() {
         return sheet;
     }
 
     public void setSheet( Sheet sheet ) {
         this.sheet = sheet;
     }
 
     public int getTeeNumber() {
         return teeNumber;
     }
 
     public void setTeeNumber( int teeNumber ) {
         this.teeNumber = teeNumber;
     }
 
     public Time getTime() {
         return time;
     }
 
     public void setTime( Time time ) {
         this.time = time;
     }
 
     public boolean isBooked() {
         return booked;
     }
 
     public void setBooked( boolean booked ) {
         this.booked = booked;
     }
 
     public boolean isTemporary() {
         return temporary;
     }
 
     public void setTemporary( boolean temporary ) {
         this.temporary = temporary;
     }
 
     public String getBookedBy() {
         return bookedBy;
     }
 
     public void setBookedBy( String bookedBy ) {
         this.bookedBy = bookedBy;
     }
 
     public Time getBookedWhen() {
         return bookedWhen;
     }
 
     public void setBookedWhen( Time bookedWhen ) {
         this.bookedWhen = bookedWhen;
     }
 
     public String getPlayerId() {
         return playerId;
     }
 
     public void setPlayerId( String playerId ) {
         this.playerId = playerId;
     }
 
     public String getPublicName() {
         return publicName;
     }
 
     public void setPublicName( String publicName ) {
         this.publicName = publicName;
     }
 
     /** Generate human-readable representation. */
     public String toString() {
         StringBuffer buf = new StringBuffer( super.toString() );
         buf.append( " Booking { " );
         // todo: insert contents here
         buf.append( " }" );
         return buf.toString();
     }
 }
 
 
 
 Booking.hbm.xml:
 
 
 
 <?xml version="1.0"?>
 <!DOCTYPE hibernate-mapping PUBLIC
         "-//Hibernate/Hibernate Mapping DTD//EN"
         "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
 
 <hibernate-mapping>
     <class name="au.com.octarine.bookingengine.model.Booking" table="bookings">
         <id name="id" column="id" type="int" >
             <generator class="identity" />
         </id>
         <many-to-one name="sheet" column="sheet_id"
class="au.com.octarine.bookingengine.model.Sheet" />
         <property name="teeNumber" column="tee_number" type="integer"/>
         <property name="time" column="time" type="time"/>
         <property name="booked" column="booked" type="boolean"/>
         <property name="temporary" column="temporary" type="boolean"/>
         <property name="bookedBy" column="booked_by" type="string" length="20" />
         <property name="bookedWhen" column="booked_when" type="time"/>
         <property name="playerId" column="player_id" type="string" length="20" />
         <property name="publicName" column="name" type="string" length="50" />
     </class>
 </hibernate-mapping>
 
 
 Generated SQL:
 
 
 Hibernate: select booking0_.id as id, booking0_.sheet_id as sheet_id, booking0_.tee_number
as tee_number, booking0_.time as time, booking0_.booked as booked, booking0_.temporary as
temporary, booking0_.booked_by as booked_by, booking0_.booked_when as booked_w8_,
booking0_.player_id as player_id, booking0_.name as name from bookings booking0_, sheets
sheet1_ where (sheet1_.course_id=?  and booking0_.sheet_id=sheet1_.id)and(sheet1_.year=? 
and booking0_.sheet_id=sheet1_.id)and(sheet1_.month=?  and
booking0_.sheet_id=sheet1_.id)and(sheet1_.day=?  and
booking0_.sheet_id=sheet1_.id)and(booking0_.booked=false )and(booking0_.time between ? and ?
) order by  booking0_.tee_number , booking0_.time
 
 
 Stack trace:
 
 
 Caused by: java.sql.SQLException: Column not found,  message from server: "Unknown column
'false' in 'where clause'"
 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 	at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
 	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
 	at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:62)
 	at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:537)
 	at net.sf.hibernate.loader.Loader.doFind(Loader.java:140)
 	at net.sf.hibernate.loader.Loader.find(Loader.java:604)
 	at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:912)
 	at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1356)
 	... 44 more


---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira