[MDEV-32064] Crash when searching for the best split of derived table Created: 2023-09-01  Updated: 2023-11-28  Resolved: 2023-10-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.6.13, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 10.6.14, 10.11.5
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2

Type: Bug Priority: Critical
Reporter: Richard DEMONGEOT Assignee: Igor Babaev
Resolution: Fixed Votes: 2
Labels: regression
Environment:

Debian 11 (fresh install) + JeMalloc + MariaDB 10.6.15 or 10.11.5

Attachments: File dump.sql     File request-jira.sql    
Issue Links:
Duplicate
is duplicated by MDEV-32660 [signal 11]Running simple query with ... Closed
Problem/Incident
is caused by MDEV-26301 Split optimization refills temporary ... Closed
Relates
relates to MDEV-31440 choose_best_splitting: crash on updat... Confirmed
relates to MDEV-31818 Server crashes in choose_best_splitting Open
relates to MDEV-31403 Server crashes in st_join_table::choo... Closed

 Description   

OS: Debian 11
Server version : MariaDB 10.6.14 (packaged by MariaDB) on tom of ZFS & on test environement MariaDB 10.6.15 (fresh OS install + fresh MariaDB install) without ZFS
Tested version : MariaDB 10.11.5 packaged by MariaDB (upgrade from 10.6.15)

Some queries in production generate a segfault.
Into a lab environement, we can reproduce the segfault, even with an explain of this request.

Both production & debug environment are using jemalloc.

Stacktrace for MariaDB 10.6.15 (test server)

230901  7:21:32 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
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.15-MariaDB-1:10.6.15+maria~deb11 source revision: 0d16eb35bc981023ce2f4912e8ecde68ca381f4e
 
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 = 467985 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f71c96252d8
 
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 = 0x7f71ea252498 thread_stack 0x30000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x56488f4912ee]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x56488ef56825]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f71eab0e140]
 
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x574)[0x56488eecf504]
 
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x56488ed503ff]
 
/usr/sbin/mariadbd(+0x7f41bf)[0x56488ed541bf]
 
/usr/sbin/mariadbd(+0x7f447e)[0x56488ed5447e]
 
/usr/sbin/mariadbd(+0x7f447e)[0x56488ed5447e]
 
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x56488ed54fd4]
 
/usr/sbin/mariadbd(+0x81f3a3)[0x56488ed7f3a3]
 
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12ec)[0x56488ed8408c]
 
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x56488ed846a0]
 
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xb3)[0x56488ed84773]
 
/usr/sbin/mariadbd(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14f)[0x56488ed84f7f]
 
/usr/sbin/mariadbd(+0x7aa641)[0x56488ed0a641]
 
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x46f4)[0x56488ed197b4]
 
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x56488ed1ad67]
 
/usr/sbin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x156d)[0x56488ed1d5fd]
 
/usr/sbin/mariadbd(_Z10do_commandP3THDb+0x138)[0x56488ed1f328]
 
/usr/sbin/mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3af)[0x56488ee2e9af]
 
/usr/sbin/mariadbd(handle_one_connection+0x5d)[0x56488ee2ecfd]
 
/usr/sbin/mariadbd(+0xc43ce2)[0x56488f1a3ce2]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f71eab02ea7]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f71ea709a2f]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f71c967bb30): SELECT test.valdouble as measuredPower,  test.valint1              as csLinkId
 
FROM tbl1 test,
 
	(SELECT MAX(test.valdate) as maxDate, test.valint1
 
	FROM tbl1 test
 
	group by test.valint1) as last_entry
 
WHERE test.valint1 = last_entry.valint1
 
and test.valdate = last_entry.maxDate
 
and test.valint1 in  (
 
3289475,
 
3289496,
 
3289562,
 
3289593,
 
3289594,
 
3289595,
 
3289626,
 
3289642,
 
3289658,
 
3289739,
 
3289750,
 
3289761,
 
3289762,
 
3289763,
 
3289774,
 
3289780,
 
3289951,
 
3289957,
 
3289988,
 
3290034,
 
1231562 )
 
 
 
Connection ID (thread ID): 31
 
Status: NOT_KILLED
 
 
 
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
 
 
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /var/lib/mysql
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             15527                15527                processes 
Max open files            102400               102400               files     
Max locked memory         524288               524288               bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       15527                15527                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: core
 
 
 
Kernel version: Linux version 5.10.0-25-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.191-1 (2023-08-16)

Stacktrace for MariaDB 10.11.5 (also test server) :

230901  7:44:32 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
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.11.5-MariaDB-1:10.11.5+maria~deb11 source revision: 7875294b6b74b53dd3aaa723e6cc103d2bb47b2c
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=2
 
max_threads=153
 
thread_count=2
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468037 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f350c00e018
 
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 = 0x7f352ccc7498 thread_stack 0x30000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x55a29f356eee]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x55a29ee34365]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f352e06d140]
 
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x57c)[0x55a29edab55c]
 
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x177)[0x55a29ec21087]
 
/usr/sbin/mariadbd(+0x85fcc3)[0x55a29ec24cc3]
 
/usr/sbin/mariadbd(+0x864b90)[0x55a29ec29b90]
 
/usr/sbin/mariadbd(+0x8654b3)[0x55a29ec2a4b3]
 
/usr/sbin/mariadbd(+0x8654b3)[0x55a29ec2a4b3]
 
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2f4)[0x55a29ec2b0a4]
 
/usr/sbin/mariadbd(+0x88c082)[0x55a29ec51082]
 
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x134c)[0x55a29ec55a5c]
 
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x55a29ec56070]
 
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x55a29ec5615a]
 
/usr/sbin/mariadbd(_Z13handle_selectP3THDP3LEXP13select_resulty+0x14f)[0x55a29ec5695f]
 
/usr/sbin/mariadbd(+0x807131)[0x55a29ebcc131]
 
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x486e)[0x55a29ebdb2de]
 
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x55a29ebdc987]
 
/usr/sbin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x15dd)[0x55a29ebdf28d]
 
/usr/sbin/mariadbd(_Z10do_commandP3THDb+0x138)[0x55a29ebe0f28]
 
/usr/sbin/mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3af)[0x55a29ed02e1f]
 
/usr/sbin/mariadbd(handle_one_connection+0x5d)[0x55a29ed0316d]
 
/usr/sbin/mariadbd(+0xc95022)[0x55a29f05a022]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f352e061ea7]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f352dc68a2f]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f350c042d30): SELECT test.valdouble as measuredPower,  test.valint1              as csLinkId
 
FROM tbl1 test,
 
	(SELECT MAX(test.valdate) as maxDate, test.valint1
 
	FROM tbl1 test
 
	group by test.valint1) as last_entry
 
WHERE test.valint1 = last_entry.valint1
 
and test.valdate = last_entry.maxDate
 
and test.valint1 in  (
 
3289475,
 
3289496,
 
3289562,
 
3289593,
 
3289594,
 
3289595,
 
3289626,
 
3289642,
 
3289658,
 
3289739,
 
3289750,
 
3289761,
 
3289762,
 
3289763,
 
3289774,
 
3289780,
 
3289951,
 
3289957,
 
3289988,
 
3290034,
 
1231562 )
 
 
 
Connection ID (thread ID): 57
 
Status: NOT_KILLED
 
 
 
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
 
 
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /var/lib/mysql
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             15527                15527                processes 
Max open files            102400               102400               files     
Max locked memory         524288               524288               bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       15527                15527                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: core
 
 
 
Kernel version: Linux version 5.10.0-25-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.191-1 (2023-08-16)


bdd-test-incident:/tmp# lsb_release -a
 
No LSB modules are available.
 
Distributor ID:	Debian
 
Description:	Debian GNU/Linux 11 (bullseye)
 
Release:	11
 
Codename:	bullseye


MariaDB [CONF_V3]> show variables like '%malloc%';
 
+------------------------+------------------------------------------------------------+
 
| Variable_name          | Value                                                      |
 
+------------------------+------------------------------------------------------------+
 
| version_malloc_library | jemalloc 5.2.1-0-gea6b3e973b477b8061e0076bb257dbd7f3faa756 |
 
+------------------------+------------------------------------------------------------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [CONF_V3]>

This issue seems to be related to MDEV-31403 - mark as fixed into 10.6.14 .

Please find attached the small dump file; and a request who crash.

Usefull information :
Bug did not occurs when items into the IN are smaller than `in_predicate_conversion_threshold` ;
==> Set the `in_predicate_conversion_threshold` to 20 for reproduce it easily.

Bug did not occurs when we have no indexes on the table (tempoary tests are `create table tbl1 select * from realtable limit 1000000` => no issues)

In some case (don't know why), first request (source the text file) generate a segfault, second one (after server restart) not. In this case, juste re-import data and crash occurs again.

Bug occurs with engine InnoDB (as into production) or MyISAM.

How to reproduce : 

install a fresh MariaDB 10.6.15
 
create database test
 
use test
 
load mysqldump
 
set session in_predicate_conversion_threshold=20;
 
source request.sql



 Comments   
Comment by Daniel Black [ 2023-09-01 ]

This may have been fixed already however post release.

Edit, wasn't the case, I missed set in_predicate_conversion_threadshold=20 in the bug statement that was key to reproducing it.

Comment by Daniel Black [ 2023-09-01 ]

likewise for 10.11.6-725bd5683461119b13c12e41ce49e5c374cdeaa6

Comment by Richard DEMONGEOT [ 2023-09-01 ]

Thanks for update.

How can i build a server with this patch?
The test case was smaller than bug into production; can try to reproduce with more data - in order to verify if there still an issue with bigger dataset.

Regards

Comment by Daniel Black [ 2023-09-01 ]

Try a 10.6 container - quay.io/mariadb-foundation/mariadb-devel:10.6 (other versions may be behind due to only just rectified build fault)

/etc/apt/sources.list.d/mariadb.list for 10.6

 
deb [trusted=yes] https://ci.mariadb.org/37976/amd64-debian-10-deb-autobake/debs ./

/etc/apt/sources.list.d/mariadb.list for 10.11

 
deb [trusted=yes] https://ci.mariadb.org/37643/amd64-debian-10-deb-autobake/debs ./

Other distros/arches available on sibling addresses along with RPMs too.

Comment by Daniel Black [ 2023-09-01 ]

Forget to set set in_predicate_conversion_threshold=20; - opps, bug still there.

Comment by Daniel Black [ 2023-09-01 ]

10.11-725bd5683461119b13c12e41ce49e5c374cdeaa6

 
(gdb) bt full
 
#0  0x00000000008da0d0 in st_join_table::choose_best_splitting (this=<optimized out>, idx=2, remaining_tables=<optimized out>, join_positions=0x7f00d4061ab0, 
    spl_pd_boundary=0x7f0164065af8) at /home/dan/repos/mariadb-server-10.11/sql/opt_split.cc:1049
 
        pos = 0x7f00d40619a0
 
        last_found = 1
 
        trace_obj = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_arr = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = true}, <No data fields>}
 
        spl_opt_info = <optimized out>
 
        join = 0x7f00d404dcd0
 
        tables_usable_for_splitting = 1
 
        thd = 0x7f00d4000c68
 
        keyuse_ext = <optimized out>
 
        best_key_keyuse_ext_start = <optimized out>
 
        best_table = <optimized out>
 
        best_rec_per_key = <optimized out>
 
        spl_plan = 0x0
 
        best_key = <optimized out>
 
        best_key_parts = <optimized out>
 
        best_param_tables = <optimized out>
 
        refills = 100
 
        excluded_tables = 6
 
