-- MariaDB 12.3.2 testcase: ORDER BY/LIMIT row-goal plan missed when join cache is enabled
--
-- Target symptom:
--   With the default-like setting join_cache_level=2 and not_null_range_scan=off,
--   MariaDB chooses a low-cost plan:
--       t3 ALL -> t1 range-checked-for-each-record -> filesort
--   ANALYZE shows that range-checked-for-each-record falls back to full scan for every outer row.
--
--   With join_cache_level=0, MariaDB can use a much faster ORDER BY/LIMIT early-stop plan:
--       read_sorted_file(t3 ordered by c4,c5) -> t1 ref lookup on i2(c0) + rowid_filter on i3(c1)
--
--   The JOIN_ORDER(t1,t3) query is included only as a diagnostic comparison: it avoids the bad
--   range-checked plan under join_cache_level=2, but it is not the best plan overall.
--
-- Expected on the author's MariaDB 12.3.2 environment, approximately:
--   BASE_BAD_jcl2_nnr_off_LIMIT_1:       ~59s, cost ~4677, 8800 full scans in range-checked-for-each-record
--   DIAG_HINTED_jcl2_LIMIT_1:            ~10-11s, cost ~11588
--   WORKAROUND_nnr_on_jcl2_LIMIT_1:      ~11-12s, same cost ~4677, BNL instead of range-checked
--   FAST_jcl0_LIMIT_1:                   ~8-13ms, cost ~14847, ORDER BY/LIMIT early stop
--   FAST_jcl0_LIMIT_10:                  ~8-13ms on this data, validates early-stop behavior is not just LIMIT 1 noise
--
-- Runtime note: the first ANALYZE is intentionally slow because it demonstrates the bad plan.

DROP DATABASE IF EXISTS mariadb_orderby_limit_join_cache_costing;
CREATE DATABASE mariadb_orderby_limit_join_cache_costing;
USE mariadb_orderby_limit_join_cache_costing;

SELECT VERSION() AS mariadb_version;

-- Make data generation deterministic and large enough to amplify the plan gap.
SET SESSION max_heap_table_size = 1073741824;
SET SESSION tmp_table_size = 1073741824;

-- Keep the optimizer settings explicit for reproducibility.
SET SESSION optimizer_prune_level = 0;
SET SESSION optimizer_join_limit_pref_ratio = 0;
SET SESSION optimizer_switch = 'not_null_range_scan=off,index_condition_pushdown=on';
SET SESSION use_stat_tables = 'preferably';
SET SESSION join_cache_level = 2;
SET SESSION join_buffer_size = 262144;

-- Scale knobs.  Original relevant cardinalities were approximately t1=171 and t3=88.
-- These defaults preserve the ratio and low-NDV distribution, but amplify the runtime gap.
SET @T0_ROWS := 880;
SET @T1_ROWS := 17100;
SET @T2_ROWS := 820;
SET @T3_ROWS := 8800;
SET @MAX_ROWS := GREATEST(@T0_ROWS, @T1_ROWS, @T2_ROWS, @T3_ROWS);

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

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

CREATE TABLE t2(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64),
  c4 BIGINT,
  c5 DOUBLE
) 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;

CREATE TEMPORARY TABLE digit(d INT NOT NULL PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO digit(d) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TEMPORARY TABLE nums(n INT NOT NULL PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO nums(n)
SELECT d0.d
     + d1.d * 10
     + d2.d * 100
     + d3.d * 1000
     + d4.d * 10000
     + d5.d * 100000 AS n
FROM digit d0
CROSS JOIN digit d1
CROSS JOIN digit d2
CROSS JOIN digit d3
CROSS JOIN digit d4
CROSS JOIN digit d5
WHERE d0.d + d1.d * 10 + d2.d * 100 + d3.d * 1000 + d4.d * 10000 + d5.d * 100000 < @MAX_ROWS;

-- Filler tables t0/t2 are kept for setup-shape fidelity. The minimized query uses t1/t3.
INSERT INTO t0(c0, c1, c2, c3, c4)
SELECT @T0_ROWS - n,
       999912 + n,
       CAST(((n * 7919) MOD 200000) - 100000 AS DOUBLE),
       CONCAT('uniform_0_3_', ((n * 104729) MOD 200000) - 100000),
       350 + ((n * 37) MOD 100)
FROM nums
WHERE n < @T0_ROWS;

-- t1: c0 has low NDV 0..4; c1 is NULL-heavy, about 1/3 non-NULL.
INSERT INTO t1(c0, c1, c2, c3, c4)
SELECT MOD(n, 5) AS c0,
       CASE WHEN MOD(n, 3) = 0
            THEN ((n * 7919) MOD 200000) - 100000
            ELSE NULL
       END AS c1,
       1200.0 + (((n * 37) MOD 2000) - 1000) / 50.0 AS c2,
       CASE WHEN MOD(n, 4) = 0
            THEN CONCAT('null_heavy_1_3_', ((n * 104729) MOD 200000) - 100000)
            ELSE NULL
       END AS c3,
       1 + ((n * 17) MOD 32) AS c4
FROM nums
WHERE n < @T1_ROWS;

INSERT INTO t2(c0, c1, c2, c3, c4, c5)
SELECT 20082 - n AS c0,
       ((n * 4241) MOD 200000) - 100000 AS c1,
       CASE WHEN MOD(n, 4) = 0 THEN CAST(((n * 7919) MOD 200000) - 100000 AS DOUBLE) ELSE NULL END AS c2,
       CONCAT('low_ndv_2_3_', MOD(n, 5)) AS c3,
       ((n * 4447) MOD 200000) - 100000 AS c4,
       CAST(((n * 3571) MOD 200000) - 100000 AS DOUBLE) AS c5
FROM nums
WHERE n < @T2_ROWS;

-- t3: c0 has low NDV 0..4, matching t1.c0; no index on ORDER BY columns c4/c5.
INSERT INTO t3(c0, c1, c2, c3, c4, c5, c6, c7)
SELECT MOD(n, 5) AS c0,
       1000000 - MOD(n, 5) AS c1,
       CASE WHEN MOD(n, 3) = 0 THEN CAST(((n * 7919) MOD 200000) - 100000 AS DOUBLE) ELSE NULL END AS c2,
       CONCAT('monotonic_asc_3_3_', 30000 + n * 4) AS c3,
       CASE WHEN MOD(n, 3) = 0
            THEN ((n * 4447) MOD 200000) - 100000
            ELSE NULL
       END AS c4,
       30000.0 + n * 6.0 AS c5,
       CONCAT('low_ndv_3_6_', MOD(n, 5)) AS c6,
       CAST(MOD(n, 5) AS DOUBLE) AS c7
FROM nums
WHERE n < @T3_ROWS;

-- Preserve the original index shape. Do not add t1(c1,c0), t1(c0,c1), or t3(c4,c5),
-- otherwise the plan shape changes and the testcase no longer targets the same issue.
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(c2);
CREATE INDEX i6 ON t3(c0);
CREATE INDEX i7 ON t3(c0, c1);

ANALYZE TABLE t2, t3, t0, t1;
ANALYZE TABLE t2 PERSISTENT FOR ALL;
ANALYZE TABLE t3 PERSISTENT FOR ALL;
ANALYZE TABLE t0 PERSISTENT FOR ALL;
ANALYZE TABLE t1 PERSISTENT FOR ALL;

SELECT 'SETTINGS_AFTER_ANALYZE' AS section;
SELECT @@optimizer_switch,
       @@optimizer_prune_level,
       @@optimizer_join_limit_pref_ratio,
       @@optimizer_use_condition_selectivity,
       @@use_stat_tables,
       @@join_cache_level,
       @@join_buffer_size,
       @@sort_buffer_size;

SELECT 'ROW_COUNTS' AS section;
SELECT 't1' AS tbl, COUNT(*) AS rows_total, SUM(c1 IS NOT NULL) AS c1_not_null FROM t1
UNION ALL
SELECT 't3' AS tbl, COUNT(*) AS rows_total, NULL AS c1_not_null FROM t3;

SELECT 'JOIN_OUTPUT_CARDINALITY_BEFORE_ORDER_LIMIT' AS section;
SELECT COUNT(*) AS join_rows_before_order_limit
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Case 1: default-like bad plan, join cache enabled and not_null_range_scan off.
-- -----------------------------------------------------------------------------
SET SESSION optimizer_prune_level = 0;
SET SESSION optimizer_join_limit_pref_ratio = 0;
SET SESSION optimizer_switch = 'not_null_range_scan=off,index_condition_pushdown=on';
SET SESSION use_stat_tables = 'preferably';
SET SESSION join_cache_level = 2;
SET SESSION join_buffer_size = 262144;

SELECT 'CASE_1_BASE_BAD_jcl2_nnr_off_LIMIT_1_EXPLAIN' AS section;
EXPLAIN FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

SELECT 'CASE_1_BASE_BAD_jcl2_nnr_off_LIMIT_1_ANALYZE' AS section;
ANALYZE FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

-- -----------------------------------------------------------------------------
-- Case 2: diagnostic hinted plan under the same join-cache setting.
-- -----------------------------------------------------------------------------
SELECT 'CASE_2_DIAG_HINTED_JOIN_ORDER_jcl2_LIMIT_1_EXPLAIN' AS section;
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t3) */ t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

SELECT 'CASE_2_DIAG_HINTED_JOIN_ORDER_jcl2_LIMIT_1_ANALYZE' AS section;
ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t3) */ t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

