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

System-versioned CONNECT tables have weird behaviour

    XMLWordPrintable

Details

    Description

      MariaDB [test]> CREATE OR REPLACE TABLE t (
          ->     label VARCHAR(50) NOT NULL,
          ->
          ->     valid_from TIMESTAMP(6)
          ->         GENERATED ALWAYS AS ROW START,
          ->     valid_to TIMESTAMP(6)
          ->         GENERATED ALWAYS AS ROW END,
          ->     PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
          -> )
          ->     WITH SYSTEM VERSIONING,
          ->     ENGINE CONNECT,
          ->     TABLE_TYPE=CSV,
          ->     FILE_NAME='t.csv'
          -> ;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [test]> INSERT INTO t (label) VALUES ('v1');
      Query OK, 1 row affected (0.001 sec)
       
      MariaDB [test]> SELECT * FROM t;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT * FROM t FOR SYSTEM_TIME ALL;
      +-------+----------------------------+----------------------------+
      | label | valid_from                 | valid_to                   |
      +-------+----------------------------+----------------------------+
      | v1    | 2018-10-15 22:40:26.000000 | 2038-01-19 03:14:07.000000 |
      +-------+----------------------------+----------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> UPDATE t SET label = 'v2';
      Query OK, 0 rows affected (0.000 sec)
      Rows matched: 0  Changed: 0  Inserted: 0  Warnings: 0
       
      MariaDB [test]> SELECT * FROM t;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT * FROM t FOR SYSTEM_TIME ALL;
      +-------+----------------------------+----------------------------+
      | label | valid_from                 | valid_to                   |
      +-------+----------------------------+----------------------------+
      | v1    | 2018-10-15 22:40:26.000000 | 2038-01-19 03:14:07.000000 |
      +-------+----------------------------+----------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> DELETE FROM t WHERE label = 'v1';
      ERROR 1194 (HY000): Table 't' is marked as crashed and should be repaired
      MariaDB [test]> SELECT * FROM t;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT * FROM t FOR SYSTEM_TIME ALL;
      +-------+----------------------------+----------------------------+
      | label | valid_from                 | valid_to                   |
      +-------+----------------------------+----------------------------+
      | v1    | 2018-10-15 22:40:26.000000 | 2038-01-19 03:14:07.000000 |
      +-------+----------------------------+----------------------------+
      1 row in set (0.003 sec)
      

      • According to SELECT, there are no rows
      • According to SELECT ... WITH SYSTEM_TIME ALL, there is one row which is stronger than any attempt to UPDATE or DELETE it
      • DELETE crashes the table, even if it remains readable WITH SYSTEM_TIME ALL
      • If the table is not versioned, there are no anomalies

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              f_razzoli Federico Razzoli
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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