-- Self-contained single-SQL repro for range optimizer memory growth.
-- The object names are intentionally generic.
--
-- Usage:
--   mysql --max_allowed_packet=512M -uroot -h127.0.0.1 -P3341 -p... \
--     < scripts/repro/single_sql_range_mem_repro.sql
--
-- The final CALL prepares and executes one EXPLAIN statement with large
-- literal IN lists.  It uses FORCE INDEX only to keep the trigger path stable
-- across installations; the issue being exercised is range construction memory
-- exceeding range_optimizer_max_mem_size.

DROP DATABASE IF EXISTS r1;
CREATE DATABASE r1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE r1;

SET SESSION group_concat_max_len = 1024 * 1024 * 512;

CREATE TABLE d10 (n int NOT NULL PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO d10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE seq (
  n int NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO seq
SELECT d0.n
     + d1.n * 10
     + d2.n * 100
     + d3.n * 1000
     + d4.n * 10000
     + d5.n * 100000
     + 1 AS n
FROM d10 d0
JOIN d10 d1
JOIN d10 d2
JOIN d10 d3
JOIN d10 d4
JOIN d10 d5
WHERE d0.n
    + d1.n * 10
    + d2.n * 100
    + d3.n * 1000
    + d4.n * 10000
    + d5.n * 100000 < 500000;

CREATE TABLE t (
  id int NOT NULL,
  c1 varchar(32) NOT NULL,
  c2 int NOT NULL,
  c3 varchar(32) NOT NULL,
  c4 varchar(32) NOT NULL,
  c5 varchar(32) NOT NULL,
  c6 varchar(32) NOT NULL,
  PRIMARY KEY (id),
  KEY k1 (c1, c2, c3 DESC, c4 DESC, c5 DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO t
SELECT n,
       'x',
       1,
       CONCAT('a', LPAD(1 + MOD(n, 522), 5, '0')),
       CONCAT('b', LPAD(1 + MOD(n, 1692), 5, '0')),
       CONCAT('z', LPAD(n, 5, '0')),
       CONCAT('v', LPAD(n, 5, '0'))
FROM seq
WHERE n <= 405000;

ANALYZE TABLE t;

DELIMITER //

CREATE PROCEDURE make_list(
  IN p_prefix varchar(8),
  IN p_count int,
  OUT p_list longtext
)
BEGIN
  SELECT GROUP_CONCAT(QUOTE(CONCAT(p_prefix, LPAD(n, 5, '0'))) ORDER BY n)
    INTO p_list
  FROM seq
  WHERE n <= p_count;
END//

CREATE PROCEDURE run_case(
  IN p_c3_count int,
  IN p_c4_count int
)
BEGIN
  DECLARE v_c3 longtext;
  DECLARE v_c4 longtext;

  SET SESSION group_concat_max_len = 1024 * 1024 * 512;

  CALL make_list('a', p_c3_count, v_c3);
  CALL make_list('b', p_c4_count, v_c4);

  SET @sql = CONCAT(
    'EXPLAIN SELECT * FROM t FORCE INDEX(k1) ',
    'WHERE c1 = ''x'' ',
    'AND c2 = 1 ',
    'AND c3 IN (', v_c3, ') ',
    'AND c4 IN (', v_c4, ') ',
    'ORDER BY c3 DESC, c4 DESC, c5 DESC'
  );

  SELECT p_c3_count AS c3_in_count,
         p_c4_count AS c4_in_count,
         p_c3_count * p_c4_count AS expected_range_count,
         LENGTH(@sql) AS sql_bytes;

  PREPARE s FROM @sql;
  EXECUTE s;
  DEALLOCATE PREPARE s;

  SHOW WARNINGS;
END//

DELIMITER ;

CALL run_case(8192, 2048);