Crash when condition_pushdown_for_derived=on on insert query with subselect on a long table

2025-05-02 14:31:25 0 [Note] Starting MariaDB 11.7.2-MariaDB-ubu2404 source revision 80067a69feaeb5df30abb1bfaf7d4e713ccbf027 server_uid KjyTHYnzkWxRQBEIh5uvt04ggeY= as process 1

2025-05-02 14:31:26 0 [Note] InnoDB: Compressed tables use zlib 1.3

2025-05-02 14:31:26 0 [Note] InnoDB: Number of transaction pools: 1

2025-05-02 14:31:26 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2025-05-02 14:31:26 0 [Warning] mariadbd: io_uring_queue_init() failed with errno 0

2025-05-02 14:31:26 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF

2025-05-02 14:31:26 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB

2025-05-02 14:31:26 0 [Note] InnoDB: Completed initialization of buffer pool

2025-05-02 14:31:26 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)

2025-05-02 14:31:26 0 [Note] InnoDB: End of log at LSN=177198

2025-05-02 14:31:26 0 [Note] InnoDB: Opened 3 undo tablespaces

2025-05-02 14:31:26 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.

2025-05-02 14:31:26 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...

2025-05-02 14:31:26 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.

2025-05-02 14:31:26 0 [Note] InnoDB: log sequence number 177198; transaction id 88

2025-05-02 14:31:26 0 [Note] Plugin 'FEEDBACK' is disabled.

2025-05-02 14:31:26 0 [Note] Plugin 'wsrep-provider' is disabled.

2025-05-02 14:31:26 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2025-05-02 14:31:26 0 [Note] InnoDB: Buffer pool(s) load completed at 250502 14:31:26

2025-05-02 14:31:28 0 [Note] Server socket created on IP: '0.0.0.0'.

2025-05-02 14:31:28 0 [Note] Server socket created on IP: '::'.

2025-05-02 14:31:28 0 [Note] mariadbd: Event Scheduler: Loaded 0 events

2025-05-02 14:31:28 0 [Note] mariadbd: ready for connections.

Version: '11.7.2-MariaDB-ubu2404'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

250502 14:31:30 [ERROR] mariadbd got signal 11 ;

Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs about how to report

a bug on https://jira.mariadb.org/.

Please include the information from the server start above, to the end of the

information below.

Server version: 11.7.2-MariaDB-ubu2404 source revision: 80067a69feaeb5df30abb1bfaf7d4e713ccbf027

The information page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/

contains instructions to obtain a better version of the backtrace below.

Following these instructions will help MariaDB developers provide a fix quicker.

Attempting backtrace. Include this in the bug report.

(note: Retrieving this information may fail)

Thread pointer: 0x731870000c68

stack_bottom = 0x7318bc200000 thread_stack 0x49000

Printing to addr2line failed

mariadbd(my_print_stacktrace+0x30)[0x55cac4a60550]

mariadbd(handle_fatal_signal+0x1f3)[0x55cac45ea763]

/lib/x86_64-linux-gnu/libc.so.6(+0x45330)[0x7318c48c7330]

mariadbd(+0x6b3c7c)[0x55cac4185c7c]

mariadbd(+0x6db1a4)[0x55cac41ad1a4]

mariadbd(_ZN20Item_direct_view_ref10fix_fieldsEP3THDPP4Item+0x93)[0x55cac4620673]

mariadbd(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x8d)[0x55cac4669b3d]

mariadbd(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x11e)[0x55cac4643efe]

mariadbd(_ZN4JOIN14optimize_innerEv+0xe3b)[0x55cac433b9eb]

mariadbd(_ZN4JOIN8optimizeEv+0x10a)[0x55cac433c34a]

mariadbd(+0x7c6e55)[0x55cac4298e55]

mariadbd(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xa2)[0x55cac4292db2]

mariadbd(_ZN4JOIN14optimize_innerEv+0x81c)[0x55cac433b3cc]

mariadbd(_ZN4JOIN8optimizeEv+0x10a)[0x55cac433c34a]

mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xcf)[0x55cac433c47f]

mariadbd(_Z13handle_selectP3THDP3LEXP13select_resulty+0x17a)[0x55cac433cfaa]

mariadbd(_Z21mysql_execute_commandP3THDb+0x42be)[0x55cac42ef5fe]

mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x19a)[0x55cac42f6c2a]

mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0xb08)[0x55cac42e85f8]

mariadbd(_Z10do_commandP3THDb+0x164)[0x55cac42ea1b4]

mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x65b)[0x55cac44788ab]

mariadbd(handle_one_connection+0x71)[0x55cac4478cf1]

mariadbd(+0xd3ebde)[0x55cac4810bde]

/lib/x86_64-linux-gnu/libc.so.6(+0x9caa4)[0x7318c491eaa4]

/lib/x86_64-linux-gnu/libc.so.6(__clone+0x44)[0x7318c49aba34]

Connection ID (thread ID): 3

