My query to retrieve events between 2 search dates does not return any results, why?

Context

I have modelized a calendar with a table. The entries are the calendar’s events:

id ; event_start_date ; event_end_date.

One entry is:

11 ; 2021-01-28 15:15:37 ; 2022-01-11 15:15:37

The script I’ve written accepts a search interval of dates as entries and should return all the events which are contained within the search interval (if the search interval is contained in the events intervals, or if one of the search interval dates is contained in the events intervals, or if the events intervals are contained in the search interval). I think that the (unique) case where no results should be returned is: if the search interval is completely out of the bounds of all the events.

Example of search, Expected and Actual behaviors

Consider the following search interval: search_start_date = 2021-01-26 00:00:00 and search_end_date = 2021-01-30 00:00:00.

Expected behavior: The event #11 should be returned because even though the search_start_date is out of the event’s interval, the search_end_date is within it.

Actual behavior: no event is returned.

The script

SGBD used: MySQL. Language used: SQL.

select *, @search_start_date:="2021-01-26 00:00:00", @search_end_date:="2021-01-30 00:00:00" 

from `calendars_events` 

where

(@search_start_date between `event_start_date` and `event_end_date`) or (@search_end_date between `event_start_date` and `event_end_date`)

or

(`event_start_date` between @search_start_date and @search_end_date) or (`event_end_date` between @search_start_date and @search_end_date)

Question

Why is the event not returned? I think I correctly use MySQL variables and I think I can use between to make dates comparison.

Answer

Variables must be initialized in the FROM clause (MySQL query / clause execution order), and the condition can be much simpler:

SELECT e.* 
FROM `calendars_events` e, (
  SELECT
    @search_start_date:="2021-01-26 00:00:00",
    @search_end_date:="2021-01-30 00:00:00"
  ) vars
WHERE @search_start_date <= event_end_date AND @search_end_date >= event_start_date

fiddle

Note that starting MySQL 8 setting user variables within expressions is deprecated and will be removed in a future release.