#1  0x0000000000788cc8 in best_access_path (join=join@entry=0x7f00d404d440, s=s@entry=0x7f00d40610e8, remaining_tables=remaining_tables@entry=2, 
    join_positions=0x7f00d4061ab0, idx=idx@entry=2, disable_jbuf=false, 
    record_count=<error reading variable: That operation is not available on integers of more than 8 bytes.>, record_count@entry=100, pos=0x7f00d4062990, 
    loose_scan_pos=0x7f00d4062aa0) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:8033
 
        loose_scan_opt = {try_loosescan = false, bound_sj_equalities = 0, handled_sj_equalities = 0, loose_scan_keyparts = 0, max_loose_keypart = 0, 
          part1_conds_met = false, quick_uses_applicable_index = 0, quick_max_loose_keypart = 0, best_loose_scan_key = 0, best_loose_scan_cost = 1.7976931348623157e+308, 
          best_loose_scan_records = 0, best_loose_scan_start_key = 0x0, best_max_loose_keypart = 0, best_ref_depend_map = 0}
 
        trace_wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_paths = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0x7f0164065eb0, 
            my_writer = 0x879e61 <optimize_semi_joins(JOIN*, unsigned long long, unsigned int, double*, double*, POSITION*)+801>, context = {writer = 0x7f0164065de8}, 
            closed = 64}, <No data fields>}
 
        trace_access_scan = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0x7f0164065da8, my_writer = 0x2, context = {writer = 0x7f0164065fa8}, 
            closed = 64}, <No data fields>}
 
        thd = 0x7f00d4000c68
 
        best_key = 0x0
 
        best_max_key_part = 0
 
        found_constraint = 0 '\000'
 
        best = 1.7976931348623157e+308
 
        best_time = 1.7976931348623157e+308
 
        records = 1.7976931348623157e+308
 
        records_for_key = 0
 
        best_ref_depends_map = 0
 
        key_dependent = 0
 
        best_filter = 0x0
 
        keyread_tmp = 0
 
        best_uses_jbuf = false
 
        use_cond_selectivity = 4
 
        eq_join_set = <optimized out>
 
        hj_start_key = 0x0
 
        spl_plan = 0x0
 
        spl_pd_boundary = 0
 
        filter = 0x0
 
        cause = 0x0
 
        best_type = JT_UNKNOWN
 
        type = JT_UNKNOWN
 
        rec = <optimized out>
 
        tmp = <optimized out>
 
#2  0x00000000007a8b61 in get_costs_for_tables (join=join@entry=0x7f00d404d440, remaining_tables=remaining_tables@entry=2, idx=2, 
    record_count=<error reading variable: That operation is not available on integers of more than 8 bytes.>, record_count@entry=100, trace_one_table=<optimized out>, 
    pos=pos@entry=0x7f00d4061a70, store_position=store_position@entry=0x7f0164065f48, allowed_tables=allowed_tables@entry=0x7f0164065fa8, stop_on_eq_ref=<optimized out>)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:10369
 
        wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        real_table_bit = <optimized out>
 
        sort_position = 0x7f00d4062990
 
        thd = 0x7f00d4000c68
 
        sort_end = 0x7f0164065ec0
 
        found_tables = 0
 
        found_eq_ref = false
 
        s = 0x7f00d40610e8
 
        disable_jbuf = <optimized out>
 
