mirror of
https://github.com/MariaDB/server.git
synced 2025-04-04 22:35:33 +02:00
1026 lines
36 KiB
Text
1026 lines
36 KiB
Text
--enable_prepare_warnings
|
||
--disable_view_protocol # Since optimizer hints are not supported inside views
|
||
SET NAMES utf8mb4;
|
||
|
||
--echo # 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;
|
||
SELECT /*+ NO_MRR(t1 idx_A) */ a FROM t1;
|
||
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
SELECT /*+ NO_MRR(t1 idx_å) */ a FROM t1;
|
||
SELECT /*+ NO_MRR(t1 idx_a, idx_å, idx_A) */ a FROM t1;
|
||
--enable_ps_protocol
|
||
DROP TABLE t1;
|
||
|
||
--echo # 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;
|
||
SELECT /*+ QB_NAME(a) BKA(t1@a) BKA(t1@å) */ * FROM t1;
|
||
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;
|
||
ANALYZE TABLE t2;
|
||
ANALYZE TABLE t3;
|
||
|
||
--echo # NO_RANGE_OPTIMIZATION hint testing
|
||
set optimizer_switch=default;
|
||
|
||
--disable_ps2_protocol
|
||
--disable_cursor_protocol
|
||
--echo # Check statistics with no hint
|
||
FLUSH STATUS;
|
||
SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
--echo # Check statistics with hint
|
||
FLUSH STATUS;
|
||
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
EXPLAIN EXTENDED SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for PRIMARY key
|
||
--echo # 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;
|
||
--echo # Turn off range access for PRIMARY & f2_idx keys
|
||
--echo # Should use index access
|
||
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
|
||
FROM t3 WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for all keys
|
||
--echo # Should use index access
|
||
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3) */ f1
|
||
FROM t3 WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for PRIMARY & f2_idx keys
|
||
--echo # 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;
|
||
|
||
--echo # 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 таблица;
|
||
--echo # Turn off range access for PRIMARY key
|
||
--echo # Should use range access by f2_индекс key
|
||
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY) */ f1
|
||
FROM таблица WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for PRIMARY & f2_индекс keys
|
||
--echo # Should use index access
|
||
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY, f2_индекс) */ f1
|
||
FROM таблица WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for all keys
|
||
--echo # Should use index access
|
||
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(таблица) */ f1
|
||
FROM таблица WHERE f1 > 30 AND f1 < 33;
|
||
--echo # Turn off range access for PRIMARY & f2_индекс keys
|
||
--echo # Should use index access
|
||
EXPLAIN EXTENDED
|
||
SELECT /*+ NO_RANGE_OPTIMIZATION(таблица PRIMARY) NO_RANGE_OPTIMIZATION(таблица f2_индекс) */ f1
|
||
FROM таблица WHERE f1 > 30 AND f1 < 33;
|
||
DROP TABLE таблица;
|
||
|
||
--echo # 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;
|
||
|
||
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;
|
||
|
||
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;
|
||
|
||
--echo # 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;
|
||
|
||
|
||
--echo # Expected warning for z_idx key, unresolved name.
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
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;
|
||
--enable_ps_protocol
|
||
|
||
--echo # 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;
|
||
|
||
--echo # 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;
|
||
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
--disable_ps2_protocol
|
||
--disable_cursor_protocol
|
||
--echo # Check statistics without hint
|
||
FLUSH STATUS;
|
||
SELECT * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
--echo # Check statistics with hint
|
||
FLUSH STATUS;
|
||
SELECT /*+ BKA() */ * FROM t12, t13 WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ BKA(t13) */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ BKA() */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ BKA(t12, t13) */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= t13.b+1);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ BKA(t12) */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= 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);
|
||
|
||
--echo # 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);
|
||
|
||
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);
|
||
|
||
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);
|
||
|
||
set join_cache_level = 8;
|
||
|
||
--echo # 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);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ NO_BKA() */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= 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);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ NO_BKA(t12) */ * FROM t12, t13
|
||
WHERE t12.a=t13.a AND (t13.b+1 <= 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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # Turn off range access for PRIMARY key.
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # Turn off ICP for a particular table
|
||
EXPLAIN EXTENDED INSERT /*+ NO_ICP(t5@QB1) */ INTO t3(f1, f2, f3)
|
||
(SELECT /*+ QB_NAME(qb1) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
|
||
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
|
||
|
||
--echo # Turn off ICP for a particular table and a key
|
||
EXPLAIN EXTENDED INSERT /*+ NO_ICP(t5@QB1 x_idx) */ INTO t3(f1, f2, f3)
|
||
(SELECT /*+ QB_NAME(qb1) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
|
||
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # Turn off ICP for a particular table
|
||
EXPLAIN EXTENDED REPLACE /*+ NO_ICP(t5@QB1) */ INTO t3(f1, f2, f3)
|
||
(SELECT /*+ QB_NAME(qb1) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
|
||
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
|
||
|
||
--echo # Turn off ICP for a particular table and a key
|
||
EXPLAIN EXTENDED REPLACE /*+ NO_ICP(t5@QB1 x_idx) */ INTO t3(f1, f2, f3)
|
||
(SELECT /*+ QB_NAME(qb1) */ t4.x, t5.y, 'filler' FROM t4, t4 t5
|
||
WHERE t4.y = 8 AND t5.x BETWEEN 7 AND t4.y+0);
|
||
|
||
--echo # Misc tests
|
||
|
||
--echo # Should issue warning
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(qb1) QB_NAME(qb1 ) */ * FROM t2;
|
||
--echo # 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;
|
||
|
||
--echo # 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;
|
||
EXECUTE stmt1;
|
||
DEALLOCATE PREPARE stmt1;
|
||
|
||
--echo # 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);
|
||
|
||
--echo # 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);
|
||
|
||
--echo # Print warnings for nonexistent names
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
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;
|
||
--enable_ps_protocol
|
||
|
||
--echo # 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';
|
||
|
||
--echo # 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;
|
||
|
||
--echo # 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;
|
||
|
||
--echo # Check that original table name is not recognized if alias is used.
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
EXPLAIN EXTENDED SELECT /*+ BKA(tbl2) */ * FROM t12 tbl12, t13 tbl13
|
||
WHERE tbl12.a=tbl13.a AND (tbl13.b+1 <= tbl13.b+1);
|
||
--enable_ps_protocol
|
||
|
||
--disable_ps2_protocol
|
||
--disable_cursor_protocol
|
||
--echo # 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);
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
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;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
FLUSH STATUS;
|
||
EXECUTE stmt1;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
DEALLOCATE PREPARE stmt1;
|
||
|
||
DROP TABLE t1, t2, t3, t10, t11, t12, t13;
|
||
|
||
--echo #
|
||
--echo # BNL and NO_BNL hint testing
|
||
--echo #
|
||
|
||
set optimizer_switch=default;
|
||
|
||
CREATE TABLE t1 (a INT, b INT);
|
||
INSERT INTO t1 VALUES (1,1),(2,2);
|
||
CREATE TABLE t2 (a INT, b INT);
|
||
INSERT INTO t2 VALUES (1,1),(2,2);
|
||
CREATE TABLE t3 (a INT, b INT);
|
||
INSERT INTO t3 VALUES (1,1),(2,2);
|
||
|
||
--disable_ps2_protocol
|
||
--disable_cursor_protocol
|
||
--echo # Check statistics without hint
|
||
FLUSH STATUS;
|
||
SELECT t1.* FROM t1,t2,t3;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
--echo # Check statistics with hint
|
||
FLUSH STATUS;
|
||
SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
EXPLAIN EXTENDED SELECT t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ NO_BNL(t2, t3) */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ NO_BNL(t1, t3) */t1.* FROM t1,t2,t3;
|
||
|
||
--echo # Make sure query results are the same for any hints combination
|
||
--sorted_result
|
||
SELECT * FROM t1,t2,t3;
|
||
--sorted_result
|
||
SELECT /*+ NO_BNL(t1, t2) */* FROM t1,t2,t3;
|
||
--sorted_result
|
||
SELECT /*+ NO_BNL(t1, t3) */* FROM t1,t2,t3;
|
||
--sorted_result
|
||
SELECT /*+ NO_BNL(t2, t3) */* FROM t1,t2,t3;
|
||
|
||
--echo # BNL() overrides current join_cache_level setting
|
||
set join_cache_level = 0;
|
||
EXPLAIN EXTENDED SELECT t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ BNL() */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ BNL(t1, t2) */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ BNL(t1, t3) */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ BNL(t2, t3) */t1.* FROM t1,t2,t3;
|
||
EXPLAIN EXTENDED SELECT /*+ BNL(t2) BNL(t3) */t1.* FROM t1,t2,t3;
|
||
|
||
DROP TABLE t1, t2, t3;
|
||
set join_cache_level = 8;
|
||
|
||
--echo # BNL in subquery
|
||
set optimizer_switch = DEFAULT;
|
||
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
|
||
CREATE TABLE t2 (a INT);
|
||
CREATE TABLE t3 (a INT, b INT, INDEX a (a,b));
|
||
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
|
||
INSERT INTO t2 VALUES (2), (3), (4), (5);
|
||
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
|
||
ANALYZE TABLE t1, t2, t3;
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
|
||
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL() */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t1, t2) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t4@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t3@subq1,t4@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(@subq1 t3, t4) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) NO_BNL(t3, t4) */ t3.b
|
||
FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
--echo # Make sure query results are the same for any hints combination
|
||
# --sorted_result
|
||
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
|
||
t2.a IN (SELECT t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
# --sorted_result
|
||
SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
|
||
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL() */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL(t1, t2) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL(@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL(t4@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL(t3@subq1,t4@subq1) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) NO_BNL(@subq1 t3, t4) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
--sorted_result
|
||
SELECT /*+ QB_NAME(q) */ *
|
||
FROM t1 JOIN t2 ON t1.a = t2.a
|
||
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) NO_BNL(t3, t4) */ t3.b
|
||
FROM t3 JOIN t1 t4 ON t3.a = t4.b);
|
||
|
||
DROP TABLE t1, t2, t3, t4;
|
||
|
||
--echo #
|
||
--echo # BNL() and NO_BNL() for LEFT JOINs
|
||
--echo #
|
||
|
||
CREATE TABLE t1 (a int);
|
||
CREATE TABLE t2 (a int);
|
||
CREATE TABLE t3 (a int);
|
||
CREATE TABLE t4 (a int);
|
||
|
||
INSERT INTO t1 VALUES (null), (2), (null), (1);
|
||
|
||
set join_cache_level = 8;
|
||
|
||
--echo # Two join buffers are employed by default (without hints):
|
||
EXPLAIN SELECT t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
|
||
WHERE t1.a OR t3.a;
|
||
|
||
--echo # Disabling either of join buffers disables another one automatically due
|
||
--echo # to join buffer employment rules:
|
||
EXPLAIN SELECT /*+ NO_BNL(t2) */ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
|
||
WHERE t1.a OR t3.a;
|
||
|
||
EXPLAIN SELECT /*+ NO_BNL(t3) */ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
|
||
WHERE t1.a OR t3.a;
|
||
|
||
--echo # Three join buffers are employed here by default (without hints):
|
||
EXPLAIN SELECT t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
|
||
--echo # Disabling either of join buffers disables others automatically due
|
||
--echo # to join buffer employment rules:
|
||
EXPLAIN SELECT /*+ NO_BNL(t2)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
EXPLAIN SELECT /*+ NO_BNL(t3)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
EXPLAIN SELECT /*+ NO_BNL(t4)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
|
||
set join_cache_level=0;
|
||
--echo # It is not allowed to enable join buffers on some subset of inner tables
|
||
--echo # of an outer join. Either all tables should use join buffers or none:
|
||
EXPLAIN SELECT /*+ BNL(t2)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
EXPLAIN SELECT /*+ BNL(t2, t3)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
EXPLAIN SELECT /*+ BNL(t2, t3, t4)*/ t1.a
|
||
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
|
||
WHERE t1.a OR t4.a;
|
||
|
||
set join_cache_level=8;
|
||
INSERT INTO t2 VALUES (1), (2), (null), (1);
|
||
--echo # BNLH buffer is used when allowed by JCL setting
|
||
EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
|
||
|
||
--echo # Make sure the hint enables BNL even when JCL=0:
|
||
set join_cache_level=0;
|
||
EXPLAIN SELECT /*+BNL(t2) */ * FROM t1, t2 WHERE t1.a=t2.a;
|
||
|
||
set join_cache_level=2;
|
||
EXPLAIN SELECT /*+BNL(t2) */ * FROM t1, t2 WHERE t1.a=t2.a;
|
||
|
||
DROP TABLE t1,t2,t3,t4;
|
||
|
||
|
||
--echo #
|
||
--echo # Mix of BNL/BKA flat and incremental join buffers for OUTER JOINs
|
||
--echo #
|
||
|
||
set optimizer_switch='outer_join_with_cache=on,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
|
||
|
||
CREATE TABLE t1 (a1 varchar(32)) ;
|
||
INSERT INTO t1 VALUES ('s'),('k');
|
||
|
||
CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
|
||
INSERT INTO t2 VALUES (7,'s');
|
||
|
||
CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
|
||
INSERT INTO t3 VALUES (7,'s');
|
||
|
||
CREATE TABLE t4 (a4 int);
|
||
INSERT INTO t4 VALUES (9);
|
||
|
||
CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
|
||
INSERT INTO t5 VALUES (7,0);
|
||
--disable_result_log
|
||
ANALYZE TABLES t1, t2, t3, t4, t5;
|
||
--enable_result_log
|
||
|
||
--echo # Disable join buffering to enable it selectively on particular tables
|
||
SET SESSION join_cache_level = 0;
|
||
EXPLAIN
|
||
SELECT t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
EXPLAIN
|
||
SELECT /*+ BNL(t4) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT /*+ BNL(t4) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
EXPLAIN
|
||
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
--echo # BNL() hint overrides join_cache_levels from 0 to 3 increasing it to 4
|
||
set join_cache_level = 1;
|
||
EXPLAIN
|
||
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
set join_cache_level = 2;
|
||
EXPLAIN
|
||
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
set join_cache_level = 3;
|
||
EXPLAIN
|
||
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
--echo # Enable all join buffering capabilities:
|
||
SET SESSION join_cache_level = 8;
|
||
EXPLAIN
|
||
SELECT t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
EXPLAIN
|
||
SELECT /*+ NO_BNL(t4)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT /*+ NO_BNL(t4)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
--echo # Disable BKA so the BNLH join buffer will be employed:
|
||
EXPLAIN
|
||
SELECT /*+ NO_BKA(t5)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT /*+ NO_BKA(t5)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
EXPLAIN
|
||
SELECT /*+ NO_BKA(t5) NO_BNL(t5)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
SELECT /*+ NO_BKA(t5) NO_BNL(t5)*/ t4.a4, t5.b5
|
||
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
|
||
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
|
||
|
||
DROP TABLE t1,t2,t3,t4,t5;
|
||
|
||
|
||
--echo # 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;
|
||
|
||
set optimizer_switch='mrr=on,mrr_cost_based=off';
|
||
|
||
--disable_ps2_protocol
|
||
--disable_cursor_protocol
|
||
--echo # Check statistics without hint
|
||
FLUSH STATUS;
|
||
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
--echo # Check statistics with hint
|
||
FLUSH STATUS;
|
||
SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
--echo # 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;
|
||
FLUSH STATUS;
|
||
CALL p();
|
||
SHOW STATUS LIKE 'handler_read%';
|
||
|
||
DROP PROCEDURE p;
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn off MRR. MRR should not be used.
|
||
EXPLAIN EXTENDED SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn off MRR. MRR should not be used.
|
||
EXPLAIN EXTENDED SELECT /*+ NO_MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # 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;
|
||
|
||
set optimizer_switch='mrr=off,mrr_cost_based=off';
|
||
|
||
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn on MRR. MRR should be used.
|
||
EXPLAIN EXTENDED SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn on MRR. MRR should be used.
|
||
EXPLAIN EXTENDED SELECT /*+ MRR(t1 IDX2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # 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;
|
||
|
||
set optimizer_switch='mrr=off,mrr_cost_based=on';
|
||
|
||
EXPLAIN EXTENDED SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn on MRR. MRR should be used.
|
||
EXPLAIN EXTENDED SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # Turn on MRR. MRR should be used.
|
||
EXPLAIN EXTENDED SELECT /*+ MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
||
--echo # 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;
|
||
|
||
DROP TABLE t1;
|
||
|
||
set optimizer_switch=default;
|
||
|
||
--echo #
|
||
--echo # Duplicate hints
|
||
--echo #
|
||
|
||
CREATE TABLE t1 (i INT PRIMARY KEY);
|
||
|
||
SELECT /*+ BKA() BKA() */ 1;
|
||
SELECT /*+ BKA(t1) BKA(t1) */ * FROM t1;
|
||
SELECT /*+ QB_NAME(q1) BKA(t1@q1) BKA(t1@q1) */ * FROM t1;
|
||
SELECT /*+ QB_NAME(q1) NO_ICP(@q1 t1 PRIMARY) NO_ICP(@q1 t1 PRIMARY) */ * FROM t1;
|
||
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Hints inside views are not supported
|
||
--echo #
|
||
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;
|
||
--echo # Make sure hints are not present inside the view definition:
|
||
SHOW CREATE VIEW v1;
|
||
EXPLAIN EXTENDED SELECT * FROM v1;
|
||
|
||
CREATE OR REPLACE VIEW v1 AS SELECT /*+ NO_MRR(t1 idx_a) BKA(t1)*/ a FROM t1;
|
||
SHOW CREATE VIEW v1;
|
||
|
||
ALTER VIEW v1 AS SELECT /*+ QB_NAME(q1)*/ a FROM t1;
|
||
SHOW CREATE VIEW v1;
|
||
SELECT * FROM v1;
|
||
|
||
--echo # 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;
|
||
|
||
--echo # Incorrect hint does not prevent view creation, only a warning generated:
|
||
CREATE VIEW v2 AS SELECT /*+ BAD HINT*/ a+10 FROM t1;
|
||
SELECT * FROM v2;
|
||
EXPLAIN EXTENDED SELECT * FROM v2;
|
||
SHOW CREATE VIEW v2;
|
||
|
||
DROP VIEW v1, v2;
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Tests of parser for optimizer hints
|
||
--echo #
|
||
|
||
CREATE TABLE t1 (i INT, j INT);
|
||
CREATE INDEX i1 ON t1(i);
|
||
CREATE INDEX i2 ON t1(j);
|
||
|
||
--echo
|
||
--echo # invalid hint sequences, must issue warnings:
|
||
--echo
|
||
SELECT /*+*/ 1;
|
||
SELECT /*+ */ 1;
|
||
SELECT /*+ * ** / // /* */ 1;
|
||
SELECT /*+ @ */ 1;
|
||
SELECT /*+ @foo */ 1;
|
||
SELECT /*+ foo@bar */ 1;
|
||
SELECT /*+ foo @bar */ 1;
|
||
SELECT /*+ `@` */ 1;
|
||
SELECT /*+ `@foo` */ 1;
|
||
SELECT /*+ `foo@bar` */ 1;
|
||
SELECT /*+ `foo @bar` */ 1;
|
||
SELECT /*+ BKA( @) */ 1;
|
||
SELECT /*+ BKA( @) */ 1;
|
||
SELECT /*+ BKA(t1 @) */ 1;
|
||
|
||
--echo
|
||
--echo # We don't support "*/" inside quoted identifiers (syntax error):
|
||
--echo
|
||
|
||
--error ER_PARSE_ERROR
|
||
SELECT /*+ BKA(`test*/`) */ 1;
|
||
|
||
--echo
|
||
--echo # invalid hint sequences, must issue warnings:
|
||
--echo
|
||
SELECT /*+ NO_ICP() */ 1;
|
||
SELECT /*+NO_ICP()*/ 1;
|
||
SELECT /*+ NO_ICP () */ 1;
|
||
SELECT /*+ NO_ICP ( ) */ 1;
|
||
|
||
SELECT /*+ NO_ICP() */ 1 UNION SELECT 1;
|
||
(SELECT /*+ NO_ICP() */ 1) UNION (SELECT 1);
|
||
|
||
((SELECT /* + NO_ICP() */ 1));
|
||
|
||
UPDATE /*+ NO_ICP() */ t1 SET i = 10;
|
||
INSERT /*+ NO_ICP() */ INTO t1 VALUES ();
|
||
DELETE /*+ NO_ICP() */ FROM t1 WHERE 1;
|
||
|
||
|
||
SELECT /*+ BKA(a b) */ 1 FROM t1 a, t1 b;
|
||
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
SELECT /*+ NO_ICP(i1) */ 1 FROM t1;
|
||
SELECT /*+ NO_ICP(i1 i2) */ 1 FROM t1;
|
||
--enable_ps_protocol
|
||
SELECT /*+ NO_ICP(@qb ident) */ 1 FROM t1;
|
||
|
||
--echo
|
||
--echo # valid hint sequences, no warnings expected:
|
||
--echo
|
||
SELECT /*+ BKA(t1) */ 1 FROM t1;
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(qb1) */ 1 UNION SELECT /*+ QB_NAME(qb2) */ 1;
|
||
EXPLAIN EXTENDED (SELECT /*+ QB_NAME(qb1) */ 1) UNION (SELECT /*+ QB_NAME(qb2) */ 1);
|
||
|
||
--echo #
|
||
--echo # test explainable statements for hint support:
|
||
--echo # they should warn with a hint syntax error near "test */"
|
||
--echo #
|
||
|
||
EXPLAIN EXTENDED SELECT /*+ test */ 1;
|
||
EXPLAIN EXTENDED INSERT /*+ test */ INTO t1 VALUES (10, 10);
|
||
EXPLAIN EXTENDED UPDATE /*+ test */ t1 SET i = 10 WHERE j = 10;
|
||
EXPLAIN EXTENDED DELETE /*+ test */ FROM t1 WHERE i = 10;
|
||
|
||
--echo
|
||
--echo # non-alphabetic and non-ASCII identifiers, should warn:
|
||
--echo
|
||
|
||
CREATE INDEX 3rd_index ON t1(i, j);
|
||
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
SELECT /*+ NO_ICP(3rd_index) */ 1 FROM t1;
|
||
|
||
CREATE INDEX $index ON t1(j, i);
|
||
SELECT /*+ NO_ICP($index) */ 1 FROM t1;
|
||
|
||
CREATE TABLE ` quoted name test` (i INT);
|
||
SELECT /*+ BKA(` quoted name test`) */ 1 FROM t1;
|
||
SELECT /*+ BKA(` quoted name test`@`select#1`) */ 1 FROM t1;
|
||
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;
|
||
SELECT /*+ BKA(" quoted name test"@"select#1") */ 1 FROM t1;
|
||
|
||
CREATE TABLE `test1``test2``` (i INT);
|
||
|
||
SELECT /*+ BKA(`test1``test2```) */ 1;
|
||
SELECT /*+ BKA("test1""test2""") */ 1;
|
||
|
||
SET SQL_MODE = '';
|
||
--echo # should warn:
|
||
SELECT /*+ BKA(" quoted name test") */ 1 FROM t1;
|
||
|
||
DROP TABLE ` quoted name test`;
|
||
DROP TABLE `test1``test2```;
|
||
--enable_ps_protocol
|
||
|
||
--echo # Valid hints, no warning:
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`*`) */ 1;
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`a*`) */ 1;
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`*b`) */ 1;
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`a
|
||
b`) */ 1;
|
||
|
||
--echo # 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;
|
||
DROP TABLE 0a;
|
||
|
||
--echo # hint syntax error: empty quoted identifier
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(``) */ 1;
|
||
|
||
SET NAMES utf8;
|
||
EXPLAIN EXTENDED SELECT /*+ QB_NAME(`\BF``\BF`) */ 1;
|
||
|
||
CREATE TABLE tableТ (i INT);
|
||
|
||
--echo # invalid hints, should warn:
|
||
|
||
# Warnings "Unresolved table/index name..." are generated during both prepare
|
||
# and execution stages. So disable PS protocol to avoid duplication
|
||
--disable_ps_protocol
|
||
SELECT /*+ BKA(tableТ) */ 1 FROM t1;
|
||
SELECT /*+ BKA(test@tableТ) */ 1 FROM t1;
|
||
DROP TABLE tableТ;
|
||
|
||
CREATE TABLE таблица (i INT);
|
||
|
||
SELECT /*+ BKA(`таблица`) */ 1 FROM t1;
|
||
SELECT /*+ BKA(таблица) */ 1 FROM t1;
|
||
SELECT /*+ BKA(test@таблица) */ 1 FROM t1;
|
||
|
||
SELECT /*+ NO_ICP(`\D1`) */ 1 FROM t1;
|
||
|
||
DROP TABLE таблица;
|
||
--enable_ps_protocol
|
||
|
||
--echo
|
||
--echo # derived tables and other subqueries:
|
||
--echo
|
||
|
||
SELECT * FROM (SELECT /*+ DEBUG_HINT3 */ 1) a;
|
||
SELECT (SELECT /*+ DEBUG_HINT3 */ 1);
|
||
SELECT 1 FROM DUAL WHERE 1 IN (SELECT /*+ DEBUG_HINT3 */ 1);
|
||
|
||
--echo
|
||
--echo # invalid hint sequences (should warn):
|
||
--echo
|
||
SELECT /*+ 10 */ 1;
|
||
SELECT /*+ NO_ICP() */ 1;
|
||
--disable_ps_protocol # to avoid warnings duplication, see details above
|
||
SELECT /*+ NO_ICP(10) */ 1;
|
||
--enable_ps_protocol
|
||
SELECT /*+ NO_ICP( */ 1;
|
||
SELECT /*+ NO_ICP) */ 1;
|
||
SELECT /*+ NO_ICP(t1 */ 1;
|
||
SELECT /*+ NO_ICP(t1 ( */ 1;
|
||
(SELECT 1) UNION (SELECT /*+ NO_ICP() */ 1);
|
||
|
||
INSERT INTO t1 VALUES (1, 1), (2, 2);
|
||
|
||
--echo
|
||
--echo # wrong place for hint, so recognize that stuff as a regular commentary:
|
||
--echo
|
||
|
||
SELECT 1 FROM /*+ regular commentary, not a hint! */ t1;
|
||
SELECT 1 FROM /*+ #1 */ t1 WHERE /*+ #2 */ 1 /*+ #3 */;
|
||
SELECT 1 FROM /*+ QB_NAME(q1) */ t1 /*+ NO_ICP() */WHERE /*+ NO_MRR(t1) */ 1 /*+ #3 */;
|
||
|
||
--echo # Warnings expected:
|
||
SELECT /*+ NO_ICP() */ 1
|
||
FROM /*+ regular commentary, not a hint! */ t1;
|
||
|
||
SELECT /*+ NO_ICP(t1) bad_hint */ 1 FROM t1;
|
||
|
||
SELECT /*+
|
||
NO_ICP(@qb ident)
|
||
*/ 1 FROM t1;
|
||
|
||
SELECT /*+
|
||
? bad syntax
|
||
*/ 1;
|
||
|
||
SELECT
|
||
/*+ ? bad syntax */ 1;
|
||
|
||
DROP TABLE t1;
|
||
set optimizer_switch = DEFAULT;
|
||
set join_cache_level = DEFAULT;
|
||
--echo #
|
||
--echo # End of 11.7 tests
|
||
--echo #
|