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

Valid query causes invalid view definition due to syntax limitation in FOR SYSTEM_TIME

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t (i INT) WITH SYSTEM VERSIONING;
      CREATE VIEW v AS SELECT * FROM t FOR system_time AS OF DATE_SUB(NOW(), INTERVAL 6 SECOND);
      

      10.3 9d97e6010eb

      MariaDB [test]> SHOW CREATE VIEW v;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '- interval 6 second' at line 1
      

      v.frm

      TYPE=VIEW
      query=select `test`.`t`.`i` AS `i` from `test`.`t` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second
      md5=7d2a1bb74cba5bc91a2c9495baf8895a
      updatable=1
      algorithm=0
      definer_user=root
      definer_host=localhost
      suid=2
      with_check_option=0
      timestamp=2018-02-21 17:19:03
      create-version=2
      source=SELECT * FROM t FOR system_time AS OF DATE_SUB(NOW(), INTERVAL 6 SECOND)
      client_cs_name=utf8
      connection_cl_name=utf8_general_ci
      view_body_utf8=select `test`.`t`.`i` AS `i` from `test`.`t` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second
      

      It doesn't work because this doesn't work:

      MariaDB [test]> select `test`.`t`.`i` AS `i` from `test`.`t` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '- interval 6 second' at line 1
      

      It wants brackets:

      MariaDB [test]> select `test`.`t`.`i` AS `i` from `test`.`t` FOR SYSTEM_TIME AS OF (current_timestamp() - interval 6 second);
      Empty set (0.00 sec)
      

      but the view is created without them.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration