SUSE Linux Enterprise Server 11 (x86_64)

Looks like duplicate of https://mariadb.atlassian.net/browse/MDEV-6892
(I'll make testing on modern tree...)

it is not a duplicate

Let it stay here:
--no-defaults --basedir=/home/bell/maria/git/server/ --datadir=/home/bell/maria/git/server/mysql-test/restoregit_3306/data/ --tmpdir=/home/bell/maria/git/server/mysql-test/var/tmp/ --core-file --lc-messages-dir=/home/bell/maria/git/server/sql/share/ --character-sets-dir=/home/bell/maria/git/server/sql/share/charset --innodb-log-file-size=536870912 --port=16000 --skip-grant-tables

difference from "short" variant: join_tab ls not initialized at all:
gdb) p join->join_tab[0].table
$7 = (TABLE *) 0xa5a5a5a5a5a5a5a5
(gdb) p join->join_tab[1].table
$8 = (TABLE *) 0xa5a5a5a5a5a5a5a5
(gdb) p join->join_tab[2].table
$9 = (TABLE *) 0xa5a5a5a5a5a5a5a5

Problem is in join->best_positions last element

Only 2 elements assigned in best positions instead of 3.

Difference with Sergey observation is that after mysql_derived_merge() call 'merged' flag is set and merge really is done

(gdb) p derived->merged
$1 = true

It looks like tables with the same map are not temporary tables:

DBUG_PRINT("XXX", ("table '%s' 0x%lx join_tab: 0x%lx map: 0x%lx",
table->s->path.str, (ulong) table,
(ulong) s, (ulong) table->map));

in make_join_statistics() output:

T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff841859d0 join_tab: 0x7fff841e5f78 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_1968_0' 0x7fff841c35b8 join_tab: 0x7fff841e6298 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c340 join_tab: 0x7fff841e65b8 map: 0x1

setup_tables() set it correctly:
T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff84185a90 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_2370_0' 0x7fff841c3678 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c400 map: 0x4
then it become:
T@4 : | | | | | | | | | | | XXX: table './kzmdb/tb_user' 0x7fff84185a90 join_tab: 0x7fff841e6038 map: 0x1
T@4 : | | | | | | | | | | | XXX: table '/home/bell/maria/git/server/mysql-test/var/tmp/#sql_2370_0' 0x7fff841c3678 join_tab: 0x7fff841e6358 map: 0x2
T@4 : | | | | | | | | | | | XXX: table './kuzudb/tb_team_bereich' 0x7fff8417c400 join_tab: 0x7fff841e6678 map: 0x1

Above was not the last setup tables, the last one process only 2 tables

last setup_tables process only 2 leaf tables

table map of the 'third' table reset during mergeing it...

The problem with UPDATE (in difference with SELECT) is that in st_select_lex::remap_tables() it appeared that derived table temporary table which we are going to merge has no table map but underlying table already has (and it was included as leaf table (not temporary one)).

The difference between SELECT & UPDATE is that full_table_list is set to true for so underlying table get in to the leaf table list.

Review feedback provided over email.

http://lists.askmonty.org/pipermail/commits/2015-March/007529.html

Sent for additional testing

Regarding the patch:
it didn't apply smoothly due to line breaks in long lines in the test case. I removed them (locally) and then it worked.

Regarding the test case:
It creates InnoDB tables, but InnoDB is disabled for the test case. Please either enable it or switch tables to MyISAM, it just doesn't make sense to generate warnings about the missing engine.

Otherwise, I've run a bunch of tests, didn't get any regressions. Please push.

for merging: https://github.com/MariaDB/server/pull/40

It is good, but if engine is not important it is better do not mention it at all (my fault)

Daniel, thank you a lot, but I think more changes should be done so I've made changes from my tree and push after test run.