[MDEV-31208] TIMESTAMP incorrect attributes? Created: 2023-05-06  Updated: 2023-05-06  Resolved: 2023-05-06

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.6.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Christopher Norris Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 22.04


 Description   

I have a table which has an 'updated' timestamp column which on update will update the timestamp, and another timestamp column which may or may not get updated but has no default/on update attributes. I noticed that the other timestamp column was returning the same value as the updated column so looked into it a bit further.

It seems DEFAULT and ON UPDATE is being set on the first TIMESTAMP column when not defined in the table definition, it seems to be reset for the second column though. 

CREATE TABLE test ( a TIMESTAMP );
 
DESCRIBE test;
 
| a     | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
 
 
 
CREATE TABLE test2 ( a TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
 
DESCRIBE test2;
 
| a     | timestamp | NO   |     | current_timestamp() |       |
 
 
 
CREATE TABLE test3 ( a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, b TIMESTAMP );
 
DESCRIBE test3;
 
| a     | timestamp | NO   |     | current_timestamp() |       |
 
| b     | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
 
 
 
CREATE TABLE test4 ( a TIMESTAMP, b TIMESTAMP );
 
DESCRIBE test4;
 
| a     | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
 
| b     | timestamp | NO   |     | 0000-00-00 00:00:00 |                               |



 Comments   
Comment by Sergei Golubchik [ 2023-05-06 ]

Seems to be in line with https://mariadb.com/kb/en/timestamp/#automatic-values

MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:

  • DEFAULT CURRENT_TIMESTAMP
  • ON UPDATE CURRENT_TIMESTAMP

and so on

Comment by Sergei Golubchik [ 2023-05-06 ]

Note also https://mariadb.com/kb/en/server-system-variables/#explicit_defaults_for_timestamp

Generated at Thu Feb 08 10:22:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.