mirror of
https://github.com/MariaDB/server.git
synced 2026-01-23 20:04:50 +01:00
5ab153c2c5
Allows [NO_]MERGE(@qb_name) and [NO_]MERGE() to apply to all tables in a target named query block and all tables in the current query block, respectively. Repairs a bug when fixing hints for derived tables that prevented derived tables from being linked with a query block's hints in the case that no derived tables were explicitly specified in the query.
208 lines
8.3 KiB
Text
208 lines
8.3 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;
|
|
set session optimizer_switch='derived_merge=on';
|
|
--enable_view_protocol
|
|
|
|
--echo #
|
|
--echo # MDEV-37389 Hint NO_MERGE(@qb_name) is accepted but has no effect
|
|
--echo #
|
|
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');
|
|
INSERT INTO salaries (emp_id, salary) VALUES
|
|
(101, 120000.00),
|
|
(102, 95000.00);
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ NO_MERGE() */ e.emp_name, s.salary FROM (
|
|
select * from employees) e JOIN (select * from salaries) s ON e.emp_id = s.emp_id;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ QB_NAME(foo) NO_MERGE(@`foo`) */ e.emp_name, s.salary FROM (
|
|
select * from employees) e JOIN (select * from salaries) s ON e.emp_id = s.emp_id;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ NO_MERGE() */ emp_id from (
|
|
select /*+ NO_MERGE() */ emp_id from (
|
|
select /*+ NO_MERGE() */ emp_id from (
|
|
select /*+ NO_MERGE() */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ NO_MERGE(@qb4) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ NO_MERGE(@qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ NO_MERGE(@qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ NO_MERGE(@qb2) NO_MERGE(@qb4) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
--echo # Test the MERGE hint variant
|
|
set session optimizer_switch='derived_merge=off';
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ MERGE() */ e.emp_name, s.salary FROM (
|
|
select * from employees) e JOIN (select * from salaries) s ON e.emp_id = s.emp_id;
|
|
|
|
EXPLAIN EXTENDED SELECT /*+ QB_NAME(foo) MERGE(@`foo`) */ e.emp_name, s.salary FROM (
|
|
select * from employees) e JOIN (select * from salaries) s ON e.emp_id = s.emp_id;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ MERGE() */ emp_id from (
|
|
select /*+ MERGE() */ emp_id from (
|
|
select /*+ MERGE() */ emp_id from (
|
|
select /*+ MERGE() */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ MERGE(@qb4) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ MERGE(@qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ MERGE(@qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
EXPLAIN EXTENDED
|
|
select /*+ MERGE(@qb2) MERGE(@qb4) */ emp_id from (
|
|
select /*+ QB_NAME(qb2) */ emp_id from (
|
|
select /*+ QB_NAME(qb3) */ emp_id from (
|
|
select /*+ QB_NAME(qb4) */ emp_id from (
|
|
select emp_id from employees) dt) du) dv) dw;
|
|
|
|
set session optimizer_switch='derived_merge=on';
|
|
DROP TABLE employees, salaries;
|
|
|
|
--echo # End of 12.1 tests
|