mariadb/mysql-test/main/opt_hints_merge.test
Dave Gosselin 2ee2e2d0f3 MDEV-36106 New-style hints: [NO_]DERIVED_CONDITION_PUSHDOWN, [NO_]MERGE
Implements and tests the optimizer hints DERIVED_CONDITION_PUSHDOWN
and NO_DERIVED_CONDITION_PUSHDOWN, table-level hints to enable and
disable, respectively, the condition pushdown for derived tables
which is typically controlled by the condition_pushdown_for_derived
optimizer switch.

Implements and tests the optimizer hints MERGE and NO_MERGE, table-level
hints to enable and disable, respectively, the derived_merge optimization
which is typically controlled by the derived_merge optimizer switch.

Sometimes hints need to be fixed before TABLE instances are available, but
after TABLE_LIST instances have been created (as in the cases of MERGE and
NO_MERGE).  This commit introduces a new function called
fix_hints_for_derived_table to allow early hint fixing for derived tables,
using only a TABLE_LIST instance (so long as such hints are not index-level).
2025-06-18 09:36:10 -04:00

95 lines
4.5 KiB
Text

--source include/have_sequence.inc
--disable_view_protocol
create table t1 select seq as i, 10*seq as j from seq_1_to_10;
create view v1 as select * from t1 where i % 2 = 0;
set @save_optimizer_switch=@@optimizer_switch;
set session optimizer_switch='derived_merge=on';
--source include/explain-no-costs.inc
explain format=json SELECT a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--source include/explain-no-costs.inc
explain format=json SELECT /*+ NO_MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT /*+ NO_MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--source include/explain-no-costs.inc
explain format=json select * from (select i, j from v1) dt where dt.j % 5 = 0;
select * from (select i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain format=json select /*+ NO_MERGE(dt) */ * from (select /*+ NO_MERGE(v1) */ i, j from v1) dt where dt.j % 5 = 0;
select /*+ NO_MERGE(dt) */ * from (select /*+ NO_MERGE(v1) */ i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain format=json select /*+ NO_MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
select /*+ NO_MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain extended SELECT /*+ NO_MERGE(dt2) NO_MERGE(dt)*/ * FROM
(SELECT i as a, j as b FROM t1) AS dt, (SELECT i as a, j as b FROM t1) AS dt2
WHERE dt.a = dt2.a and dt.b = dt2.b AND dt.a < 3 AND dt.b > 8;
set session optimizer_switch='derived_merge=off';
--source include/explain-no-costs.inc
explain format=json SELECT a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--source include/explain-no-costs.inc
explain format=json select /*+ MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
select /*+ MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain format=json select * from (select i, j from v1) dt where dt.j % 5 = 0;
select * from (select i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain format=json select /*+ MERGE(dt) */ * from (select /*+ MERGE(v1) */ i, j from v1) dt where dt.j % 5 = 0;
select /*+ MERGE(dt) */ * from (select /*+ MERGE(v1) */ i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain format=json select /*+ MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
select /*+ MERGE(dt) */ * from (select i, j from v1) dt where dt.j % 5 = 0;
--source include/explain-no-costs.inc
explain extended SELECT /*+ MERGE(dt2) MERGE(dt)*/ * FROM
(SELECT i as a, j as b FROM t1) AS dt, (SELECT i as a, j as b FROM t1) AS dt2
WHERE dt.a = dt2.a and dt.b = dt2.b AND dt.a < 3 AND dt.b > 8;
set optimizer_switch=@save_optimizer_switch;
# Test warnings
explain extended select /*+ NO_MERGE(t) */ * from (select * from t1) t;
explain extended select /*+ MERGE(t) */ * from (select * from t1) t;
create table t2 select seq as i, 10*seq as j from seq_1_to_10;
create view v2 as select * from t2;
set session optimizer_switch='derived_merge=off';
explain extended
SELECT /*+ merge(wrong_name) */a, b FROM
(SELECT /*+ merge(wrong_name) */ i as a, j as b FROM
(select i*10 as i, j*5 as j from v2) dt_in) AS dt_out
WHERE a < 3 AND b > 8;
SELECT /*+ MERGE(dt) MERGE(foo) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--disable_ps_protocol
SELECT /*+ MERGE(dt) MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--enable_ps_protocol
SELECT /*+ MERGE(bar) MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT /*+ MERGE(bar) MERGE(foo) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT /*+ NO_MERGE(dt) NO_MERGE(foo) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--disable_ps_protocol
SELECT /*+ NO_MERGE(dt) NO_MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
--enable_ps_protocol
SELECT /*+ NO_MERGE(bar) NO_MERGE(dt) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
SELECT /*+ NO_MERGE(bar) NO_MERGE(foo) */ a, b FROM (SELECT i as a, j as b FROM t1) AS dt WHERE a < 3 AND b > 8;
drop table t1, t2;
drop view v1, v2;
--enable_view_protocol