-- MariaDB optimizer performance testcase (scale-up auxiliary)
-- Root cause target: SELECT DISTINCT + duplicate-insensitive inner join fanout
--
-- This is a synthetic scale-up of the small testcase.  It intentionally keeps
-- the result cardinality tiny (7 DISTINCT rows) while making t1 have many
-- matching duplicate rows for the c0 values that join with t0/t3.  It also adds
-- many non-matching t1.c0 values to keep the average index fanout misleadingly
-- low, similar to the original ZIPF-like data model.
--
-- Run with:
--   mariadb --comments --table < mariadb_distinct_semijoin_missed_scaleup.sql > scaleup_result.txt 2>&1
--
-- --comments is required so optimizer hints are preserved.

DROP DATABASE IF EXISTS mariadb_distinct_semijoin_scaleup;
CREATE DATABASE mariadb_distinct_semijoin_scaleup;
USE mariadb_distinct_semijoin_scaleup;

SET SESSION optimizer_prune_level = 0;
SET SESSION optimizer_join_limit_pref_ratio = 0;

SELECT '===== ENV =====' AS case_marker;
SELECT VERSION() AS mariadb_version;
SELECT @@optimizer_prune_level AS optimizer_prune_level,
       @@optimizer_join_limit_pref_ratio AS optimizer_join_limit_pref_ratio;
       
SELECT VERSION() AS mariadb_version;

-- Keep table layout close to the original testcase.
CREATE TABLE t0(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64),
  c4 BIGINT,
  c5 DOUBLE,
  c6 VARCHAR(64),
  c7 DOUBLE
) ENGINE=InnoDB;

CREATE TABLE t1(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64),
  c4 BIGINT,
  c5 DOUBLE,
  c6 VARCHAR(64)
) ENGINE=InnoDB;

CREATE TABLE t2(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64),
  c4 BIGINT,
  c5 DOUBLE,
  c6 VARCHAR(64)
) ENGINE=InnoDB;

CREATE TABLE t3(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64),
  c4 BIGINT,
  c5 DOUBLE,
  c6 VARCHAR(64),
  c7 DOUBLE
) ENGINE=InnoDB;

-- Data scale parameters used by this file:
--   t0 rows          = 320  (40 rows for each c0 in 0..7)
--   t3 rows          = 320  (40 rows for each c0 in 0..7)
--   hot t1 rows      = 3500 (500 rows for each c0 in 1..7)
--   non-matching t1  = 3500 unique c0 values, to preserve a skew/ZIPF-like pattern
--   full distinct result cardinality = 7 rows, because c0=0 has no matching t1 row.

DELIMITER //
CREATE PROCEDURE fill_scaleup_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE k INT DEFAULT 0;

  -- t0/t3: low NDV c0, anti-correlated c1, monotonic c7, duplicated projected values.
  SET i = 0;
  WHILE i < 320 DO
    SET k = MOD(i, 8);
    INSERT INTO t0(c0, c1, c2, c3, c4, c5, c6, c7)
    VALUES (
      k,
      1000000 - k,
      (i * 17) - 10000,
      CONCAT('low_ndv_0_3_', MOD(i, 5)),
      200000 - i,
      IF(MOD(i, 4) = 0, NULL, (i * 13) - 5000),
      CONCAT('monotonic_desc_0_6_', 100000 - i),
      1000000 - i
    );

    INSERT INTO t3(c0, c1, c2, c3, c4, c5, c6, c7)
    VALUES (
      k,
      1000000 - k,
      300000 - i,
      IF(MOD(i, 3) = 0, NULL, CONCAT('null_heavy_3_3_', i)),
      400000 - i,
      500000 + i,
      CONCAT('monotonic_desc_3_6_', 900000 - i),
      IF(MOD(i, 2) = 0, NULL, (i * 31) - 7000)
    );

    -- t2 is not used by the reproducing query.  Keep a small amount of data so
    -- the schema still resembles the original generated testcase.
    IF i < 128 THEN
      INSERT INTO t2(c0, c1, c2, c3, c4, c5, c6)
      VALUES (
        200000 - i,
        600000 - i,
        MOD(i, 5),
        CONCAT('low_ndv_2_3_', MOD(i, 5)),
        700000 - i,
        800000 + i,
        CONCAT('uniform_2_6_', i)
      );
    END IF;

    SET i = i + 1;
  END WHILE;

  -- Hot matching t1 keys: c0=1..7, 500 rows each.
  -- This is the duplicate-insensitive fanout that the default inner join may expand.
  SET i = 0;
  WHILE i < 3500 DO
    SET k = 1 + MOD(i, 7);
    INSERT INTO t1(c0, c1, c2, c3, c4, c5, c6)
    VALUES (
      k,
      10000 + i,
      1 + MOD(i, 31),
      CONCAT('monotonic_desc_1_3_', 200000 - i),
      300000 + MOD(i, 101),
      (i * 19) - 40000,
      CONCAT('gaussian_1_6_', 1000 + MOD(i, 97))
    );
    SET i = i + 1;
  END WHILE;

  -- Non-matching high-NDV t1 keys: these keep the t1.c0 distribution skewed.
  -- They should not join with t0/t3 but make the data model look more like the
  -- original ZIPF-style t1.c0 column with many non-joining values.
  SET i = 0;
  WHILE i < 3500 DO
    INSERT INTO t1(c0, c1, c2, c3, c4, c5, c6)
    VALUES (
      100000 + i,
      20000 + i,
      1 + MOD(i, 31),
      CONCAT('noise_1_3_', i),
      500000 + MOD(i, 101),
      (i * 23) - 30000,
      CONCAT('noise_1_6_', MOD(i, 97))
    );
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;

