MySQL COALESCE(), UNION behavior on zerofilled columns

I’ve just filed a (potential) bug report, depending on your views for MySQL’s COALESCE() behavior:

http://bugs.mysql.com/bug.php?id=65763

If you have a zerofill column and perform the COALESCE() function on it, the leading zeros are truncated.

As I mention in the bug report, this may not matter to most – but it does change the output one would expect and is worthy of notice or a mention in the official documentation in my book.

Test scenario 1:

INSERT INTO coalesceTest (paddedNumber) VALUES (5), (10), (200), (3000);

SELECT COALESCE(paddedNumber), paddedNumber from coalesceTest;

Results:

Test scenario 2:

Results:

 

Leave a Reply

Your email address will not be published. Required fields are marked *