40 lines
1.5 KiB
Text
40 lines
1.5 KiB
Text
--source include/have_hypergraph.inc
|
|
--source include/have_debug.inc
|
|
|
|
--echo #
|
|
--echo # Bug#35120119 Hypergraph: rescan_cost for hash-join is too low
|
|
--echo #
|
|
|
|
CREATE TABLE t (x INT);
|
|
INSERT INTO t VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
|
|
|
|
CREATE TABLE t1 (x INT);
|
|
CREATE TABLE t2 (x INT);
|
|
CREATE TABLE t3 (x INT);
|
|
|
|
INSERT INTO t1 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
|
|
INSERT INTO t2 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
|
|
INSERT INTO t3 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
|
|
|
|
SET optimizer_switch = 'hypergraph_optimizer=on';
|
|
|
|
# Force the plan (t1 NLJ (t2 HJ t3)). The join_buffer_size will affect the
|
|
# estimated rescan cost of (t2 HJ t3) since it is used to determine whether the
|
|
# hash TABLE built over t3 is kept in memory between rescans, or whether it has to
|
|
# be rebuilt for iteration of the inner loop in the nested loop join.
|
|
SET DEBUG='+d,subplan_tokens,force_subplan_0xe22c12cb96055ddc,force_subplan_0x8ab90bde1dd6f68c';
|
|
SET @old_join_buffer_size = @@join_buffer_size;
|
|
|
|
SET SESSION join_buffer_size = 128;
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
|
|
|
|
SET SESSION join_buffer_size = 1024;
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
|
|
|
|
SET SESSION join_buffer_size = 8192;
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
|
|
|
|
SET DEBUG='-d,subplan_tokens,force_subplan_0xe22c12cb96055ddc,force_subplan_0x8ab90bde1dd6f68c';
|
|
|
|
SET join_buffer_size = @old_join_buffer_size;
|
|
DROP TABLE t, t1, t2, t3;
|