[MDEV-30930] Make more date conditions sargable Created: 2023-03-27  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: 11.5

Type: New Feature Priority: Major
Reporter: Oleg Smirnov Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-8320 Allow index usage for DATE(datetime_c... Closed

 Description   

In further development of MDEV-8320 make such conditions sargable:

  • YEAR(column) = const1 AND MONTH(column) <=> const2
  • YEAR(column) = const1 AND MONTH(column) BETWEEN const1 AND const2
  • YEAR(column) BETWEEN const1 AND const2
  • DATE(column) BETWEEN const1 AND const2

 Comments   
Comment by Sergei Petrunia [ 2023-03-27 ]

It is fairly straightforward to extend the approach for MDEV-8320 to handle YEAR|DATE BETWEEN const AND const.

Handling MONTH(...) is harder. In order to handle it, we need to know if YEAR() is fixed (or has a limited number of matching values)...

Comment by Ahmad Faruk [ 2023-11-21 ]

What will happen if the datetime column contains micro or miliseconds?

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