#3  0x00000000007a78a9 in best_extension_by_limited_search (join=join@entry=0x7f00d404d440, remaining_tables=remaining_tables@entry=2, idx=2, 
    record_count=<error reading variable: That operation is not available on integers of more than 8 bytes.>, record_count@entry=100, 
    read_time=<error reading variable: That operation is not available on integers of more than 8 bytes.>, search_depth=search_depth@entry=60, 
    use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x7f01640660d8)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:10628
 
        table_map = <optimized out>
 
        trace_one_table = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        arr = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        best_ref = 0x7f00d4061a70
 
        thd = 0x7f00d4000c68
 
        best_record_count = 1.7976931348623157e+308
 
        best_read_time = 1.7976931348623157e+308
 
        tables_left = <optimized out>
 
        found_eq_ref_tables = 0
 
        used_eq_ref_table = 0
 
        sort = 0x7f0164065ec0
 
        allowed_tables = 2
 
        sort_end = 0x7f0164065ec0
 
        found_tables = <optimized out>
 
        accepted_tables = <optimized out>
 
        min_rec_count = <optimized out>
 
        min_rec_count_read_time = <optimized out>
 
        min_cost = <optimized out>
 
        min_cost_record_count = <optimized out>
 
        best_res = <optimized out>
 
        s = <optimized out>
 
        allowed_current_tables = <optimized out>
 
#4  0x00000000007a8517 in best_extension_by_limited_search (join=join@entry=0x7f00d404d440, remaining_tables=remaining_tables@entry=6, idx=1, 
    record_count=<error reading variable: That operation is not available on integers of more than 8 bytes.>, record_count@entry=100, 
    read_time=<error reading variable: That operation is not available on integers of more than 8 bytes.>, search_depth=search_depth@entry=61, 
    use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x7f0164066298)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:10832
 
        trace_rest = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_one_table = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        filter_cmp_gain = <optimized out>
 
        pushdown_cond_selectivity = <error reading variable pushdown_cond_selectivity (That operation is not available on integers of more than 8 bytes.)>
 
        real_table_bit = <optimized out>
 
        position = <optimized out>
 
        current_record_count = <optimized out>
 
        current_read_time = 1.7976931348623157e+308
 
        partial_join_cardinality = <optimized out>
 
        loose_scan_pos = <optimized out>
 
        pos = 0x7f0164066070
 
        thd = 0x7f00d4000c68
 
        best_record_count = 100
 
        best_read_time = 48.049999999999997
 
        tables_left = <optimized out>
 
        found_eq_ref_tables = <optimized out>
 
        used_eq_ref_table = 0
 
        sort = <optimized out>
 
        allowed_tables = 6
 
        sort_end = 0x7f0164066080
 
        found_tables = <optimized out>
 
        accepted_tables = <optimized out>
 
        min_rec_count = 100
 
        min_rec_count_read_time = 48.049999999999997
 
        min_cost = 48.049999999999997
 
        min_cost_record_count = 100
 
        best_res = <optimized out>
 
        s = 0x7f00d40614b0
 
        allowed_current_tables = <optimized out>
 
#5  0x00000000007a8517 in best_extension_by_limited_search (join=0x7f00d404d440, remaining_tables=remaining_tables@entry=7, idx=idx@entry=0, 
    record_count=<error reading variable: That operation is not available on integers of more than 8 bytes.>, 
    read_time=<error reading variable: That operation is not available on integers of more than 8 bytes.>, search_depth=search_depth@entry=62, 
    use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x7f01640663d0)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:10832
 
        trace_rest = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_one_table = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        filter_cmp_gain = <optimized out>
 
        pushdown_cond_selectivity = <error reading variable pushdown_cond_selectivity (That operation is not available on integers of more than 8 bytes.)>
 
        real_table_bit = <optimized out>
 
        position = <optimized out>
 
        current_record_count = <optimized out>
 
        current_read_time = 1.7976931348623157e+308
 
        partial_join_cardinality = <optimized out>
 
        loose_scan_pos = <optimized out>
 
        pos = 0x7f0164066210
 
        thd = 0x7f00d4000c68
 
        best_record_count = 1.7976931348623157e+308
 
        best_read_time = 1.7976931348623157e+308
 
        tables_left = <optimized out>
 
        found_eq_ref_tables = <optimized out>
 
        used_eq_ref_table = 0
 
        sort = <optimized out>
 
        allowed_tables = 7
 
        sort_end = 0x7f0164066240
 
        found_tables = <optimized out>
 
        accepted_tables = <optimized out>
 
        min_rec_count = 1.7976931348623157e+308
 
        min_rec_count_read_time = 1.7976931348623157e+308
 
        min_cost = 1.7976931348623157e+308
 
        min_cost_record_count = 1.7976931348623157e+308
 
        best_res = <optimized out>
 
        s = 0x7f00d4060d20
 
        allowed_current_tables = <optimized out>
 
