mirror of
https://github.com/MariaDB/server.git
synced 2025-07-27 05:35:00 +02:00
2ee2e2d0f3
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).
95 lines
4.5 KiB
Text
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
|