-- MariaDB optimizer cost mis-ranking repro
-- Case: lower-cost default join order is slower than JOIN_ORDER(t1,t0)
-- Target tested version: MariaDB 12.3.2
--
-- Run:
--   mariadb --table --verbose < mariadb_70127_final_bug_repro.sql > mariadb_70127_final_bug_repro.out 2>&1
--
-- Expected symptom:
--   ratio=0 and ratio=1 both choose BASE plan t0 -> t1.
--   Forced JOIN_ORDER(t1,t0) has higher estimated cost but lower actual time.
--   BASE does ~200k ref lookups into t1; HINTED filters t1 first and probes t0 only 5 times.

DROP DATABASE IF EXISTS mariadb1232qpg10y701270;
CREATE DATABASE mariadb1232qpg10y701270;
USE mariadb1232qpg10y701270;

-- Keep this configurable. 200000 makes the gap visible but still reasonably small.
SET @scale_rows := 200000;

-- Match the original table/index shape.
CREATE TABLE t0(
  c0 BIGINT,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR(64)
) ENGINE=InnoDB;

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

-- Small helper table for compact data generation.
CREATE TEMPORARY TABLE digit(d INT PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO digit(d) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

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

-- t0 has one row per c0 in [1000, 1000 + @scale_rows - 1].
INSERT INTO t0(c0, c1, c2, c3)
SELECT 1000 + n,
       7 + (n MOD 97),
       CAST((n * 17) MOD 200000 AS SIGNED) - 100000,
       CONCAT('scale_t0_', n)
FROM seq;

-- t1 has the same c0 domain. Only 5 low-c0 rows satisfy the predicate:
--   (t1.c6 = '') OR (t1.c1 = 0)
-- They are c0 = 1000..1004, so a t0-first DESC scan must traverse almost all high c0 values.
INSERT INTO t1(c0, c1, c2, c3, c4, c5, c6)
SELECT 1000 + n,
       CASE WHEN n < 5 THEN 0 ELSE 100000000 + n END,
       CASE WHEN n MOD 3 = 0 THEN NULL ELSE CAST((n * 31) MOD 200000 AS SIGNED) - 100000 END,
       CONCAT('scale_t1_c3_', n MOD 97),
       10000 + n,
       10000.0 + n,
       CASE WHEN n < 5 THEN 'match_nonempty_c6' ELSE CONCAT('gaussian_', 1500 + (n MOD 200)) END
FROM seq;

CREATE INDEX i0 ON t0(c0);
CREATE INDEX i1 ON t0(c0, c1);
CREATE INDEX i2 ON t1(c0);
CREATE INDEX i3 ON t1(c0, c1);

SET SESSION optimizer_prune_level = 0;
SET SESSION optimizer_search_depth = 62;
SET SESSION optimizer_use_condition_selectivity = 4;
SET SESSION use_stat_tables = PREFERABLY_FOR_QUERIES;
SET SESSION max_statement_time = 30;

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

SELECT '===== environment =====' AS marker;
SELECT VERSION() AS version, @@version_comment AS version_comment;
SHOW VARIABLES WHERE Variable_name IN (
  'optimizer_join_limit_pref_ratio',
  'optimizer_prune_level',
  'optimizer_search_depth',
  'optimizer_use_condition_selectivity',
  'use_stat_tables',
  'histogram_size',
  'histogram_type',
  'join_buffer_size',
  'max_statement_time'
);

SELECT '===== table/index/cardinality sanity =====' AS marker;
SHOW INDEX FROM t0;
SHOW INDEX FROM t1;
SELECT 't0' AS table_name, COUNT(*) AS rows_n, COUNT(DISTINCT c0) AS ndv_c0, MIN(c0) AS min_c0, MAX(c0) AS max_c0 FROM t0
UNION ALL
SELECT 't1' AS table_name, COUNT(*) AS rows_n, COUNT(DISTINCT c0) AS ndv_c0, MIN(c0) AS min_c0, MAX(c0) AS max_c0 FROM t1;

SELECT '===== predicate/result sanity =====' AS marker;
SELECT COUNT(*) AS total_t1_rows,
       SUM((c6 = '') OR (c1 = 0)) AS matched_t1_rows,
       SUM((c6 = '') OR (c1 = 0)) / COUNT(*) AS matched_ratio,
       MIN(CASE WHEN (c6 = '') OR (c1 = 0) THEN c0 END) AS min_match_c0,
       MAX(CASE WHEN (c6 = '') OR (c1 = 0) THEN c0 END) AS max_match_c0
FROM t1;

SELECT t0.c0, COUNT(*) AS join_rows
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;

-- ----------------------------------------------------------------------
-- Q: deterministic report query.
-- This avoids the non-deterministic SELECT-list issue of SELECTing non-GROUPed columns.
-- ----------------------------------------------------------------------

SELECT '################################################################################' AS marker;
SELECT '===== ratio=0 BASE: EXPLAIN =====' AS marker;
SET SESSION optimizer_join_limit_pref_ratio = 0;
EXPLAIN FORMAT=JSON
SELECT t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;

SELECT '===== ratio=0 BASE: ANALYZE =====' AS marker;
FLUSH STATUS;
ANALYZE FORMAT=JSON
SELECT t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;
SHOW SESSION STATUS LIKE 'Handler_read%';

SELECT '===== ratio=0 HINTED JOIN_ORDER(t1,t0): EXPLAIN =====' AS marker;
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;

SELECT '===== ratio=0 HINTED JOIN_ORDER(t1,t0): ANALYZE =====' AS marker;
FLUSH STATUS;
ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;
SHOW SESSION STATUS LIKE 'Handler_read%';

SELECT '################################################################################' AS marker;
SELECT '===== ratio=1 BASE: EXPLAIN =====' AS marker;
SET SESSION optimizer_join_limit_pref_ratio = 1;
EXPLAIN FORMAT=JSON
SELECT t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;

SELECT '===== ratio=1 BASE: ANALYZE =====' AS marker;
FLUSH STATUS;
ANALYZE FORMAT=JSON
SELECT t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;
SHOW SESSION STATUS LIKE 'Handler_read%';

SELECT '===== ratio=1 HINTED JOIN_ORDER(t1,t0): EXPLAIN =====' AS marker;
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;

SELECT '===== ratio=1 HINTED JOIN_ORDER(t1,t0): ANALYZE =====' AS marker;
FLUSH STATUS;
ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t0.c0, MIN(t0.c3) AS min_c3, MIN(t0.c1) AS min_c1
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE (t1.c6 = '') OR (t1.c1 = 0)
GROUP BY t0.c0
ORDER BY t0.c0 DESC
LIMIT 5;
SHOW SESSION STATUS LIKE 'Handler_read%';

SELECT '===== done =====' AS marker;
