This original query:
(1) SELECT ts0 FROM t1
WHERE DATE(ts0) <= '2024-01-23';
was rewritten (by MDEV-8320) to:
(2) SELECT ts0 FROM t1
WHERE ts0 <= '2024-01-23 23:59.59.999999';
-- DATETIME comparison, Item_datetime on the right side
which was further optimized (by MDEV-32148) to:
(3) SELECT ts0 FROM t1
WHERE ts0 <= TIMESTAMP/* WITH LOCAL TIME ZONE*/ '2024-01-23 23:59.59.999999';
-- TIMESTAMP comparison, Item_timestamp_literal on the right side
The origin of the problem was in (2) - in the MDEV-8320 related code.
The recent new code for MDEV-32148 revealed this problem.
Item_datetime on step (2) was always created in an inconsistent way:
- with Item::decimals==0
- with ltime.second_part==999999,
without taking into account the precision of the left side
(e.g. ts0 in the above example)
On step (3), Item_timestamp_literal was created in an inconsistent way too,
because it copied the inconsistent data from step (2):
- with Item::decimals==0 (copied from Item_datetime::decimals)
- with m_value.tv_usec==999999 (copied from ltime.second_part of Item_datetime)
Later, the Item_timestamp_literal performed save_in_field()
and crashed in my_timestamp_to_binary() on a DBUG_ASSERT checking
consistency between the fractional precision and the fractional seconds value.
Fix:
On step (2) create Item_datetime with truncating maximum possible
second_part value of 999999 according to the the left side fractional
second precision. So for example it sets second_part as follows:
- 000000 for TIMESTAMP(0)
- 999000 for TIMESTAMP(3)
- 999999 for TIMESTAMP(6)
This automatically makes the code create a consistent Item_timestamp_literal
on step (3).
This also makes TIMESTAMP comparison work faster, because now
Item_timestamp_literal is created with Item::decimals value equal
to the Item_field (which is on the other side of the comparison),
so the low level function Type_handler_timestamp_common::cmp_native()
goes the fastest execution path optimized for the case when both sides
have equal fractional precision.
Adding a helper class TimeOfDay to reuse the code when populating:
- the last datetime point for YEAR()
- the last datetime point for DATE()
with a given fractional precision.
This class also helped to unify the equal code in create_start_bound()
and create_end_bound() into a single method create_bound().
Rewrite datetime comparison conditions into sargeable. For example,
YEAR(col) <= val -> col <= YEAR_END(val)
YEAR(col) < val -> col < YEAR_START(val)
YEAR(col) >= val -> col >= YEAR_START(val)
YEAR(col) > val -> col > YEAR_END(val)
YEAR(col) = val -> col BETWEEN YEAR_START(val) AND YEAR_END(val)
Do the same with DATE(col), for example:
DATE(col) <= val -> col <= DAY_END(val)
After such a rewrite index lookup on column "col" can be employed
