Hitting assertions is quite a bit pain. There's quite a few confirmed assertions that aren't fixed: search 'text ~ "Assertion" and status=CONFIRMED'

Situation is like this: Item_field::print() see that field belongs to constant table and want to print values (why?) but the field it try to print is not marked for read f2.t1.test (why?).

Here is what is already printed before the crash:

select 6 AS `f1` from  <materialize> (select `test`.`t2`.`f3` from `test`.`t2` having (`test`.`t2`.`f3` >= 8)) semi join (`test`.`t2`) where ((`test`.`t2`.`f3` = 6) and (

and probably field is not marked for write because of materialization (not sure yet

It is strange that field has index 1 (i.e. second field) when the field is really first and first field is allowed to read:

(gdb) p *table->read_set->bitmap
$5 = 1
(gdb) p this->field_index
$6 = 1

Especially because we see that 6 was already printed in SELECT and it is the same field

It is really f2 field that is left expression of IN so second field is correct and it is just not marked for read.

What it should be if switch off the assertion:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 test.t1.f2 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join)
4 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1276 Field or reference 'f2' of SELECT #3 was resolved in SELECT #1
Note 1003 select 6 AS `f1` from <materialize> (select `test`.`t2`.`f3` from `test`.`t2` having (`test`.`t2`.`f3` >= 8)) semi join (`test`.`t2`) where ((`test`.`t2`.`f3` = 6) and (9 = `<subquery4>`.`f3`))

Without debug output or EXPLAIN value of f2.t1 is really not used.

(gdb) p all_tables->next_global->table_name
$5 = 0x7fffed72c4d8 "t1"
(gdb) p all_tables->next_global->table->s->path
$6 =

(gdb) p all_tables->next_global->table->read_set
$7 = (MY_BITMAP *) 0x7fffed4ad980
(gdb) p all_tables->next_global->table->read_set->bitmap
$8 = (my_bitmap_map *) 0x7fffef7c7908
(gdb) p all_tables->next_global->table->read_set->bitmap[0]
$9 = 0
(gdb) watch *((my_bitmap_map *) 0x7fffef7c7908)
Hardware watchpoint 2: *((my_bitmap_map *) 0x7fffef7c7908)
(gdb)

First all read_set set all bits by including *
then all bits reset by SELECT_LEX::update_used_tables()
then again both set by walking through fields
then again both reset in SELECT_LEX::update_used_tables() (yet another one optimize)
but only 0 bit is set back.

join->conds in first call of used tables update and on second one are different. Second contain no field we are looking for, so it is not updated.

2 calls of SELECT_LEX::update_used_tables() :

(gdb) p join
$2 = (JOIN *) 0x7fffed777088
(gdb) p this
$3 = (st_select_lex * const) 0x7fffef4b6168
(gdb) p this->select_number
$4 = 1
(gdb) p dbug_print_item(join->conds)
$5 = 0x555556d4d4e0 <dbug_item_print_buf> "t1.f1 in (subquery#3)"
(gdb)

(gdb) p join
$6 = (JOIN *) 0x7fffed777088
(gdb) p this
$7 = (st_select_lex * const) 0x7fffef4b6168
(gdb) p this->select_number
$8 = 1
(gdb) p dbug_print_item(join->conds)
$9 = 0x555556d4d4e0 <dbug_item_print_buf> "(1 and 1 and (t1.f1 = t2.f3))"

condition transformation above made by
convert_join_subqueries_to_semijoins -> convert_subq_to_sj
and first it looks like this:
(t1.f1 in (subquery#3) and (1 and (t1.f1 = t2.f3)))

t1.f2 returned by:
setup_jtbm_semi_joins -> setup_jtbm_semi_joins
and it converted to
((1 and 1 and (t1.f1 = t2.f3)) and ((t1.f2 = `<subquery4>`.f3)))

but used tables was not updated after this

revision-id: 30a3b97bb321706a0e271268ee30ed62964311a7 (mariadb-10.1.10-24-g30a3b97)
parent(s): ff8d4009a7743b99c1c13831172b08041a4b272e
committer: Oleksandr Byelkin
timestamp: 2016-01-25 21:52:37 +0100
message:

MDEV-7827: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_long::val_str on EXPLAIN EXTENDED

It looks like printing expression can touch fields which never be calculated normally, so it have no sens to print it by value

Assert added: a view field should be never print by values (normally view is not optimized and constant tables is not read so we are safe)

—

An alternative fix:

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc

index 898e3ae..c7be909 100644

--- a/sql/sql_lex.cc

+++ b/sql/sql_lex.cc

@@ -3916,6 +3916,19 @@ void SELECT_LEX::update_used_tables()

       tl->on_expr->update_used_tables();

       tl->on_expr->walk(&Item::eval_not_null_tables, 0, NULL);

     }

+    /* 

+      - There is no need to check sj_on_expr, because merged semi-joins inject

+        sj_on_expr into the parent's WHERE clase.

+      - For non-merged semi-joins (aka JTBMs), we need to check their

+        left_expr. There is no need to check the rest of the subselect, we know

+        it is uncorrelated and so cannot refer to any tables in this select.

+    */

+    if (tl->jtbm_subselect)

+    {

+      Item *left_expr= tl->jtbm_subselect->left_expr;

+      left_expr->walk(&Item::update_table_bitmaps_processor, FALSE, NULL);

+    }

+    

     embedding= tl->embedding;

     while (embedding)

     {

It makes the crash go away.
I am not sure about what is exactly the reason I have to call Item::update_table_bitmaps_processor. I first tried update_used_tables() and that didn't work : as soon as Item_ref saw that it refers to upper subselect, it stopped doing anything.

sanja, we need to discuss this.

We've agreed that the last version of the fix is correct and should be pushed.