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:

@Entity
public class Person extends Party {
  private String lastName = "";
  private String firstName = "";
  @Formula("ltrim(concat(concat(first_name, ' '), last_name))")
  private String name;

  @Override
  public String getName() {
    this.name = (firstName + ' ' + lastName).trim();
    return name;
  }
...
}

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.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • Fred Toussi  On February 22, 2011 at 17:06

    HSQLDB 2.x supports CONCAT with variable number or arguments. Please try the latest snapshot or GA release.

    • Jason  On February 22, 2011 at 17:23

      Thanks for the heads up.

      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.

  • Pablo Alba  On March 29, 2012 at 00:56

    Thank you! You are a life saver! I was having the same problem 🙂

    • Jason  On March 29, 2012 at 08:02

      Glad to be of service.

  • Bruno Medeiros  On February 14, 2013 at 23:36

    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…. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: