MySQL to Groovy Gotchas

I've been running some raw sql in groovy recently (against a mysql db), and while most of the time the marshalling from sql result-sets to groovy objects is super convenient, there have been a couple things that caught me by surprise.

TINYINT Display Width

I had originally assumed that the "display widths" for integer types (like the (4) in INT(4)) were merely ornamental. But it turns out that, at least for TINYINTs, somewhere along the sql-to-groovy marshalling chain the display width is used to determine whether a TINYINT value should be marshalled as a boolean (for TINYINT(1)) or as a byte (for TINYINT(2)).

That's actually pretty clever, but not what I expected — maybe for BIT(1), where the values could only be 0 or 1— but not for TINYINT(1), where you'd expect the values might at least range from -9 to 9 (and still can actually be -128 to 127).

GROUP_CONCAT

I would have expected the result of this to be marshaled as a String (at least for TEXT fields) — but this seems to be instead marshalled as some sort of primitive array. When I dumped out its class name, it was [B. I think that might be like a primitive byte array; however, using groovy's join() method didn't work on it, so there must be something a little more complicated going on there. I didn't bother to check it out further — I just used the following to convert it to a string:

def sql = new Sql(dataSource) sql.eachRow('SELECT GROUP_CONCAT(my_field) AS concat FROM my_table GROUP BY other_field') { row -> println (row.concat as Character[]).join('') }