search

systopia / de.systopia.birthdays

CiviCRM extension to deal with birthdays
Other
4 stars 9 forks source link

issue#22 Fix birthday filters on Percona #23

Closed aydun closed 1 year ago

aydun commented 3 years ago

Percona and MariaDB handle some date/datetime comparisons differently.

Comparisons of date with an 8 digit integer or string are the same and as desired: MariaDB:

select curdate(), curdate()>20210720, curdate()<20210720, curdate()<20210730, curdate()>'20210720', curdate()<'20210720', curdate()<'20210730'; +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+ | curdate() | curdate()>20210720 | curdate()<20210720 | curdate()<20210730 | curdate()>'20210720' | curdate()<'20210720' | curdate()<'20210730' | +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+ | 2021-07-27 | 1 | 0 | 1 | 1 | 0 | 1 | +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+

Percona:

select curdate(), curdate()>20210720, curdate()<20210720, curdate()<20210730, curdate()>'20210720', curdate()<'20210720', curdate()<'20210730'; +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+ | curdate() | curdate()>20210720 | curdate()<20210720 | curdate()<20210730 | curdate()>'20210720' | curdate()<'20210720' | curdate()<'20210730' | +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+ | 2021-07-27 | 1 | 0 | 1 | 1 | 0 | 1 | +------------+--------------------+--------------------+--------------------+----------------------+----------------------+----------------------+

However, comparing to a 14 digit datetime as string and integer produces different results: MariaDB:

select curdate(), curdate()>20210720235959, curdate()<20210720235959, curdate()<20210730235959, curdate()>'20210720235959', curdate()<'20210720235959', curdate()<'20210730235959'; +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+ | curdate() | curdate()>20210720235959 | curdate()<20210720235959 | curdate()<20210730235959 | curdate()>'20210720235959' | curdate()<'20210720235959' | curdate()<'20210730235959' | +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+ | 2021-07-27 | 1 | 0 | 1 | 1 | 0 | 1 | +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+

Percona:

select curdate(), curdate()>20210720235959, curdate()<20210720235959, curdate()<20210730235959, curdate()>'20210720235959', curdate()<'20210720235959', curdate()<'20210730235959'; +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+ | curdate() | curdate()>20210720235959 | curdate()<20210720235959 | curdate()<20210730235959 | curdate()>'20210720235959' | curdate()<'20210720235959' | curdate()<'20210730235959' | +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+ | 2021-07-27 | 0 | 1 | 1 | 1 | 0 | 1 | +------------+--------------------------+--------------------------+--------------------------+----------------------------+----------------------------+----------------------------+

Comparing a date with a datetime string produces the same, desired results but comparing a date to a datetime produces different results on Percona.

When filtering by birthdays in 'this calendar month', the comparison is against a 14 digit integer giving incorrect results on Percona, despite working correctly on MariaDB.

The fix here is to convert the calculated date to a datetime before the comparison.

Observed on 10.3.29-MariaDB-0ubuntu0.20.10.1 and 5.7.31-percona-sure1-log