#6  0x000000000078c66b in greedy_search (join=0x7f00d404d440, remaining_tables=7, search_depth=62, use_cond_selectivity=4)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:9615
 
        pos = <optimized out>
 
        best_pos = {table = <optimized out>, records_read = 0, cond_selectivity = 0, read_time = 7.4485139140767993e-317, prefix_record_count = 0, prefix_cost = 0, 
          key = <optimized out>, partial_join_cardinality = <optimized out>, spl_plan = <optimized out>, spl_pd_boundary = 139641533895856, 
          range_rowid_filter_info = 0x7f00d404dcd0, ref_depend_map = 15664469735422044928, key_dependent = 14219059, dups_producing_tables = 3, 
          inner_tables_handled_with_other_sjs = 0, dups_weedout_picker = {<Semi_join_strategy_picker> = {<No data fields>}, first_dupsweedout_table = <optimized out>, 
            dupsweedout_tables = <optimized out>, is_used = <optimized out>}, firstmatch_picker = {<Semi_join_strategy_picker> = {<No data fields>}, 
            first_firstmatch_table = <optimized out>, first_firstmatch_rtbl = 0, firstmatch_need_tables = 64750755155836672, is_used = <optimized out>}, 
          loosescan_picker = {<Semi_join_strategy_picker> = {<No data fields>}, first_loosescan_table = <optimized out>, loosescan_need_tables = 32512, 
            loosescan_key = 32512, loosescan_parts = 15075840, is_used = <optimized out>}, sjmat_picker = {<Semi_join_strategy_picker> = {<No data fields>}, 
            is_used = <optimized out>, sjm_scan_last_inner = <optimized out>, sjm_scan_need_tables = <optimized out>}, sj_strategy = 3557188833, type = 32512, 
          n_sj_tables = 3557188920, use_join_buffer = false}
 
        record_count = 1
 
        read_time = 0
 
        idx = 0
 
        usable_tables = 7
 
        size_remain = <optimized out>
 
        n_tables = <optimized out>
 
        best_table = <optimized out>
 
        best_idx = <optimized out>
 
        eq_ref_tables = <optimized out>
 
        pos = <optimized out>
 
        is_interleave_error = <optimized out>
 
        dummy = <optimized out>
 
#7  choose_plan (join=join@entry=0x7f00d404d440, join_tables=join_tables@entry=7) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:9181
 
        wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_plan = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        use_cond_selectivity = 4
 
        search_depth = 62
 
        thd = 0x7f00d4000c68
 
        jtab_sort_func = <optimized out>
 
        straight_join = <optimized out>
 
#8  0x0000000000778b86 in make_join_statistics (join=join@entry=0x7f00d404d440, 
    tables_list=@0x7f00d4013730: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f00d404e958, last = 0x7f00d405c8b8, elements = 3}, <No data fields>}, 
    keyuse_array=keyuse_array@entry=0x7f00d404d790) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:6036
 
        records = 1
 
        unit = 0x7f00d4004f20
 
        error = <optimized out>
 
        table = <optimized out>
 
        outer_join = <optimized out>
 
        no_rows_const_tables = <optimized out>
 
        sargables = <optimized out>
 
        ti = <optimized out>
 
        thd = 0x7f00d4000c68
 
        table_count = 3
 
        sort_space = <optimized out>
 
        table_vector = <optimized out>
 
        stat = <optimized out>
 
        stat_ref = <optimized out>
 
        stat_vector = <optimized out>
 
        i = <optimized out>
 
        stat_end = 0x7f00d4061878
 
        all_table_map = <optimized out>
 
        found_const_table_map = <optimized out>
 
        const_count = <optimized out>
 
        s = 0x7f00d4061878
 
        tables = <optimized out>
 
        keyuse = <optimized out>
 
        start_keyuse = <optimized out>
 
        const_ref = <optimized out>
 
        has_expensive_keyparts = <optimized out>
 
        key = <optimized out>
 
        eq_part = <optimized out>
 
        ref_changed = <optimized out>
 
#9  0x0000000000774d05 in JOIN::optimize_inner (this=this@entry=0x7f00d404d440) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:2561
 
        trace_wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_prepare = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a78 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        trace_steps = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe60a00 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, 
            closed = false}, <No data fields>}
 
        eq_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1690358 <end_of_list>, last = 0x7f0164066950, elements = 0}, <No data fields>}
 
        sel = <optimized out>
 
        ignore_on_expr = <optimized out>
 
#10 0x000000000077252c in JOIN::optimize (this=this@entry=0x7f00d404d440) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:1895
 
        res = 0
 
        init_state = JOIN::NOT_OPTIMIZED
 
#11 0x000000000076c977 in mysql_select (thd=thd@entry=0x7f00d4000c68, tables=<optimized out>, 
    fields=@0x7f00d40137d0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f00d4013b10, last = 0x7f00d4013c80, elements = 2}, <No data fields>}, 
    conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f00d404d418, 
    unit=0x7f00d4004f20, select_lex=0x7f00d4013518) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:5106
 
        err = <optimized out>
 
        free_join = true
 
        join = 0x7f00d404d440
 
#12 0x000000000076c84f in handle_select (thd=thd@entry=0x7f00d4000c68, lex=lex@entry=0x7f00d4004e48, result=result@entry=0x7f00d404d418, 
    setup_tables_done_option=setup_tables_done_option@entry=0) at /home/dan/repos/mariadb-server-10.11/sql/sql_select.cc:586
 
        unit = 0x7f00d4004f20
 
        select_lex = 0x7f00d4013518
 
        res = <optimized out>
 
#13 0x000000000073d07a in execute_sqlcom_select (thd=thd@entry=0x7f00d4000c68, all_tables=0x7f00d4013cf8) at /home/dan/repos/mariadb-server-10.11/sql/sql_parse.cc:6288
 
        save_protocol = 0x0
 
        lex = 0x7f00d4004e48
 
        result = 0x7f00d404d418
 
        res = <optimized out>
 
