[MDEV-15980] FOR SYSTEM_TIME BETWEEN and FROM .. TO work with negative intervals Created: 2018-04-22  Updated: 2018-05-12  Resolved: 2018-05-12

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

Note: I'm not sure it's a bug, and even less sure it's important, it just looks odd and most likely will trigger reports from some meticulous users.

MariaDB [test]> select *, row_start, row_end from t1 for system_time from '2018-04-23 02:00:00' to '2018-04-23 01:35:00';
+------+----------------------------+----------------------------+
| i    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    3 | 2018-04-23 01:30:14.208582 | 2038-01-19 05:14:07.999999 |
+------+----------------------------+----------------------------+
1 row in set (0.00 sec)

Same for BETWEEN.

Current documentation just says

BETWEEN start AND end will show all rows that were visible at any point between two specified points in time. It works inclusively, a row visible exactly at start or exactly at end will be shown too.

and about the same for FROM .. TO.

So, technically it doesn't say that start should be earlier than end, although common sense suggests so, and also it's reasonable to expect semantics close to the old operator BETWEEN, which doesn't return anything for backward intervals.



 Comments   
Comment by Sergei Golubchik [ 2018-04-25 ]

The standard grammar is

FOR SYSTEM_TIME BETWEEN [ ASYMMETRIC | SYMMETRIC ] <point in time 1> AND <point in time 2>

If SYMMETRIC then points can be specified in any order, if ASYMMETRIC, then point1 must be less or equal than point2.

If neither is specified, ASYMMETRIC is implied.

So, you're right, the first point in time should be less or equal than the second one.

Comment by Eugene Kosov (Inactive) [ 2018-04-26 ]

Relevant part of standard is 7.6 <table reference> General Rules

Comment by Eugene Kosov (Inactive) [ 2018-04-27 ]

https://github.com/MariaDB/server/pull/733/files

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