(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().
83a79ba...
by
Ian Gilfillan <email address hidden>
MDEV-32148 Inefficient WHERE timestamp_column=datetime_const_expr
Changing the way how a the following conditions are evaluated:
WHERE timestamp_column=datetime_const_expr
(for all comparison operators: =, <=>, <, >, <=, >=, <> and for NULLIF)
Before the change it was always performed as DATETIME.
That was not efficient, as involved per-row TIMESTAMP->DATETIME conversion
for timestamp_column. For example, in case of the SYSTEM time zone
it involved a localtime_r() call, which is known to be slow.
After the change it's performed as TIMESTAMP in many cases.
This allows to avoid per-row conversion, as it works the other way around:
datetime_const_expr is converted to TIMESTAMP once before the execution stage.
Note, datetime_const_expr must be inside monotone continuous periods of
the current time zone, i.e. not near these anomalies:
- DST changes (spring forward, fall back)
- leap seconds
MDEV-33112 innodb_undo_log_truncate=ON is blocking page write
When innodb_undo_log_truncate=ON causes an InnoDB undo tablespace
to be truncated, we must guarantee that the undo tablespace will
be rebuilt atomically: After mtr_t::commit_shrink() has durably
written the mini-transaction that rebuilds the undo tablespace,
we must not write any old pages to the tablespace.
To guarantee this, in trx_purge_truncate_history() we used to
traverse the entire buf_pool.flush_list in order to acquire
exclusive latches on all pages for the undo tablespace that
reside in the buffer pool, so that those pages cannot be written
and will be evicted during mtr_t::commit_shrink(). But, this
traversal may interfere with the page writing activity of
buf_flush_page_cleaner(). It would be better to lazily discard
the old pages of the truncated undo tablespace.
fil_space_t::create_lsn: A new field, identifying the LSN of the
latest rebuild of a tablespace.
buf_page_t::flush(), buf_flush_try_neighbors(): Evict pages whose
FIL_PAGE_LSN is below fil_space_t::create_lsn.
mtr_t::commit_shrink(): Update fil_space_t::create_lsn and
fil_space_t::size right before the log is durably written and the
tablespace file is being truncated.
fsp_page_create(), trx_purge_truncate_history(): Simplify the logic.
