mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-11 02:09:15 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/opt_hints.test
Dave Gosselin 9ef9984abf MDEV-37260 Implicitly named query blocks, CREATE VIEW AS supports hints 
Query blocks have implicit names, such as `select#1`, formulated
by appending their select number to the string `select#`.  This patch
allows hints to scope their applicability by implicit query block
name.  For example,
  SELECT /*+ JOIN_ORDER(@`select#2` t1, t2) */ ...
@`select#2` is an implicit query block name.  Users can control hint
applicability per query block without first naming the blocks with
QB_NAME().

Hints may now be specified within VIEWs during their creation and
they are applied locally within that VIEW's scope.  For example,
  CREATE VIEW v1 AS
    SELECT /*+ IGNORE_INDEX(t1 idx1) */ FROM t1 ... GROUP BY ... HAVING ...
In many cases and for some parts of the VIEW, the query plan
doesn't really depend on how the VIEW is used, so it makes sense
to control a part of the query plan from the VIEW definition.

Implicit names are not yet supported in VIEWs.  Attempting to create
a VIEW with an implicit name reference will cause the server to create
the VIEW, but it will emit a warning and exclude that hint from the query.
2025-10-03 15:27:35 -04:00

1033 lines
38 KiB
Text
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

--source include/have_sequence.inc
--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 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 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);
--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 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);
--echo # 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);
--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, t4, t10, t11, t12, t13;
--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 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 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;
--echo
--echo # MDEV-36486 Optimizer hints are resolved against the INSERT part of INSERT..SELECT
--echo
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);
--echo # 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;
--echo # 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;
--echo # 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;
--echo # If hints are present at both INSERT and SELECT parts,
--echo # 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;
--echo # Table `t2` cannot be resolved since it is not present in the SELECT part
--echo # (a warning expected):
# 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
INSERT INTO t2 (a) SELECT /*+ no_range_optimization (t2)*/ a FROM t1 WHERE a>1 AND a<=3;
--enable_ps_protocol
DROP TABLE t1, t2;
set optimizer_switch = DEFAULT;
set join_cache_level = DEFAULT;
--echo #
--echo # MDEV-36675 Optimizer hints parser catches irrelevant `thd->is_error()`
--echo # set by multi-RENAME TABLE
--echo #
CREATE TABLE t1 (a INT);
CREATE TRIGGER t1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t1 VALUES (0);
--error ER_NO_SUCH_TABLE
RENAME TABLE t1 TO t2, t3 TO t4;
--replace_column 7 #
SHOW CREATE TRIGGER t1;
RENAME TABLE t1 TO t2;
--replace_column 7 #
SHOW CREATE TRIGGER t1;
DROP TABLE t2;
--echo #
--echo # End of 12.0 tests
--echo #
--echo #
--echo # MDEV-37260 Hints addressed by implicit query block name
--echo #
--echo # 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);
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);
--echo # End Simple JOIN Query
--echo # 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;
EXPLAIN SELECT * FROM (SELECT ten.a AS a FROM (ten JOIN twenty) WHERE (ten.a = twenty.a) LIMIT 1000 ) T;
--echo # End JOIN_ORDER Example
--echo # CTE Example
--disable_view_protocol
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);
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;
--source include/explain-no-costs.inc
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;
--echo # End CTE Example
--echo # 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;
--echo # End Recursive CTE Example
--echo # Deeply nested query block example
--source include/explain-no-costs.inc
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;
--echo # End deeply nested query block example
--echo # Stored procedure example
DELIMITER |;
CREATE PROCEDURE sp_test_implicit_hint()
BEGIN
EXPLAIN SELECT /*+ NO_MERGE(@`select#1` dt) */ *
FROM (SELECT * FROM ten) dt;
END|
DELIMITER ;|
CALL sp_test_implicit_hint();
DROP PROCEDURE sp_test_implicit_hint;
DELIMITER |;
CREATE PROCEDURE sp_test_implicit_hint()
BEGIN
EXPLAIN SELECT /*+ MERGE(@`select#1` dt) */ *
FROM (SELECT * FROM ten) dt;
END|
DELIMITER ;|
CALL sp_test_implicit_hint();
DROP PROCEDURE sp_test_implicit_hint;
--echo # End stored procedure example
--echo # Prepared statement example
PREPARE stmt1 FROM 'EXPLAIN SELECT /*+ NO_MERGE(@`select#1` dt) */ * FROM (SELECT * FROM ten) dt;';
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'EXPLAIN SELECT /*+ MERGE(@`select#1` dt) */ * FROM (SELECT * FROM ten) dt;';
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
--echo # End prepared statement example.
--echo # 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;
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;
--echo # End INDEX/JOIN_INDEX example
DROP TABLE salaries, employees, ten, twenty, t1, t2, t3, t4, t5, t6, t7;
DROP VIEW v1, v2, v6;
--enable_view_protocol
--echo #
--echo # MDEV-37260 Specifying hints in queries during CREATE VIEW AS
--echo #
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;
--echo # `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;
EXPLAIN SELECT * FROM v2;
SHOW CREATE VIEW v2;
DROP VIEW v2;
--echo # 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;
SHOW CREATE VIEW v2;
--echo # 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;
--echo # 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';
SELECT * FROM v5;
SELECT * FROM information_schema.session_status WHERE variable_name= 'handler_icp_match';
EXPLAIN SELECT * from v5;
--echo # End named hint example.
--echo # 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);
EXPLAIN SELECT * FROM t4 x, v5 y WHERE x.a < 10 AND (x.b+1>3);
EXPLAIN SELECT * FROM v5 x, t4 y WHERE y.a < 10 AND (y.b+1>3);
EXPLAIN SELECT * FROM v5 x, v5 y;
--echo # 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.
--echo # 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;
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;
SHOW CREATE VIEW v7;
--echo # End INDEX/JOIN_INDEX example
--echo # 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;
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;
--echo # End example of many nested derived tables are not merged.
--echo # 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;
EXPLAIN EXTENDED SELECT * FROM v23;
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(du) */ * FROM (SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * from v23) dt) du;
--echo # merged view will retain v23 in note
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(v23) */ * FROM v23;
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;
EXPLAIN EXTENDED SELECT * FROM v24;
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(du) */ * FROM (SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * from v24) dt) du;
--echo # merged view will retain v24 in note
EXPLAIN EXTENDED SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM v24) dt;
--echo # End example of VIEW hints displaying properly in warning note
--echo # 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;
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;
--echo # merged view will retail qb name reference in the note
EXPLAIN EXTENDED INSERT INTO ten SELECT * FROM v25;
--echo # End INSERT example without and with VIEW
--echo # 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;
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;
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;
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;
--echo # End CTE JOIN'd with itself
DROP TABLE ten, twenty, t1, t2, t4, t7;
DROP VIEW v2, v3, v5, v7, v23, v24, v25;
--echo # End of 12.2 tests
Reference in a new issue View git blame Copy permalink