I found something interesting at work yesterday. One of our developers mentioned that when he called a certain method with various sets of parameters, he wasn’t getting back what he expected to get back, based on what he knew was in the database. I put on my sleuth hat and began my investigation.

We use Hibernate for our database layer, and we prefer to use the @NamedQuery annotation to store our queries with the entities they represent. This works out very well for us. But back to the problem. I quickly got to the appropriate .java file and inspected the query. (Obviously I’ve changed the class names, but this is essentially what I found in the file.)

select distinct f
from Foo f
left join fetch f.bar
left join fetch f.baz
lef join fetch f.plonk
where f.id = :fooId

Now, do you notice the typo? Check out line five. It says “lef” instead of “left.” When I first saw this, I thought to myself, “How can this even get parsed by Hibernate into SQL, let alone return any results.” We all work in a large room together, so I mused out loud about this problem. One of our other Really Smart Guys™ came over to have a look. He saw the typo, thought for a second and said, “‘lef’ is being treated as an alias for f.baz on the previous line.” And sure enough, he was right. Just as on the second line where you see “from Foo f,” that ‘f’ is an alias for the entity called Foo. We could have put aliases on each “left join” line, at each line’s end, had we wanted or needed to. By misspelling “left” on line five as “lef,” we unintentionally slapped an alias on the join that is on line four. Even though the query is split across multiple lines here, the HQL parser would see it as one continuous string, and after passing by “fetch f.baz” the next token it would see would be “lef,” which it would interpret as an alias for “f.baz.”

So, as far as parsing the query and translating it into SQL goes, everything is just fine. But there is still a problem caused by the misspelling. Since the parser decided that “lef” was actually an alias, the next bit that it sees is “join fetch f.plonk” which results in a regular inner join, instead of the outer join we really wanted. What this means is that for records in the Foo table who can’t be joined to records in the Plonk table, either because the key is null, or there just isn’t a record in the Plonk table that matches, those records will be excluded from the result set. That’s the behavior our developer was seeing. Changing “lef” to “left” made the whole thing work and the developer got the results he needed.