Mariadb crashes with query

CREATE TABLE `table1` (

`id_product`int(11),

`id_stock`varchar(32),

`quantity` mediumint(9),

PRIMARY KEY (`id_product`,`id_stock`)

);

CREATE TABLE `table2` (

`id_product`int(10) unsigned,

`id_shop`int(10) unsigned,

PRIMARY KEY (`id_product`,`id_shop`)

) ;

SELECT backups.id_product, quantity_backup, quantity_bl

FROM (

SELECT id_product, SUM(quantity) as quantity_backup

FROM`table1`

) backups

INNER JOIN (

SELECT id_product

FROM`table1`

GROUP BY id_product

) normals ON (normals.id_product = backups.id_product)

LEFT JOIN (

SELECT quantity as quantity_bl, id_product FROM table1

) bl ON (backups.id_product = bl.id_product )

WHERE

backups.id_product IN (

SELECT id_product

FROM table2

);

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: 231030 13:35:20 [ERROR] mysqld got signal 11 ;

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: This could be because you hit a bug. It is also possible that this binary

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: or one of the libraries it was linked against is corrupt, improperly built,

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: or misconfigured. This error can also be caused by malfunctioning hardware.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: We will try our best to scrape up some info that will hopefully help

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: diagnose the problem, but since we have already crashed,

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: something is definitely wrong and this may fail.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Server version: 10.11.5-MariaDB source revision: 7875294b6b74b53dd3aaa723e6cc103d2bb47b2c

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: key_buffer_size=134217728

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: read_buffer_size=131072

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: max_used_connections=1

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: max_threads=153

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: thread_count=1

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: It is possible that mysqld could use up to

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468037 K  bytes of memory

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Hope that's ok; if not, decrease some variables in the equation.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Thread pointer: 0x7f0500000c58

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Attempting backtrace. You can use the following information to find out

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: where mysqld died. If you see no messages after this, something went

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: terribly wrong...

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: stack_bottom = 0x7f05400cdd20 thread_stack 0x49000

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: mysys/stacktrace.c:216(my_print_stacktrace)[0x55ffb66a0b6e]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/signal_handler.cc:241(handle_fatal_signal)[0x55ffb607e4c7]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f0557744140]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x55ffb5ff0e04]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:8028(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55ffb5e9c4a1]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:10364(get_costs_for_tables(JOIN*, unsigned long long, unsigned int, double, Json_writer_object*, st_join_table**, SORT_POSITION**, unsigned long long*, bool) [clone .isra.498])[0x55ffb5e9fb80]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:10544(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x55ffb5e9ff13]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:10833(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x55ffb5ea0a79]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:10833(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x55ffb5ea0a79]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:9607(choose_plan(JOIN*, unsigned long long))[0x55ffb5ea10d7]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:6028(JOIN::optimize_inner())[0x55ffb5ea9fa3]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:1897(JOIN::optimize())[0x55ffb5eaa662]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:5098(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55ffb5eaa74e]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_select.cc:598(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x55ffb5eab054]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_parse.cc:6290(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ffb5cd19e6]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_parse.cc:3959(mysql_execute_command(THD*, bool))[0x55ffb5e3b9d0]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_parse.cc:8035(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55ffb5e3dd8b]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_parse.cc:1953(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55ffb5e401c8]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55ffb5e416f3]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55ffb5f4d5e7]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: sql/sql_connect.cc:1324(handle_one_connection)[0x55ffb5f4d884]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55ffb62d0d6c]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f0557738ea7]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f055733fa2f]

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Trying to get some variables.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Some pointers may be invalid and cause the dump to abort.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Query (0x7f05000109d0): SELECT backups.id_product, quantity_backup, quantity_bl

Oct 30 13:35:20visua mysqld[1226516]: FROM (

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: SELECT id_product, SUM(quantity) as quantity_backup

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: FROM`table1`

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: ) backups

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: INNER JOIN (

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: SELECT id_product

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: FROM`table1`

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: GROUP BY id_product

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: ) normals ON (normals.id_product = backups.id_product)

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: LEFT JOIN (

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: SELECT quantity as quantity_bl, id_product FROM table1

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: ) bl ON (backups.id_product = bl.id_product )

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: WHERE

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: backups.id_product IN (

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: SELECT id_product

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: FROM table2

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: )

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Connection ID (thread ID): 3

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Status: NOT_KILLED

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=off

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: information that should help you find out what is causing the crash.

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Writing a core file...

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Working directory at /var/lib/mysql

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Resource Limits:

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Limit                     Soft Limit           Hard Limit           Units

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max cpu time              unlimited            unlimited            seconds

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max file size             unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max data size             unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max stack size            8388608              unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max core file size        unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max resident set          unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max processes             63896                63896                processes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max open files            655350               655350               files

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max locked memory         unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max address space         unlimited            unlimited            bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max file locks            unlimited            unlimited            locks

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max pending signals       63896                63896                signals

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max msgqueue size         819200               819200               bytes

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max nice priority         0                    0

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max realtime priority     0                    0

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Max realtime timeout      unlimited            unlimited            us

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h %e

Oct 30 13:35:20 testing.server.tld mysqld[1226516]: Kernel version: Linux version 5.10.0-21-amd64 (debian-kernel@lists.debian.org) (gcc-10 (Debian 10.2.1-6) 10.2.1 20210110, GNU ld (GNU Binutils for Debian) 2.35.2) #1 SMP Debian 5.10.162-1 (2023-01-21)

Oct 30 13:35:20 testing.server.tld systemd[1]: mysqld.service: Main process exited, code=killed, status=11/SEGV

Oct 30 13:35:20 testing.server.tld systemd[1]: mysqld.service: Failed with result 'signal'.