[MDEV-14771] Syntax error upon "FOR system_time" query from derived table Created: 2017-12-25  Updated: 2017-12-27  Resolved: 2017-12-27

Status: Closed
Project: MariaDB Server
Component/s: Parser, Versioned Tables
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description    

MariaDB [test]> create or replace table t1 (i int) with system versioning;
 
Query OK, 0 rows affected (0.18 sec)

This does not work:

MariaDB [test]> select * from (select * from t1) drv for system_time as of now();
 
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 'system_time as of now()' at line 1

The right syntax apparently is

MariaDB [test]> select * from (select * from t1) for system_time as of now() drv;
 
Empty set (0.00 sec)

I want to ensure it's intentional, since the working syntax looks confusing.

Test case for copy-pasting

 
CREATE TABLE t1 (i INT) WITH SYSTEM VERSIONING;
 
SELECT * FROM t1 FOR system_time ALL;
 
SELECT * FROM ( SELECT * FROM t1 ) t FOR system_time ALL;
 
 
 
# Cleanup
 
DROP TABLE t1;



 Comments   
Comment by Sergei Golubchik [ 2017-12-27 ]

It is intentional. That's from SQL:2016, part II, <table reference>:

<table primary> ::=
 
    <table or query name>
 
        [ <query system time period specification> ]
 
        [ <correlation or recognition> ]
 
  | ...
 
 
 
<query system time period specification> ::=
 
    FOR SYSTEM_TIME AS OF <point in time 1>
 
  | FOR SYSTEM_TIME ...
 
 
 
<correlation or recognition> ::=
 
    [ AS ] <correlation name>
 
    [ <parenthesized derived column list> ]

As you see, FOR SYSTEM_TIME goes before AS identifier

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