-- MariaDB testcase: equality-equivalent >= and <= join predicates miss ref access
-- Target: MariaDB optimizer, default settings first

DROP DATABASE IF EXISTS mariadb_ge_le_1000_notnull;
CREATE DATABASE mariadb_ge_le_1000_notnull;
USE mariadb_ge_le_1000_notnull;

-- Start from default optimizer-related session settings.
SET SESSION optimizer_switch = DEFAULT;
SET SESSION optimizer_prune_level = DEFAULT;
SET SESSION optimizer_search_depth = DEFAULT;
SET SESSION join_cache_level = DEFAULT;
SET SESSION max_statement_time = 0;

SET @N := 1000;
SET @MATCH_N := 10;

CREATE TABLE t1 (
  c0 BIGINT NOT NULL,
  c1 BIGINT NOT NULL,
  c2 DOUBLE NOT NULL,
  KEY i_t1_c1 (c1)
) ENGINE=InnoDB;

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

CREATE TABLE t3 (
  c0 BIGINT NOT NULL,
  c1 BIGINT NOT NULL,
  c2 DOUBLE NOT NULL,
  c3 VARCHAR(64) NOT NULL,
  c4 BIGINT NOT NULL,
  KEY i_t3_c0 (c0),
  KEY i_t3_c1 (c1),
  KEY i_t3_c0_c1 (c0, c1)
) ENGINE=InnoDB;

CREATE TEMPORARY TABLE seq_1_to_1000(n INT NOT NULL PRIMARY KEY) ENGINE=MEMORY;