START TRANSACTION;
CALL fill_scaleup_data();
COMMIT;
DROP PROCEDURE fill_scaleup_data;

CREATE INDEX i0 ON t0(c0);
CREATE INDEX i1 ON t0(c0, c1);
CREATE INDEX i2 ON t1(c0);
CREATE INDEX i3 ON t1(c1);
CREATE INDEX i4 ON t2(c0);
CREATE INDEX i5 ON t2(c0, c1);
CREATE INDEX i6 ON t3(c0);
CREATE INDEX i7 ON t3(c0, c1);

SELECT '===== ANALYZE TABLES =====' AS case_marker;
ANALYZE TABLE t0, t1, t2, t3;
ANALYZE TABLE t0 PERSISTENT FOR ALL;
ANALYZE TABLE t1 PERSISTENT FOR ALL;
ANALYZE TABLE t2 PERSISTENT FOR ALL;
ANALYZE TABLE t3 PERSISTENT FOR ALL;

SELECT '===== DATA SHAPE =====' AS case_marker;
SELECT 't0' AS tbl, COUNT(*) AS row_count, COUNT(DISTINCT c0) AS ndv_c0, COUNT(DISTINCT c1) AS ndv_c1, COUNT(DISTINCT c7) AS ndv_c7 FROM t0
UNION ALL
SELECT 't1' AS tbl, COUNT(*) AS row_count, COUNT(DISTINCT c0) AS ndv_c0, COUNT(DISTINCT c1) AS ndv_c1, NULL AS ndv_c7 FROM t1
UNION ALL
SELECT 't3' AS tbl, COUNT(*) AS row_count, COUNT(DISTINCT c0) AS ndv_c0, COUNT(DISTINCT c1) AS ndv_c1, COUNT(DISTINCT c7) AS ndv_c7 FROM t3;

SELECT '===== JOIN FANOUT BY t0.c0 =====' AS case_marker;
SELECT k.c0,
       COALESCE(t0s.cnt, 0) AS t0_rows,
       COALESCE(t1s.cnt, 0) AS t1_rows,
       COALESCE(t3s.cnt, 0) AS t3_rows,
       COALESCE(t0s.cnt, 0) * COALESCE(t1s.cnt, 0) * COALESCE(t3s.cnt, 0) AS raw_inner_join_rows
FROM (SELECT DISTINCT c0 FROM t0) AS k
LEFT JOIN (SELECT c0, COUNT(*) AS cnt FROM t0 GROUP BY c0) AS t0s ON t0s.c0 = k.c0
LEFT JOIN (SELECT c0, COUNT(*) AS cnt FROM t1 GROUP BY c0) AS t1s ON t1s.c0 = k.c0
LEFT JOIN (SELECT c0, COUNT(*) AS cnt FROM t3 GROUP BY c0) AS t3s ON t3s.c0 = k.c0
ORDER BY k.c0;

SELECT '===== FULL DISTINCT RESULT CARDINALITY =====' AS case_marker;
SELECT COUNT(*) AS inner_join_distinct_rows
FROM (
  SELECT DISTINCT t0.c1 AS ref0, t3.c1 AS t3_c1, t0.c0 AS t0_c0
  FROM t3
  JOIN t0 ON t3.c0 = t0.c0
  JOIN t1 ON t0.c0 = t1.c0
) AS q;
SELECT COUNT(*) AS exists_rewrite_distinct_rows
FROM (
  SELECT DISTINCT t0.c1 AS ref0, t3.c1 AS t3_c1, t0.c0 AS t0_c0
  FROM t3
  JOIN t0 ON t3.c0 = t0.c0
  WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.c0 = t0.c0)
) AS q;

