Some basic datatypes and functions in oracle compatibility mode do not work (MDEV-19162)

[MDEV-12513] INTERVAL data type Created: 2017-04-17  Updated: 2021-05-11

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Compatibility

Attachments: PNG File Time Intervals lubridate R.PNG    
Issue Links:
Blocks
is blocked by MDEV-4912 Data type plugin API version 1 Closed
is blocked by MDEV-12514 Split Item_temporal_func::fix_length_... Closed
Duplicate
duplicates MDEV-13088 SUGGESTION: New Data Type: Time Inter... Closed
duplicates MDEV-19144 sql_mode="oracle" does not support in... Closed
PartOf
is part of MDEV-21086 New data types Open

 Description   

Currently INTERVAL literals can appear in context of the DATE_ADD function and the operator +.
For example:

SELECT DATE_ADD(OrderDate,INTERVAL 30 DAY) AS OrderPayDate FROM Orders
 
SELECT OrderDate + INTERVAL 30 DAY AS OrderParDate FROM Orders;
 
SELECT INTERVAL 30 DAY + OrderDate AS OrderParDate FROM Orders;

We will change INTERVAL from a DATE_ADD and + specific syntax to a full-functional data type, so intervals can appear in all context where a regular data type can, including:

  • INTERVAL table fields
  • INTERVAL stored routine variables, parameters, function return values
  • Hybrid built-in functions and operators, such as COALESCE, IF, CASE, LEAST, GREATEST.
  • MIN and MAX aggregate functions

 Comments   
Comment by Juan Telleria [ 2017-06-14 ]

In R, there is a package called "lubridate" which also offers a "Time Interval" Class:

R Package Lubridate (GPL v2)

I attach a vignette snapshot which contains some of the valuable functions this "Time Interval" Class offers.

Comment by Juan Telleria [ 2017-06-14 ]

Interval Data Type could be really valuable for performing JOINs between tables by checking a temporal overlap between Events in a fast way.

R lubridate package offers a function called int_overlaps() wich is valuable for performing in-memory joins between data frames.

Also %within% function is of great interest.

Thank you!

Comment by Juan Telleria [ 2017-06-15 ]

What would be amazing would be to be able to do the following SQL query in few seconds, having "Column_Interval_Data_Type" indexed:

SELECT
 
  *
 
FROM
 
  t_Table_Name1 AS A
 
  LEFT JOIN t_Table_Name2 AS B
 
    ON
 
      A.Column_Interval_Data_Type INT_OVERLAPS (B.Column_Interval_Data_Type);

At the moment I have to take the following steps in order to perform similar queries in MariaDB:

  1. 1. Both Table UNION, ORDER BY DateTime Type.
  2. 2. CURSOR: Check overlapping condition and assign IDs in MEMORY table, old vs new.
  3. 3. GROUP BY, Create the new table with time overlaps.

Which also performs pretty fast, in a couple of minutes. Other option might be making use of Window Functions.

Thank you.

Comment by Juan Telleria [ 2017-07-04 ]

R Code for Intervals in "lubridate" package is as follows:

github lubridate

Generated at Thu Feb 08 07:58:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.