INSERT INTO seq_1_to_1000(n)
SELECT ones.d + 10 * tens.d + 100 * hundreds.d + 1 AS n
FROM
  (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS ones
CROSS JOIN
  (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
CROSS JOIN
  (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
WHERE ones.d + 10 * tens.d + 100 * hundreds.d + 1 <= @N;

INSERT INTO t1(c0, c1, c2)
SELECT
  n,
  1000000 + n,
  n MOD 5
FROM seq_1_to_1000;

INSERT INTO t2(c0, c1, c2, c3, c4, c5, c6)
SELECT
  n,
  CASE WHEN n <= @MATCH_N THEN 3000000 + n ELSE 2000000 + n END,
  n * 1.0,
  CONCAT('t2_', n),
  n,
  n * 2.0,
  CONCAT('payload_', n MOD 10)
FROM seq_1_to_1000;

INSERT INTO t3(c0, c1, c2, c3, c4)
SELECT
  1000000 + n,
  3000000 + n,
  n * 3.0,
  CONCAT('t3_', n),
  n
FROM seq_1_to_1000;

DROP TEMPORARY TABLE seq_1_to_1000;

ANALYZE TABLE t1, t2, t3;

SELECT '0. version, default settings, row counts' AS phase;
SELECT VERSION() AS mariadb_version;
SELECT @@optimizer_prune_level AS optimizer_prune_level,
       @@optimizer_search_depth AS optimizer_search_depth,
       @@join_cache_level AS join_cache_level;
SELECT 't1' AS table_name, COUNT(*) AS row_count, MIN(c1) AS min_c1, MAX(c1) AS max_c1 FROM t1
UNION ALL
SELECT 't2', COUNT(*), MIN(c1), MAX(c1) FROM t2
UNION ALL
SELECT 't3', COUNT(*), MIN(c1), MAX(c1) FROM t3;

SELECT '1. join-overlap sanity' AS phase;
SELECT
  (SELECT COUNT(*) FROM t1 JOIN t3 ON t3.c0 = t1.c1) AS t1_t3_eq_rows,
  (SELECT COUNT(*) FROM t2 JOIN t3 ON t3.c1 = t2.c1) AS t2_t3_eq_rows,
  (SELECT COUNT(*) FROM t1 JOIN t3 ON t3.c0 = t1.c1 JOIN t2 ON t3.c1 = t2.c1) AS final_join_rows;

SELECT '2. safe result equivalence check' AS phase;
SELECT 'EQUALITY_FORM' AS variant, COUNT(*) AS result_rows, COALESCE(SUM(ref0 + ref1), 0) AS checksum
FROM (
  SELECT t1.c1 AS ref0, t2.c1 AS ref1
  FROM t3
  INNER JOIN t1 ON t3.c0 = t1.c1
  INNER JOIN t2 ON t3.c1 = t2.c1
  WHERE t2.c1 <= 1596112929
) AS q
UNION ALL
SELECT 'BETWEEN_EQUIV', COUNT(*), COALESCE(SUM(ref0 + ref1), 0)
FROM (
  SELECT t1.c1 AS ref0, t2.c1 AS ref1
  FROM t3
  INNER JOIN t1 ON t3.c0 BETWEEN t1.c1 AND t1.c1
  INNER JOIN t2 ON t3.c1 BETWEEN t2.c1 AND t2.c1
  WHERE t2.c1 <= 1596112929
) AS q
UNION ALL
SELECT 'NOT_NE_EQUIV', COUNT(*), COALESCE(SUM(ref0 + ref1), 0)
FROM (
  SELECT t1.c1 AS ref0, t2.c1 AS ref1
  FROM t3
  INNER JOIN t1 ON NOT (t3.c0 <> t1.c1)
  INNER JOIN t2 ON NOT (t3.c1 <> t2.c1)
  WHERE t2.c1 <= 1596112929
) AS q;

SELECT '3. default optimizer: equality form' AS phase;
EXPLAIN
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 = t2.c1
WHERE t2.c1 <= 1596112929;
EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 = t2.c1
WHERE t2.c1 <= 1596112929;
ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 = t2.c1
WHERE t2.c1 <= 1596112929;

SELECT '4. default optimizer: BETWEEN x AND x equivalent form' AS phase;
EXPLAIN
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 BETWEEN t1.c1 AND t1.c1
INNER JOIN t2 ON t3.c1 BETWEEN t2.c1 AND t2.c1
WHERE t2.c1 <= 1596112929;
EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 BETWEEN t1.c1 AND t1.c1
INNER JOIN t2 ON t3.c1 BETWEEN t2.c1 AND t2.c1
WHERE t2.c1 <= 1596112929;
ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 BETWEEN t1.c1 AND t1.c1
INNER JOIN t2 ON t3.c1 BETWEEN t2.c1 AND t2.c1
WHERE t2.c1 <= 1596112929;

SELECT '5. default optimizer: NOT <> equivalent control form' AS phase;
EXPLAIN
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON NOT (t3.c0 <> t1.c1)
INNER JOIN t2 ON NOT (t3.c1 <> t2.c1)
WHERE t2.c1 <= 1596112929;
EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON NOT (t3.c0 <> t1.c1)
INNER JOIN t2 ON NOT (t3.c1 <> t2.c1)
WHERE t2.c1 <= 1596112929;
ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON NOT (t3.c0 <> t1.c1)
INNER JOIN t2 ON NOT (t3.c1 <> t2.c1)
WHERE t2.c1 <= 1596112929;

SELECT '6. default optimizer: >= and <= equivalent form' AS phase;
EXPLAIN
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 >= t1.c1 AND t3.c0 <= t1.c1
INNER JOIN t2 ON t3.c1 >= t2.c1 AND t3.c1 <= t2.c1
WHERE t2.c1 <= 1596112929;
EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 >= t1.c1 AND t3.c0 <= t1.c1
INNER JOIN t2 ON t3.c1 >= t2.c1 AND t3.c1 <= t2.c1
WHERE t2.c1 <= 1596112929;
ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 >= t1.c1 AND t3.c0 <= t1.c1
INNER JOIN t2 ON t3.c1 >= t2.c1 AND t3.c1 <= t2.c1
WHERE t2.c1 <= 1596112929;

SELECT '7. GE/LE result check' AS phase;
SELECT 'GE_LE_EQUIV' AS variant, COUNT(*) AS result_rows, COALESCE(SUM(ref0 + ref1), 0) AS checksum
FROM (
  SELECT t1.c1 AS ref0, t2.c1 AS ref1
  FROM t3
  INNER JOIN t1 ON t3.c0 >= t1.c1 AND t3.c0 <= t1.c1
  INNER JOIN t2 ON t3.c1 >= t2.c1 AND t3.c1 <= t2.c1
  WHERE t2.c1 <= 1596112929
) AS q;

SELECT '8. sample result rows' AS phase;
SELECT t1.c1 AS ref0, t2.c1 AS ref1
FROM t3
INNER JOIN t1 ON t3.c0 = t1.c1
INNER JOIN t2 ON t3.c1 = t2.c1
WHERE t2.c1 <= 1596112929
ORDER BY ref0
LIMIT 20;

SELECT 'done' AS phase;
