Igor Babaev (Inactive) added a comment - 2017-04-23 18:30 - edited Started parallel execution with 10.0 where the test case works fine: In Item_in_subselect::create_row_in_to_exists_cond() 10.0 line 2399: if (!abort_on_null && left_expr->element_index(i)->maybe_null) (gdb) p dbug_print_item((Item*)left_expr) $8 = 0x186e4e0 <dbug_item_print_buf> "(`test`.`t2`.`i`,`test`.`t2`.`pk`)" 10.2 line 2482 (the same): if (!abort_on_null && left_expr->element_index(i)->maybe_null) (gdb) p dbug_print_item((Item*)left_expr) $24 = 0x1cf2a20 <dbug_item_print_buf> "(t2.i,t1.pk)" The original expression is (t2.i, t2.pk). This raises the first question: who makes the substitution in 10.2 and why? Formally the substitution is ok as RIGHT JOIN is converted into INNER JOIN and ON condition (t2.pk = t1.pk) is added WHERE. The problem appears here: 10.2 (gdb) p/x ((Item*)(((Item*)left_expr)->element_index(1)))->maybe_null $25 = 0x1 It means that maybe_null flag hasn't been corrected after the conversion. Let's change the left operand of NOT IN predicate for (t2.pk = t1.pk) on 10.0: SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) WHERE (t2.i, t1.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo'; Then we have in 10.0: (gdb) p dbug_print_item((Item*)left_expr) $13 = 0x186e4e0 <dbug_item_print_buf> "(`test`.`t2`.`i`,`test`.`t1`.`pk`)" (gdb) p/x ((Item*)(((Item*)left_expr)->element_index(1)))->maybe_null $14 = 0x1 Now we can continue comparison: We came to the next statement : if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(i)))) and after stepping into get_cond_guard() we see: return pushed_cond_guards ? pushed_cond_guards + i : NULL with pushed_cond_guards == NULL in 10.2 and pushed_cond_guards != NULL in 10.0 pushed_cond_guards is set up in the caller Item_in_subselect::create_in_to_exists_cond() in the line init_cond_guards(); We have to return to this line. In function Item_in_subselect::init_cond_guards() in line if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards) we have: in 10.2 (gdb) p ((Item*) left_expr)->maybe_null $26 = false this is correct, but here pushed_cond_guards remains NULL In 10.0 we have (gdb) p ((Item*) left_expr)->maybe_null $15 = true This is incorrect, but this forces creation of pushed_cond_guards. So in 10.2 we have two bugs with maybe_null flags that leads to creation of unnecessary, but valid Item _trig_cond: MariaDB [test]> explain extended SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) WHERE (t2.i, t1.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo'; +------+--------------------+-------+--------+---------------+---------+---------+------------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ref | PRIMARY,c | c | 11 | const | 1 | 100.00 | Using index condition | | 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.pk | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t4 | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index; Using join buffer (flat, BNL join) | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+----------+-------------------------------------------------+ 4 rows in set, 1 warning (16.48 sec)   MariaDB [test]> show warnings; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i` AS `i`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i` AS `i`,`test`.`t2`.`c` AS `c` from `test`.`t` `t2` join `test`.`t` `t1` where ((`test`.`t1`.`c` = 'foo') and (`test`.`t2`.`pk` = `test`.`t1`.`pk`) and (not(<expr_cache><`test`.`t2`.`i`,`test`.`t1`.`pk`>(<in_optimizer>((`test`.`t2`.`i`,`test`.`t1`.`pk`),<exists>(select `test`.`t3`.`i`,`test`.`t3`.`i` from `test`.`t` `t3` join `test`.`t` `t4` where ((<cache>(`test`.`t2`.`i`) = `test`.`t3`.`i`) and trigcond((<cache>(`test`.`t1`.`pk`) = `test`.`t3`.`i`))))))))) | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ In 10.2 we have only one bug with the maybe_null flag, but as a result an Invalid Item_trig_cond is created and this causes a crash.

Igor Babaev (Inactive) added a comment - 2017-04-24 00:25 Here's the answer for the above question why we see (t2.i, t1.pk) instead of original (t2.i, t2.pk) in 10.2 and don't see this substitution in 10.0 when optimizing the subquery. The expression (t2.i, t1.pk) is the result of invocation of substitute_for_best_equal_field(). In 10.0 this substitution does not happen because t2.pk lacks the reference to the equality t1.pk=t2.pk. It lacks it because there is no specific implementation of equal_fields_propagator for the class Item_row. In 10.2 the code that sets such references were rewritten and now such references can be set for row elements. To avoid this difference in substitution we'll be comparing the behaviour of 10.0 and 10.2 for the query SELECT * FROM t t1 right JOIN t t2 ON (t2.pk = t1.pk) WHERE (t2.i, t1.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo';

Igor Babaev (Inactive) added a comment - 2017-04-24 22:58 Ok, the query SELECT * FROM t t1 right JOIN t t2 ON (t2.pk = t1.pk) WHERE (t2.i, t1.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo'; works fine in 10.2. So the substitution of t2.pk/t1.pk is critical to demostrate the bug. Here's what started happening after the patch that fixed the bug mdev-10454. The patch added an implementation of propagate_equal_fields() for the class Item_row and thus opened the possibility of the above substitution. At the prepare stage after setup_conds() called for WHERE condition had completed the flag of maybe_null of the Item_row object created for (t2.i, t2.pk) was set to false because the maybe_null flags of both elements were set to false. However the flag of maybe_null for t1.pk from ON condition were set to true because t1 was an inner table of an outer join. At the optimization stage the outer join was converted to inner join, but the maybe_null flags were not corrected and remained the same. So after the substitution t2.pk/t1.pk. the maybe_null flag for the row remained false while the maybe_flag for the second element of the row was true. As a result, the guards variables were not created for the elements of the row, but a guard object for the second element was created. The object were not valid because it referred to NULL as a guard variable. This ultimately caused a crash when the expression with the guard was evaluated at the execution stage. The patch made sure that the guard objects are not created without guard variables. Yet it does not resolve the problem of inconsistent maybe_null flags. and it might be that the problem will pop op in other pieces of code.

Igor Babaev (Inactive) added a comment - 2017-04-25 16:46 The fix for this bug was pushed into the 10.2 tree.