CONCAT Gotcha between MySQL and HSQLDB

Hibernate abstracts most of the data access layer so you can switch out database platforms without changing any code (unless you count configuration as code). This allows me to use HSQLDB for my tests and MySQL for production. I love HSQLDB for integration tests. Running embedded in memory it’s super fast and you don’t have to configure a server or worry about the existing state of the database when you start your test suite (it’s empty!).

There are, of course, perils to testing with it when your production database is something else (and it almost certainly is). I have a Person class with a formula name field:

It took me a while to arrive at that formula, though. The first one I tried was concat(first_name, ' ', last_name) but HSQLDB complained that it didn’t know the concat formula. I knew it did, so I found that strange, but tried hunting around for alternatives. I then tried first_name || ' ' || last_name. That worked and all of my integration tests were passing so I thought everything was great. I deployed to MySQL and didn’t get any errors, so everything’s working like a charm, right?

Nope. But you knew that – why would I bother to write about that? Anyway, MySQL doesn’t complain about syntax because it is treating the || as a logical ‘OR’ and is somehow able to come up with 'Jason' OR ' ' OR 'Erickson' = 0. Why 0? Well, if you have a string where MySQL expects a number, it will try to convert the string to a number. A string that cannot be parsed to a number is not an error. It’s 0. So 0 || 0 || 0 = 0.

Anyway, now I don’t have any errors, but everybody’s name now evaluates to 0. Except that it doesn’t. See, that getter for name? That was there because I wanted it to work whether the Person had already been persisted or not. That means that it looked like it was working in all of the code that accessed the bean except… find by name. The query, “FROM Party WHERE name = ?” was never finding my people by name. This was quite a puzzle for me.

Well, anyway, I finally figured out what the problem was, and a little “aha” flash told me that the HSQLDB version of concat did not support an arbitrary number of parameters, so concat(first_name, ' ', last_name) wouldn’t work, but concat(concat(first_name, ' '), last_name) would. Then I added an ltrim to take care of the case with no first name and I was set.

Unfortunately, the HSQLDialect for Hibernate 3.5.3-Final did not play nicely with HSQLDB 2.0. This is fixed in 3.5.4, but I think I remember trying to update Hibernate to the latest version but ran into some other version conflict and gave up as I had this working version.

I had a similar problem, I have a @Formula with 2 integer parameters. I don’t know why (probably because of a bug) HSQLDB 2.x doesn’t accept two integer as arguments to concat. So after a few hours of wasted time, I could workaround the problem:
@Formula(“concat( ”, myint1, myint2 )”)

I hope it helps!

Trackbacks

[…] Second, many databases are not easy to deploy. For instance, the Oracle Database 11g Quick Installation Guide is for Linux x86 is 26 pages long. There are, of course, a number of embedded and very portable databases but these are often used mainly for testing with another, more heavyweight database being deployed for p…. […]