-- Run in an empty test schema. Verified on MySQL 8.0.46 and 8.0.30-debug.
-- Creates t_c (3 rows) and t_step (3003 rows); no external data is needed.
-- Unpatched: count = 1; intersection disabled: count = 3; indexes ignored: count = 3.
-- With the proposed 8.0.30-based fix: all three counts = 3.

SELECT VERSION(), @@version_comment;
SET @saved_optimizer_switch = @@SESSION.optimizer_switch;
SET SESSION optimizer_switch =
  'index_merge=on,index_merge_intersection=on,derived_condition_pushdown=off';

CREATE TABLE t_c (
  irt_nbr varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  cfm_idx varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (irt_nbr, cfm_idx),
  KEY idx1 (irt_nbr)
) ENGINE=InnoDB;

CREATE TABLE t_step (
  id      bigint NOT NULL AUTO_INCREMENT,
  cfm_idx varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  irt_nbr varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (id),
  KEY idx_1 (cfm_idx),
  KEY idx_2 (irt_nbr)
) ENGINE=InnoDB;

INSERT INTO t_c (irt_nbr, cfm_idx) VALUES
('IRN001', 'CFM_AAAAAAAAAA0001'),
('IRN002', 'CFM_BBBBBBBBBB0002'),
('IRN003', 'CFM_CCCCCCCCCC0003');

INSERT INTO t_step (cfm_idx, irt_nbr) VALUES
('CFM_AAAAAAAAAA0001', 'IRN001'),
('CFM_BBBBBBBBBB0002', 'IRN002'),
('CFM_CCCCCCCCCC0003', 'IRN003');

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT 'CFM_AAAAAAAAAA0001', CONCAT('IRN_NOISE_A_', LPAD(i,6,'0')) FROM n;

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT 'CFM_BBBBBBBBBB0002', CONCAT('IRN_NOISE_B_', LPAD(i,6,'0')) FROM n;

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT 'CFM_CCCCCCCCCC0003', CONCAT('IRN_NOISE_C_', LPAD(i,6,'0')) FROM n;

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT CONCAT('CFM_NOISE_', LPAD(i,6,'0')), 'IRN001' FROM n;

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT CONCAT('CFM_NOISE_', LPAD(i+500,6,'0')), 'IRN002' FROM n;

INSERT INTO t_step (cfm_idx, irt_nbr)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 500)
SELECT CONCAT('CFM_NOISE_', LPAD(i+1000,6,'0')), 'IRN003' FROM n;

ANALYZE TABLE t_c, t_step;

-- Inspect the dynamic range access path and collation warnings.
EXPLAIN SELECT COUNT(*)
FROM t_c c STRAIGHT_JOIN t_step step
  ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');
SHOW WARNINGS;

-- Expected 3; observed 1 on the unpatched server.
SELECT COUNT(*)
FROM t_c c STRAIGHT_JOIN t_step step
  ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');

-- Only the IRN001 row is returned on the unpatched server.
SELECT c.irt_nbr, step.cfm_idx, step.irt_nbr, step.id
FROM t_c c STRAIGHT_JOIN t_step step
  ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');

-- Same query with intersection disabled: returns 3.
SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ COUNT(*)
FROM t_c c STRAIGHT_JOIN t_step step
  ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');

-- Independent reference with both secondary indexes ignored: returns 3.
SELECT COUNT(*)
FROM t_c c STRAIGHT_JOIN t_step step IGNORE INDEX (idx_1, idx_2)
  ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');

SET SESSION optimizer_switch = @saved_optimizer_switch;