spetrunia

+------+-------------+-------+--------+----------------------------------------------------------------------------------------------+----------+---------+---------------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                                                                | key      | key_len | ref                                   | rows | Extra                                                               |
+------+-------------+-------+--------+----------------------------------------------------------------------------------------------+----------+---------+---------------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | t4    | range  | PRIMARY,SampleId,ExamSettingVersionId,IsArchived,IsDeleted,IX_ArchivedExam,IX_SampleProducer | SampleId | 8       | NULL                                  | 2    | Us

spetrunia

diff --git a/mysql-test/main/_a1.test b/mysql-test/main/_a1.test
new file mode 100644
index 00000000000..acab370b289
--- /dev/null
+++ b/mysql-test/main/_a1.test
@@ -0,0 +1,8 @@
+CREATE TABLE t1 (a INT, b VARCHAR(300));
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
+insert into t1 select T.a, T.b from t1 as T, t1 as X1, t1 as X2, t1 as X3;
+EXPLAIN SELECT /*+ MAX_EXECUTION_TIME(000149) */* FROM t1;

spetrunia

create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);

create table t2 as select * from t1;

create table t3 as select * from t2;


select * from t1 
where 

spetrunia

diff --git a/sql/table.cc b/sql/table.cc
index 2e597583f07..7f6f83f2a87 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1475,6 +1475,24 @@ key_map TABLE_SHARE::usable_indexes(THD *thd)                                             
 {
   key_map usable_indexes(keys_in_use);
   usable_indexes.subtract(ignored_indexes);
+
+  /*

spetrunia

create table t1 (a1 int, a2 int) engine=myisam;
insert into t1 values (1,1), (2,2);
create table t2 (b1 int, b2 int) engine=myisam;
insert into t2 values (3,3), (1,1);
create table t3 (c int) select a1 as c from t1;
 
prepare s from
'with cte as 
( select * from t3
    where c in (

spetrunia

diff --git a/sql/item.cc b/sql/item.cc
index 20289c317a3..255795015f0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5408,6 +5408,17 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
 }
 
 
+static
+SELECT_LEX *derived_merge_parent(SELECT_LEX *sel)

spetrunia

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cacea1370af..e3392e62f4f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15170,12 +15170,16 @@ uint check_join_cache_usage(JOIN_TAB *tab,
   bool no_bka_cache= !hint_table_state_or_fallback(join->thd,
                           tab->tab_list->table, BKA_HINT_ENUM,
                           join->allowed_join_cache_types & JOIN_CACHE_BKA_BIT);
+  bool hint_forces_bka= hint_table_state_or_fallback(join->thd,
+                                                     tab->tab_list->table,

spetrunia

Q1
MariaDB [test]> explain select * from t1, t2 where t2.a=t1.c;
+------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 1000 | Using where |
|    1 | SIMPLE      | t2    | ref  | a             | a    | 5       | test.t1.c | 1    |             |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0,002 sec)

spetrunia

MariaDB [test]> explain extended with CTE1 as (select /*+ QB_NAME(aaa)  */ t3.a from t3 where t3.a<3) select /*+ NO_RANGE_OPTIMIZATION(t3@aaa) */ * from CTE1 a, CTE1 b;
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|    1 | SIMPLE      | t3    | range | a             | a    | 5       | NULL | 2    |   100.00 | Using where; Using index                                     |
|    1 | SIMPLE      | t3    | index | a             | a    | 5       | NULL | 1000 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+-

spetrunia

MariaDB [test]> explain extended with CTE1 as (select /*+ QB_NAME(aaa) NO_RANGE_OPTIMIZATION(t3@aaa ) */ t3.a from t3 where t3.a<3) select * from CTE1 a, CTE1 b;
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|    1 | SIMPLE      | t3    | index | a             | a    | 5       | NULL | 1000 |   100.00 | Using where; Using index                                     |
|    1 | SIMPLE      | t3    | index | a             | a    | 5       | NULL | 1000 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+------+-