Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40788

TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP does not auto-update on ENGINE=DuckDB

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP does not auto-update on ENGINE=DuckDB. If ON UPDATE CURRENT_TIMESTAMP is not supported by DuckDB, a clear error message should be returned instead when creating the table.

      Test case

      INSTALL SONAME 'ha_duckdb';
      CREATE TABLE t1 (id INT PRIMARY KEY, v INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=DuckDB;
      INSERT INTO t1 VALUES (1, 100, '2020-01-01 00:00:00');
      SELECT SLEEP(2);
      UPDATE t1 SET v=200 WHERE id=1;
      SELECT * FROM t1;
      

      CLI Output

      13.1.0-opt>INSTALL SONAME 'ha_duckdb';
      Query OK, 0 rows affected (1.181 sec)
       
      13.1.0-opt>CREATE TABLE t1 (id INT PRIMARY KEY, v INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=DuckDB;
      Query OK, 0 rows affected (0.033 sec)
       
      13.1.0-opt>INSERT INTO t1 VALUES (1, 100, '2020-01-01 00:00:00');
      Query OK, 1 row affected (0.071 sec)
       
      13.1.0-opt>SELECT SLEEP(2);
      +----------+
      | SLEEP(2) |
      +----------+
      |        0 |
      +----------+
      1 row in set (2.000 sec)
       
      13.1.0-opt>UPDATE t1 SET v=200 WHERE id=1;
      Query OK, 1 row affected (0.073 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      13.1.0-opt>SELECT * FROM t1;
      +----+------+---------------------+
      | id | v    | ts                  |
      +----+------+---------------------+
      |  1 |  200 | 2020-01-01 00:00:00 |
      +----+------+---------------------+
      1 row in set (0.014 sec)
       
      13.1.0-opt>SHOW CREATE TABLE t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id` int(11) NOT NULL,
        `v` int(11) DEFAULT NULL,
        `ts` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`)
      ) ENGINE=DUCKDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      1 row in set (0.003 sec)
       
      13.1.0-opt>
      

      Attachments

        Issue Links

          Activity

            People

              drrtuy Roman
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 0.5h
                  0.5h

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.