#14 0x0000000000737f75 in mysql_execute_command (thd=thd@entry=0x7f00d4000c68, is_called_from_prepared_stmt=<optimized out>)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_parse.cc:3959
 
        privileges_requested = <optimized out>
 
        ots = {ctx = 0x7f00d4004ae8, traceable = false}
 
        res = 1
 
        up_result = 0
 
        lex = 0x7f00d4004e48
 
        select_lex = 0x7f00d4013518
 
        first_table = 0x7f00d4013cf8
 
        unit = 0x7f00d4004f20
 
        have_table_map_for_update = <optimized out>
 
        all_tables = 0x0
 
        rpl_filter = <optimized out>
 
        orig_binlog_format = <optimized out>
 
        orig_current_stmt_binlog_format = <optimized out>
 
        error = <optimized out>
 
        wsrep_error_label = <optimized out>
 
#15 0x0000000000733e9a in mysql_parse (thd=thd@entry=0x7f00d4000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f0164067550)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_parse.cc:8033
 
        found_semicolon = <optimized out>
 
        error = <optimized out>
 
        lex = 0x7f00d4004e48
 
        err = <optimized out>
 
#16 0x00000000007324d5 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f00d4000c68, 
    packet=packet@entry=0x7f00d40086b9 "SELECT test.valdouble as measuredPower,  test.valint1", ' ' <repeats 14 times>, "as csLinkId\nFROM tbl1 test,\n\t(SELECT MAX(test.valdate) as maxDate, test.valint1\n\tFROM tbl1 test\n\tgroup by test.valint1) as last_entry"..., packet_length=packet_length@entry=491, blocking=true)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_parse.cc:1894
 
        parser_state = {m_lip = {lookahead_token = -1, lookahead_yylval = 0x0, m_thd = 0x7f00d4000c68, m_ptr = 0x7f00d40132cc "\004", m_tok_start = 0x7f00d40132cc "\004", 
            m_tok_end = 0x7f00d40132cc "\004", m_end_of_query = 0x7f00d40132cb "", m_tok_start_prev = 0x7f00d40132cb "", 
            m_buf = 0x7f00d40130e0 "SELECT test.valdouble as measuredPower,  test.valint1", ' ' <repeats 14 times>, "as csLinkId\nFROM tbl1 test,\n\t(SELECT MAX(test.valdate) as maxDate, test.valint1\n\tFROM tbl1 test\n\tgroup by test.valint1) as last_entry"..., m_buf_length = 491, m_echo = true, m_echo_saved = false, 
            m_cpp_buf = 0x7f00d4013328 "SELECT test.valdouble as measuredPower,  test.valint1", ' ' <repeats 14 times>, "as csLinkId\nFROM tbl1 test,\n\t(SELECT MAX(test.valdate) as maxDate, test.valint1\n\tFROM tbl1 test\n\tgroup by test.valint1) as last_entry"..., m_cpp_ptr = 0x7f00d4013513 "", m_cpp_tok_start = 0x7f00d4013513 "", 
            m_cpp_tok_start_prev = 0x7f00d4013513 "", m_cpp_tok_end = 0x7f00d4013513 "", m_body_utf8 = 0x0, m_body_utf8_ptr = 0x0, m_cpp_utf8_processed_ptr = 0x0, 
            next_state = MY_LEX_END, found_semicolon = 0x0, ignore_space = false, stmt_prepare_mode = false, multi_statements = true, yylineno = 29, m_digest = 0x0, 
            in_comment = NO_COMMENT, in_comment_saved = NO_COMMENT, m_cpp_text_start = 0x7f00d401350a "1231562 )", m_cpp_text_end = 0x7f00d4013511 " )", 
            m_underscore_cs = 0x0}, m_yacc = {yacc_yyss = 0x0, yacc_yyvs = 0x0, m_set_signal_info = {m_item = {0x0 <repeats 13 times>}}, m_lock_type = TL_READ_DEFAULT, 
            m_mdl_type = MDL_SHARED_READ}, m_digest_psi = 0x0}
 
        packet_end = <optimized out>
 
        net = <optimized out>
 
        error = false
 
        do_end_of_statement = true
 
        drop_more_results = false
 
#17 0x00000000007342e0 in do_command (thd=0x7f00d4000c68, blocking=true) at /home/dan/repos/mariadb-server-10.11/sql/sql_parse.cc:1407
 
        packet = 0x7f00d40086b8 "\003SELECT test.valdouble as measuredPower,  test.valint1", ' ' <repeats 14 times>, "as csLinkId\nFROM tbl1 test,\n\t(SELECT MAX(test.valdate) as maxDate, test.valint1\n\tFROM tbl1 test\n\tgroup by test.valint1) as last_entr"...
 
        net = <optimized out>
 
        command = COM_QUERY
 
        packet_length = 492
 
        return_value = <optimized out>
 
#18 0x0000000000842f9e in do_handle_one_connection (connect=<optimized out>, connect@entry=0x2723288, put_in_cache=true)
 
    at /home/dan/repos/mariadb-server-10.11/sql/sql_connect.cc:1416
 
        create_user = true
 
        thr_create_utime = <optimized out>
 
        thd = 0x0
 
