Thanks for the report and test case.

Results of investigation discussion:
We get to the wrong query result, because the optimizer gets Impossible where after reading const tables for the subquery.

This should not happen (as the subquery produces rows). However it does happen: IN->EXISTS rewrite changes the subquery to be

exists (select 1 from t1 where trig_cond(null=1))

The trig_cond is "enabled" at optimization phase, the optimizer sees it as constant, evaluates it, finds it to be NULL, and concludes that the subquery has "Impossible WHERE".

That is,

Subquery's left expression is NULL. This means that trig_cond should be disabled when we evaluate the subquery. However, subquery optimization code assumes it to be enabled, and produces a wrong query result.

More details:

I stop here in the subquery:

  Breakpoint 5, make_join_select (join=0x7fffcdcf15c0,...)

(gdb) p join->select_lex->select_number

  $45 = 2

(gdb) p dbug_print_item(cond)

  $48 = 0x15674c0 <dbug_item_print_buf> "trigcond(<cache>((NULL = 1)))"

(gdb) p cond->val_int()

  $49 = 0

(gdb) p cond->const_item()

  $50 = false

(gdb) p cond->used_tables()

  $51 = 0

Then, the trigcond is moved into join->exec_const_cond, and we reach this code:

        if (join->exec_const_cond && !join->exec_const_cond->is_expensive() &&

            !join->exec_const_cond->val_int())

        {

          DBUG_PRINT("info",("Found impossible WHERE condition"));

where is_expensive()=false, and cond->val_int()==0.

So, for trigcond(const_expr), we have

1. const_item() = FALSE
2. used_tables() = 0

#1 seems to be correct (as the value may change during the statement execution, depending on whether trigger is ON or OFF)

#2 seems to be incorrect. Should it be OUTER_REF_TABLE_BIT instead?

According to documentation:
trigcond(X) := TRUE when the "linked" outer expression oe_i is NULL

For our query the above condition holds "SELECT 1 from dual where NULL in (select t1 from t1)"
but the condition join->exec_const_cond->val_int() return 0 which is wrong.