Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.3.21, 10.3.23, 10.5.5, 10.3(EOL), 10.4(EOL), 10.5
-
Tested on both AWS CentOS 7 VM and MariaDB Docker container
Description
Partial stack trace:
Thread 1 (Thread 0x7fbf90110700 (LWP 12354)):
|
#0 0x00007fbf973e5aa1 in __pthread_kill (threadid=<optimized out>, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
|
__arg2 = 12354
|
_a3 = 11
|
_a1 = 12335
|
resultvar = <optimized out>
|
__arg3 = 11
|
__arg1 = 12335
|
_a2 = 12354
|
pd = <optimized out>
|
tid = 12354
|
val = <optimized out>
|
#1 0x0000556c5e03967e in handle_fatal_signal (sig=11) at /usr/src/debug/MariaDB-10.5.5/src_0/sql/signal_handler.cc:330
|
curr_time = 1599787104
|
tm = {tm_sec = 24, tm_min = 18, tm_hour = 1, tm_mday = 11, tm_mon = 8, tm_year = 120, tm_wday = 5, tm_yday = 254, tm_isdst = 0, tm_gmtoff = 0, tm_zone = 0x556c603ab540 "UTC"}
|
thd = 0x7fbf58000da8
|
print_invalid_query_pointer = false
|
#2 <signal handler called>
|
No locals.
|
#3 row_search_mvcc (buf=<optimized out>, mode=PAGE_CUR_GE, prebuilt=0x7fbf582b3430, match_mode=<optimized out>, direction=0) at /usr/src/debug/MariaDB-10.5.5/src_0/storage/innobase/row/row0sel.cc:4747
|
heap = 0x0
|
set_also_gap_locks = <optimized out>
|
err = DB_SUCCESS
|
offsets_ = {300, 0 <repeats 23 times>, 65535, 65535, 65535, 32751, 0, 0, 0, 0, 2112, 22541, 32703, 0, 65535, 65535, 65535, 32751, 3496, 22528, 32703, 0, 65535, 65535, 65535, 32751, 0, 0, 0, 0, 65535, 65535, 65535, 32751, 0, 0, 0, 16368, 29576, 22541, 32703, 0, 65535, 65535, 65535, 32751, 0, 0, 0, 0, 14544, 22541, 32703, 0 <repeats 13 times>, 56416, 36880, 32703, 0, 14544, 22541, 32703, 0, 56320, 36880, 32703, 0, 16024, 22541, 0, 0, 0, 0, 0, 0, 0, 0, 21868, 0, 56512, 22572, 32703, 0, 65533, 65535, 65535, 32751, 0, 0, 0, 16404, 0, 0, 0, 0, 62243, 37008, 30037, 16600, 0, 0, 60416, 16557, 0, 0, 0, 0, 0, 0, 0, 0, 4353, 24303, 21868, 0, 1, 0 <repeats 11 times>, 513, 0, 0, 0, 4528, 24303, 21868, 0, 0, 0, 0, 0, 0, 0, 0, 0, 55809, 36880, 32703, 0, 56160, 36880, 32703, 0, 56160, 36880, 32703, 0, 14544, 22541, 32703, 0, 2, 0, 0, 0, 3, 0, 0, 0, 56512, 22572, 32703, 0, 15976, 22541, 32703, 0, 56768, 36880, 32703, 0, 58923, 24040, 21868, 0, 16024...}
|
offsets = 0x7fbf9010d9b0
|
index = 0x7fbf58069690
|
clust_index = <optimized out>
|
mtr_has_extra_clust_latch = 0
|
moves_up = <optimized out>
|
table_lock_waited = 0
|
search_tuple = <optimized out>
|
pcur = <optimized out>
|
trx = 0x7fbf7d91c1c0
|
rec = 0x0
|
vrow = 0x0
|
unique_search = <optimized out>
|
same_user_rec = 140460732503440
|
need_vrow = <optimized out>
|
did_semi_consistent_read = false
|
comp = 1
|
clust_rec = 0x7fbf9010d600 "؍\016X\277\177"
|
next_buf = 0x0
|
spatial_search = <optimized out>
|
mtr = {m_last = 0x0, m_last_offset = 0, m_log_mode = 0, m_modifications = 0, m_made_dirty = 0, m_inside_ibuf = 0, m_freed_in_system_tablespace = 0, m_trim_pages = 0, m_memo = {m_heap = 0x0, m_list = {<ilist<mtr_buf_t::block_t, void>> = {sentinel_ = {next = 0x7fbf9010dc48, prev = 0x7fbf9010dc48}}, size_ = 1}, m_size = 0, m_first_block = {<ilist_node<void>> = {next = 0x7fbf9010dc28, prev = 0x7fbf9010dc28}, m_data = "\200\215\062^lU\000\000 \336\020\220\277\177\000\000\022 3^lU\000\000\310\344\rX\277\177\000\000\060\064+X\277\177\000\000\060\064+X\277\177\000\000x\237\aX\277\177\000\000\300\334\020\220\277\177\000\000C\275\364]lU\000\000\300\344\rX\277\177\000\000 \347\rX\277\177\000\000\260f\000X\277\177\000\000*\353`^lU\000\000\000\335\020\220\277\177\000\000\272\355\364]lU\000\000@\263\rX\277\177\000\000`\240\rX\277\177\000\000\000\335\020\220\277\177\000\000\033\354\364]lU\000\000\200\253\rX\277\177\000\000`\240\rX\006\000\000\000\060\337\020\220\277\177\000\000\252\377\347]lU\000\000\060\335\020\220\277\177\000\000\b\343\rX\277\177\000\000"..., m_used = 0}}, m_log = {m_heap = 0x0, m_list = {<ilist<mtr_buf_t::block_t, void>> = {sentinel_ = {next = 0x7fbf9010de78, prev = 0x7fbf9010de78}}, size_ = 1}, m_size = 0, m_first_block = {<ilist_node<void>> = {next = 0x7fbf9010de58, prev = 0x7fbf9010de58}, m_data = "04+X\277\177\000\000\060\064+X\277\177\000\000x\237\aX\277\177\000\000\060\064+X\277\177\000\000\002\000\000\000\000\000\000\000\220\226\006X\277\177\000\000P\022+X\277\177\000\000\320\340\020\220\277\177\000\000\ngA^lU\000\000|\000\000\000\277\177\000\000\220e\006X\277\177\000\000\000\000\000\000\277\177\000\000\000\000\000\000\000\000\000\000\003\000\000\000\000\000\000\300\370\067+X\277\177\000\000\070\314\rX\277\177\000\000\f\000\000\000\000\000\000\000\360\065+X\277\177\000\000\001", '\000' <repeats 23 times>, "\070\314\rX\277\177\000\000\r\271\361]lU\000\000\020\341\020\220\277\177\000\000\200\215\062^lU\000\000"..., m_used = 0}}, m_user_space = 0x0, m_commit_lsn = 0, m_freed_pages = 0x0}
|
result_rec = <optimized out>
|
row_sel_get_clust_rec_for_mysql = {cached_clust_rec = 0x0, cached_old_vers = 0x0}
|
next_offs = <optimized out>
|
Part of query which crashes performs a JOIN of a real InnoDB table with a temporary table generated via CTE earlier in the query. The ON condition directly compares like columns from the real table to like columns from the temporary table with =. There are four such comparisons in the ON clause, joined together via AND logic.
Note that removing any one of the comparisons from the ON conditional returns NULL and avoids a crash. Due to the nature of AND, we expect the result of all four conditions being evaluated to also be NULL. Database is crashing while attempting to evaluate a condition it does not need to return a valid result for the given query.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
On a side note, optimizer trace does not cover LATERAL DERIVED optimization at all. This is a gap in the optimizer trace and it should be fixed. (Filed MDEV-24325 for this)
Taking another look at this.
Why does LATERAL DERIVED optimization pick KEY(a), not KEY(a,b) ?
This is because column a can be "bound" by the top select with the equality "t1.a=cte.a", while column "b" cannot be.
Why does it need to do sorting at all? The subquery enumerating one GROUPBY group, so sorting should not be necessary at all?
As t1.b is not bound, the subquery will enumerate multiple GROUP BY groups.
Sorting is not necessary though, as grouping is done using temp.table (and not by the sort-then-group algorithm)
Should sorting code avoid using keys for which KEYUSE objects are disabled? Or should all KEYUSEs be enabled?
This query tries to switch from KEY(a) to KEY(a,b) while the plan to do splitting assumes KEY(a) is used.
We should either A. make adjustments to the splitting choice, or B. disallow changing the used index in such cases.
Patch implementing B: https://github.com/MariaDB/server/commit/3c564ae0e1235990794f2d0ab6c9b5760386d19f.diff
Igor, please review
Ok to push into 10.3 after addressing the notes in the review feedback sent by email in:
"Review feedback for the fix of MDEV-23723".
Questions: