Bug #10179 error in default value setting
Submitted: 26 Apr 2005 14:10 Modified: 18 May 2005 2:36
Reporter: Tomas Ulin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:current 5.0 bk OS:Linux (linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[26 Apr 2005 14:10] Tomas Ulin
Description:
default setting of bit(1) to 1 and char(1) to 'a'  not working when certain columns preside these.

They become 0 and NULL respectively.  Removing columns before in the create table removes the issue...

mysql> create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c24 bit(1) NOT NULL default 1, c25 char(1) defau
lt 'a', primary key(c1)) engine=myisam;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t9 set c1=1;
Query OK, 1 row affected (0.01 sec)

mysql> select hex(c24),c25 from t9;
+----------+------+
| hex(c24) | c25  |
+----------+------+
| 0        | NULL |
+----------+------+
1 row in set (0.00 sec)

mysql> 
mysql> drop table t9;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c24 bit(1) NOT NULL default 1, primary key(c1)) 
engine=myisam;
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t9 set c1=1;
Query OK, 1 row affected (0.00 sec)

mysql> select hex(c24) from t9;
+----------+
| hex(c24) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

mysql> 
mysql> drop table t9;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c25 char(1) default 'a', primary key(c1)) engine
=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t9 set c1=1;
Query OK, 1 row affected (0.00 sec)

mysql> select c25 from t9;
+------+
| c25  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

How to repeat:
drop table t9;
create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c24 bit(1) NOT NULL default 1, c25 char(1) default 'a', primary key(c1)) engine=myisam;
insert into t9 set c1=1;
select hex(c24),c25 from t9;

drop table t9;
create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c24 bit(1) NOT NULL default 1, primary key(c1)) engine=myisam;
insert into t9 set c1=1;
select hex(c24) from t9;

drop table t9;
create table t9 (c1 int, c16 time, c18 tinyint, c19 bool,  c21 char(10), c23 bit(1), c25 char(1) default 'a', primary key(c1)) engine=myisam;
insert into t9 set c1=1;
select c25 from t9;
[18 May 2005 2:36] Paul Dubois
Noted in 5.0.6 changelog.