mirror of
https://github.com/MariaDB/server.git
synced 2025-08-24 03:12:20 +02:00
b11767846f
Due to complications while parsing of INSERT..SELECT statements, optimizer hints placed at the INSERT part are ignored. At the same time, hints placed at the SELECT part of INSERT..SELECT statements are fully supported.
832 lines
27 KiB
Text
832 lines
27 KiB
Text
--source include/have_innodb.inc
|
|
--enable_prepare_warnings
|
|
--disable_view_protocol # Since optimizer hints are not supported inside views
|
|
|
|
set default_storage_engine=innodb;
|
|
|
|
CREATE TABLE t1 (f1 INT NOT NULL);
|
|
INSERT INTO t1 VALUES (9),(0), (7);
|
|
|
|
CREATE TABLE t2 (f1 INT NOT NULL);
|
|
INSERT INTO t2 VALUES
|
|
(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
|
|
|
|
CREATE TABLE t3 (f1 INT NOT NULL);
|
|
INSERT INTO t3 VALUES (9),(0), (7), (4), (5);
|
|
|
|
CREATE TABLE t4 (f1 INT NOT NULL);
|
|
INSERT INTO t4 VALUES (0), (7);
|
|
|
|
CREATE TABLE t5 (f1 INT NOT NULL, PRIMARY KEY(f1));
|
|
INSERT INTO t5 VALUES (7);
|
|
|
|
CREATE TABLE t6(f1 INT NOT NULL, PRIMARY KEY(f1));
|
|
INSERT INTO t6 VALUES (7);
|
|
|
|
ANALYZE TABLE t1, t2, t3, t4, t5, t6;
|
|
|
|
set join_cache_level = 8;
|
|
|
|
--echo # Warning expected: hint must be ignored as malformed (no table names)
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX()*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Hint must be ignored as malformed (table names are not allowed for the fixed order)
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER(t2)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Hint is applied: QB name is allowed for the fixed order
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qb1)*/ count(*) FROM t2, t1;
|
|
|
|
--echo # Invalid QB name for the fixed order
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qb3)*/ count(*) FROM t2, t1;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER()*/ STRAIGHT_JOIN count(*) FROM t1, t2;
|
|
|
|
--echo # JOIN_FIXED_ORDER() cannot be combined with other join order hints
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t2) JOIN_FIXED_ORDER()*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER() JOIN_PREFIX(t2)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Multiple join order hints of same type are not allowed (except JOIN_ORDER())
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2)*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_SUFFIX(t1) JOIN_SUFFIX(t2)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Multiple JOIN_ORDER() hints are applied
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2,t1) JOIN_ORDER(@q1 t3,t2)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
|
|
--echo # Variation of last syntax
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2@q1, t1) JOIN_ORDER(t3, t2@q1)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
|
|
--echo # Invalid mix of notations
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1 t2, t1@q1)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
|
|
--echo # Hint is correct
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(t1@qb1, t2)*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_PREFIX(@qb1, t2)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Invalid table name
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_SUFFIX(t3)*/ count(*) FROM t1, t2;
|
|
|
|
--echo # Invalid query block name
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(@qbXXX t1)*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(t1, t2@qb3)*/ count(*) FROM t1, t2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qbXXX)*/ count(*) FROM t2, t1;
|
|
|
|
--echo # Warning expected as the hint must not confuse `t2` in the subquery
|
|
--echo # with `t2` in the join
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t2@subq)*/ (SELECT /*+ QB_NAME(subq)*/ max(f1) FROM t2) as SQ, f1
|
|
FROM t2;
|
|
|
|
--echo # No more than 64 join order hints are allowed. It is hard to construct
|
|
--echo # a test case where 64 hints will be applicable and only one, exceeding
|
|
--echo # the maximum, will produce a warning. Usually so many hints will conflict
|
|
--echo # with each other and generate warnings.
|
|
--echo # But in the case below we can observe that the first warning
|
|
--echo # is about JOIN_ORDER(t1,t2,t3,t4,t5,t6) which is actually
|
|
--echo # the last hint in the list, and it is the 65th in order.
|
|
--echo # This happens because the check for exceeding the maximum is performed
|
|
--echo # during the preparation phase, while other checks are performed during
|
|
--echo # hints application. So, if JOIN_ORDER(t1,t2,t3,t4,t5,t6)
|
|
--echo # is mentioned first in the list of warnings, the test case passes.
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+
|
|
JOIN_PREFIX(t2,t1) JOIN_SUFFIX(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t1,t2,t3,t4,t5,t6) */
|
|
count(*) FROM t1, t2, t3;
|
|
|
|
--echo # Original query with no hints
|
|
let $query= SELECT count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Check name resolving
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_PREFIX(t3, t2, t2@subq2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t2) AND
|
|
t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t2);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Check conflicting hints
|
|
--echo # Second JOIN_PREFIX is conflicting
|
|
let $query= SELECT /*+ JOIN_PREFIX(t3, t2, t1) JOIN_PREFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Second JOIN_SUFFIX is conflicting
|
|
let $query= SELECT /*+ JOIN_SUFFIX(t3, t2) JOIN_SUFFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Both JOIN_ORDERs applicable
|
|
let $query= SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t1, t2, t5@subq2) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Unresolved table name t7 in JOIN_ORDER hint, hint ignored
|
|
let $query= SELECT /*+ JOIN_ORDER(t1, t7, t5) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # All hints are applicable
|
|
let $query= SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
|
|
JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Second JOIN_ORDER is ignored
|
|
let $query= SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t2, t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_SUFFIX is ignored
|
|
let $query= SELECT /*+ JOIN_ORDER(t3, t2) JOIN_SUFFIX(t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_PREFIX is ignored
|
|
let $query= SELECT /*+ JOIN_ORDER(t3, t2) JOIN_PREFIX(t2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # All hints are applicable
|
|
let $query= SELECT /*+ JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) JOIN_PREFIX(t2, t5@subq2, t4@subq1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_PREFIX with all tables.
|
|
let $query= SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_SUFFIX with all tables.
|
|
let $query= SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_ORDER with all tables.
|
|
let $query= SELECT /*+ JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_PREFIX, JOIN_ORDER, JOIN_SUFFIX with all tables.
|
|
let $query= SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # JOIN_ORDER is ignored due to STRAIGHT_JOIN.
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2, t1) */ STRAIGHT_JOIN count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Test JOIN_FIXED_ORDER.
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_FIXED_ORDER(@q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
let $query= SELECT /*+ JOIN_FIXED_ORDER() */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
let $query= SELECT /*+ QB_NAME(q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Testing STRAIGHT_JOIN
|
|
|
|
let $query= SELECT count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # t3 can not be first
|
|
let $query= SELECT /*+ JOIN_PREFIX(t3, t1) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Hint is applicable
|
|
let $query= SELECT /*+ JOIN_PREFIX(t1, t2, t3) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Hint is applicable
|
|
let $query= SELECT /*+ JOIN_SUFFIX(t4, t5) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Alternative syntax
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_ORDER(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Alternative syntax
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
--echo # Alternative syntax
|
|
let $query= SELECT /*+ QB_NAME(q1) JOIN_SUFFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
|
|
DROP TABLE t1, t2, t3, t4 ,t5, t6;
|
|
|
|
--echo # Testing embedded join
|
|
|
|
CREATE TABLE t1 (f1 INT);
|
|
CREATE TABLE t2 (f1 INT);
|
|
CREATE TABLE t3 (f1 INT);
|
|
CREATE TABLE t4 (f1 INT);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t4, t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t1, t4) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t4, t1, t2) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t3, t4) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN SELECT /*+ JOIN_ORDER(t4, t3) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_SUFFIX(t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_SUFFIX(t2, t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
|
|
CREATE TABLE t2
|
|
(
|
|
f1 INT(11) DEFAULT NULL
|
|
);
|
|
INSERT INTO t2 VALUES (0),(1),(2);
|
|
|
|
CREATE TABLE t3
|
|
(
|
|
f1 INT(11) DEFAULT NULL
|
|
);
|
|
INSERT INTO t3 VALUES (0),(1),(2);
|
|
|
|
--echo # Original query
|
|
EXPLAIN EXTENDED DELETE
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
--echo # Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
--echo # Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta1, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
--echo # Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta2, ta1) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
--echo # Hint should be ignored
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_SUFFIX(ta2, t3, ta1) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
--echo # Both hints are ignored
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(ta1, t2, t3) JOIN_SUFFIX(t3, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo # Const table behavior, table order is not changed, hint is applicable.
|
|
--echo # Note: Const tables are excluded from the process of dependency setting
|
|
--echo # since they are always first in the table order. Note that it
|
|
--echo # does not prevent the hint from being applied to the non-const
|
|
--echo # tables of the hint.
|
|
|
|
CREATE TABLE t1(f1 INT) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
CREATE TABLE t2(f1 INT) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t1, t2) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1;
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t2, t1) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug#23144274 WL9158:ASSERTION `JOIN->BEST_READ < DOUBLE(1.79769313486231570815E+308L)' FAILED
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (f1));
|
|
|
|
CREATE TABLE t2 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 varchar(255) CHARACTER SET utf8mb3 DEFAULT NULL,
|
|
f3 varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f3(f3));
|
|
|
|
EXPLAIN SELECT /*+ JOIN_SUFFIX(t1, t2) */ t2.f3 FROM t2 LEFT JOIN t1
|
|
ON t2.f1 = t1.f1 WHERE t1.f2 NOT LIKE ('FMGAU') OR t2.f2 > 't';
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
f1 int(11) DEFAULT NULL,
|
|
KEY f1 (f1)
|
|
);
|
|
|
|
CREATE TABLE t2
|
|
(
|
|
f1 int(11) DEFAULT NULL,
|
|
f2 varchar(255) CHARACTER SET utf8mb3 DEFAULT NULL,
|
|
KEY f2 (f2),
|
|
KEY f1 (f1)
|
|
);
|
|
|
|
CREATE TABLE t3 (
|
|
f1 int(11) DEFAULT NULL,
|
|
f2 varchar(255) CHARACTER SET cp932 DEFAULT NULL,
|
|
KEY f1 (f1),
|
|
KEY f2 (f2)
|
|
);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t3) JOIN_ORDER(t1, t2) */ t3.f1
|
|
FROM ( t2 INNER JOIN t3 ON t3.f2 = t2.f2 LEFT JOIN t1 ON t1.f1 = t3.f1 )
|
|
WHERE NOT (t2.f1 >= 7);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */ t3.f1
|
|
FROM ( t2 INNER JOIN t3 ON t3.f2 = t2.f2 LEFT JOIN t1 ON t1.f1 = t3.f1 )
|
|
WHERE NOT (t2.f1 >= 7);
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
CREATE TABLE t1 (
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
f2 INT(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
|
|
CREATE TABLE t2 (
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t1, t1) */ t2.f1 FROM t1 JOIN t2 ON t1.f2 = t2.f1;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
f1 DATETIME,
|
|
f2 DATE,
|
|
f3 VARCHAR(1),
|
|
KEY (f1)
|
|
) ENGINE=myisam;
|
|
|
|
CREATE TABLE t2
|
|
(
|
|
f1 VARCHAR(1),
|
|
f2 INT,
|
|
f3 VARCHAR(1),
|
|
KEY (f1)
|
|
) ENGINE=innodb;
|
|
|
|
CREATE TABLE t3
|
|
(
|
|
f1 VARCHAR(1),
|
|
f2 DATE,
|
|
f3 DATETIME,
|
|
f4 INT
|
|
) ENGINE=myisam;
|
|
|
|
# Warning "Field or reference 'test.als2.f2' of SELECT #2 was resolved in SELECT #1"
|
|
# is generated during both PREPARE and EXECUTE of a prepared statement, so disable
|
|
# the PS protocol for this test
|
|
--disable_ps_protocol
|
|
EXPLAIN EXTENDED
|
|
UPDATE /*+ JOIN_ORDER(t2, als1, als3) JOIN_FIXED_ORDER() */ t3 AS als1
|
|
JOIN t1 AS als2 ON (als1.f3 = als2 .f1)
|
|
JOIN t1 AS als3 ON (als1.f1 = als3.f3)
|
|
RIGHT OUTER JOIN t3 AS als4 ON (als1.f3 = als4.f2)
|
|
SET als1.f4 = 'eogqjvbhzodzimqahyzlktkbexkhdwxwgifikhcgblhgswxyutepc'
|
|
WHERE ('i','b') IN (SELECT f3, f1 FROM t2 WHERE f2 <> f2 AND als2.f2 IS NULL);
|
|
--enable_ps_protocol
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
|
|
CREATE TABLE t1(
|
|
f1 VARCHAR(1)) ENGINE=myisam;
|
|
|
|
CREATE TABLE t2(
|
|
f1 VARCHAR(1),
|
|
f2 VARCHAR(1),
|
|
f3 DATETIME,
|
|
KEY(f2)) ENGINE=innodb;
|
|
|
|
CREATE TABLE t3(
|
|
f1 INT,
|
|
f2 DATE,
|
|
f3 VARCHAR(1),
|
|
KEY(f3)) ENGINE=myisam;
|
|
|
|
CREATE TABLE t4(
|
|
f1 VARCHAR(1),
|
|
KEY(f1)) ENGINE=innodb;
|
|
ALTER TABLE t4 DISABLE KEYS;
|
|
INSERT INTO t4 VALUES ('x'), (NULL), ('d'), ('x'), ('u');
|
|
ALTER TABLE t4 ENABLE KEYS;
|
|
|
|
CREATE TABLE t5(
|
|
f1 VARCHAR(1),
|
|
KEY(f1) ) ENGINE=myisam;
|
|
INSERT INTO t5 VALUES (NULL), ('s'), ('c'), ('x'), ('z');
|
|
|
|
EXPLAIN EXTENDED UPDATE /*+ JOIN_ORDER(t4, alias1, alias3) */ t3 AS alias1
|
|
JOIN t5 ON (alias1.f3 = t5.f1)
|
|
JOIN t3 AS alias3 ON (alias1.f2 = alias3.f2 )
|
|
RIGHT OUTER JOIN t1 ON (alias1.f3 = t1.f1)
|
|
SET alias1.f1 = -1
|
|
WHERE ( 'v', 'o' ) IN
|
|
(SELECT DISTINCT t2.f1, t2.f2 FROM t4 RIGHT OUTER JOIN t2 ON (t4.f1 = t2.f1)
|
|
WHERE t2.f3 BETWEEN '2001-10-04' AND '2003-05-15');
|
|
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
|
|
CREATE TABLE t1 (
|
|
f1 INT(11) DEFAULT NULL,
|
|
f3 VARCHAR(1) DEFAULT NULL,
|
|
f2 INT(11) DEFAULT NULL,
|
|
KEY (f1)
|
|
) ENGINE=MyISAM;
|
|
|
|
CREATE TABLE t2(
|
|
f1 INT(11) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
|
|
CREATE TABLE t3 (
|
|
f1 VARCHAR(1) DEFAULT NULL,
|
|
f2 VARCHAR(1) DEFAULT NULL,
|
|
KEY (f2)
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
# Warning "Field or reference 'test.ta2.f3' of SELECT #2 was resolved in SELECT #1"
|
|
# is generated during both PREPARE and EXECUTE of a prepared statement, so disable
|
|
# the PS protocol for this test
|
|
--disable_ps_protocol
|
|
EXPLAIN EXTENDED UPDATE /*+ JOIN_SUFFIX(ta1, t2) */
|
|
t1 AS ta1 JOIN t1 AS ta2 ON ta1.f1 = ta2.f1 RIGHT JOIN t2 ON (ta1.f1 = t2.f1)
|
|
SET ta1.f2 = '', ta2.f3 = ''
|
|
WHERE ('n', 'r') IN (SELECT f2, f1 FROM t3 WHERE f1 <> f2 XOR ta2.f3 IS NULL);
|
|
--enable_ps_protocol
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
|
|
CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) charset latin1 ENGINE=MyISAM;
|
|
|
|
CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1))
|
|
charset latin1 ENGINE=MyISAM;
|
|
CREATE TABLE t5(f1 INT(11) NOT NULL AUTO_INCREMENT, f2 INT(11) DEFAULT NULL, PRIMARY KEY (f1))
|
|
charset latin1 ENGINE=InnoDB;
|
|
CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1))
|
|
charset latin1 ENGINE=InnoDB;
|
|
CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL)
|
|
charset latin1 ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),KEY (f2),KEY (f3)) charset latin1 ENGINE=MyISAM;
|
|
|
|
CREATE TABLE t11(f1 INT(11) DEFAULT NULL,f2 VARCHAR(10) DEFAULT NULL,
|
|
KEY (f1),KEY (f2)) charset latin1 ENGINE=InnoDB;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_ORDER(alias11, alias8) */ 1
|
|
FROM t4 AS alias4
|
|
LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
|
|
LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
|
|
ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
|
|
JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
|
|
ON alias4.f2 = alias11.f2;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_ORDER(alias11, alias10, alias8, alias7) */ 1
|
|
FROM t4 AS alias4
|
|
LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
|
|
LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
|
|
ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
|
|
JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
|
|
ON alias4.f2 = alias11.f2;
|
|
|
|
DROP TABLES t2, t4, t5, t6, t7, t10, t11;
|
|
|
|
CREATE TABLE t1 (f1 VARCHAR(255) DEFAULT NULL, f2 VARCHAR(255) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t3(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT, f2 VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),KEY (f2),KEY (f3)) ENGINE=InnoDB;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(alias8, alias6) */ 1
|
|
FROM t1 AS alias1
|
|
LEFT JOIN t7 alias7
|
|
JOIN t2 AS alias2
|
|
LEFT JOIN t3 AS alias3 JOIN t4 AS alias4 ON alias4.f2 = alias3.f1
|
|
ON alias4.f1 = alias2.f1
|
|
ON alias2.f2 = alias7.f1
|
|
JOIN t10 AS alias5
|
|
LEFT JOIN t6 AS alias6 JOIN t2 AS alias8 ON alias6.f1 = alias8.f2
|
|
ON alias6.f1 = alias5.f1
|
|
ON alias5.f3 = alias7.f1
|
|
ON alias1.f2 = alias7.f1;
|
|
|
|
DROP TABLES t1, t2, t3, t4, t6, t7, t10;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug#23144230 WL#9158 : OPT_HINTS_QB::APPLY_JOIN_ORDER_HINTS - MYSQLD GOT SIGNAL 11
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) DEFAULT NULL,
|
|
f3 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f2 (f2))
|
|
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE t2 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) DEFAULT NULL,
|
|
f3 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f2 (f2))
|
|
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(alias1) */ alias2.f3 AS field1
|
|
FROM ( SELECT * FROM t1 ) AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2
|
|
ON alias1.f2 = alias2.f3
|
|
WHERE alias2.f2 IS NULL HAVING (field1 != 3 AND field1 >= 8);
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug#23651098 WL#9158 : ASSERTION `!(SJ_NEST->SJ_INNER_TABLES & JOIN->CONST_TABLE_MAP)' FAILED
|
|
--echo #
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t2
|
|
(
|
|
f1 VARCHAR(1) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
|
|
CREATE TABLE t3
|
|
(
|
|
f1 VARCHAR(1) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t2, t1) */ t1.f1 FROM t1, t2
|
|
WHERE t2.f1 IN (SELECT t3.f1 FROM t3) AND t1.f1 = 183;
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo #
|
|
--echo # Bug23715779 SELECT QUERY WITH JOIN_PREFIX() HINT RETURNS INCORRECT RESULT
|
|
--echo #
|
|
|
|
CREATE TABLE t1(f1 INT(11) NOT NULL);
|
|
INSERT INTO t1 VALUES (10);
|
|
|
|
CREATE TABLE t2
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
f2 INT(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY (f2)
|
|
);
|
|
INSERT INTO t2 VALUES (1, 7), (2, 1), (4, 7);
|
|
|
|
CREATE TABLE t4(f1 INT DEFAULT NULL);
|
|
INSERT INTO t4 VALUES (2);
|
|
|
|
ANALYZE TABLE t1, t2, t4;
|
|
|
|
let $query=
|
|
SELECT
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
let $query=
|
|
SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
eval $query;
|
|
eval explain extended $query;
|
|
|
|
DROP TABLE t1, t2, t4;
|
|
|
|
--echo
|
|
--echo MDEV-36638 Some optimizer hint warnings are returned as errors
|
|
--echo
|
|
|
|
SET @save_sql_mode = @@sql_mode;
|
|
SET SQL_MODE = 'STRICT_TRANS_TABLES';
|
|
|
|
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, f1 INT NOT NULL, PRIMARY KEY (id));
|
|
INSERT INTO t1 (id, f1) VALUES (1,9),(2,0), (3,7);
|
|
|
|
CREATE TABLE t2 (id int(11), f2 INT NOT NULL);
|
|
INSERT INTO t2 (id, f2) VALUES
|
|
(4,5),(3,3),(1,0),(1,3),(6,1),(2,0),(4,1),(2,7),(2,1),(1,0),(3,0),(5,8),(5,4),
|
|
(3,9),(2,0),(7,2),(2,0),(1,8),(6,5),(4,1);
|
|
|
|
CREATE TABLE tn (fn_1 INT, fn_2 INT);
|
|
|
|
ANALYZE TABLE t1, t2, tn;
|
|
|
|
EXPLAIN EXTENDED
|
|
INSERT INTO tn (fn_1, fn_2)
|
|
SELECT /*+ JOIN_ORDER(t2,t1) JOIN_FIXED_ORDER() */ f1,f2
|
|
FROM t2 JOIN t1 ON t1.id=t2.id ORDER BY f1, f2;
|
|
|
|
EXPLAIN EXTENDED
|
|
INSERT INTO tn (fn_1, fn_2)
|
|
SELECT /*+ JOIN_ORDER(t1,t2) */ f1,f2 FROM t2 LEFT JOIN t1 ON t1.id=t2.id
|
|
ORDER BY f1, f2;
|
|
|
|
DROP TABLE tn;
|
|
DROP TABLE t1, t2;
|
|
|
|
CREATE TABLE t1 (a int, b varchar(50), c varchar(50));
|
|
INSERT t1 (a,b) VALUES (1,'1'), (2,'2'), (3,'3');
|
|
CREATE TRIGGER tr BEFORE UPDATE ON t1 FOR EACH ROW
|
|
SET new.c = (SELECT /*+ NO_RANGE_OPTIMIZATION(t1) */ 1);
|
|
UPDATE t1 SET t1.b = 10;
|
|
DROP TABLE t1;
|
|
|
|
SET SQL_MODE = @save_sql_mode;
|