When I try to compare boolean field in a query with a boolean constant, Hibernate first substitutes 1 for true and 0 for false and the whole query goes incorrect, as "Data types must be compatible".

As a fix, I explicitly specified the substitution rule in the hibernate configuration file, it looks like (now commented)

Imho it's an ugly trick. Isn't there another way to forbid the substitution?

I've pasted an excerpt from server log and from config file. Note what happens to "where info.active=true".


Hibernate version:
3.1

Name and version of the database:
MaxDB 7.5.00

Mapping documents:
<server>
<mbean code="org.hibernate.jmx.HibernateService" name="jboss.jca:service=ipub/PortalKernelService">
<depends>jboss.jca:service=RARDeployer</depends>
<attribute name="CacheProviderClass">org.hibernate.cache.EhCacheProvider</attribute>
<attribute name="JndiName">java:/ipub/hibernate/KernelHibernateFactory</attribute>
<attribute name="Datasource">java:/jdbc/ipub</attribute>
<attribute name="Dialect">org.hibernate.dialect.SAPDBDialect</attribute>
<attribute name="ShowSqlEnabled">false</attribute>
<attribute name="UserTransactionName">UserTransaction</attribute>
<attribute name="TransactionStrategy">org.hibernate.transaction.JTATransactionFactory</attribute>
<attribute name="FlushBeforeCompletionEnabled">true</attribute>
<attribute name="AutoCloseSessionEnabled">true</attribute>
<attribute name="TransactionManagerLookupStrategy">org.hibernate.transaction.JBossTransactionManagerLookup</attribute>
<!--
I had to add this in order to fix the strange Hibernate behaviour: for some reason, it does
substitute 'true' with 1 and 'false' with 0, which is incorrect
-->
<!--<attribute name="QuerySubstitutions">true true, false false</attribute>-->
<attribute name="MapResources">
........
</attribute>
</mbean>
</server>


Debug level Hibernate log excerpt:

2006-03-29 19:07:33,642 DEBUG [org.hibernate.engine.query.QueryPlanCache] located HQL query plan in cache (select count(info) from com.smartphonelabs.portal.values.UserInfo as info where info.active=true and info.uid != 'UIDREQUEST')
2006-03-29 19:07:33,642 DEBUG [org.hibernate.engine.query.HQLQueryPlan] find: select count(info) from com.smartphonelabs.portal.values.UserInfo as info where info.active=true and info.uid != 'UIDREQUEST'
2006-03-29 19:07:33,642 DEBUG [org.hibernate.engine.QueryParameters] named parameters: {}
2006-03-29 19:07:33,642 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 46)
2006-03-29 19:07:33,642 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
2006-03-29 19:07:33,642 DEBUG [org.hibernate.SQL] select count(userinfo0_.p_id)
as col_0_0_ from user_info userinfo0_ where userinfo0_.active=1 and userinfo0_.user_id<>'UIDREQUEST'
2006-03-29 19:07:33,642 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
2006-03-29 19:07:33,643 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select count(userinfo0_.p_id) as col_0_0_ from user_info userinfo0_ where userinfo0_.active=1 and userinfo0_.user_id<>'UIDREQUEST']
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-8006] (at 93): Data types must be compatible
19:07:33,646 INFO [STDOUT] org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
.........

What type is the active column? If it's bit, then why is 1/0 not correct? And if it's varchar and you're storing those strings true and false, then you can't use boolean/truefalse as the hibernate type.

If your DB has an actual truefalse datatype (or similar), then you'll have to implement it as a UserType. I don't see a MaxDB dialect in hibernate, what dialect are you using?

Thanx for a reply, tenwit.


It is a boolean column, a quote from the .hbm.xml descriptor:


The dialect is org.hibernate.dialect.SAPDBDialect, see my prev post. Is implementing a custom UserType the only solution? I think there must be an easer way out. After all, it's just a standard boolean.

I'm looking for the way to tell hibernate not to replace boolean constants in the query string. Maybe there is a mistake in the dialect?

Actually, I meant what type is the column in the DB? boolean isn't an SQL type. It might be bit, int, smallint, tinyint, char or varchar.

Standard booleans are not written as true and false, so it's not a "standard" boolean. It's definitely a non-standard boolean. If your DB handles "true" and "false" as booleans, and that's what you want to write, then yes I think you're going to have to write a UserType. It's a very special case that hibernate doesn't yet handle. If you can get it working, you might post your new UserType to JIRA with the proposal that it becomes part of the distribution, to handle this MaxDB flavour.

It is of sql type "boolean".

MaxDB datatype manual says it really exists and handles TRUE and FALSE. So does Postgresql.

Even more, the BOOLEAN type is present in the standart as optional (has feature ID T031). I suppose the described hibernate behaviour is either a bug or (much more likely) a misconfiguration issue.

I believe there already exists a solution. Maybe someone has an idea where to look for the UserType?

Actually boolean isn't a standard SQL type, but that's the way of DBMS implementors: they do it their own way, not according to the standard. I've googled around and found that a few DBMSs use it, so I guess that someone must have tacked on support for this non-standard data type, especially seeing as JDBC supports it natively (see java.sql.Types.BOOLEAN).

If you can't find a working version, you could try this. I haven't even compiled it, so there's probably bugs and typos, but it should be a good start:That's based off code at http://www.hibernate.org/189.html, written by Matt Dowell.