Whilst working on data analysis using temporary tables on "10.2.11-MariaDB-10.2.11+maria~xenial-log" I noticed an issue with CTE's. Mariadb fails to reopen temporary tables if they are used more than once with the CTE query. (Note that if temporary tables are converted to permanent tables the below test case work fine).

Running the below test case in database dbtestcase returns : "ERROR 1146 (42S02) at line 10 in file: 'testcase.sql': Table 'dbtestcase.tbcte2' doesn't exist"

Testcase:

drop table if exists tbcte1;
drop table if exists tbcte2;

create table tbcte1 (term char(10));
create temporary table tbcte2 (term char(10));

insert into tbcte1 values ('TERM01'),('TERM02'),('TERM03');
insert into tbcte2 values ('TERM02'),('TERM03'),('TERM04');

with c1 as (select * from tbcte1),
c2 as (select * from tbcte2)
select *
from c1
left outer join c2 on c1.term = c2.term
union all
select *
from c1
right outer join c2 on c1.term = c2.term
where c1.term is null;

Server config:

--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --lc_messages_dir=/usr/share/mysql --lc_messages=en_US --skip-external-locking --bind-address=0.0.0.0 --max_connections=100 --connect_timeout=5 --wait_timeout=600 --max_allowed_packet=16M --thread_cache_size=128 --sort_buffer_size=4M --bulk_insert_buffer_size=16M --tmp_table_size=32M --max_heap_table_size=32M --myisam_recover_options=BACKUP --key_buffer_size=128M --table_open_cache=400 --myisam_sort_buffer_size=512M --concurrent_insert=2 --read_buffer_size=2M --read_rnd_buffer_size=1M --query_cache_limit=128K --query_cache_size=64M --log_warnings=2 --slow_query_log_file=/var/log/mysql/mariadb-slow.log --long_query_time=10 --log_slow_verbosity=query_plan --log_bin=/var/log/mysql/mariadb-bin --log_bin_index=/var/log/mysql/mariadb-bin.index --expire_logs_days=10 --max_binlog_size=100M --sql_mode=ONLY_FULL_GROUP_BY --default_storage_engine=InnoDB --innodb_buffer_pool_size=256M --innodb_log_buffer_size=8M --innodb_file_per_table=1 --innodb_open_files=400 --innodb_io_capacity=400 --innodb_flush_method=O_DIRECT