MySQL Bugs: #65763: COALESCE(), UNION truncates padding on zero-filled values

Bug #65763	COALESCE(), UNION truncates padding on zero-filled values
Submitted:	28 Jun 2012 15:16	Modified:	12 Feb 2018 22:20
Reporter:	Justin Rovang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.64, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	COALESCE, UNION, ZEROFILL

Description:
Tested on:
MySQL 5.1, 5.5 on CentOS, Debian.

This may, or may not matter to some, but it does manipulate the output so worth a mention.

How to repeat:
CREATE TABLE IF NOT EXISTS `coalesceTest` (
	paddedNumber INT(4) UNSIGNED ZEROFILL NOT NULL 
);

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

SELECT COALESCE(paddedNumber), paddedNumber from `coalesceTest`;

Thank you for the problem report. Verified with 5.1.64 on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql --column-type-info -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.64-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS `coalesceTest` (
    -> paddedNumber INT(4) UNSIGNED ZEROFILL NOT NULL 
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> INSERT INTO `coalesceTest` (paddedNumber) VALUES (5), (10), (200), (3000);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT COALESCE(paddedNumber), paddedNumber from `coalesceTest`;
Field   1:  `COALESCE(paddedNumber)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 4
Decimals:   0
Flags:      NOT_NULL UNSIGNED BINARY NUM 

Field   2:  `paddedNumber`
Catalog:    `def`
Database:   `test`
Table:      `coalesceTest`
Org_table:  `coalescetest`
Type:       LONG
Collation:  binary (63)
Length:     4
Max_length: 4
Decimals:   0
Flags:      NOT_NULL UNSIGNED ZEROFILL NO_DEFAULT_VALUE NUM 

+------------------------+--------------+
| COALESCE(paddedNumber) | paddedNumber |
+------------------------+--------------+
|                      5 |         0005 |
|                     10 |         0010 |
|                    200 |         0200 |
|                   3000 |         3000 |
+------------------------+--------------+
4 rows in set (0.01 sec)

Manual (http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_coalesce) says nothing about this "feature" of COALESCE(), so I'd agree that this is a bug.

It appears as though this is also present in derived tables using a UNION.

(Using same table setup as above):

SELECT * FROM (
	SELECT
		paddedNumber
	FROM
		coalesceTest t
	WHERE
		t.paddedNumber > 500
	UNION SELECT
		paddedNumber
	FROM
		coalesceTest t
	WHERE
		t.paddedNumber <= 100
) numbers

OUTPUT:
3000
5
10

I should add this doesn't happen using a typical derived table...

No truncation of zeros, Test 1:

SELECT * FROM (
	SELECT
		paddedNumber
	FROM
		coalesceTest t
	WHERE
		t.paddedNumber <= 500
) numbers

Test 2: 

SELECT 
	*
FROM
	coalesceTest t1
INNER JOIN
	coalesceTest t2
ON
	t2.paddedNumber >= 1000

I agree that a UNION query like e.g. in its simplest form 

  select paddedNumber from coalesceTest 
  union 
  select paddedNumber from coalesceTest;

should preserve the ZEROFILL column attribute as the actual
column value is not touched in any way here, but as soon as
the column value gets part of an expression (and calling 
COALESCE, or any other function, with that column value as 
a parameter is just that) ZEROFILL won't be preserved in
any meaningful way ... the same is e.g. true for:

  SELECT GREATEST(paddedNumber,0) FROM coalesceTest;

or 

  SELECT MIN(paddedNumber) FROM coalesceTest;

or 

  SELECT paddedNumber + 0 FROM coalesceTest;

None of these preserve the ZEROFILL attribute, and IMHO 
none of them should ... and there is no need to document
this on specific functions like COALESCE either ...

And then again it is all documented behavior: 

 Note: The ZEROFILL attribute is ignored when a column 
       is involved in expressions or UNION queries. 

(around the middle of http://dev.mysql.com/doc/refman/5.1/en/numeric-type-attributes.html )

Posted by developer:
 
This is not a bug.
As Hartmut pointed out, this is documented behavior.