Igor Babaev (Inactive) added a comment - 2018-01-12 06:26 - edited The code of get_sort_by_table() that checks that ordering can be done using fields of only one table while ((table= ti++) && !(map & table->table->map)) ; if (map != table->table->map) DBUG_RETURN(0); // More than one table was incorrect. The code uses the previously calculated value of 'map' containing table_map bits for all tables the order by items depend on. It includes bits for all const (single_row) tables that should be excluded before the the loop in the above code starts. map&= ~const_tables; while ((table= ti++) && !(map & table->table->map)) ; if (map != table->table->map) DBUG_RETURN(0); // More than one table The function get_sort_by_table() is called in make_join_statistics() to set the value of JOIN::sort_by_table. Later this value can be used by the function setup_semijoin_dups_elimination() in the following code. (The function JOIN::JOIN_TAB *get_sort_by_join_tab() returns 0 if sort_by_table set to 0.) /* Make sure that possible sorting of rows from the head table is not to be employed. */ if (join->get_sort_by_join_tab()) { join->simple_order= 0; join->simple_group= 0; join->need_tmp= join->test_if_need_tmp_table(); } This code is executed for the reported query. The code prohibits using sorting by the first non constant joined table and, as a result, it requires creation of a temporary table containing all rows from the result set. Sorting the records from the temporary table produces the necessary ordering. Let's see what's going on when executing the reported query with the current code. The call of the function get_sort_by_table() set JOIN::sort_by_table to 0. As a result the above call join->get_sort_by_join_tab() returns 0 and the code join->simple_order= 0; is skipped. So the value of join->simple_order is not changed and it remains the same as it was set by the previously called function remove_const that does not use the value of join->sort_by_table. After the above 'if' statement the function setup_semijoin_dups_elimination() calls init_dups_weedout(). The latter sets JOIN_TAB::keep_current_rowid to TRUE for the third joined table, namely for the table t2. It also sets the field JOIN::flush_weedout_table for this table. The execution plan chosen for the query requires a sequential scan of the table t2. At the beginning of this scan the function SJ_TMP_TABLE::sj_weedout_delete_rows() is called. This function deletes all rows from the temporary table used for weedout. The function is called only once. Then the execution process tries to build rows of the result set. It starts from reading the first record from t2. Yet as the join was marked with the flag simple_order the needed fields of the records of t2 first are sorted and placed into a buffer. So actually the records of t2 are read from this buffer by the call-back function rr_unpack_from_buffer(). After a record of t2 has been read into the record buffer for t2 its rowid is saved into the temporary table used for weedout due to setting of keep_current_rowid for t2 to true. However rr_unpack_from_buffer() cannot read this rowid from the buffer because it has never been written there. So it reads 'something' that is interpreted as a rowid and this 'something' is always the same. That's why the result set contains only one row no matter how many rows contains t2.

Oleksandr Byelkin added a comment - 2018-01-12 21:08 OK to push.

Igor Babaev (Inactive) added a comment - 2018-01-18 18:18 A fix for this bug was pushed into the 5.5 tree.