The three arguments are not placed in the native sql query. I tried registering a function but had same results. I got around the DATE_ADD function by registration.

Hibernate version:
3.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():
public class MysqlCustomDialect extends MySQLMyISAMDialect
{

public MysqlCustomDialect()
{
super();
registerFunction("date_add_interval",
new SQLFunctionTemplate(Hibernate.DATE,"DATE_ADD(?1, INTERVAL ?2 ?3)"));
registerFunction("if_function",
new SQLFunctionTemplate(Hibernate.STRING,"IF(?1,?2,?3)"));
}
}

Full stack trace of any exception that occurs:

Name and version of the database you are using:
MySQL 4???

The generated SQL (show_sql=true):
Hibernate: /* from Activity a WHERE a.location = 20 AND ( (( date_add_interval( IF( 2>0, start_date, '2005-08-05' ),start_time, HOUR_SECOND ) >= '2005-08-05 10:00:00'))) */ select activity0_.id as id6_, activity0_.type as type6_, activity0_.cost as cost6_, activity0_.id_user as id5_6_, activity0_.id_location as id6_6_, activity0_1_.id_destination as id2_7_, activity0_.discriminator as discrimi2_6_ from production.activity activity0_ left outer join production.activitycommute activity0_1_ on activity0_.id=activity0_1_.id where activity0_.id_location=20 and DATE_ADD(IF(), INTERVAL start_time HOUR_SECOND)>='2005-08-05 10:00:00'
org.hibernate.exception.SQLGrammarException: could not execute query

Debug level Hibernate log excerpt:

Edit: Ack, my apologies, misread your post. Not actually the same issue, although the parameters are being placed in the code, using Hibernate 3.2 and the code mentioned below.


I have run into the same problem as mentioned, and attempted to work around it by creating a custom "if" function in my Dialect.

The problem with this approach is that SQLFunctions only allow their return type to be static or changed in reference to the first parameter, which is counter intuitive for an if (or ifnull) function, as it would mean specifying the then/else clauses prior to the condition.

Instead I found the only suitable approach was to create a batch of functions with differing return types, named "ifint", "ifstring" etc, but this is an ugly work around and pollutes my HQL with a bucket load of proprietary function names.

If you need an immediate solution, I'd advise creating a subtype of your Dialect (in my case MySQL), and registering a selection of if functions, as follows:

I couldn't make this work, so I used CASE WHEN ... THEN ... ELSE ... END (worked in my case)
see http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html#queryhql-expressions

There is also the solution to either use COALESCE(...) or (as some say) NULLIF(..)
see e.g. http://stackoverflow.com/questions/1657637/ifnull-equivalent-in-hibernate-query-language