mariadb/mysql-test/main/opt_hints.result

SET NAMES utf8mb4;
# Testing that index names in hints are accent sensitive case insensitive
CREATE TABLE t1 (a INT, ä INT, INDEX idx_a(a), INDEX idx_ä(ä));
INSERT INTO t1 VALUES (1,1),(2,2);
SELECT /*+ NO_MRR(t1 idx_a) */ a FROM t1;
a
1
2
SELECT /*+ NO_MRR(t1 idx_A) */ a FROM t1;
a
1
2
SELECT /*+ NO_MRR(t1 idx_å) */ a FROM t1;
a
1
2
Warnings:
Warning	4222	Unresolved index name `t1`@`select#1` `idx_å` for NO_MRR hint
SELECT /*+ NO_MRR(t1 idx_a, idx_å, idx_A) */ a FROM t1;
a
1
2
Warnings:
Warning	4239	Hint NO_MRR(`t1` `idx_A`) is ignored as conflicting/duplicated (an index hint of the same type has already been specified for this key)
Warning	4222	Unresolved index name `t1`@`select#1` `idx_å` for NO_MRR hint
DROP TABLE t1;
# Testing that query block names are accent sensitive case insensitive
CREATE TABLE t1 (a INT);
SELECT /*+ QB_NAME(a) BKA(t1@a) BKA(t1@A) */ * FROM t1;
a
Warnings:
Warning	4219	Hint BKA(`t1`@`A`) is ignored as conflicting/duplicated
SELECT /*+ QB_NAME(a) BKA(t1@a) BKA(t1@å) */ * FROM t1;
a
Warnings:
Warning	4220	Query block name `å` is not found for BKA hint
DROP TABLE t1;
CREATE TABLE t1(f1 INT, f2 INT);
INSERT INTO t1 VALUES
(1,1),(2,2),(3,3);
CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, f3 CHAR(200), KEY(f1, f2));
INSERT INTO t2 VALUES
(1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'),
(2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), (2,4, 'qwerty'),(2,5, 'qwerty'),
(3,1, 'qwerty'),(3,4, 'qwerty'),
(4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty'),
(1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'),
(2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), (2,4, 'qwerty'),(2,5, 'qwerty'),
(3,1, 'qwerty'),(3,4, 'qwerty'),
(4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty');
CREATE TABLE t3 (f1 INT NOT NULL, f2 INT, f3 VARCHAR(32),
PRIMARY KEY(f1), KEY f2_idx(f1), KEY f3_idx(f3));
INSERT INTO t3 VALUES
(1, 1, 'qwerty'), (2, 1, 'ytrewq'),
(3, 2, 'uiop'), (4, 2, 'poiu'), (5, 2, 'lkjh'),
(6, 2, 'uiop'), (7, 2, 'poiu'), (8, 2, 'lkjh'),
(9, 2, 'uiop'), (10, 2, 'poiu'), (11, 2, 'lkjh'),
(12, 2, 'uiop'), (13, 2, 'poiu'), (14, 2, 'lkjh');
INSERT INTO t3 SELECT f1 + 20, f2, f3 FROM t3;
INSERT INTO t3 SELECT f1 + 40, f2, f3 FROM t3;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
ANALYZE TABLE t3;
Table	Op	Msg_type	Msg_text
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	OK
# NO_RANGE_OPTIMIZATION hint testing
set optimizer_switch=default;
# Check statistics with no hint
FLUSH STATUS;
SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
f1
31
32
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	2
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
# Check statistics with hint
FLUSH STATUS;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
f1
31
32
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	0
Handler_read_last	0
Handler_read_next	56
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
EXPLAIN EXTENDED SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	range	PRIMARY,f2_idx	PRIMARY	4	NULL	2	100.00	Using where; Using index
Warnings:
Note	1003	select `test`.`t3`.`f1` AS `f1` from `test`.`t3` where `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33
# Turn off range access for PRIMARY key
# Should use range access by f2_idx key
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	range	PRIMARY,f2_idx	f2_idx	4	NULL	2	100.00	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`t3`@`select#1` `PRIMARY`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t3` where `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33
# Turn off range access for PRIMARY & f2_idx keys
# Should use index access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	index	PRIMARY,f2_idx	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`t3`@`select#1` `PRIMARY`) NO_RANGE_OPTIMIZATION(`t3`@`select#1` `f2_idx`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t3` where `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33
# Turn off range access for all keys
# Should use index access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	index	PRIMARY,f2_idx	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`t3`@`select#1`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t3` where `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33
# Turn off range access for PRIMARY & f2_idx keys
# Should use index access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) NO_RANGE_OPTIMIZATION(t3 f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	index	PRIMARY,f2_idx	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`t3`@`select#1` `PRIMARY`) NO_RANGE_OPTIMIZATION(`t3`@`select#1` `f2_idx`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t3` where `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33
# Create a clone of t3 with cyrillic names
CREATE TABLE таблица (f1 INT NOT NULL, поле2 INT, поле3 VARCHAR(32),
PRIMARY KEY(f1), KEY f2_индекс(f1), KEY f3_индекс(поле3))
AS SELECT * FROM t3;
ANALYZE TABLE таблица;
Table	Op	Msg_type	Msg_text
test.таблица	analyze	status	Engine-independent statistics collected
test.таблица	analyze	status	Table is already up to date
# Turn off range access for PRIMARY key
# Should use range access by f2_индекс key
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY) */ f1
FROM таблица WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	таблица	range	PRIMARY,f2_индекс	f2_индекс	4	NULL	2	100.00	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`таблица`@`select#1` `PRIMARY`) */ `test`.`таблица`.`f1` AS `f1` from `test`.`таблица` where `test`.`таблица`.`f1` > 30 and `test`.`таблица`.`f1` < 33
# Turn off range access for PRIMARY & f2_индекс keys
# Should use index access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY, f2_индекс) */ f1
FROM таблица WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	таблица	index	PRIMARY,f2_индекс	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`таблица`@`select#1` `PRIMARY`) NO_RANGE_OPTIMIZATION(`таблица`@`select#1` `f2_индекс`) */ `test`.`таблица`.`f1` AS `f1` from `test`.`таблица` where `test`.`таблица`.`f1` > 30 and `test`.`таблица`.`f1` < 33
# Turn off range access for all keys
# Should use index access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица) */ f1
FROM таблица WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	таблица	index	PRIMARY,f2_индекс	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`таблица`@`select#1`) */ `test`.`таблица`.`f1` AS `f1` from `test`.`таблица` where `test`.`таблица`.`f1` > 30 and `test`.`таблица`.`f1` < 33
# Turn off range access for PRIMARY & f2_индекс keys
# Should use index access
EXPLAIN EXTENDED
SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY) NO_RANGE_OPTIMIZATION(таблица f2_индекс) */ f1
FROM таблица WHERE f1 > 30 AND f1 < 33;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	таблица	index	PRIMARY,f2_индекс	PRIMARY	4	NULL	56	4.11	Using where; Using index
Warnings:
Note	1003	select /*+ NO_RANGE_OPTIMIZATION(`таблица`@`select#1` `PRIMARY`) NO_RANGE_OPTIMIZATION(`таблица`@`select#1` `f2_индекс`) */ `test`.`таблица`.`f1` AS `f1` from `test`.`таблица` where `test`.`таблица`.`f1` > 30 and `test`.`таблица`.`f1` < 33
DROP TABLE таблица;
# NO_ICP hint testing
set optimizer_switch='index_condition_pushdown=on';
CREATE TABLE t4 (x INT, y INT, KEY x_idx(x), KEY y_idx(y));
INSERT INTO t4 (x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13);
UPDATE t4 SET y=x;
EXPLAIN EXTENDED SELECT * FROM
(SELECT t4.x, t5.y FROM t4, t4 t5 WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
EXPLAIN EXTENDED SELECT * FROM
(SELECT /*+ NO_ICP(t5 x_idx, y_idx) */ t4.x, t5.y FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select /*+ NO_ICP(`t5`@`select#2` `x_idx`) NO_ICP(`t5`@`select#2` `y_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
EXPLAIN EXTENDED SELECT /*+ NO_ICP(t5@qb1 x_idx) */ * FROM
(SELECT /*+ QB_NAME(QB1) */ t4.x, t5.y FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select /*+ NO_ICP(`t5`@`QB1` `x_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
# Cyrillic query block name
EXPLAIN EXTENDED SELECT /*+ NO_ICP(t5@блок1 x_idx) */ * FROM
(SELECT /*+ QB_NAME(блок1) */ t4.x, t5.y FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select /*+ NO_ICP(`t5`@`блок1` `x_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
# Expected warning for z_idx key, unresolved name.
EXPLAIN EXTENDED SELECT * FROM
(SELECT /*+ NO_ICP(t5 y_idx, x_idx, z_idx) */ t4.x, t5.y FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Warning	4222	Unresolved index name `t5`@`select#2` `z_idx` for NO_ICP hint
Note	1003	select /*+ NO_ICP(`t5`@`select#2` `y_idx`) NO_ICP(`t5`@`select#2` `x_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
# ICP should still be used
EXPLAIN EXTENDED SELECT * FROM
(SELECT /*+ NO_ICP(t5 y_idx) */ t4.x, t5.y FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0) AS TD;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select /*+ NO_ICP(`t5`@`select#2` `y_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0)
# BKA & NO_BKA hint testing
set optimizer_switch = DEFAULT;
set optimizer_switch = 'mrr=on,mrr_cost_based=off';
set join_cache_level = 8;
CREATE TABLE t10(a INT);
INSERT INTO t10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t11(a INT);
INSERT INTO t11 SELECT A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C;
CREATE TABLE t12(a INT, b INT);
INSERT INTO t12 SELECT a,a from t10;
CREATE TABLE t13(a INT, b INT, c INT, filler CHAR(100), key (a,b));
INSERT INTO t13 select a,a,a, 'filler-data' FROM t11;
# Make sure BKA is expected to be used when there are no hints
EXPLAIN
SELECT * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
# Disable BKA
set optimizer_switch='join_cache_bka=off';
EXPLAIN EXTENDED
SELECT * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
# Check statistics without hint
FLUSH STATUS;
SELECT * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
a	b	a	b	c	filler
0	0	0	0	0	filler-data
1	1	1	1	1	filler-data
2	2	2	2	2	filler-data
3	3	3	3	3	filler-data
4	4	4	4	4	filler-data
5	5	5	5	5	filler-data
6	6	6	6	6	filler-data
7	7	7	7	7	filler-data
8	8	8	8	8	filler-data
9	9	9	9	9	filler-data
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	1012
# Check statistics with hint
FLUSH STATUS;
SELECT /*+ BKA() */ * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
a	b	a	b	c	filler
0	0	0	0	0	filler-data
1	1	1	1	1	filler-data
2	2	2	2	2	filler-data
3	3	3	3	3	filler-data
4	4	4	4	4	filler-data
5	5	5	5	5	filler-data
6	6	6	6	6	filler-data
7	7	7	7	7	filler-data
8	8	8	8	8	filler-data
9	9	9	9	9	filler-data
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	10
Handler_read_last	0
Handler_read_next	10
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	10
Handler_read_rnd_deleted	0
Handler_read_rnd_next	11
EXPLAIN EXTENDED SELECT /*+ BKA(t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ BKA(t12, t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`t12`@`select#1`) BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ BKA(t12) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select /*+ BKA(`t12`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ QB_NAME(QB1) BKA(t13@QB1) */ * FROM t12, t13 
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ QB_NAME(`QB1`) BKA(`t13`@`QB1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
# Hint overrides both join_cache_level and optimizer switch
set join_cache_level = 0;
EXPLAIN EXTENDED SELECT /*+ BKA(t12, t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`t12`@`select#1`) BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
set join_cache_level = 2;
EXPLAIN EXTENDED SELECT /*+ BKA(t12, t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`t12`@`select#1`) BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
set join_cache_level = 4;
EXPLAIN EXTENDED SELECT /*+ BKA(t12, t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`t12`@`select#1`) BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
set join_cache_level = 8;
# Enable BKA
set optimizer_switch='join_cache_bka=on';
EXPLAIN EXTENDED SELECT /*+ NO_BKA(t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select /*+ NO_BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select /*+ NO_BKA(@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ NO_BKA(t12, t13) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select /*+ NO_BKA(`t12`@`select#1`) NO_BKA(`t13`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ NO_BKA(t12) */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ NO_BKA(`t12`@`select#1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
EXPLAIN EXTENDED SELECT /*+ QB_NAME(QB1) NO_BKA(t13@QB1) */ * FROM t12, t13 
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select /*+ QB_NAME(`QB1`) NO_BKA(`t13`@`QB1`) */ `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
# UPDATE|DELETE|INSERT|REPLACE hint testing
EXPLAIN EXTENDED UPDATE t3
SET f3 = 'mnbv' WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
(SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t3	range	PRIMARY,f2_idx,f3_idx	PRIMARY	4	NULL	2	100.00	Using index condition; Using where
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3.33	Using where
1	PRIMARY	t2	ref	f1	f1	8	test.t3.f1,test.t3.f2	2	50.00	Using where; FirstMatch(t3)
Warnings:
Note	1003	update `test`.`t3` semi join (`test`.`t1` join `test`.`t2`) set `test`.`t3`.`f3` = 'mnbv' where `test`.`t1`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f2` = `test`.`t3`.`f2` and `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33 and `test`.`t3`.`f2` between `test`.`t3`.`f1` and `test`.`t1`.`f2` and `test`.`t3`.`f2` + 1 >= `test`.`t3`.`f1` + 1 and `test`.`t3`.`f3` = `test`.`t2`.`f3`
# Turn off range access for PRIMARY key.
# Range access should be used for f2_idx key.
EXPLAIN EXTENDED UPDATE /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) */ t3
SET f3 = 'mnbv' WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
(SELECT /*+ BKA(t2) NO_BNL(t1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t3	range	PRIMARY,f2_idx,f3_idx	f2_idx	4	NULL	2	100.00	Using index condition; Using where
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3.33	Using where
1	PRIMARY	t2	ref	f1	f1	8	test.t3.f1,test.t3.f2	2	50.00	Using where; FirstMatch(t3)
Warnings:
Note	1003	update /*+ BKA(`t2`@`select#2`) NO_BNL(`t1`@`select#2`) NO_RANGE_OPTIMIZATION(`t3`@`select#1` `PRIMARY`) */ `test`.`t3` semi join (`test`.`t1` join `test`.`t2`) set `test`.`t3`.`f3` = 'mnbv' where `test`.`t1`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f2` = `test`.`t3`.`f2` and `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33 and `test`.`t3`.`f2` between `test`.`t3`.`f1` and `test`.`t1`.`f2` and `test`.`t3`.`f2` + 1 >= `test`.`t3`.`f1` + 1 and `test`.`t3`.`f3` = `test`.`t2`.`f3`
# Turn off range access for all keys.
EXPLAIN EXTENDED UPDATE /*+ NO_RANGE_OPTIMIZATION(t3) */ t3
SET f3 = 'mnbv' WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
(SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	range	f1	f1	4	NULL	1	100.00	Using index condition; Start temporary
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	11.11	Using where
1	PRIMARY	t3	eq_ref	PRIMARY,f2_idx,f3_idx	PRIMARY	4	test.t2.f1	1	100.00	Using where; End temporary
Warnings:
Note	1003	update /*+ NO_RANGE_OPTIMIZATION(`t3`@`select#1`) */ `test`.`t3` semi join (`test`.`t1` join `test`.`t2`) set `test`.`t3`.`f3` = 'mnbv' where `test`.`t1`.`f1` = `test`.`t2`.`f1` and `test`.`t3`.`f1` = `test`.`t2`.`f1` and `test`.`t3`.`f2` = `test`.`t2`.`f2` and `test`.`t2`.`f1` > 30 and `test`.`t2`.`f1` < 33 and `test`.`t2`.`f2` between `test`.`t2`.`f1` and `test`.`t1`.`f2` and `test`.`t2`.`f2` + 1 >= `test`.`t2`.`f1` + 1 and `test`.`t3`.`f3` = `test`.`t2`.`f3`
EXPLAIN EXTENDED DELETE FROM t3
WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
(SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t3	range	PRIMARY,f2_idx,f3_idx	PRIMARY	4	NULL	2	100.00	Using index condition; Using where
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3.33	Using where
1	PRIMARY	t2	ref	f1	f1	8	test.t3.f1,test.t3.f2	2	50.00	Using where; FirstMatch(t3)
Warnings:
Note	1003	delete  from `test`.`t3` using (`test`.`t1` join `test`.`t2`) where `test`.`t1`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f1` = `test`.`t3`.`f1` and `test`.`t2`.`f2` = `test`.`t3`.`f2` and `test`.`t3`.`f1` > 30 and `test`.`t3`.`f1` < 33 and `test`.`t3`.`f2` between `test`.`t3`.`f1` and `test`.`t1`.`f2` and `test`.`t3`.`f2` + 1 >= `test`.`t3`.`f1` + 1 and `test`.`t3`.`f3` = `test`.`t2`.`f3`
# Turn off range access. Range access should not be used.
EXPLAIN EXTENDED
DELETE /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) NO_BNL(t1@QB1) */ FROM t3
WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
(SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	range	f1	f1	4	NULL	1	100.00	Using index condition; Start temporary
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	11.11	Using where
1	PRIMARY	t3	eq_ref	PRIMARY,f2_idx,f3_idx	PRIMARY	4	test.t2.f1	1	100.00	Using where; End temporary
Warnings:
Note	1003	delete /*+ NO_BNL(`t1`@`qb1`) NO_RANGE_OPTIMIZATION(`t3`@`select#1` `PRIMARY`) NO_RANGE_OPTIMIZATION(`t3`@`select#1` `f2_idx`) */  from `test`.`t3` using (`test`.`t1` join `test`.`t2`) where `test`.`t1`.`f1` = `test`.`t2`.`f1` and `test`.`t3`.`f1` = `test`.`t2`.`f1` and `test`.`t3`.`f2` = `test`.`t2`.`f2` and `test`.`t2`.`f1` > 30 and `test`.`t2`.`f1` < 33 and `test`.`t2`.`f2` between `test`.`t2`.`f1` and `test`.`t1`.`f2` and `test`.`t2`.`f2` + 1 >= `test`.`t2`.`f1` + 1 and `test`.`t3`.`f3` = `test`.`t2`.`f3`
# Make sure ICP is expected to be used when there are no hints
EXPLAIN EXTENDED INSERT INTO t3(f1, f2, f3)
(SELECT t4.x, t5.y, 'filler' FROM t4, t4 t5 WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(insert into `test`.`t3`(f1,f2,f3) select `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP. ICP should not be used.
EXPLAIN EXTENDED INSERT INTO t3(f1, f2, f3)
(SELECT /*+ NO_ICP(t5) */t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(insert into `test`.`t3`(f1,f2,f3) select /*+ NO_ICP(`t5`@`select#1`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP for a particular table
EXPLAIN EXTENDED INSERT INTO t3(f1, f2, f3)
(SELECT /*+ NO_ICP(t5) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(insert into `test`.`t3`(f1,f2,f3) select /*+ NO_ICP(`t5`@`select#1`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP for a particular table and a key
EXPLAIN EXTENDED INSERT INTO t3(f1, f2, f3)
(SELECT /*+ QB_NAME(qb1) NO_ICP(t5@QB1 x_idx)*/ t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(insert into `test`.`t3`(f1,f2,f3) select /*+ QB_NAME(`qb1`) NO_ICP(`t5`@`qb1` `x_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Make sure ICP is expected to be used when there are no hints
EXPLAIN EXTENDED REPLACE INTO t3(f1, f2, f3)
(SELECT t4.x, t5.y, 'filler' FROM t4, t4 t5 WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(replace into `test`.`t3`(f1,f2,f3) select `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP. ICP should not be used.
EXPLAIN EXTENDED REPLACE INTO t3(f1, f2, f3)
(SELECT /*+ NO_ICP(t5) */t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(replace into `test`.`t3`(f1,f2,f3) select /*+ NO_ICP(`t5`@`select#1`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP for a particular table
EXPLAIN EXTENDED REPLACE INTO t3(f1, f2, f3)
(SELECT /*+ QB_NAME(qb1) NO_ICP(t5@QB1)*/ t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(replace into `test`.`t3`(f1,f2,f3) select /*+ QB_NAME(`qb1`) NO_ICP(`t5`@`qb1`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Turn off ICP for a particular table and a key
EXPLAIN EXTENDED REPLACE INTO t3(f1, f2, f3)
(SELECT /*+ NO_ICP(t5 x_idx) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	ref	y_idx	y_idx	5	const	1	100.00	
1	SIMPLE	t5	range	x_idx	x_idx	5	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	(replace into `test`.`t3`(f1,f2,f3) select /*+ NO_ICP(`t5`@`select#1` `x_idx`) */ `test`.`t4`.`x` AS `x`,`test`.`t5`.`y` AS `y`,'filler' AS `filler` from `test`.`t4` join `test`.`t4` `t5` where `test`.`t4`.`y` = 8 and `test`.`t5`.`x` between 7 and <cache>(8 + 0))
# Misc tests
# Should issue warning
EXPLAIN EXTENDED SELECT /*+ QB_NAME(qb1) QB_NAME(qb1 ) */ * FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	28	100.00	
Warnings:
Warning	4219	Hint QB_NAME(`qb1`) is ignored as conflicting/duplicated
Note	1003	select /*+ QB_NAME(`qb1`) */ `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3` from `test`.`t2`
# Should issue warning
EXPLAIN EXTENDED SELECT /*+ BKA(@qb1) QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t2	ALL	f1	NULL	NULL	NULL	28	25.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Warning	4220	Query block name `qb1` is not found for BKA hint
Note	1003	select /*+ QB_NAME(`qb1`) */ `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f2` between `test`.`t1`.`f1` and `test`.`t1`.`f2` and `test`.`t2`.`f2` + 1 >= `test`.`t1`.`f1` + 1
# Should not crash
PREPARE stmt1 FROM "SELECT /*+ BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1";
EXECUTE stmt1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
EXECUTE stmt1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
DEALLOCATE PREPARE stmt1;
# Check use of alias
set optimizer_switch='join_cache_bka=off';
EXPLAIN EXTENDED
SELECT * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	hash_ALL	a	#hash#a	5	test.t12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select `test`.`t12`.`a` AS `a`,`test`.`t12`.`b` AS `b`,`test`.`t13`.`a` AS `a`,`test`.`t13`.`b` AS `b`,`test`.`t13`.`c` AS `c`,`test`.`t13`.`filler` AS `filler` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
# Turn on BKA for multiple tables. BKA should be used for tbl13.
EXPLAIN EXTENDED SELECT /*+ BKA(tbl12, tbl13) */ * FROM t12 tbl12, t13 tbl13 
WHERE tbl12.a=tbl13.a AND (tbl13.b+1 <= tbl13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	tbl13	ref	a	a	5	test.tbl12.a	1	100.00	Using index condition; Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
Warnings:
Note	1003	select /*+ BKA(`tbl12`@`select#1`) BKA(`tbl13`@`select#1`) */ `test`.`tbl12`.`a` AS `a`,`test`.`tbl12`.`b` AS `b`,`test`.`tbl13`.`a` AS `a`,`test`.`tbl13`.`b` AS `b`,`test`.`tbl13`.`c` AS `c`,`test`.`tbl13`.`filler` AS `filler` from `test`.`t12` `tbl12` join `test`.`t13` `tbl13` where `test`.`tbl13`.`a` = `test`.`tbl12`.`a` and `test`.`tbl13`.`b` + 1 <= `test`.`tbl13`.`b` + 1
# Print warnings for nonexistent names
EXPLAIN EXTENDED
SELECT /*+ BKA(t2) NO_BNL(t1) BKA(t3) NO_RANGE_OPTIMIZATION(t3 idx1) NO_RANGE_OPTIMIZATION(t3) */
t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t2	ALL	f1	NULL	NULL	NULL	28	25.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Warning	4221	Unresolved table name `t3`@`select#1` for BKA hint
Warning	4221	Unresolved table name `t3`@`select#1` for NO_RANGE_OPTIMIZATION hint
Warning	4222	Unresolved index name `t3`@`select#1` `idx1` for NO_RANGE_OPTIMIZATION hint
Note	1003	select /*+ BKA(`t2`@`select#1`) NO_BNL(`t1`@`select#1`) */ `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f2` between `test`.`t1`.`f1` and `test`.`t1`.`f2` and `test`.`t2`.`f2` + 1 >= `test`.`t1`.`f1` + 1
# Check illegal syntax
EXPLAIN EXTENDED SELECT /*+ BKA(qb1 t3@qb1) */ f2 FROM
(SELECT /*+ QB_NAME(qb1) */ f2, f3, f1 FROM t3 WHERE f1 > 2 AND f3 = 'poiu') AS TD
WHERE TD.f1 > 2 AND TD.f3 = 'poiu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	ALL	PRIMARY,f2_idx,f3_idx	NULL	NULL	NULL	56	27.55	Using where
Warnings:
Warning	1064	Optimizer hint syntax error near 't3@qb1) */ f2 FROM
(SELECT /*+ QB_NAME(qb1) */ f2, f3, f1 FROM t3 WHERE f1 > ...' at line 1
Note	1003	select `test`.`t3`.`f2` AS `f2` from `test`.`t3` where `test`.`t3`.`f1` > 2 and `test`.`t3`.`f3` = 'poiu' and `test`.`t3`.`f1` > 2
# Check illegal syntax
EXPLAIN EXTENDED SELECT * FROM
(SELECT /*+ QB_NAME(qb1) BKA(@qb1 t1@qb1, t2@qb1, t3) */ t2.f1, t2.f2, t2.f3 FROM t1,t2,t3) tt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	28	100.00	Using join buffer (flat, BNL join)
1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	56	100.00	Using index; Using join buffer (incremental, BNL join)
Warnings:
Warning	1064	Optimizer hint syntax error near '@qb1, t2@qb1, t3) */ t2.f1, t2.f2, t2.f3 FROM t1,t2,t3) tt' at line 2
Note	1003	select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` join `test`.`t3`
# Check '@qb_name table_name' syntax
EXPLAIN EXTENDED SELECT /*+ BKA(@qb1 t13) */ * FROM (SELECT /*+ QB_NAME(QB1) */ t12.a, t13.b FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1)) AS s1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	t13	ref	a	a	5	test.t12.a	1	100.00	Using where; Using index
Warnings:
Note	1003	select /*+ BKA(`t13`@`QB1`) */ `test`.`t12`.`a` AS `a`,`test`.`t13`.`b` AS `b` from `test`.`t12` join `test`.`t13` where `test`.`t13`.`a` = `test`.`t12`.`a` and `test`.`t13`.`b` + 1 <= `test`.`t13`.`b` + 1
# Check that original table name is not recognized if alias is used.
EXPLAIN EXTENDED SELECT /*+ BKA(tbl2) */ * FROM t12 tbl12, t13 tbl13 
WHERE tbl12.a=tbl13.a AND (tbl13.b+1 <= tbl13.b+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl12	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
1	SIMPLE	tbl13	hash_ALL	a	#hash#a	5	test.tbl12.a	1000	0.10	Using where; Using join buffer (flat, BNLH join)
Warnings:
Warning	4221	Unresolved table name `tbl2`@`select#1` for BKA hint
Note	1003	select `test`.`tbl12`.`a` AS `a`,`test`.`tbl12`.`b` AS `b`,`test`.`tbl13`.`a` AS `a`,`test`.`tbl13`.`b` AS `b`,`test`.`tbl13`.`c` AS `c`,`test`.`tbl13`.`filler` AS `filler` from `test`.`t12` `tbl12` join `test`.`t13` `tbl13` where `test`.`tbl13`.`a` = `test`.`tbl12`.`a` and `test`.`tbl13`.`b` + 1 <= `test`.`tbl13`.`b` + 1
# Check that PS and conventional statements give the same result.
FLUSH STATUS;
SELECT /*+ BKA(t13) */ * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
a	b	a	b	c	filler
0	0	0	0	0	filler-data
1	1	1	1	1	filler-data
2	2	2	2	2	filler-data
3	3	3	3	3	filler-data
4	4	4	4	4	filler-data
5	5	5	5	5	filler-data
6	6	6	6	6	filler-data
7	7	7	7	7	filler-data
8	8	8	8	8	filler-data
9	9	9	9	9	filler-data
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	10
Handler_read_last	0
Handler_read_next	10
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	10
Handler_read_rnd_deleted	0
Handler_read_rnd_next	11
PREPARE stmt1 FROM "SELECT /*+ BKA(t13) */ * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1)";
FLUSH STATUS;
EXECUTE stmt1;
a	b	a	b	c	filler
0	0	0	0	0	filler-data
1	1	1	1	1	filler-data
2	2	2	2	2	filler-data
3	3	3	3	3	filler-data
4	4	4	4	4	filler-data
5	5	5	5	5	filler-data
6	6	6	6	6	filler-data
7	7	7	7	7	filler-data
8	8	8	8	8	filler-data
9	9	9	9	9	filler-data
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	10
Handler_read_last	0
Handler_read_next	10
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	10
Handler_read_rnd_deleted	0
Handler_read_rnd_next	11
FLUSH STATUS;
EXECUTE stmt1;
a	b	a	b	c	filler
0	0	0	0	0	filler-data
1	1	1	1	1	filler-data
2	2	2	2	2	filler-data
3	3	3	3	3	filler-data
4	4	4	4	4	filler-data
5	5	5	5	5	filler-data
6	6	6	6	6	filler-data
7	7	7	7	7	filler-data
8	8	8	8	8	filler-data
9	9	9	9	9	filler-data
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	10
Handler_read_last	0
Handler_read_next	10
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	10
Handler_read_rnd_deleted	0
Handler_read_rnd_next	11
DEALLOCATE PREPARE stmt1;
DROP TABLE t1, t2, t3, t4, t10, t11, t12, t13;
# MRR & NO_MRR hint testing
set optimizer_switch=default;
set join_cache_level = 8;
CREATE TABLE t1
(
f1 int NOT NULL DEFAULT '0',
f2 int NOT NULL DEFAULT '0',
f3 int NOT NULL DEFAULT '0',
INDEX idx1(f2, f3), INDEX idx2(f3)
);
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
INSERT INTO t1(f2, f3) VALUES (3,4), (3,4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
set optimizer_switch='mrr=on,mrr_cost_based=off';
# Check statistics without hint
FLUSH STATUS;
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
f1	f2	f3
0	3	4
0	3	4
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	2
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
# Check statistics with hint
FLUSH STATUS;
SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
f1	f2	f3
0	3	4
0	3	4
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	2
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
# Make sure hints are preserved in a stored procedure body
CREATE PROCEDURE p() SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW CREATE PROCEDURE p;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3	utf8mb4	utf8mb4_uca1400_ai_ci	utf8mb4_uca1400_ai_ci
FLUSH STATUS;
CALL p();
f1	f2	f3
0	3	4
0	3	4
SHOW STATUS LIKE 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_last	0
Handler_read_next	2
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
DROP PROCEDURE p;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn off MRR. MRR should not be used.
EXPLAIN EXTENDED SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select /*+ NO_MRR(`t1`@`select#1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn off MRR. MRR should not be used.
EXPLAIN EXTENDED SELECT /*+ NO_MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select /*+ NO_MRR(`t1`@`select#1` `idx2`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn off MRR for unused key. MRR should be used.
EXPLAIN EXTENDED SELECT /*+ NO_MRR(t1 idx1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select /*+ NO_MRR(`t1`@`select#1` `idx1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
set optimizer_switch='mrr=off,mrr_cost_based=off';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR. MRR should be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR. MRR should be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1 IDX2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1` `IDX2`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR for unused key. MRR should not be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1 idx1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1` `idx1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
set optimizer_switch='mrr=off,mrr_cost_based=on';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR. MRR should be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR. MRR should be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where; Rowid-ordered scan
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1` `idx2`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
# Turn on MRR for unused key. MRR should not be used.
EXPLAIN EXTENDED SELECT /*+ MRR(t1 IDX1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	select /*+ MRR(`t1`@`select#1` `IDX1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3` from `test`.`t1` where `test`.`t1`.`f2` <= 3 and 3 <= `test`.`t1`.`f3`
DROP TABLE t1;
set optimizer_switch=default;
#
# Duplicate hints
#
CREATE TABLE t1 (i INT PRIMARY KEY);
SELECT /*+ BKA() BKA() */ 1;
1
1
Warnings:
Warning	4219	Hint BKA() is ignored as conflicting/duplicated
SELECT /*+ BKA(t1) BKA(t1) */ * FROM t1;
i
Warnings:
Warning	4219	Hint BKA(`t1`) is ignored as conflicting/duplicated
SELECT /*+ QB_NAME(q1) BKA(t1@q1) BKA(t1@q1) */ * FROM t1;
i
Warnings:
Warning	4219	Hint BKA(`t1`@`q1`) is ignored as conflicting/duplicated
SELECT /*+ QB_NAME(q1) NO_ICP(@q1 t1 PRIMARY) NO_ICP(@q1 t1 PRIMARY) */ * FROM t1;
i
Warnings:
Warning	4239	Hint NO_ICP(`t1`@`q1` `PRIMARY`) is ignored as conflicting/duplicated (an index hint of the same type has already been specified for this key)
DROP TABLE t1;
#
# Hints inside views are supported
#
CREATE TABLE t1 (a INT, INDEX idx_a(a));
INSERT INTO t1 VALUES (1),(2);
CREATE VIEW v1 AS SELECT /*+ NO_MRR(t1 idx_a) */ a FROM t1;
SELECT * FROM v1;
a
1
2
# Make sure hints are present inside the view definition:
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select /*+ NO_MRR(`t1` `idx_a`) */ `t1`.`a` AS `a` from `t1`	utf8mb4	utf8mb4_uca1400_ai_ci
EXPLAIN EXTENDED SELECT * FROM v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	index	NULL	idx_a	5	NULL	2	100.00	Using index
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
CREATE OR REPLACE VIEW v1 AS SELECT /*+ NO_MRR(t1 idx_a) BKA(t1)*/ a FROM t1;
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select /*+ BKA(`t1`) NO_MRR(`t1` `idx_a`) */ `t1`.`a` AS `a` from `t1`	utf8mb4	utf8mb4_uca1400_ai_ci
ALTER VIEW v1 AS SELECT /*+ QB_NAME(q1)*/ a FROM t1;
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select /*+ QB_NAME(`q1`) */ `t1`.`a` AS `a` from `t1`	utf8mb4	utf8mb4_uca1400_ai_ci
SELECT * FROM v1;
a
1
2
# Wrong place for the hint, must be simply ignored:
CREATE OR REPLACE VIEW v1 AS SELECT a /*+ NO_ICP(t1 idx_a)*/ FROM t1;
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	utf8mb4	utf8mb4_uca1400_ai_ci
# Incorrect hint does not prevent view creation, only a warning generated:
CREATE VIEW v2 AS SELECT /*+ BAD HINT*/ a+10 FROM t1;
Warnings:
Warning	1064	Optimizer hint syntax error near 'BAD HINT*/ a+10 FROM t1' at line 1
SELECT * FROM v2;
a+10
11
12
EXPLAIN EXTENDED SELECT * FROM v2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	index	NULL	idx_a	5	NULL	2	100.00	Using index
Warnings:
Note	1003	select `test`.`t1`.`a` + 10 AS `a+10` from `test`.`t1`
SHOW CREATE VIEW v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` + 10 AS `a+10` from `t1`	utf8mb4	utf8mb4_uca1400_ai_ci
DROP VIEW v1, v2;
DROP TABLE t1;
#
# Tests of parser for optimizer hints
#
CREATE TABLE t1 (i INT, j INT);
CREATE INDEX i1 ON t1(i);
CREATE INDEX i2 ON t1(j);

# invalid hint sequences, must issue warnings:

SELECT /*+*/ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '*/ 1' at line 1
SELECT /*+ */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '*/ 1' at line 1
SELECT /*+ * ** / // /* */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '* ** / // /* */ 1' at line 1
SELECT /*+ @ */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '@ */ 1' at line 1
SELECT /*+ @foo */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '@foo */ 1' at line 1
SELECT /*+ foo@bar */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'foo@bar */ 1' at line 1
SELECT /*+ foo @bar */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'foo @bar */ 1' at line 1
SELECT /*+ `@` */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '`@` */ 1' at line 1
SELECT /*+ `@foo` */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '`@foo` */ 1' at line 1
SELECT /*+ `foo@bar` */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '`foo@bar` */ 1' at line 1
SELECT /*+ `foo @bar` */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '`foo @bar` */ 1' at line 1
SELECT /*+ BKA( @) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT /*+ BKA( @) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT /*+ BKA(t1 @) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1

# We don't support "*/" inside quoted identifiers (syntax error):

SELECT /*+ BKA(`test*/`) */ 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`) */ 1' at line 1

# invalid hint sequences, must issue warnings:

SELECT  /*+ NO_ICP() */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT  /*+NO_ICP()*/ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ')*/ 1' at line 1
SELECT  /*+ NO_ICP () */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT  /*+ NO_ICP (  ) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT  /*+ NO_ICP() */ 1 UNION SELECT 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1 UNION SELECT 1' at line 1
(SELECT /*+ NO_ICP() */ 1) UNION (SELECT 1);
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1) UNION (SELECT 1)' at line 1
((SELECT  /* + NO_ICP() */ 1));
1
1
UPDATE  /*+ NO_ICP() */ t1 SET i = 10;
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ t1 SET i = 10' at line 1
INSERT  /*+ NO_ICP() */ INTO t1 VALUES ();
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ INTO t1 VALUES ()' at line 1
DELETE  /*+ NO_ICP() */ FROM t1 WHERE 1;
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ FROM t1 WHERE 1' at line 1
SELECT /*+ BKA(a b) */   1 FROM t1 a, t1 b;
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'b) */   1 FROM t1 a, t1 b' at line 1
SELECT /*+ NO_ICP(i1) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `i1`@`select#1` for NO_ICP hint
SELECT /*+ NO_ICP(i1 i2) */ 1 FROM t1;
1
Warnings:
Warning	4222	Unresolved index name `i1`@`select#1` `i2` for NO_ICP hint
SELECT /*+ NO_ICP(@qb ident) */ 1 FROM t1;
1
Warnings:
Warning	4220	Query block name `qb` is not found for NO_ICP hint

# valid hint sequences, no warnings expected:

SELECT /*+ BKA(t1) */    1 FROM t1;
1
EXPLAIN EXTENDED SELECT /*+ QB_NAME(qb1) */ 1 UNION SELECT /*+ QB_NAME(qb2) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select /*+ QB_NAME(`qb1`) */ 1 AS `1` union /* select#2 */ select /*+ QB_NAME(`qb2`) */ 1 AS `1`
EXPLAIN EXTENDED (SELECT /*+ QB_NAME(qb1) */ 1) UNION (SELECT /*+ QB_NAME(qb2) */ 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	(/* select#1 */ select /*+ QB_NAME(`qb1`) */ 1 AS `1`) union (/* select#2 */ select /*+ QB_NAME(`qb2`) */ 1 AS `1`)
#
# test explainable statements for hint support:
# they should warn with a hint syntax error near "test */"
#
EXPLAIN EXTENDED SELECT /*+ test */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Warning	1064	Optimizer hint syntax error near 'test */ 1' at line 1
Note	1003	select 1 AS `1`
EXPLAIN EXTENDED INSERT /*+ test */ INTO t1 VALUES (10, 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
Warnings:
Warning	1064	Optimizer hint syntax error near 'test */ INTO t1 VALUES (10, 10)' at line 1
Note	1003	insert into `test`.`t1` values (10,10)
EXPLAIN EXTENDED UPDATE /*+ test */ t1 SET i = 10 WHERE j = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Warning	1064	Optimizer hint syntax error near 'test */ t1 SET i = 10 WHERE j = 10' at line 1
Note	1003	update `test`.`t1` set `test`.`t1`.`i` = 10 where `test`.`t1`.`j` = 10
EXPLAIN EXTENDED DELETE /*+ test */ FROM t1 WHERE i = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Warning	1064	Optimizer hint syntax error near 'test */ FROM t1 WHERE i = 10' at line 1
Note	1003	delete  from `test`.`t1` using dual where `test`.`t1`.`i` = 10

# non-alphabetic and non-ASCII identifiers, should warn:

CREATE INDEX 3rd_index ON t1(i, j);
SELECT /*+ NO_ICP(3rd_index) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `3rd_index`@`select#1` for NO_ICP hint
CREATE INDEX $index ON t1(j, i);
SELECT /*+ NO_ICP($index) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `$index`@`select#1` for NO_ICP hint
CREATE TABLE ` quoted name test` (i INT);
SELECT /*+ BKA(` quoted name test`) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name ` quoted name test`@`select#1` for BKA hint
SELECT /*+ BKA(` quoted name test`@`select#1`) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name ` quoted name test`@`select#1` for BKA hint
DROP TABLE ` quoted name test`;
SET SQL_MODE = 'ANSI_QUOTES';
CREATE TABLE " quoted name test" (i INT);
SELECT /*+ BKA(" quoted name test") */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name " quoted name test"@"select#1" for BKA hint
SELECT /*+ BKA(" quoted name test"@"select#1") */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name " quoted name test"@"select#1" for BKA hint
CREATE TABLE `test1``test2``` (i INT);
SELECT /*+ BKA(`test1``test2```) */ 1;
1
1
Warnings:
Warning	4221	Unresolved table name "test1`test2`"@"select#1" for BKA hint
SELECT /*+ BKA("test1""test2""") */ 1;
1
1
Warnings:
Warning	4221	Unresolved table name "test1""test2"""@"select#1" for BKA hint
SET SQL_MODE = '';
# should warn:
SELECT /*+ BKA(" quoted name test") */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name ` quoted name test`@`select#1` for BKA hint
DROP TABLE ` quoted name test`;
DROP TABLE `test1``test2```;
# Valid hints, no warning:
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`*`) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select /*+ QB_NAME(`*`) */ 1 AS `1`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`a*`) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select /*+ QB_NAME(`a*`) */ 1 AS `1`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`*b`) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select /*+ QB_NAME(`*b`) */ 1 AS `1`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`a
b`) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select /*+ QB_NAME(`a
b`) */ 1 AS `1`
# Identifiers starting with digits must be supported:
CREATE OR REPLACE TABLE 0a (8a INT, KEY 6a(8a));
EXPLAIN EXTENDED SELECT /*+ NO_MRR(0a 6a) BKA(0a)*/ 8a FROM 0a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	0a	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
Warnings:
Note	1003	select /*+ BKA(`0a`@`select#1`) NO_MRR(`0a`@`select#1` `6a`) */ NULL AS `8a` from `test`.`0a`
DROP TABLE 0a;
# hint syntax error: empty quoted identifier
EXPLAIN EXTENDED SELECT /*+ QB_NAME(``) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Warning	1064	Optimizer hint syntax error near '``) */ 1' at line 1
Note	1003	select 1 AS `1`
SET NAMES utf8;
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`\BF``\BF`) */ 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select /*+ QB_NAME(`\BF``\BF`) */ 1 AS `1`
CREATE TABLE tableТ (i INT);
# invalid hints, should warn:
SELECT /*+ BKA(tableТ) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `tableТ`@`select#1` for BKA hint
SELECT /*+ BKA(test@tableТ) */ 1 FROM t1;
1
Warnings:
Warning	4220	Query block name `tableТ` is not found for BKA hint
DROP TABLE tableТ;
CREATE TABLE таблица (i INT);
SELECT /*+ BKA(`таблица`) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `таблица`@`select#1` for BKA hint
SELECT /*+ BKA(таблица) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `таблица`@`select#1` for BKA hint
SELECT /*+ BKA(test@таблица) */ 1 FROM t1;
1
Warnings:
Warning	4220	Query block name `таблица` is not found for BKA hint
SELECT /*+ NO_ICP(`\D1`) */ 1 FROM t1;
1
Warnings:
Warning	4221	Unresolved table name `\D1`@`select#1` for NO_ICP hint
DROP TABLE таблица;

# derived tables and other subqueries:

SELECT * FROM (SELECT /*+ DEBUG_HINT3 */ 1) a;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'DEBUG_HINT3 */ 1) a' at line 1
SELECT (SELECT /*+ DEBUG_HINT3 */ 1);
(SELECT /*+ DEBUG_HINT3 */ 1)
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'DEBUG_HINT3 */ 1)' at line 1
SELECT 1 FROM DUAL WHERE 1 IN (SELECT /*+ DEBUG_HINT3 */ 1);
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'DEBUG_HINT3 */ 1)' at line 1

# invalid hint sequences (should warn):

SELECT /*+ 10 */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '10 */ 1' at line 1
SELECT /*+ NO_ICP() */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT /*+ NO_ICP(10) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '10) */ 1' at line 1
SELECT /*+ NO_ICP( */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '*/ 1' at line 1
SELECT /*+ NO_ICP) */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1' at line 1
SELECT /*+ NO_ICP(t1 */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '*/ 1' at line 1
SELECT /*+ NO_ICP(t1 ( */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '( */ 1' at line 1
(SELECT 1) UNION (SELECT /*+ NO_ICP() */ 1);
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1)' at line 1
INSERT INTO t1 VALUES (1, 1), (2, 2);

# wrong place for hint, so recognize that stuff as a regular commentary:

SELECT 1 FROM /*+ regular commentary, not a hint! */ t1;
1
1
1
SELECT 1 FROM /*+ #1 */ t1 WHERE /*+ #2 */ 1 /*+ #3 */;
1
1
1
SELECT 1 FROM /*+ QB_NAME(q1) */ t1 /*+ NO_ICP() */WHERE /*+ NO_MRR(t1) */ 1 /*+ #3 */;
1
1
1
# Warnings expected:
SELECT /*+ NO_ICP() */ 1
FROM /*+ regular commentary, not a hint! */ t1;
1
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near ') */ 1
FROM /*+ regular commentary, not a hint! */ t1' at line 1
SELECT /*+ NO_ICP(t1) bad_hint */ 1 FROM t1;
1
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near 'bad_hint */ 1 FROM t1' at line 1
SELECT /*+
NO_ICP(@qb ident)
*/ 1 FROM t1;
1
1
1
Warnings:
Warning	4220	Query block name `qb` is not found for NO_ICP hint
SELECT /*+
? bad syntax
*/ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '? bad syntax
*/ 1' at line 2
SELECT
/*+ ? bad syntax */ 1;
1
1
Warnings:
Warning	1064	Optimizer hint syntax error near '? bad syntax */ 1' at line 2
DROP TABLE t1;

# MDEV-36486 Optimizer hints are resolved against the INSERT part of INSERT..SELECT

CREATE TABLE t1 (a INT, KEY(a));
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 (a INT, KEY(a));
INSERT INTO t2 VALUES (1),(2),(3);
# See that the range optimization is employed when there are no hints:
EXPLAIN EXTENDED
INSERT INTO t1 (a) SELECT a FROM t1 WHERE a>1 AND a<=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	a	a	5	NULL	2	100.00	Using where; Using index; Using temporary
Warnings:
Note	1003	insert into `test`.`t1`(a) select sql_buffer_result `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 and `test`.`t1`.`a` <= 3
# No range optimization any more:
EXPLAIN EXTENDED
INSERT INTO t1 (a) SELECT /*+ no_range_optimization (t1 a)*/ a FROM t1 WHERE a>1 AND a<=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	index	a	a	5	NULL	3	100.00	Using where; Using index; Using temporary
Warnings:
Note	1003	insert into `test`.`t1`(a) select /*+ NO_RANGE_OPTIMIZATION(`t1`@`select#1` `a`) */ sql_buffer_result `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 and `test`.`t1`.`a` <= 3
# Hints at the INSERT part of a INSERT..SELECT are not supported:
EXPLAIN EXTENDED
INSERT  /*+ no_range_optimization (t1)*/ INTO t1 (a) SELECT a FROM t1 WHERE a>1 AND a<=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	a	a	5	NULL	2	100.00	Using where; Using index; Using temporary
Warnings:
Warning	4225	Optimizer hints at the INSERT part of a INSERT..SELECT statement are not supported
Note	1003	insert into `test`.`t1`(a) select sql_buffer_result `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 and `test`.`t1`.`a` <= 3
# If hints are present at both INSERT and SELECT parts,
# those at the INSERT part are ignored
EXPLAIN EXTENDED
INSERT  /*+ no_range_optimization (t1)*/ INTO t1 (a) SELECT /*+ mrr(t1)*/ a 
FROM t1 WHERE a>1 AND a<=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	a	a	5	NULL	2	100.00	Using where; Using index; Using temporary
Warnings:
Warning	4225	Optimizer hints at the INSERT part of a INSERT..SELECT statement are not supported
Note	1003	insert into `test`.`t1`(a) select /*+ MRR(`t1`@`select#1`) */ sql_buffer_result `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 and `test`.`t1`.`a` <= 3
# Table `t2` cannot be resolved since it is not present in the SELECT part
# (a warning expected):
EXPLAIN EXTENDED
INSERT INTO t2 (a) SELECT /*+ no_range_optimization (t2)*/ a FROM t1 WHERE a>1 AND a<=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	a	a	5	NULL	2	100.00	Using where; Using index
Warnings:
Warning	4221	Unresolved table name `t2`@`select#1` for NO_RANGE_OPTIMIZATION hint
Note	1003	insert into `test`.`t2`(a) select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 and `test`.`t1`.`a` <= 3
DROP TABLE t1, t2;
set optimizer_switch = DEFAULT;
set join_cache_level = DEFAULT;
#
# MDEV-36675 Optimizer hints parser catches irrelevant `thd->is_error()`
#              set by multi-RENAME TABLE
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER t1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t1 VALUES (0);
RENAME TABLE t1 TO t2, t3 TO t4;
ERROR 42S02: Table 'test.t3' doesn't exist
SHOW CREATE TRIGGER t1;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
t1		CREATE DEFINER=`root`@`localhost` TRIGGER t1 AFTER INSERT ON `t1` FOR EACH ROW INSERT INTO t1 VALUES (0)	utf8mb3	utf8mb3_uca1400_ai_ci	utf8mb4_uca1400_ai_ci	#
RENAME TABLE t1 TO t2;
SHOW CREATE TRIGGER t1;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
t1		CREATE DEFINER=`root`@`localhost` TRIGGER t1 AFTER INSERT ON `t2` FOR EACH ROW INSERT INTO t1 VALUES (0)	utf8mb3	utf8mb3_uca1400_ai_ci	utf8mb4_uca1400_ai_ci	#
DROP TABLE t2;
#
# End of 12.0 tests
#
#
# MDEV-37260 Hints addressed by implicit query block name
#
# Simple JOIN Query
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department VARCHAR(50)
);
CREATE TABLE salaries (
emp_id INT,
salary DECIMAL(10, 2),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
INSERT INTO employees (emp_id, emp_name, department) VALUES
(101, 'Alice', 'Engineering'),
(102, 'Bob', 'Engineering'),
(103, 'Charlie', 'Sales'),
(104, 'Diana', 'Sales'),
(105, 'Eve', 'Engineering');
INSERT INTO salaries (emp_id, salary) VALUES
(101, 120000.00),
(102, 95000.00),
(103, 110000.00),
(104, 85000.00),
(105, 130000.00);
EXPLAIN
SELECT /*+ NO_MERGE(s@`select#1`) */ e.emp_name, s.salary FROM employees e JOIN  (select * from salaries) s ON e.emp_id = s.emp_id WHERE     s.salary > (SELECT AVG(salary) FROM salaries);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	e	ALL	PRIMARY	NULL	NULL	NULL	5	
1	PRIMARY	<derived2>	ref	key0	key0	5	test.e.emp_id	1	Using where
3	SUBQUERY	salaries	ALL	NULL	NULL	NULL	NULL	5	
2	DERIVED	salaries	ALL	NULL	NULL	NULL	NULL	5	
EXPLAIN
SELECT /*+ MERGE(s@`select#1`) */ e.emp_name, s.salary FROM employees e JOIN  (select * from salaries) s ON e.emp_id = s.emp_id WHERE     s.salary > (SELECT AVG(salary) FROM salaries);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	salaries	ALL	emp_id	NULL	NULL	NULL	5	Using where
1	PRIMARY	e	eq_ref	PRIMARY	PRIMARY	4	test.salaries.emp_id	1	
3	SUBQUERY	salaries	ALL	NULL	NULL	NULL	NULL	5	
# End Simple JOIN Query
# JOIN_ORDER Example
CREATE TABLE ten(a int);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE twenty(a int);
INSERT INTO twenty SELECT a FROM ten;
INSERT INTO twenty SELECT a+10 FROM ten;
EXPLAIN SELECT /*+ JOIN_ORDER(@`select#2` twenty,ten) */  * FROM (SELECT ten.a AS a FROM (ten JOIN twenty) WHERE (ten.a = twenty.a) LIMIT 1000 ) T;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	200	
2	DERIVED	twenty	ALL	NULL	NULL	NULL	NULL	20	
2	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
EXPLAIN SELECT * FROM (SELECT ten.a AS a FROM (ten JOIN twenty) WHERE (ten.a = twenty.a) LIMIT 1000 ) T;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	200	
2	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	twenty	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
# End JOIN_ORDER Example
# CTE Example
create table t1 (a int, b int, c int);
create table t2 (a int, b int, c int, d decimal);
insert into t1 values
(1,21,345), (1,33,7), (8,33,114), (1,21,500), (1,19,107), (5,14,787),
(8,33,123), (9,10,211), (5,16,207), (1,33,988), (5,27,132), (1,21,104),
(6,20,309), (6,20,315), (1,21,101), (8,33,404), (9,10,800), (1,21,123),
(7,11,708), (6,20,214);
create index t1_a on t1 (a);
insert into t2 values
(2,3,207,207.0000), (1,21,909,12.0000), (7,13,312,406.0000),
(8,64,248,107.0000), (6,20,315,279.3333), (1,19,203,107.0000),
(8,80,800,314.0000), (3,12,231,190.0000), (6,23,303,909.0000);
Warnings:
Note	1265	Data truncated for column 'd' at row 5
create view v1 as select a, b, max(c) as max_c, avg(c) as avg_c from t1
group by a,b having max_c < 707;
create table t3 select 2*seq as a, 2*seq+1 as b from seq_0_to_1000;
CREATE TABLE t4 (a INT, b INT);
INSERT INTO t4 VALUES (1,2),(2,3),(3,4);
create table t5 select seq as i, 10*seq as j from seq_1_to_10;
create view v2 as select * from t5;
create table t6 (a int primary key);
insert into t6 select * from seq_1_to_50;
create view v6 as select a from t6 where a mod 2 = 1;
explain format=json with cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(v1@`select#2`) */ * from cte;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "loops": 1,
          "rows": 9,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "<derived3>",
            "access_type": "ALL",
            "loops": 9,
            "rows": 20,
            "cost": "COST_REPLACED",
            "filtered": 100,
            "attached_condition": "v1.max_c > 300 or v1.max_c < 135"
          },
          "buffer_type": "flat",
          "buffer_size": "238",
          "join_type": "BNL",
          "attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
          "materialized": {
            "query_block": {
              "select_id": 3,
              "cost": "COST_REPLACED",
              "having_condition": "max_c < 707",
              "filesort": {
                "sort_key": "t1.a, t1.b",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "t1",
                        "access_type": "ALL",
                        "loops": 1,
                        "rows": 20,
                        "cost": "COST_REPLACED",
                        "filtered": 100
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  }
}
# End CTE Example
# Recursive CTE Example
explain format=json with recursive cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select * from cte where max_c < 100
) select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@`select#2` v1) */ * from cte;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.018043228,
    "nested_loop": [
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ALL",
          "loops": 1,
          "rows": 180,
          "cost": 0.018043228,
          "filtered": 100,
          "materialized": {
            "query_block": {
              "recursive_union": {
                "table_name": "<union2,3>",
                "access_type": "ALL",
                "query_specifications": [
                  {
                    "query_block": {
                      "select_id": 2,
                      "cost": 0.039600611,
                      "nested_loop": [
                        {
                          "table": {
                            "table_name": "t2",
                            "access_type": "ALL",
                            "loops": 1,
                            "rows": 9,
                            "cost": 0.011443245,
                            "filtered": 100
                          }
                        },
                        {
                          "block-nl-join": {
                            "table": {
                              "table_name": "<derived4>",
                              "access_type": "ALL",
                              "loops": 9,
                              "rows": 20,
                              "cost": 0.028157366,
                              "filtered": 100,
                              "attached_condition": "v1.max_c > 300 or v1.max_c < 135"
                            },
                            "buffer_type": "flat",
                            "buffer_size": "238",
                            "join_type": "BNL",
                            "attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
                            "materialized": {
                              "query_block": {
                                "select_id": 4,
                                "cost": 0.025950929,
                                "having_condition": "max_c < 707",
                                "filesort": {
                                  "sort_key": "t1.a, t1.b",
                                  "temporary_table": {
                                    "nested_loop": [
                                      {
                                        "table": {
                                          "table_name": "t1",
                                          "access_type": "ALL",
                                          "loops": 1,
                                          "rows": 20,
                                          "cost": 0.0131637,
                                          "filtered": 100
                                        }
                                      }
                                    ]
                                  }
                                }
                              }
                            }
                          }
                        }
                      ]
                    }
                  },
                  {
                    "query_block": {
                      "select_id": 3,
                      "operation": "UNION",
                      "cost": 0.018043228,
                      "nested_loop": [
                        {
                          "table": {
                            "table_name": "<derived2>",
                            "access_type": "ALL",
                            "loops": 1,
                            "rows": 180,
                            "cost": 0.018043228,
                            "filtered": 100,
                            "attached_condition": "cte.max_c < 100"
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    ]
  }
}
# End Recursive CTE Example
# Deeply nested query block example
explain format=json
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@`select#5` dv6) */ * from
(select * from
(select * from
(select * from
(select avg_a from
(select avg(a) as avg_a from v6) dv6
) dv4) dv3) dv2) dv1 where avg_a <> 0;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "<derived6>",
          "access_type": "ALL",
          "loops": 1,
          "rows": 50,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "attached_condition": "dv6.avg_a <> 0",
          "materialized": {
            "query_block": {
              "select_id": 6,
              "cost": "COST_REPLACED",
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t6",
                    "access_type": "index",
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": ["a"],
                    "loops": 1,
                    "rows": 50,
                    "cost": "COST_REPLACED",
                    "filtered": 100,
                    "attached_condition": "t6.a MOD 2 = 1",
                    "using_index": true
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}
# End deeply nested query block example
# Stored procedure example
CREATE PROCEDURE sp_test_implicit_hint()
BEGIN
EXPLAIN SELECT /*+ NO_MERGE(@`select#1` dt) */ *
FROM (SELECT * FROM ten) dt;
END|
CALL sp_test_implicit_hint();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
DROP PROCEDURE sp_test_implicit_hint;
CREATE PROCEDURE sp_test_implicit_hint()
BEGIN
EXPLAIN SELECT /*+ MERGE(@`select#1` dt) */ *
FROM (SELECT * FROM ten) dt;
END|
CALL sp_test_implicit_hint();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	
DROP PROCEDURE sp_test_implicit_hint;
# End stored procedure example
# Prepared statement example
PREPARE stmt1 FROM 'EXPLAIN SELECT /*+ NO_MERGE(@`select#1` dt) */ * FROM (SELECT * FROM ten) dt;';
EXECUTE stmt1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'EXPLAIN SELECT /*+ MERGE(@`select#1` dt) */ * FROM (SELECT * FROM ten) dt;';
EXECUTE stmt1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	
DEALLOCATE PREPARE stmt1;
# End prepared statement example.
# INDEX/JOIN_INDEX example
CREATE TABLE t7 (a INT, b INT, c INT, d INT,
KEY i_a(a), KEY i_b(b),
KEY i_ab(a,b), KEY i_c(c), KEY i_d(d));
INSERT INTO t7 VALUES
(1,1,1,1),(2,2,2,1),(3,3,3,1),(4,4,4,1),
(5,5,5,1),(6,6,6,1),(7,7,7,1),(8,8,8,1);
INSERT INTO t7 SELECT a,b, c + 10, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 20, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 40, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 80, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 160, d FROM t7;
ANALYZE TABLE t7;
Table	Op	Msg_type	Msg_text
test.t7	analyze	status	Engine-independent statistics collected
test.t7	analyze	status	OK
EXPLAIN SELECT /*+ NO_MERGE(@`select#1` dt) NO_INDEX(@`select#2` t7) */ * FROM (SELECT a FROM t7 WHERE a > 1 AND a < 3) dt WHERE dt.a = 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	32	Using where
2	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	Using where
# End INDEX/JOIN_INDEX example
DROP TABLE salaries, employees, ten, twenty, t1, t2, t3, t4, t5, t6, t7;
DROP VIEW v1, v2, v6;
#
# MDEV-37260 Specifying hints in queries during CREATE VIEW AS
#
CREATE TABLE ten(a int);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE twenty(a int);
INSERT INTO twenty SELECT a FROM ten;
INSERT INTO twenty SELECT a+10 FROM ten;
# `select#X` in a VIEW example will result in warning
CREATE VIEW v2 AS SELECT /*+ JOIN_ORDER(@`select#3` twenty, ten) */ * FROM (SELECT ten.a AS a FROM (ten JOIN twenty) WHERE (ten.a = twenty.a) LIMIT 1000 ) t;
Warnings:
Warning	4242	Implicit query block names are ignored for hints specified within VIEWs
EXPLAIN SELECT * FROM v2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	200	
3	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
3	DERIVED	twenty	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
SHOW CREATE VIEW v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t`.`a` AS `a` from (select `ten`.`a` AS `a` from (`ten` join `twenty`) where `ten`.`a` = `twenty`.`a` limit 1000) `t`	utf8mb3	utf8mb3_uca1400_ai_ci
DROP VIEW v2;
# Creating a VIEW with hints otherwise will work
CREATE VIEW v2 AS SELECT /*+ JOIN_ORDER(@qb2 twenty,ten) */ * FROM (SELECT /*+ QB_NAME(qb2) */ ten.a AS a FROM (ten JOIN twenty) WHERE (ten.a = twenty.a) LIMIT 1000 ) t;
EXPLAIN SELECT * FROM v2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	200	
3	DERIVED	twenty	ALL	NULL	NULL	NULL	NULL	20	
3	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
SHOW CREATE VIEW v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select /*+ JOIN_ORDER(@`qb2` `twenty`,`ten`) */ `t`.`a` AS `a` from (select /*+ QB_NAME(`qb2`) */ `ten`.`a` AS `a` from (`ten` join `twenty`) where `ten`.`a` = `twenty`.`a` limit 1000) `t`	utf8mb3	utf8mb3_uca1400_ai_ci
# This next CREATE VIEW statement should match the output of SHOW CREATE VIEW v2.  The following EXPLAIN output should match EXPLAIN SELECT * FROM v2 as a way to demonstrate that the output of SHOW CREATE VIEW is correct.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select /*+ JOIN_ORDER(@`qb2` `twenty`,`ten`) */ `t`.`a` AS `a` from (select /*+ QB_NAME(`qb2`) */ `ten`.`a` AS `a` from (`ten` join `twenty`) where `ten`.`a` = `twenty`.`a` limit 1000) `t`;
EXPLAIN SELECT * FROM v3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	200	
3	DERIVED	twenty	ALL	NULL	NULL	NULL	NULL	20	
3	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
# Named hint example.
CREATE TABLE t4 (
pk INT PRIMARY KEY,
a INT,
b INT,
c INT,
filler varchar(100),
key(a,b,c)
) engine=myisam;
INSERT INTO t4 SELECT seq, seq, seq, seq, 'hello' FROM seq_1_to_10000;
CREATE VIEW v5 AS SELECT /*+ NO_ICP(t4) */ * FROM t4 WHERE a < 10 AND (b+1>3);
FLUSH STATUS;
SELECT * FROM information_schema.session_status WHERE variable_name= 'handler_icp_match';
VARIABLE_NAME	VARIABLE_VALUE
HANDLER_ICP_MATCH	0
SELECT * FROM v5;
pk	a	b	c	filler
3	3	3	3	hello
4	4	4	4	hello
5	5	5	5	hello
6	6	6	6	hello
7	7	7	7	hello
8	8	8	8	hello
9	9	9	9	hello
SELECT * FROM information_schema.session_status WHERE variable_name= 'handler_icp_match';
VARIABLE_NAME	VARIABLE_VALUE
HANDLER_ICP_MATCH	0
EXPLAIN SELECT * from v5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	a	a	5	NULL	8	Using where
# End named hint example.
# JOIN of VIEW with table where the local hint in the VIEW specifies NO_ICP and is JOIN'd with the same table allowing ICP.
EXPLAIN SELECT * FROM t4 x, t4 y WHERE x.a < 10 AND (x.b+1>3) AND y.a < 10 AND (y.b+1>3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	x	range	a	a	5	NULL	8	Using index condition
1	SIMPLE	y	range	a	a	5	NULL	8	Using index condition; Using where; Using join buffer (flat, BNL join)
EXPLAIN SELECT * FROM t4 x, v5 y WHERE x.a < 10 AND (x.b+1>3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	x	range	a	a	5	NULL	8	Using index condition
1	SIMPLE	t4	range	a	a	5	NULL	8	Using where; Using join buffer (flat, BNL join)
EXPLAIN SELECT * FROM v5 x, t4 y WHERE y.a < 10 AND (y.b+1>3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	a	a	5	NULL	8	Using where
1	SIMPLE	y	range	a	a	5	NULL	8	Using index condition; Using where; Using join buffer (flat, BNL join)
EXPLAIN SELECT * FROM v5 x, v5 y;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	range	a	a	5	NULL	8	Using where
1	SIMPLE	t4	range	a	a	5	NULL	8	Using where; Using join buffer (flat, BNL join)
# End of JOIN of VIEW with table where the local hint in the VIEW specifies NO_ICP and is JOIN'd with the same table allowing ICP.
# INDEX/JOIN_INDEX example
CREATE TABLE t7 (a INT, b INT, c INT, d INT,
KEY i_a(a), KEY i_b(b),
KEY i_ab(a,b), KEY i_c(c), KEY i_d(d));
INSERT INTO t7 VALUES
(1,1,1,1),(2,2,2,1),(3,3,3,1),(4,4,4,1),
(5,5,5,1),(6,6,6,1),(7,7,7,1),(8,8,8,1);
INSERT INTO t7 SELECT a,b, c + 10, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 20, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 40, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 80, d FROM t7;
INSERT INTO t7 SELECT a,b, c + 160, d FROM t7;
ANALYZE TABLE t7;
Table	Op	Msg_type	Msg_text
test.t7	analyze	status	Engine-independent statistics collected
test.t7	analyze	status	OK
CREATE VIEW v7 AS SELECT /*+ NO_MERGE(dt) NO_INDEX(@qb t7) */ * FROM (SELECT /*+ QB_NAME(qb) */ a FROM t7 WHERE a > 1 AND a < 3) dt WHERE dt.a = 2;
EXPLAIN SELECT * FROM v7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	32	Using where
3	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	Using where
SHOW CREATE VIEW v7;
View	Create View	character_set_client	collation_connection
v7	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v7` AS select /*+ NO_INDEX(`t7`@`qb`) NO_MERGE(`dt`) */ `dt`.`a` AS `a` from (select /*+ QB_NAME(`qb`) */ `t7`.`a` AS `a` from `t7` where `t7`.`a` > 1 and `t7`.`a` < 3) `dt` where `dt`.`a` = 2	utf8mb3	utf8mb3_uca1400_ai_ci
# End INDEX/JOIN_INDEX example
# Example of many nested derived tables are not merged.
EXPLAIN SELECT /*+ NO_MERGE(da) */ * FROM (SELECT /*+ NO_MERGE(db) */ * FROM (SELECT /*+ NO_MERGE(dc) */ * FROM (SELECT /*+ NO_MERGE(dd) */ * FROM (SELECT /*+ NO_MERGE(de) */ * FROM (SELECT /*+ NO_MERGE(df) */ * FROM (SELECT /*+ NO_MERGE(dg) */ * FROM (SELECT /*+ NO_MERGE(dh) */ * FROM (SELECT /*+ NO_MERGE(di) */ * FROM (SELECT /*+ NO_MERGE(dj) */ * FROM (SELECT /*+ NO_MERGE(dk) */ * FROM (SELECT /*+ NO_MERGE(dl) */ * FROM (SELECT * FROM ten) dl) dk) dj) di) dh) dg) df) de) dd) dc) db) da;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	10	
3	DERIVED	<derived4>	ALL	NULL	NULL	NULL	NULL	10	
4	DERIVED	<derived5>	ALL	NULL	NULL	NULL	NULL	10	
5	DERIVED	<derived6>	ALL	NULL	NULL	NULL	NULL	10	
6	DERIVED	<derived7>	ALL	NULL	NULL	NULL	NULL	10	
7	DERIVED	<derived8>	ALL	NULL	NULL	NULL	NULL	10	
8	DERIVED	<derived9>	ALL	NULL	NULL	NULL	NULL	10	
9	DERIVED	<derived10>	ALL	NULL	NULL	NULL	NULL	10	
10	DERIVED	<derived11>	ALL	NULL	NULL	NULL	NULL	10	
11	DERIVED	<derived12>	ALL	NULL	NULL	NULL	NULL	10	
12	DERIVED	<derived13>	ALL	NULL	NULL	NULL	NULL	10	
13	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
EXPLAIN SELECT /*+ NO_MERGE(da@`select#1`) */ * FROM (SELECT /*+ NO_MERGE(db@`select#2`) */ * FROM (SELECT /*+ NO_MERGE(dc@`select#3`) */ * FROM (SELECT /*+ NO_MERGE(dd@`select#4`) */ * FROM (SELECT /*+ NO_MERGE(de@`select#5`) */ * FROM (SELECT /*+ NO_MERGE(df@`select#6`) */ * FROM (SELECT /*+ NO_MERGE(dg@`select#7`) */ * FROM (SELECT /*+ NO_MERGE(dh@`select#8`) */ * FROM (SELECT /*+ NO_MERGE(di@`select#9`) */ * FROM (SELECT /*+ NO_MERGE(dj@`select#10`) */ * FROM (SELECT /*+ NO_MERGE(dk@`select#11`) */ * FROM (SELECT /*+ NO_MERGE(dl@`select#12`) */ * FROM (SELECT * FROM ten) dl) dk) dj) di) dh) dg) df) de) dd) dc) db) da;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	10	
3	DERIVED	<derived4>	ALL	NULL	NULL	NULL	NULL	10	
4	DERIVED	<derived5>	ALL	NULL	NULL	NULL	NULL	10	
5	DERIVED	<derived6>	ALL	NULL	NULL	NULL	NULL	10	
6	DERIVED	<derived7>	ALL	NULL	NULL	NULL	NULL	10	
7	DERIVED	<derived8>	ALL	NULL	NULL	NULL	NULL	10	
8	DERIVED	<derived9>	ALL	NULL	NULL	NULL	NULL	10	
9	DERIVED	<derived10>	ALL	NULL	NULL	NULL	NULL	10	
10	DERIVED	<derived11>	ALL	NULL	NULL	NULL	NULL	10	
11	DERIVED	<derived12>	ALL	NULL	NULL	NULL	NULL	10	
12	DERIVED	<derived13>	ALL	NULL	NULL	NULL	NULL	10	
13	DERIVED	ten	ALL	NULL	NULL	NULL	NULL	10	
# End example of many nested derived tables are not merged.
# Example of VIEW hints displaying properly in warning note
CREATE TABLE t1 ( a int, b int);
INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
CREATE TABLE t2 AS SELECT * from t1;
ALTER TABLE t2 ADD INDEX(a);
CREATE VIEW v23 AS SELECT /*+ NO_INDEX(t2 a) */ * FROM t2 WHERE a<2;
SHOW CREATE VIEW v23;
View	Create View	character_set_client	collation_connection
v23	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v23` AS select /*+ NO_INDEX(`t2` `a`) */ `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` where `t2`.`a` < 2	utf8mb3	utf8mb3_uca1400_ai_ci
EXPLAIN EXTENDED SELECT * FROM v23;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
Warnings:
Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(du) */ * FROM (SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * from v23) dt) du;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	100.00	
2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	10	100.00	
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select /*+ NO_MERGE(`dt`) NO_MERGE(`du`) */ `du`.`a` AS `a`,`du`.`b` AS `b` from (/* select#2 */ select `dt`.`a` AS `a`,`dt`.`b` AS `b` from (/* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2) `dt`) `du`
# merged view will retain v23 in note
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(v23) */ * FROM v23;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
Warnings:
Note	1003	select /*+ NO_MERGE(`v23`) */ `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2
CREATE VIEW v24 AS SELECT /*+ INDEX(`t2` `a`) */ `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` where `t2`.`a` < 2;
SHOW CREATE VIEW v24;
View	Create View	character_set_client	collation_connection
v24	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v24` AS select /*+ INDEX(`t2` `a`) */ `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` where `t2`.`a` < 2	utf8mb3	utf8mb3_uca1400_ai_ci
EXPLAIN EXTENDED SELECT * FROM v24;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	range	a	a	5	NULL	1	100.00	Using index condition
Warnings:
Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(du) */ * FROM (SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * from v24) dt) du;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
3	DERIVED	t2	range	a	a	5	NULL	1	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select /*+ NO_MERGE(`dt`) NO_MERGE(`du`) */ `du`.`a` AS `a`,`du`.`b` AS `b` from (/* select#2 */ select `dt`.`a` AS `a`,`dt`.`b` AS `b` from (/* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2) `dt`) `du`
# merged view will retain v24 in note
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM v24) dt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	t2	range	a	a	5	NULL	1	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select /*+ NO_MERGE(`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b` from (/* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`a` < 2) `dt`
# End example of VIEW hints displaying properly in warning note
# INSERT example without and with VIEW
EXPLAIN EXTENDED INSERT INTO ten SELECT /*+ NO_MERGE(dv) NO_BNL(@qb) */ 100*COUNT(dv.a) AS a FROM ( SELECT /*+ QB_NAME(qb) */ dt.a AS a FROM ten dt, ten du WHERE dt.a % 2 = 1 ) dv WHERE dv.a = 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
2	DERIVED	dt	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
2	DERIVED	du	ALL	NULL	NULL	NULL	NULL	10	100.00	
Warnings:
Note	1003	insert into `test`.`ten` /* select#1 */ select /*+ NO_BNL(@`qb`) NO_MERGE(`dv`@`select#1`) */ 100 * count(`dv`.`a`) AS `a` from (/* select#2 */ select /*+ QB_NAME(`qb`) */ `test`.`dt`.`a` AS `a` from `test`.`ten` `dt` join `test`.`ten` `du` where `test`.`dt`.`a` = 3) `dv` where `dv`.`a` = 3
CREATE VIEW v25 AS SELECT /*+ NO_MERGE(dv) NO_BNL(@qb) */ 100*COUNT(dv.a) AS a FROM ( SELECT /*+ QB_NAME(qb) */ dt.a AS a FROM ten dt, ten du WHERE dt.a % 2 = 1 ) dv WHERE dv.a = 3;
# merged view will retail qb name reference in the note
EXPLAIN EXTENDED INSERT INTO ten SELECT * FROM v25;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	100	100.00	
3	DERIVED	<derived4>	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
4	DERIVED	dt	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
4	DERIVED	du	ALL	NULL	NULL	NULL	NULL	10	100.00	
Warnings:
Note	1003	insert into `test`.`ten` /* select#1 */ select `v25`.`a` AS `a` from `test`.`v25`
# End INSERT example without and with VIEW
# CTE JOIN'd with itself
EXPLAIN EXTENDED WITH cte1 AS (SELECT * FROM t7 WHERE a<10 AND b<10 LIMIT 10) SELECT /*+ NO_INDEX(t7@`select#2`) NO_INDEX(t7@`select#3`) */ * FROM cte1 AS tbl1, cte1 AS tbl2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	100.00	
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	10	100.00	Using join buffer (flat, BNL join)
3	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	100.00	Using where
2	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	100.00	Using where
Warnings:
Note	1003	with cte1 as (/* select#2 */ select `test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t7`.`c` AS `c`,`test`.`t7`.`d` AS `d` from `test`.`t7` where `test`.`t7`.`a` < 10 and `test`.`t7`.`b` < 10 limit 10)/* select#1 */ select /*+ NO_INDEX(`t7`@`select#2`) NO_INDEX(`t7`@`select#3`) */ `tbl1`.`a` AS `a`,`tbl1`.`b` AS `b`,`tbl1`.`c` AS `c`,`tbl1`.`d` AS `d`,`tbl2`.`a` AS `a`,`tbl2`.`b` AS `b`,`tbl2`.`c` AS `c`,`tbl2`.`d` AS `d` from `cte1` `tbl1` join `cte1` `tbl2`
EXPLAIN EXTENDED WITH cte1 AS (SELECT * FROM t7 WHERE a<10 AND b<10 LIMIT 10) SELECT /*+ NO_INDEX(t7@`select#2`) INDEX(t7@`select#3`) */ * FROM cte1 AS tbl1, cte1 AS tbl2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	100.00	
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	10	100.00	Using join buffer (flat, BNL join)
3	DERIVED	t7	range	i_a,i_b,i_ab	i_a	5	NULL	240	93.75	Using index condition; Using where
2	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	100.00	Using where
Warnings:
Note	1003	with cte1 as (/* select#2 */ select `test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t7`.`c` AS `c`,`test`.`t7`.`d` AS `d` from `test`.`t7` where `test`.`t7`.`a` < 10 and `test`.`t7`.`b` < 10 limit 10)/* select#1 */ select /*+ NO_INDEX(`t7`@`select#2`) INDEX(`t7`@`select#3`) */ `tbl1`.`a` AS `a`,`tbl1`.`b` AS `b`,`tbl1`.`c` AS `c`,`tbl1`.`d` AS `d`,`tbl2`.`a` AS `a`,`tbl2`.`b` AS `b`,`tbl2`.`c` AS `c`,`tbl2`.`d` AS `d` from `cte1` `tbl1` join `cte1` `tbl2`
EXPLAIN EXTENDED WITH cte1 AS (SELECT * FROM t7 WHERE a<10 AND b<10 LIMIT 10) SELECT /*+ INDEX(t7@`select#2`) NO_INDEX(t7@`select#3`) */ * FROM cte1 AS tbl1, cte1 AS tbl2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	100.00	
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	10	100.00	Using join buffer (flat, BNL join)
3	DERIVED	t7	ALL	NULL	NULL	NULL	NULL	256	100.00	Using where
2	DERIVED	t7	range	i_a,i_b,i_ab	i_a	5	NULL	240	93.75	Using index condition; Using where
Warnings:
Note	1003	with cte1 as (/* select#2 */ select `test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t7`.`c` AS `c`,`test`.`t7`.`d` AS `d` from `test`.`t7` where `test`.`t7`.`a` < 10 and `test`.`t7`.`b` < 10 limit 10)/* select#1 */ select /*+ INDEX(`t7`@`select#2`) NO_INDEX(`t7`@`select#3`) */ `tbl1`.`a` AS `a`,`tbl1`.`b` AS `b`,`tbl1`.`c` AS `c`,`tbl1`.`d` AS `d`,`tbl2`.`a` AS `a`,`tbl2`.`b` AS `b`,`tbl2`.`c` AS `c`,`tbl2`.`d` AS `d` from `cte1` `tbl1` join `cte1` `tbl2`
EXPLAIN EXTENDED WITH cte1 AS (SELECT * FROM t7 WHERE a<10 AND b<10 LIMIT 10) SELECT /*+ INDEX(t7@`select#2`) INDEX(t7@`select#3`) */ * FROM cte1 AS tbl1, cte1 AS tbl2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	100.00	
1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	10	100.00	Using join buffer (flat, BNL join)
3	DERIVED	t7	range	i_a,i_b,i_ab	i_a	5	NULL	240	93.75	Using index condition; Using where
2	DERIVED	t7	range	i_a,i_b,i_ab	i_a	5	NULL	240	93.75	Using index condition; Using where
Warnings:
Note	1003	with cte1 as (/* select#2 */ select `test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t7`.`c` AS `c`,`test`.`t7`.`d` AS `d` from `test`.`t7` where `test`.`t7`.`a` < 10 and `test`.`t7`.`b` < 10 limit 10)/* select#1 */ select /*+ INDEX(`t7`@`select#2`) INDEX(`t7`@`select#3`) */ `tbl1`.`a` AS `a`,`tbl1`.`b` AS `b`,`tbl1`.`c` AS `c`,`tbl1`.`d` AS `d`,`tbl2`.`a` AS `a`,`tbl2`.`b` AS `b`,`tbl2`.`c` AS `c`,`tbl2`.`d` AS `d` from `cte1` `tbl1` join `cte1` `tbl2`
# End CTE JOIN'd with itself
DROP TABLE ten, twenty, t1, t2, t4, t7;
DROP VIEW v2, v3, v5, v7, v23, v24, v25;
# End of 12.2 tests