MySQL Bugs: #14171: Wrong (NOT NULL) default for BINARY(n)

Bug #14171	Wrong (NOT NULL) default for BINARY(n)
Submitted:	20 Oct 2005 10:35	Modified:	24 Jan 2007 21:02
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
When I insert a record in a table without specifying a value
for a BINARY(n) NOT NULL column, I get n spaces as content.
I assume that this is wrong and guess it should be
   HEX (column) = "empty"
   physical column content = 
                      "nothing" + padded zero's till the full length
                       of the BINARY column is reached.
                                       =
                       n times \00

CREATE TABLE t1 (f1 BIGINT, f2    BINARY(2) NOT NULL);
CREATE TABLE t2 (f1 BIGINT, f2 VARBINARY(2) NOT NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` bigint(20) default NULL,
  `f2` binary(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f1` bigint(20) default NULL,
  `f2` varbinary(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 SET f1 = 1;
Warnings:
Warning	1364	Field 'f2' doesn't have a default value
INSERT INTO t1 SET f1 = 2, f2 = CAST('a' AS BINARY(1));
INSERT INTO t2 SET f1 = 1;
Warnings:
Warning	1364	Field 'f2' doesn't have a default value
INSERT INTO t2 SET f1 = 2, f2 = CAST('a' AS BINARY(1));
SELECT f1, f2, HEX(f2) FROM t1;
f1	f2	HEX(f2)
1	  	2020      <---  I assume that there should be 
                                      \x00\x00 for f2 and an empty
                                      HEX value like in the following VARBINARY
                                      example.
2	a\x00 61  
SELECT f1, f2, HEX(f2) FROM t2;
f1	f2	HEX(f2)
1		
2	a	61

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.2007.3.2, 2005-10-19

BTW: There was an intended change in the behaviour of BINARY(n)
         in MySQL 5.0 (pad with zero's ...) compared to 4.1 (no padding). 
         That means, the bug is only in MySQL 5.0.
         The results in MySQL 4.1 are different, but consistent and 
         correct.

How to repeat:
Please use my attached testscript ml034.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml034.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml034
  inspect r/ml034.reject    # The protocol of the execution.

test script

Attachment: ml034.test (application/test, text), 526 bytes.

It must be mentioned, that this bug might harm customers, who perform
1. Have a table created in MySQL 4.1 with 
    CREATE TABLE .... <column> BINARY(n) NOT NULL  <no explicit default !!>
    MySQL 4.1 Show CREATE TABLE presents
        ... BINARY(n) NOT NULL DEFAULT ''  (two single quotes without space between)
2. Upgrade the data in place (without mysqldump) to MySQL 5.0
    MySQL 5.0 Show CREATE TABLE presents
        ... BINARY(n) NOT NULL DEFAULT ' '  (two single quotes with space between)
3. Downgrade everything to MySQL 4.1 (via mysqldump)
     MySQL 4.1 Show CREATE TABLE presents
        ... BINARY(n) NOT NULL DEFAULT ' '  (two single quotes with space between)
That means you will not get a table with the same properties.

I assume that 
- the probability of a table/application history like above is very rare
- customers will have to adjust their tables/applications when being
   faced with the new properties of BINARY in MySQL 5.0 and they will
   remember such things when downgrading
So I guess such unexpected Upgrade/Downgrade issues are very rare.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17335

ChangeSet@1.2349, 2006-12-22 20:54:32+03:00, evgen@moonbone.local +3 -0
  Bug#14171: Wrong internal default value for a BINARY field.
  
  A BINARY field is represented by the Field_string class. The space character
  is used as the filler for unused characters in such a field. But a BINARY field 
  should use \x00 instead.
  
  Field_string:reset() now detects whether the current field is a BINARY one
  and if so uses the \x00 character as a default value filler.

I tested with MySQL 5.1.15 pulled today.
Last changeset was: ChangeSet@1.2384, 2007-01-11 23:05:13+01:00.

I see:

mysql> CREATE TABLE t1 (f1 BIGINT, f2    BINARY(2) NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 (f1) values (1);
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'f2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.02 sec)

mysql> select f1,hex(f2) from t1;
+------+---------+
| f1   | hex(f2) |
+------+---------+
|    1 | 2020    |
+------+---------+
1 row in set (0.39 sec)

I'm setting status back to "patch queued".

fixed in 5.0.34, 5.1.15-beta

Noted in 5.0.34, 5.1.15 changelogs.

Inserting a row into a table without specifying a value for a
BINARY(N) NOT NULL column caused the column to be set to spaces, not
zeroes.