Default page

Your IP: 216.73.216.124
Server IP: 50.28.103.30
Server: Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software: nginx/1.28.0
PHP Version: 8.3.12
Buat File | Buat Folder

View File Name : ds_mrr-big.test

# The include statement below is a temp one for tests that are yet to
#be ported to run with InnoDB,
#but needs to be kept for tests that would need MyISAM in future.
--source include/force_myisam_default.inc

--source include/big_test.inc
# As per discussion with Olav, skipping this test from valgrind runs
--source include/not_valgrind.inc

--echo #
--echo # Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
--echo #              MEMORY LEADING TO SYSTEM CRASH
--echo #

# This test will cause the server to allocate many GBs of memory and most 
# likely run out of memory if this bug gets re-introduced. 
# A reduced version of this test is include in include/mrr_tests.inc

CREATE TABLE ten (a INTEGER);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE hundred (a INTEGER);
INSERT INTO hundred
SELECT a1.a + 10 * a2.a FROM ten a1, ten a2;

CREATE TABLE thousand (a INTEGER);
INSERT INTO thousand
SELECT a1.a + 10 * a2.a + 100 * a3.a FROM ten a1, ten a2, ten a3;

CREATE TABLE t1 (
  pk INTEGER NOT NULL,
  i1 INTEGER NOT NULL,
  c1 VARCHAR(10) NOT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t1
  SELECT a, 1, 'MySQL' FROM thousand;

CREATE TABLE t2 (
  pk INTEGER NOT NULL,
  c1 VARCHAR(10) NOT NULL,
  c2 varchar(10) NOT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t2
  SELECT a, 'MySQL', 'MySQL' FROM ten;

CREATE TABLE t3 (
  pk INTEGER NOT NULL,
  c1 VARCHAR(10) NOT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t3
  SELECT a, 'MySQL' FROM hundred;

CREATE TABLE t4 (
  pk int(11) NOT NULL,
  c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
  c2 varchar(10) NOT NULL,
  c3 varchar(10) NOT NULL,
  PRIMARY KEY (pk),
  KEY k1 (c1_key)
);

# t4 is empty

CREATE TABLE t5 (
  pk INTEGER NOT NULL,
  c1 VARCHAR(10) NOT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t5
  SELECT a, 'MySQL' FROM ten;

set optimizer_switch=`mrr=on,mrr_cost_based=off`;
#set optimizer_switch='mrr=off';

let query=
SELECT STRAIGHT_JOIN *
FROM 
  (t1 LEFT JOIN 
     (t2 LEFT JOIN
       (t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
      ON t2.c1 = t4.c3) 
   ON t1.c1 = t4.c2)
  RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
WHERE t1.i1 = 1;

eval EXPLAIN $query;
eval $query;

DROP TABLE ten, hundred, thousand;
DROP TABLE t1, t2, t3, t4, t5;