Bug #37289 TIMESTAMP field DEFAULT value fails to apply depending on situation.

• Submitted: 9 Jun 2008 16:58
• Modified: 5 Nov 2008 17:33
• Reporter: Shawn Green
• Status: Won't fix
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.22,5.1.22
• OS: Any
• CPU Architecture: Any

[9 Jun 2008 16:58] Shawn Green

Description:
When presented with non-constant NULL values, for example: from the results of a calculation, the DEFAULT VALUE fails to engage

How to repeat:
CREATE TABLE a (
  tstamp1 timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
);

SELECT FROM_UNIXTIME(NOW());

INSERT a (tstamp1) values (now()), (NULL), (FROM_UNIXTIME(NOW()));

show warnings;

select * from a;

INSERT a (tstamp1) VALUES (FROM_UNIXTIME(NOW()));

=============
results
=============
mysql>SELECT FROM_UNIXTIME(NOW());
+----------------------+
| FROM_UNIXTIME(NOW()) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>INSERT a (tstamp1) values (now()), (NULL), (FROM_UNIXTIME(NOW()));
Query OK, 3 rows affected, 1 warning (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql>show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'tstamp1' at row 3 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>select * from a;
+---------------------+
| tstamp1             |
+---------------------+
| 2008-06-05 07:19:01 |
| 2008-06-05 07:19:01 |
| 0000-00-00 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql>INSERT a (tstamp1) VALUES (FROM_UNIXTIME(NOW()));
ERROR 1048 (23000): Column 'tstamp1' cannot be null

Suggested fix:
1) Repair or enable the DEFAULT value constraint to operate consistently depending on use case.

2) Make the WARNING from the multi-row insert match or correspond to the ERROR raised from inserting that same computed NULL value added separately in a single-row insert.