MySQL Bugs: #104567: Inconsistent result defining binary column default values

Bug #104567	Inconsistent result defining binary column default values
Submitted:	8 Aug 2021 13:48	Modified:	16 Aug 2021 22:56
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7, 8.0, 5.7.35, 8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	default values, hex

Description:
No

How to repeat:
Reported as a problem by a user (https://forums.mysql.com/read.php?101,698061,698061#msg-698061)

-- 5.7, X operator
drop table if exists binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT X'aaaa00aaaaaaaaffffff'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| col   | varbinary(100) | YES  |     |         |       |
+-------+----------------+------+-----+---------+-------+

-- 8.0, X operator
drop table if exists binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT X'aaaa00aaaaaaaaffffff'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| col   | varbinary(100) | YES  |     | 0xAAAA  |       |
+-------+----------------+------+-----+---------+-------+

-- 5.7, no X operator:
drop table binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT 'ªª ªªªªÿÿÿ'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------------------+-------+
| Field | Type           | Null | Key | Default             | Extra |
+-------+----------------+------+-----+---------------------+-------+
| col   | varbinary(100) | YES  |     | ªª ªªªªÿÿÿ          |       |
+-------+----------------+------+-----+---------------------+-------+

-- 8.0, no X operator:
drop table binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT 'ªª ªªªªÿÿÿ'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+------------------------------------------+-------+
| Field | Type           | Null | Key | Default                                  | Extra |
+-------+----------------+------+-----+------------------------------------------+-------+
| col   | varbinary(100) | YES  |     | 0xC2AAC2AA20C2AAC2AAC2AAC2AAC3BFC3BFC3BF |       |
+-------+----------------+------+-----+------------------------------------------+-------+

Suggested fix:
Don't use the X hex operator to define default values. 

But still, shouldn't these results be more consistent?

Hello Peter,

Thank you for the report and feedback!

Thanks,
Umesh

Hi. The default value of the table is completely ok:

mysql> CREATE TABLE t (col varbinary(100) DEFAULT X'aaaa00aaaaaaaaffffff' ) ENGINE=InnoDB;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into t() values();
Query OK, 1 row affected (0,00 sec)

mysql> select * from t;
+------------------------+
| col                    |
+------------------------+
| 0xAAAA00AAAAAAAAFFFFFF |
+------------------------+
1 row in set (0,00 sec)

The problem is that show columns displays the default value incorrect of there is a 0-byte in the value:

mysql> CREATE TABLE t1 ( col varbinary(100) DEFAULT X'aabbccddeeff' ) ENGINE=InnoDB;
Query OK, 0 rows affected (0,01 sec)

mysql> show columns from t1;
+-------+----------------+------+-----+----------------+-------+
| Field | Type           | Null | Key | Default        | Extra |
+-------+----------------+------+-----+----------------+-------+
| col   | varbinary(100) | YES  |     | 0xAABBCCDDEEFF |       |
+-------+----------------+------+-----+----------------+-------+
1 row in set (0,00 sec)

mysql> CREATE TABLE t2 ( col varbinary(100) DEFAULT X'aa00bbccddeeff' ) ENGINE=InnoDB;
Query OK, 0 rows affected (0,00 sec)

mysql> show columns from t2;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| col   | varbinary(100) | YES  |     | 0xAA    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0,01 sec)

> Hi. The default value of the table is completely ok
> The problem is that show columns displays the default value incorrect of there is a 0-byte in the value

?! The pad value is 0x00, so the default value is not completely ok, should not MySQL reject this?

MySQL does not pad VARBINARY (see https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html) so as far as I can see, the problem is in SHOW COLUMNS.