Archive for October, 2010

Casual mysql fail – careful with that group_concat

4 October 2010 1 comment

I was doing a minor maintenance job on a non-normalized table which required large grouping and gathering of id’s, which in turn were to be processed by other routine. Each group would returned a comma-separated list using mysql’s group_concat. Yet, some groups failed to do so, resulting with strings that ended with a comma (this was a bug that was very hard to trace, actually).

As it turns out, the group_concat resulting string has a limit! Now, let’s say for a moment that this was acceptable RDBMS design (it is not!) – the server does not throw exception on exceeding this limit, nor does it give any warning – which was the reason for some tough debugging. It simply truncated the result in a shorter list!

That is plain wrong!

In no single imaginable scenario an aggregate function is allowed to return false result. I really don’t care if you swap-up my server or wait an hour – the most important thing is that my query gets the correct data. No optimization is ever more valuable than reliability and getting the right data.

Categories: programming Tags: , ,