[MDEV-31449] Assertion `s->table->opt_range_condition_rows <= s->found_records' failed in apply_selectivity_for_table Created: 2023-06-09  Updated: 2023-08-29  Resolved: 2023-06-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6, 10.9, 10.10, 10.11
Fix Version/s: 10.8.8, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 Description   

Note: the regression label and "blocker" priority are preliminary. The failure started happening after the below-mentioned commit, but since it added the assertion which is now failing, I don't know whether the underlying problem existed before. If the analysis confirms that it did, please remove the label and feel free to adjust the priority accordingly.

CREATE TABLE t1 (a INT, b INT);
 
INSERT INTO t1 VALUES (1,2),(3,4);
 
 
 
CREATE TABLE t2 (c INT);
 
INSERT INTO t2 VALUES (5),(6);
 
 
 
SET OPTIMIZER_USE_CONDITION_SELECTIVITY = 1;
 
 
 
SELECT * FROM
 
(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, 
(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

10.6 bf0a54df

 
mariadbd: /data/src/10.6/sql/sql_select.cc:7657: double apply_selectivity_for_table(JOIN_TAB*, uint, bool*): Assertion `s->table->opt_range_condition_rows <= s->found_records' failed.
 
230610  1:08:53 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f2700853df2 in __GI___assert_fail (assertion=0x555878f72700 "s->table->opt_range_condition_rows <= s->found_records", file=0x555878f6df80 "/data/src/10.6/sql/sql_select.cc", line=7657, function=0x555878f72760 "double apply_selectivity_for_table(JOIN_TAB*, uint, bool*)") at ./assert/assert.c:101
 
#10 0x000055587700a8dd in apply_selectivity_for_table (s=0x62d00019af90, use_cond_selectivity=1, force_estimate=0x7f26f911b330) at /data/src/10.6/sql/sql_select.cc:7657
 
#11 0x00005558770107c3 in best_access_path (join=0x629000277270, s=0x62d00019af90, remaining_tables=63, join_positions=0x62d00019bd88, idx=0, disable_jbuf=true, record_count=1, pos=0x62d00019bd88, loose_scan_pos=0x7f26f911b790) at /data/src/10.6/sql/sql_select.cc:8661
 
#12 0x0000555877019818 in best_extension_by_limited_search (join=0x629000277270, remaining_tables=63, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.6/sql/sql_select.cc:10350
 
#13 0x00005558770154a2 in greedy_search (join=0x629000277270, remaining_tables=63, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.6/sql/sql_select.cc:9487
 
#14 0x00005558770131b9 in choose_plan (join=0x629000277270, join_tables=63) at /data/src/10.6/sql/sql_select.cc:9047
 
#15 0x0000555876ffd00e in make_join_statistics (join=0x629000277270, tables_list=..., keyuse_array=0x629000277598) at /data/src/10.6/sql/sql_select.cc:5977
 
#16 0x0000555876fd95d0 in JOIN::optimize_inner (this=0x629000277270) at /data/src/10.6/sql/sql_select.cc:2529
 
#17 0x0000555876fd25cb in JOIN::optimize (this=0x629000277270) at /data/src/10.6/sql/sql_select.cc:1868
 
#18 0x0000555876ff3e70 in mysql_select (thd=0x62b00007e218, tables=0x62b000089aa8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x629000277240, unit=0x62b000082588, select_lex=0x62b000085410) at /data/src/10.6/sql/sql_select.cc:5069
 
#19 0x0000555876fc3d7e in handle_select (thd=0x62b00007e218, lex=0x62b0000824c0, result=0x629000277240, setup_tables_done_option=0) at /data/src/10.6/sql/sql_select.cc:559
 
#20 0x0000555876f28f4d in execute_sqlcom_select (thd=0x62b00007e218, all_tables=0x62b000089aa8) at /data/src/10.6/sql/sql_parse.cc:6273
 
#21 0x0000555876f17626 in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:3949
 
#22 0x0000555876f33ec5 in mysql_parse (thd=0x62b00007e218, rawbuf=0x62b000085238 "SELECT * FROM\n(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, \n(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2", length=182, parser_state=0x7f26f911da30) at /data/src/10.6/sql/sql_parse.cc:8036
 
#23 0x0000555876f09d0c in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x62900025d219 "SELECT * FROM\n(SELECT t1.* FROM t1 WHERE t1.a IN (SELECT MAX(t2.c) FROM t2 JOIN t1)) AS sq1, \n(SELECT t2.* FROM t2 JOIN t1 ON (t1.b IN (SELECT t1.b FROM t2 STRAIGHT_JOIN t1))) AS sq2", packet_length=182, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
 
#24 0x0000555876f06a40 in do_command (thd=0x62b00007e218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
 
#25 0x0000555877373afe in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
 
#26 0x00005558773734bf in handle_one_connection (arg=0x608000002d38) at /data/src/10.6/sql/sql_connect.cc:1318
 
#27 0x0000555877fd0866 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/10.6/storage/perfschema/pfs.cc:2201
 
#28 0x00007f27008a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#29 0x00007f27009285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible with at least MyISAM, InnoDB, Aria, on 10.6-10.11.
Also reproducible with empty tables.
Could not reproduce with the provided test case on 11.x.

The failure started happening after this commit in 10.6.13:

commit 4329ec5d3b109cb0bcbee151b5800dc7b19d1945
 
Author: Sergei Petrunia
 
Date:   Thu Mar 9 17:04:07 2023 +0300
 
 
 
    MDEV-30812: Improve output cardinality estimates for hash join
 
    
    Introduce @@optimizer_switch flag: hash_join_cardinality



 Comments   
Comment by Sergei Petrunia [ 2023-06-12 ]

A candidate fix:

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index 709a456cd9b..f9b655d5af7 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -5799,7 +5799,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 
         get_delayed_table_estimates(s->table, &s->records, &s->read_time,
 
                                     &s->startup_cost);
 
         s->found_records= s->records;
 
-        table->opt_range_condition_rows=s->records;
 
+        s->table->opt_range_condition_rows=s->records;
 
       }
 
       else
 
         s->scan_time();

The problem was a typo bug: we were assigning table->opt_range_condition_rows instead of table->opt_range_condition_rows.

The bug could show up only for queries using "delayed tables" - SJ-Materialized tables or derived tables.

Comment by Sergei Petrunia [ 2023-06-12 ]

The problem existed before the fix for MDEV-30812.

The only relationship with MDEV-30812 code is that MDEV-30812 has added an assert.

Comment by Sergei Petrunia [ 2023-06-12 ]

bb-10.6-MDEV-31449

Comment by Oleksandr Byelkin [ 2023-06-14 ]

It should be fixed in 10.5, because it has the same code.

Comment by Sergei Petrunia [ 2023-08-22 ]

ralf.gebhardt, as for the documentation: this probably doesn't need to be in the release notes as this assertion is an off-by-one error which happens in the edge case.

(edge case= the optimizer expects that the subquery produces 1 or 0 rows; then, one execution path has a "bump it up to 2 rows" where the other execution path doesn't.)

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