[MDEV-13729] Update always modifies the first timestamp column in a table. Created: 2017-09-04  Updated: 2017-09-05  Resolved: 2017-09-05

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: John Doughnut Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

windows x64


 Description   

Very surprising issue, reproduced in both mariadb 10.2 and mysql. Different ENGINE doesn't affect the result.

Reproduce:
CREATE TABLE `ttt` (`ii` int, `dd` TIMESTAMP, `d2` TIMESTAMP);
insert into ttt (dd, d2) values ('2017-01-01 00:00:00', '2017-08-02 20:45:10');

SELECT * FROM ttt; ## OK, returns null 2017-01-01 00:00:00 2017-08-02 20:45:10
update ttt set ii=1;

SELECT * FROM ttt; ## Returns 1 2017-09-04 14:14:28 2017-08-02 20:45:10

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-09-04 ]

Moved this from MCOL to MDEV as this does not appear to be a ColumnStore issue.

Comment by Alice Sherepa [ 2017-09-05 ]

Hi John Doughnut,
this case is not a bug, but expected behavior, it is documented here https://mariadb.com/kb/en/the-mariadb-library/timestamp/

MariaDB [test]> show create table ttt;
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                        |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| ttt   | CREATE TABLE `ttt` (
 
  `ii` int(11) DEFAULT NULL,
 
  `dd` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `d2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.01 sec)

Generated at Thu Feb 08 08:07:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.