[MDEV-15765] BETWEEN not working in certain cases - Jira

In MariaDB 10.2.14 the following SQLs do not work as intended, specifically BETWEEN seems to be broken. In 10.2.13 everything works as expected.

-- Not work

SELECT

FROM (SELECT  date('2018-01-01') AS DAT

  UNION ALL

  SELECT  date('2018-01-01') AS DAT) AS t

WHERE t.DAT BETWEEN date ('2017-01-01') AND date ('2019-01-01');

-- not work

SELECT

FROM (SELECT  date('2018-01-01') AS d) AS t

WHERE t.d BETWEEN date ('2017-01-01') AND date ('2019-01-01');

-- work

 SELECT 'aaaa' AS d FROM dual

 WHERE date('2018-01-01') BETWEEN date ('2017-01-01') AND date ('2019-01-01');

is duplicated by

MDEV-15829 BETWEEN datetime not working with derived table and condition pushdown

MDEV-15860 Datetime range search in subquery returns an empty resultset

MDEV-16195 MariaDB 10.2 Select Query Between Dates Not Returning Data