Status: NOT_KILLED

Query (0x731870010bb0): INSERT INTO table001(column221,column93,column230,column229)

SELECT

v.column221,v.column93,v.column230,v.column229

FROM view001 v

WHERE v.column93 <= '2025-05-03 00:00:00'

ON DUPLICATE KEY UPDATE

column221=v.column221,column93=v.column93,column230=v.column230,column229=v.column229

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=on,sargable_casefold=on

Writing a core file...

Working directory at /var/lib/mysql

Resource Limits (excludes unlimited resources):

Limit                     Soft Limit           Hard Limit           Units     

Max stack size            8388608              unlimited            bytes     

Max open files            32204                32204                files     

Max locked memory         8388608              8388608              bytes     

Max pending signals       254301               254301               signals   

Max msgqueue size         819200               819200               bytes     

Max nice priority         0                    0                    

Max realtime priority     0                    0                    

Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h

Kernel version: Linux version 6.13.7-arch1-1 (linux@archlinux) (gcc (GCC) 14.2.1 20250207, GNU ld (GNU Binutils) 2.44) #1 SMP PREEMPT_DYNAMIC Thu, 13 Mar 2025 18:12:00 +0000

2025-05-02 12:24:45 0 [Note] Starting MariaDB 10.6.19-MariaDB-ubu2004 source revision 8f020508c87461bbeee551c3001acac56d506ae7 server_uid KjyTHYnzkWxRQBEIh5uvt04ggeY= as process 1

2025-05-02 12:24:45 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2025-05-02 12:24:45 0 [Note] InnoDB: Number of pools: 1

2025-05-02 12:24:45 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2025-05-02 12:24:45 0 [Note] InnoDB: Using Linux native AIO

2025-05-02 12:24:45 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728

2025-05-02 12:24:45 0 [Note] InnoDB: Completed initialization of buffer pool

2025-05-02 12:24:45 0 [Note] InnoDB: 128 rollback segments are active.

2025-05-02 12:24:45 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2025-05-02 12:24:45 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2025-05-02 12:24:45 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2025-05-02 12:24:45 0 [Note] InnoDB: 10.6.19 started; log sequence number 874508; transaction id 652

2025-05-02 12:24:45 0 [Note] Plugin 'FEEDBACK' is disabled.

2025-05-02 12:24:45 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2025-05-02 12:24:45 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.

2025-05-02 12:24:45 0 [Note] Server socket created on IP: '0.0.0.0'.

2025-05-02 12:24:45 0 [Note] Server socket created on IP: '::'.

2025-05-02 12:24:45 0 [Note] mariadbd: ready for connections.

Version: '10.6.19-MariaDB-ubu2004'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

2025-05-02 12:24:45 0 [Note] InnoDB: Buffer pool(s) load completed at 250502 12:24:45

250502 12:25:47 [ERROR] mysqld got signal 11 ;

Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

Server version: 10.6.19-MariaDB-ubu2004 source revision: 8f020508c87461bbeee551c3001acac56d506ae7

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=1

max_threads=153

thread_count=1

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468011 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7eae4401a948

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x7eae74735d58 thread_stack 0x49000

Printing to addr2line failed

mariadbd(my_print_stacktrace+0x32)[0x5edf19ac54e2]

mariadbd(handle_fatal_signal+0x485)[0x5edf19562e75]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x14420)[0x7eae783e7420]

mariadbd(_ZNK10Item_field12type_handlerEv+0xc)[0x5edf19591f6c]

mariadbd(_ZN8Item_ref10check_colsEj+0x25)[0x5edf192acd85]

mariadbd(_ZN20Item_direct_view_ref10fix_fieldsEP3THDPP4Item+0x88)[0x5edf1958f6a8]

mariadbd(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x8c)[0x5edf195dc8fc]

mariadbd(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x11d)[0x5edf195b01ad]

mariadbd(_ZN4JOIN14optimize_innerEv+0xb2d)[0x5edf1937f96d]

mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5edf19382f10]

mariadbd(+0x79989c)[0x5edf192cc89c]

mariadbd(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0x99)[0x5edf192cc0a9]

mariadbd(_ZN4JOIN14optimize_innerEv+0xfdc)[0x5edf1937fe1c]

mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5edf19382f10]

mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xb7)[0x5edf19382fe7]

mariadbd(_Z13handle_selectP3THDP3LEXP13select_resultm+0x157)[0x5edf193837f7]

mariadbd(_Z21mysql_execute_commandP3THDb+0x4ebe)[0x5edf1931c25e]

mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x5edf19307377]

mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x10d5)[0x5edf193141b5]

mariadbd(_Z10do_commandP3THDb+0x13e)[0x5edf1931652e]

mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3b7)[0x5edf194316f7]

mariadbd(handle_one_connection+0x5d)[0x5edf19431a4d]

mariadbd(+0xc9b106)[0x5edf197ce106]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x8609)[0x7eae783db609]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7eae77fc7353]