On the Hibernate website (and elsewhere), one of the touted advantages of Hibernate over roll-your-own SQL is:
Hibernate Core for Java generates SQL for you, relieves you from manual JDBC result set handling and object conversion, and keeps your application portable to all SQL databases.
Well, not exactly. Many functions in HQL (Hibernate Query Language) are simply passed through verbatim to the underlying database engine, without any modification.
This is normally fine, but on my first ever HQL query I tried to use a natural-logarithms function — which is log() in HSQLDB (my testing server) and ln() in PostgreSQL (my production server). Which means that queries written to target the production environment fail with a charming NullPointerException in the test environment. Half a day’s debugging, right there.
More worryingly, imagine if I had innocently written the query using log(). All my tests would have passed. Then I would have deployed the application to the production environment, and it would still have worked, but all the queries would have happily returned the wrong answers — because log() in PostgreSQL means base-10 logarithms.
Hibernate not only fails to insulate you from dialect differences like these, it also introduces a false sense of safety by pretending that it does.
Andrew.
There are several other posts in this series. Please read the disclaimer before you write an angry reply.
{ 9 } Comments
> Hibernate not only fails to insulate you from dialect differences like these, it also introduces a false sense of safety by pretending that it does.
You are being very unfair here. HQL functions are generally being translated to their SQL counterparts, with respect to chosen dialect. Even when you limit query results, for example, Hibernate will issue a select top 100 on MySQL, and where rownum < 100 on Oracle.
Now, obviously ln() and log() are not covered by respective HSQLDB and PostgreSQL dialect(s). But, the entire mechanism is completely open and adapting your own variant of dialect from Hibernate sources, if needed, is quite straight-forward. The majority of functions for majority of vendors is supported. Personally, I worked one application deployed on MySQL, MSSQL and Oracle, with no problems whatsoever.
Admittedly, Hibernate developers could have decided to disallow references to functions not explicitly covered by chosen dialect. But, on the other hand, that enables Hibernate users to use functions from DBMS version newer then Hibernate, by default. If MS would, say, add a MAGIC_NUMBER function into the next SQL Server release, you would be able to use it from HQL immediately.
OK then, so what exactly *should* Hibernate do when you use a highly vendor-specific function that is not part of the SQL standard and is not listed in the HQL documentation as portable?
* throw an exception and prevent you from using it?
* log a useless and annoying warning?
* something else?
I’m not exactly clear on what your proposed solution is.
Gavin, thanks for the feedback. Well for a start, it would be nice if you could work around it with the hibernate.query.substitutions property, but this does absolutely nothing:
http://forum.hibernate.org/viewtopic.php?t=993080
Leaving me wondering what this property is for.
Stanasic — actually I’d rather have an exception saying “not supported in HQL, use native SQL instead”, rather than end up with a situation where the results of a query can silently go from correct to incorrect when moving to a different DB.
From my point of view, throwing an exception like “not supported in HQL” makes Hibernate unusable in scenario when you exactly want to use specific, non-standard, DBMS function. I’m not trying to downplay your problem, but it seems to me you’re expecting Hibernate to know about all non-standard SQL functions and their implementations in each particular DBMS, which, in my opinion, is unrealistic.
You might want to take a look into Dialect implementations in Hibernate source. In order for Hibernate to solve the ln/log and similar issues, they would firstly have to define their own standard in HQL, say, log() in HQL stands for base-10 logarithm, and ln() is natural logarithm. Then, all Dialect classes should be modified to reflect that – meaning that HSQL users would then have to call ln() instead of log() in their HQL queries, which would then make no sense to them, etc. I hope you’re seeing where I’m going with this example.
But, with the Dialect mechanism being completely open, you might want to try to roll your own, one that suits your precise needs. In particular, lines 133-134 in org.hibernate.dialect HSQLDialect could be changed to map log() and ln() into ln() and log10() respectively.
I am surprised about query substitution, though, since the documentation specifically states it can be used for renaming an SQL function. If you can make a minimal, standalone example that shows the bug, I suppose you can submit it to their JIRA.
Doesn’t HQL support the JDBC escaped functions?
I think this link might help: http://forums.hibernate.org/viewtopic.php?p=2231852&sid=28f5b868ecb5fdd6f59f2ee2743f48f4
For PostgreSQL JDBC supported: http://jdbc.postgresql.org/documentation/83/escaped-functions.html
Have NO idea about HSQLDB
Hope this helps in restoring some of Hibernate’s credibility :)
Though I believe that ORM itself is broken.
I guess I’m a bit confused as to what the problem is, other than you didn’t familiarize yourself with HQL and possibly substitutions not working how you expect them to (I don’t have any experience with them, so I don’t know if what you are describing is a bug or if they just don’t work that way).
The ability to use database-specific (and, yes, non-portable) functions is a very important feature and almost a requirement for many applications. You just have to make sure you know when you are doing something non-portable, which takes a small amount of reading upfront. The situation is the same when you start looking at the JPA side of things – sometimes you are using Hibernate-specific functions that are not going to work with other JPA implementations.
How would you resolve this in a better way? Not let people use database-specific functions?
I’m also not sure how it is taking you so long to debug things. Hibernate will print out the queries it is emitting and you can easily turn on logging, so even if the error message you get doesn’t always pinpoint the problem, it shouldn’t take that long to figure out what is going on.
As a general rule, when I am trying to debug query issues, I almost always start by grabbing the sql that Hibernate is sending to the database and running it in SQL Developer or whatever database tool you use. I’ve found that this normally gives you a pretty good idea of where the problem is.
Your piece just makes me wonder why you don’t have an integration testing enviroment where you have the exact same setup as production before you go live which you can run some tests against, but hey that could just be me :)
I don’t think that there is a hibernate issue here… For me hibernate is an object / relational mapping plus persistence framework.
HQL is actually a meta language similar to SQL just to smooth things up between your objects and the db.
Still in this case don’t forget that you are not using hibernate as an intermediate between your objects and the database. You are using it as a standard jdbc connection without all the “bells and whistles”.
Things that HQL does not understand are passed AS IS in the database query. And there is an example of that in the HQL part of the hibernate manual (with an MS SQL query (14.14 section))
To wrap up… if your database couldn’t handle sub-selects would you expect hibernate to handle them as well just because it does it on some other database?
Sorry m8… you are doing it wrong…
Panagiotis — the analogy with sub-selects is misleading since a sub-select will just fail if the underlying DB can’t do it. But the ln vs log vs log10 issue is a genuine *dialect* difference which I thought was the kind of thing Hibernate’s idea of dialects was designed for. Please re-read the quote from Hibernate’s web site at the top of the post.
Also I’m not sure how a NullPointerException from the depths of HQL’s query parser could be a sign of me “doing it wrong”, could you elaborate?
Likewise, Spencer, your debugging suggestions are only useful if the problem occurs *after* Hibernate has parsed the HQL and generated SQL. Not if it dies in an ugly manner before getting that far.
{ 2 } Trackbacks
[...] Actually, you only get this error if you’re lucky — if you’re unlucky you get something even less helpful. [...]
[...] Actually, you only get this error if you’re lucky — if you’re unlucky you get something even less helpful. [...]
Post a Comment