Varun Gupta (Inactive) added a comment - 2019-09-16 07:52 - edited ANALYSIS Looking at the test case in the description: The WHERE clause is A.a = B.a AND A.id=65 There is a primary key on A.id, so we will only read one table, hence so A is a const table. Then we substitute the value of A.a read from the const table in the condition A.a = B.a, so the condition now becomes B.a = 65. So the join planner picked ref access for table B The key is on column B.a (gdb) p table->alias.Ptr $11 = 0x7fffe00127e0 "B" (gdb) p table->cond_selectivity $9 = 0.01 (gdb) p pos->key $14 = (KEYUSE *) 0x7fffe000aa30 (gdb) p pos->key->key $15 = 1 (gdb) p table->quick_keys.is_set(key) $12 = true (gdb) p table->quick_rows[key] $13 = 1 Discount the seletivity for ref(const). sel /= ( double )table->quick_rows[key] / ( double ) table->stat_records(); After discount, we have (gdb) p sel $16 = 1 Discount selectivity for ref(non-const) Here we again consider the case B.a = A.a (gdb) p next_field->table->alias.Ptr $26 = 0x7fffe00126c0 "A" (gdb) p next_field->field_name $27 = 0x7fffe001055c "a" (gdb) p field->field_name $30 = 0x7fffe001055c "a" (gdb) p field->table->alias.Ptr $31 = 0x7fffe00127e0 "B" (gdb) p sel $28 = 1 (gdb) p field->cond_selectivity $33 = 0.01 So here the discount happens sel/= field->cond_selectivity; (gdb) p sel $29 = 100 Then we hit the assert, as selectivity value is again absurd. So the discount should have only happened once not twice, we should make sure that selectivity for a field is discounted only once

Varun Gupta (Inactive) added a comment - 2019-09-19 14:40 Patch http://lists.askmonty.org/pipermail/commits/2019-September/014003.html

Varun Gupta (Inactive) added a comment - 2019-09-24 15:46 - edited --source include/have_sequence.inc create table t1 (id int, a int, b int, key idx1(id), key idx2(a)); insert into t1 select seq,seq, seq from seq_1_to_100; MariaDB [test]> set optimizer_use_condition_selectivity=1; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.id = B.a and A.id = 65; +------+-------------+-------+------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | A | ref | id | id | 5 | const | 1 | | | 1 | SIMPLE | B | ref | idx2 | idx2 | 5 | const | 1 | | +------+-------------+-------+------+---------------+------+---------+-------+------+-------+ 2 rows in set (0.00 sec) Now lets consider the same query with optimizer_use_condition_selectivity=2 EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.id = B.a and A.id = 65; Table A has a sargable condition A.id = 65 [index idx1] Table B has a sargable condition B.a = 65 ( because A.id = 65) [index idx2] So for cond_selectivity for table B we would have selectivity(B.a=65) = table->quick_rows [1] / table_records Now while discounting in table_cond_selectivity for table B Case 1 Check if we have a prefix of key=const that matches a quick select. Here we make the first discount for B.a = 65 , which makes sense Case 2 Go through the remaining keypart where key_part_i=const equalities and find those that were already taken into account in table->cond_selectivity.This is not required for our example as the quick select matches all the keyparts in the WHERE clause for table B Case 3 If the field f from the table is equal to a field from one the earlier joined tables then the selectivity of the range conditions over the field f must be discounted. In this case we discount the selectivity for the condition B.a = A.id. This is incorrect as we have already discounted the selecitvity for B.a=65 which is the same as B.a= A.id.

Sergei Petrunia added a comment - 2019-09-26 14:08 The patch is ok to push.

Marko Mäkelä added a comment - 2019-12-03 06:18 A larger change that addresses these problems is being worked on in MDEV-20740 .

Michael Widenius added a comment - 2022-04-13 17:26 I checked this test case against 10.7-selectivity and it works (no crash) I have added this test case to the 10.7-selectivty tree

Michael Widenius added a comment - 2025-04-01 17:41 psergei Can you please get the fix pushed to 10.11 and ensure that it is not getting into 11.4 (as this was already fixed in the 11.0 branch)