#19 0x0000000000842dbb in handle_one_connection (arg=arg@entry=0x2723288) at /home/dan/repos/mariadb-server-10.11/sql/sql_connect.cc:1318
 
        connect = 0x2723288
 
#20 0x0000000000b39fee in pfs_spawn_thread (arg=0x26d2c38) at /home/dan/repos/mariadb-server-10.11/storage/perfschema/pfs.cc:2201
 
        typed_arg = 0x26d2c38
 
        klass = <optimized out>
 
        pfs = <optimized out>
 
        user_start_routine = 0x842d40 <handle_one_connection(void*)>
 
        user_arg = 0x2723288
 
#21 0x00007f0166eae907 in start_thread () from /lib64/libc.so.6
 
No symbol table info available.
 
#22 0x00007f0166f34870 in clone3 () from /lib64/libc.so.6
 
No symbol table info available.
 
(gdb) list
 
1044	    else
 
1045	    {
 
1046	      table_map last_found= this->table->map;
 
1047	      for (const POSITION *pos= &join_positions[idx - 1]; ; pos--)
 
1048	      {
 
1049	        if (pos->table->table->map & excluded_tables)
 
1050	          continue;
 
1051	        if (pos->partial_join_cardinality < refills)
 
1052		{
 
1053	          *spl_pd_boundary= last_found;
 
(gdb) p pos->table
 
$1 = (JOIN_TAB *) 0x0

Comment by Alice Sherepa [ 2023-09-01 ]

The failure started happening after this commit in 10.4.29:

commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb
 
Author: Igor Babaev
 
Date:   Tue May 2 23:17:07 2023 -0700
 
 
    MDEV-26301 Split optimization refills temporary table too many times

coping the test case from added files here:

set session in_predicate_conversion_threshold=20;
 
 
 
CREATE TABLE tbl1 (
 
  ID int unsigned NOT NULL,
 
  valint1 int  unsigned,
 
  valdouble double,
 
  valdate datetime,
 
  PRIMARY KEY (ID),
 
  KEY  (valint1),
 
  KEY  (valint1,valdate)
 
);
 
 
 
INSERT INTO tbl1 VALUES (1,3289763,1,'2021-02-09 18:31:35'),(2,3289750,1,'2021-02-09 18:31:35'),(3,3289780,1173,'2021-02-09 18:31:35'),(4,3289762,2,'2021-02-09 18:31:36'),(5,3289774,2334,'2021-02-09 18:31:36'),(6,3289739,1934,'2021-02-09 18:31:36'),(7,3289761,1,'2021-02-09 18:31:37'),(8,3289763,1,'2021-02-10 11:05:19'),(9,3289750,1,'2021-02-10 11:05:19'),(10,3289780,0,'2021-02-10 11:05:35'),(11,3289762,2,'2021-02-10 11:05:47'),(12,3289774,429,'2021-02-10 11:05:47'),(13,3289739,1958,'2021-02-10 11:06:00'),(14,3289761,1,'2021-02-10 11:06:08'),(15,3289957,0,'2021-02-10 13:04:44'),(16,3289988,1993,'2021-02-10 13:04:45'),(17,3289951,1896,'2021-02-10 13:04:59'),(18,3289957,1994,'2021-02-10 13:07:40'),(19,3289988,5,'2021-02-10 13:07:40'),(20,3289951,1897,'2021-02-10 13:07:40'),(21,3289594,0,'2021-02-11 14:19:38'),(22,3289642,0,'2021-02-11 14:19:38'),(23,3289626,2150,'2021-02-11 14:19:38'),(24,3289562,0,'2021-02-11 14:19:39'),(25,3289593,1046,'2021-02-11 14:19:39'),(26,3289496,1,'2021-02-11 14:19:45'),(27,3289475,1074,'2021-02-11 14:19:50'),(28,3289658,1155,'2021-02-11 14:19:56'),(29,3289595,0,'2021-02-11 14:20:01'),(30,3290334,903,'2021-02-11 16:22:44'),(31,3290284,479,'2021-02-11 16:23:00'),(32,3290327,236,'2021-02-11 16:23:00'),(33,3290854,0,'2021-02-15 17:29:59'),(34,3290824,0,'2021-02-15 17:30:13'),(35,3290875,0,'2021-02-15 17:30:14'),(36,3290897,2,'2021-02-15 17:30:19'),(37,3290800,0,'2021-02-15 17:30:24'),(38,3290822,0,'2021-02-15 17:30:25'),(39,3290901,2667,'2021-02-15 17:30:30'),(40,3290835,0,'2021-02-15 17:30:36'),(41,3290875,0,'2021-02-15 17:35:33'),(42,3290824,1330,'2021-02-15 17:35:39'),(43,3290854,769,'2021-02-15 17:35:44'),(44,3290897,2,'2021-02-15 17:35:50'),(45,3290822,748,'2021-02-15 17:35:50'),(46,3290800,1007,'2021-02-15 17:35:56'),(47,3290901,7018,'2021-02-15 17:35:56'),(48,3290835,779,'2021-02-15 17:36:17'),(49,3290824,1329,'2021-02-15 17:40:30'),(50,3290875,764,'2021-02-15 17:40:31'),(51,3290854,763,'2021-02-15 17:40:36'),(52,3290897,2347,'2021-02-15 17:40:47'),(53,3290822,1,'2021-02-15 17:41:01'),(54,3290800,1018,'2021-02-15 17:41:07'),(55,3290901,3936,'2021-02-15 17:41:08'),(56,3290835,784,'2021-02-15 17:41:24'),(57,3290824,1313,'2021-02-15 17:44:47'),(58,3290875,758,'2021-02-15 17:44:48'),(59,3290854,767,'2021-02-15 17:44:48'),(60,3290897,2438,'2021-02-15 17:44:48'),(61,3290822,738,'2021-02-15 17:44:49'),(62,3290800,1003,'2021-02-15 17:44:54'),(63,3290901,4686,'2021-02-15 17:44:55'),(64,3290835,778,'2021-02-15 17:45:13'),(65,3290824,1303,'2021-02-15 17:51:16'),(66,3290875,753,'2021-02-15 17:51:16'),(67,3290854,766,'2021-02-15 17:51:22'),(68,3290897,1,'2021-02-15 17:51:22'),(69,3290822,743,'2021-02-15 17:51:28'),(70,3290901,5718,'2021-02-15 17:51:33'),(71,3290800,1018,'2021-02-15 17:51:34'),(72,3290835,785,'2021-02-15 17:51:48'),(73,3290824,1310,'2021-02-15 18:21:30'),(74,3290875,754,'2021-02-15 18:21:30'),(75,3290854,782,'2021-02-15 18:21:36'),(76,3290897,2,'2021-02-15 18:21:36'),(77,3290822,745,'2021-02-15 18:21:53'),(78,3290800,1011,'2021-02-15 18:21:54'),(79,3290901,8998,'2021-02-15 18:21:54'),(80,3290835,0,'2021-02-15 18:22:00'),(81,3290936,0,'2021-02-15 18:25:28'),(82,3290895,0,'2021-02-15 18:25:28'),(83,3290832,0,'2021-02-15 18:25:28'),(84,3290878,796,'2021-02-15 18:25:52'),(85,3290900,730,'2021-02-15 18:25:52'),(86,3290856,0,'2021-02-15 18:26:11'),(87,3290904,816,'2021-02-15 18:26:17'),(88,3290882,0,'2021-02-15 18:26:25'),(89,3290883,1031,'2021-02-15 18:27:16'),(90,3290918,1749,'2021-02-15 18:27:17'),(91,3290831,0,'2021-02-15 18:59:11'),(92,3290884,477,'2021-02-15 18:59:12'),(93,3290899,483,'2021-02-15 18:59:12'),(94,3290848,486,'2021-02-15 18:59:35'),(95,3290880,487,'2021-02-15 18:59:35'),(96,3290798,0,'2021-02-15 18:59:52'),(97,3290777,983,'2021-02-15 19:00:10'),(98,3290811,488,'2021-02-15 19:00:10'),(99,3290917,1283,'2021-02-15 19:00:36'),(100,3290858,482,'2021-02-15 19:00:42');
 
 
 
SELECT test.valdouble,
 
       test.valint1
 
FROM tbl1 test,
 
 (SELECT max(test.valdate) AS maxdate, test.valint1 FROM tbl1 test GROUP BY test.valint1) AS last_entry
 
WHERE test.valint1 = last_entry.valint1
 
 AND test.valdate = last_entry.maxdate
 
 AND test.valint1  IN  (3289475,3289496,3289562,3289593,3289594,3289595,3289626,3289642,3289658,3289739,3289750,3289761,3289762,3289763,3289774,3289780,3289951,3289957,3289988,3290034,1231562 );

Comment by Sergei Petrunia [ 2023-10-11 ]

As mentioned above,

The failure started happening after this commit in 10.4.29:

commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb
Author: Igor Babaev
Date: Tue May 2 23:17:07 2023 -0700

Comment by Igor Babaev [ 2023-10-12 ]

The following query causes the same kind of crash. This query does not require conversion from IN predicate into IN subquery, rather it uses IN subquery directly:

SELECT test.valdouble, test.valint1  
FROM tbl1 test,
 
         (SELECT max(test.valdate) AS maxdate, test.valint1 FROM tbl1 test GROUP BY test.valint1) AS last_entry  WHERE test.valint1 = last_entry.valint1   AND test.valdate = last_entry.maxdate   AND
 
            test.valint1  IN  (select a from t0);

Here t0 has been defined and filled by the commands:

create table t0 (a int);
 
 insert into t0 values
 
(3289475),(3289496),(3289562),(3289593),(3289594),(3289595),(3289626),
 
(3289642),(3289658),(3289739),(3289750),(3289761),(3289762),(3289763),
 
(3289774),(3289780),(3289951),(3289957),(3289988),(3290034),(1231562);

Comment by Oleksandr Byelkin [ 2023-10-16 ]

OK to push

Comment by Igor Babaev [ 2023-10-17 ]

A fix for this bug was pushed into 10.4. It should be merged upstream as it is.

Generated at Thu Feb 08 10:28:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.