Hibernate version: 3.2.5.ga
Name and version of the database you are using: Postgres 8.3


works fine when run against hsqldb but fails against postgres. I've turned on hibernate logging and the generated sql ends with "order by rand()" in both cases, however postgres doesn't have a rand() function - it uses random() instead.

The PostgresSQLDialect class contains the line



I'm not 100% sure how the dialects work, but suspect this should be



Certainly when I extend the dialect as follows my hql statement works in both hsqldb and postgres environments.



Is this a bug or am I doing something daft?

I would not say it is a bug. I think Hibernate developers has deliberately chosen to register custom SQL functions with the same name as are used in the underlying database. If you look around you will find plenty of examples. Here is one more:

In MySQL the log() function returns the natural logarithm of the argument, but in PostgreSQL it returns the 10-based logarithm. ln() returns the natural logarithm in both database. To get 10-based logarithm with MySQL you need to use log10().

In other words, if you want a database-independent application when using non-standardized functionality you need to implement some kind of dialect-like functionality. In our project we have a simple interface for everything we need. For example:

I hear what you're saying and agree that a different approach (such as the one you proposed) is required if you have a complex SQL statement which is considerably different because db specific functionality, but in the case where calling rand() in an HQL statement will work in almost all dialects, but not postgres because the authors happend to name the function "random", surely it would make sense to map them to the same HQL function name?

Even gavin king suggests using the rand() function in HQL

http://www.mail-archive.com/hibernate-d ... 02820.html

Also you'll find other examples like the following where the underlying db's ltrim and rtrim functions have been combined and assigned to an HQL "trim" function. The only reason I can see for this is to ensure that the HQL trim function is available and consistent in as many dialects as possible.

I was just trying to suggest an alternate solution. I don't consider this a bug. If you want to support different databases you will run into problems with differences that Hibernate doesn't solve for you.

Another solution is to register the function in your Configuration object before you call Configuration.buildSessionFactory().