SELECT '===== RESULT EQUIVALENCE: default vs hinted vs EXISTS =====' AS case_marker;
DROP TEMPORARY TABLE IF EXISTS q_default;
DROP TEMPORARY TABLE IF EXISTS q_hinted;
DROP TEMPORARY TABLE IF EXISTS q_exists;
CREATE TEMPORARY TABLE q_default AS
SELECT DISTINCT t0.c1 AS ref0, t3.c1 AS t3_c1, t0.c0 AS t0_c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;
CREATE TEMPORARY TABLE q_hinted AS
SELECT /*+ JOIN_ORDER(t3, t0, t1) */ DISTINCT t0.c1 AS ref0, t3.c1 AS t3_c1, t0.c0 AS t0_c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;
CREATE TEMPORARY TABLE q_exists AS
SELECT DISTINCT t0.c1 AS ref0, t3.c1 AS t3_c1, t0.c0 AS t0_c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.c0 = t0.c0)
ORDER BY t0.c0 ASC
LIMIT 8;
SELECT 'default_rows' AS check_name, COUNT(*) AS cnt FROM q_default
UNION ALL SELECT 'hinted_rows', COUNT(*) FROM q_hinted
UNION ALL SELECT 'exists_rows', COUNT(*) FROM q_exists;
SELECT 'default_minus_hinted' AS check_name, COUNT(*) AS cnt FROM q_default d
WHERE NOT EXISTS (SELECT 1 FROM q_hinted h WHERE h.ref0 <=> d.ref0 AND h.t3_c1 <=> d.t3_c1 AND h.t0_c0 <=> d.t0_c0)
UNION ALL
SELECT 'hinted_minus_default', COUNT(*) FROM q_hinted h
WHERE NOT EXISTS (SELECT 1 FROM q_default d WHERE h.ref0 <=> d.ref0 AND h.t3_c1 <=> d.t3_c1 AND h.t0_c0 <=> d.t0_c0)
UNION ALL
SELECT 'default_minus_exists', COUNT(*) FROM q_default d
WHERE NOT EXISTS (SELECT 1 FROM q_exists e WHERE e.ref0 <=> d.ref0 AND e.t3_c1 <=> d.t3_c1 AND e.t0_c0 <=> d.t0_c0)
UNION ALL
SELECT 'exists_minus_default', COUNT(*) FROM q_exists e
WHERE NOT EXISTS (SELECT 1 FROM q_default d WHERE e.ref0 <=> d.ref0 AND e.t3_c1 <=> d.t3_c1 AND e.t0_c0 <=> d.t0_c0);

SELECT '===== CASE A: default DISTINCT inner join / EXPLAIN JSON =====' AS case_marker;
EXPLAIN FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE A1: default DISTINCT inner join / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE A_FORCED: forced bad JOIN_ORDER(t0,t1,t3) / EXPLAIN JSON =====' AS case_marker;
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t0, t1, t3) */ DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE A_FORCED1: forced bad JOIN_ORDER(t0,t1,t3) / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t0, t1, t3) */ DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE B: JOIN_ORDER(t3,t0,t1) / EXPLAIN JSON =====' AS case_marker;
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t3, t0, t1) */ DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE B1: JOIN_ORDER(t3,t0,t1) / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t3, t0, t1) */ DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE C: EXISTS rewrite / EXPLAIN JSON =====' AS case_marker;
EXPLAIN FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
WHERE EXISTS (
  SELECT 1
  FROM t1
  WHERE t1.c0 = t0.c0
)
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CASE C1: EXISTS rewrite / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
WHERE EXISTS (
  SELECT 1
  FROM t1
  WHERE t1.c0 = t0.c0
)
ORDER BY t0.c0 ASC
LIMIT 8;

SELECT '===== CONTROL F: no ORDER/LIMIT default / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0;

SELECT '===== CONTROL F_EXISTS: no ORDER/LIMIT EXISTS / ANALYZE JSON =====' AS case_marker;
ANALYZE FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
WHERE EXISTS (
  SELECT 1
  FROM t1
  WHERE t1.c0 = t0.c0
);

SELECT '===== CONTROL E: optimizer_join_limit_pref_ratio=100 / EXPLAIN+ANALYZE =====' AS case_marker;
SET SESSION optimizer_join_limit_pref_ratio = 100;
EXPLAIN FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;
ANALYZE FORMAT=JSON
SELECT DISTINCT t0.c1 AS ref0, t3.c1, t0.c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0 ASC
LIMIT 8;
SET SESSION optimizer_join_limit_pref_ratio = 0;

SELECT '===== DONE =====' AS case_marker;