-- Check result equivalence between the default query and the diagnostic hinted query.
SELECT 'RESULT_EQUIVALENCE_BASE_VS_HINTED' AS section;
SELECT 'BASE' AS side, t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

SELECT 'HINTED' AS side, t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

-- -----------------------------------------------------------------------------
-- Case 3: parameter workaround. not_null_range_scan=on changes the bad plan into BNL
-- and avoids the repeated full-scan range-checked path, although the cost remains similar.
-- -----------------------------------------------------------------------------
SET SESSION join_cache_level = 2;
SET SESSION optimizer_switch = 'not_null_range_scan=on,index_condition_pushdown=on';

SELECT 'CASE_3_WORKAROUND_nnr_on_jcl2_LIMIT_1_EXPLAIN' AS section;
EXPLAIN FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

SELECT 'CASE_3_WORKAROUND_nnr_on_jcl2_LIMIT_1_ANALYZE' AS section;
ANALYZE FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

-- -----------------------------------------------------------------------------
-- Case 4: much faster ORDER BY/LIMIT early-stop plan when join cache is disabled.
-- This is the strongest comparison: estimated cost is higher, but actual runtime is milliseconds.
-- -----------------------------------------------------------------------------
SET SESSION optimizer_switch = 'not_null_range_scan=off,index_condition_pushdown=on';
SET SESSION join_cache_level = 0;

SELECT 'CASE_4_FAST_jcl0_LIMIT_1_EXPLAIN' AS section;
EXPLAIN FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

SELECT 'CASE_4_FAST_jcl0_LIMIT_1_ANALYZE' AS section;
ANALYZE FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 1;

-- LIMIT 10 validation: confirms the fast plan is an ORDER BY/LIMIT early-stop plan, not just
-- measurement noise from LIMIT 1.  ANALYZE only, to keep the output readable.
SELECT 'CASE_5_FAST_jcl0_LIMIT_10_ANALYZE' AS section;
ANALYZE FORMAT=JSON
SELECT t3.c6 AS ref0
FROM t1 INNER JOIN t3 ON t1.c0 = t3.c0
WHERE t1.c1 IS NOT NULL
ORDER BY t3.c4 ASC, t3.c5 DESC
LIMIT 10;

-- Restore baseline for manual follow-up.
SET SESSION join_cache_level = 2;
SET SESSION optimizer_switch = 'not_null_range_scan=off,index_condition_pushdown=on';
SET SESSION optimizer_join_limit_pref_ratio = 0;
SET SESSION optimizer_prune_level = 0;

-- Optional manual checks, not run here to keep the community testcase output focused:
--   SET SESSION optimizer_join_limit_pref_ratio = 100;
--   EXPLAIN FORMAT=JSON SELECT t3.c6 AS ref0 FROM t1 INNER JOIN t3 ON t1.c0=t3.c0
--     WHERE t1.c1 IS NOT NULL ORDER BY t3.c4 ASC, t3.c5 DESC LIMIT 1;
--
--   SET SESSION optimizer_use_condition_selectivity = 1; -- try 1..5
--   EXPLAIN FORMAT=JSON SELECT t3.c6 AS ref0 FROM t1 INNER JOIN t3 ON t1.c0=t3.c0
--     WHERE t1.c1 IS NOT NULL ORDER BY t3.c4 ASC, t3.c5 DESC LIMIT 1;
