`ON UPDATE CURRENT_TIMESTAMP` in column definition does not update TIMESTAMP

[GypsyCosmonaut]

Steps to reproduce

DDL, DML, DQL

CREATE TABLE relation (time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, random INT) ;

INSERT INTO relation SET random = 11 ;

SELECT * FROM relation ;

UPDATE relation SET random = 22 where random = 11 ;

SELECT * FROM relation ;

You'll see that MySQL updates the time field, however Dolt does not

Dolt version 1.1.0

[timsehn]

I can repro:

PS C:\Users\timse\dolthub\dolt> cd .\test_update\
PS C:\Users\timse\dolthub\dolt\test_update> dolt init --fun
Successfully initialized dolt data repository.
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "CREATE TABLE relation (time TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(), random INT)"
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "show create table relation"
+----------+------------------------------------------------------------------+
| Table    | Create Table                                                     |
+----------+------------------------------------------------------------------+
| relation | CREATE TABLE `relation` (                                        |
|          |   `time` timestamp(6) DEFAULT (CURRENT_TIMESTAMP()),             |
|          |   `random` int                                                   |
|          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+----------+------------------------------------------------------------------+

PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "INSERT INTO relation SET random = 11"
Query OK, 1 row affected (0.00 sec)
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "select * from relation"
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 02:48:35 | 11     |
+---------------------+--------+

PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "UPDATE relation SET random = 22 where random = 11"
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "select * from relation"
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 02:48:35 | 22     |
+---------------------+--------+

PS C:\Users\timse\dolthub\dolt\test_update>

It looks like we are silently dropping that update trigger. I didn't even know you could define it like that.

You can workaround this in Dolt with a separate trigger:

PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "create trigger update_timestamp
before update on relation
for each row
set new.time=CURRENT_TIMESTAMP()"
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "select * from relation"
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 02:48:35 | 22     |
+---------------------+--------+

PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "UPDATE relation SET random = 33 where random = 22"
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
PS C:\Users\timse\dolthub\dolt\test_update> dolt sql -q "select * from relation"
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 02:54:14 | 33     |
+---------------------+--------+

But good bug. We will fix.

[timsehn]

I was dubious you could do this after consulting the MySQL documentation but indeed this does work in MySQL:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> CREATE TABLE relation (time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, random INT) ;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table relation;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                            |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| relation | CREATE TABLE `relation` (
  `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `random` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> INSERT INTO relation SET random = 11 ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM relation ;
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 14:34:36 |     11 |
+---------------------+--------+
1 row in set (0.01 sec)

mysql> UPDATE relation SET random = 22 where random = 11 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM relation ;
+---------------------+--------+
| time                | random |
+---------------------+--------+
| 2023-05-25 14:34:54 |     22 |
+---------------------+--------+
1 row in set (0.00 sec)

[timsehn]

Relevant MySQL docs on this faeture:

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

[nicktobey]

This is wild. The MySQL reference for CREATE TABLE lists what's implied to be the complete grammar for the statement, and ON UPDATE CURRENT_TIMESTAMP isn't listed there. No wonder we aren't parsing it correctly.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

[GypsyCosmonaut]

@timsehn @nicktobey could you please help in getting this closed as it's something we're working on in inventory management..

[timsehn]

I'll talk to the team today.