-- MariaDB missed impossible condition repro
-- Bug pattern:
--   t1.c0 IS NULL AND t1.c0 = t0.c0
-- is unsatisfiable for INNER JOIN with normal equality (=).
-- The optimizer should ideally produce an impossible/empty plan.
--
-- Main test uses optimizer_prune_level=0 to reproduce the larger runtime impact
-- observed by the fuzzer. At the end, the same query is also tested with
-- optimizer_prune_level=1 as an additional/default-setting comparison.

DROP DATABASE IF EXISTS mdev_null_eq_impossible;
CREATE DATABASE mdev_null_eq_impossible;
USE mdev_null_eq_impossible;


SELECT VERSION() AS mariadb_version;

-- Main reproduction setting: disable join-order pruning.
-- This is used to show the larger performance impact of the missed impossible
-- condition. The bug itself should not depend on this setting; see the final
-- optimizer_prune_level=1 comparison section.
SET SESSION optimizer_prune_level = 0;
SHOW VARIABLES LIKE 'optimizer_prune_level';

-- Scale knobs. Increase @N_NULL if the runtime impact is too small.
SET @N_NON_NULL := 50000;
SET @N_NULL     := 2000;
SET @N_SEQ_LIMIT := 50000;

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

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

CREATE TEMPORARY TABLE digits(n INT PRIMARY KEY) ENGINE=Memory;
INSERT INTO digits 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 n
FROM (
  SELECT d0.n + 10*d1.n + 100*d2.n + 1000*d3.n + 10000*d4.n AS n
  FROM digits d0
  JOIN digits d1
  JOIN digits d2
  JOIN digits d3
  JOIN digits d4
) AS s
WHERE n < @N_SEQ_LIMIT;

-- Many ordinary matching non-NULL rows, to make the data look realistic.
INSERT INTO t0(c0, c1, c2, c3)
SELECT 1000000 + n,
       3000000 + 3*n,
       n * 1.0,
       CONCAT('t0_nonnull_', LPAD(n, 5, '0'))
FROM seq
WHERE n < @N_NON_NULL;

INSERT INTO t1(c0, c1, c2, c3, c4)
SELECT 1000000 + n,
       3000000 + 3*n,
       -n * 1.0,
       CONCAT('t1_nonnull_', LPAD(n, 5, '0')),
       CASE WHEN MOD(n, 3) = 0 THEN NULL ELSE -n END
FROM seq
WHERE n < @N_NON_NULL;

-- Explicit NULL rows on both sides. With normal equality (=), NULL does NOT
-- join to NULL. This prevents the report from looking like it only relies on
-- absence of NULL values in the data.
INSERT INTO t0(c0, c1, c2, c3)
SELECT NULL,
       -1000000 - n,
       n * 1.0,
       CONCAT('t0_null_', LPAD(n, 5, '0'))
FROM seq
WHERE n < @N_NULL;

INSERT INTO t1(c0, c1, c2, c3, c4)
SELECT NULL,
       -2000000 - n,
       -n * 1.0,
       CONCAT('t1_null_', LPAD(n, 5, '0')),
       NULL
FROM seq
WHERE n < @N_NULL;

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);

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

-- Basic sanity checks.
SELECT COUNT(*) AS t0_rows, SUM(c0 IS NULL) AS t0_null_c0 FROM t0;
SELECT COUNT(*) AS t1_rows, SUM(c0 IS NULL) AS t1_null_c0 FROM t1;

-- This must be zero under SQL NULL semantics.
SELECT COUNT(*) AS eq_join_should_be_zero
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL;

-- This should be one, proving that NULL rows exist and would match only under
-- NULL-safe equality.
SELECT EXISTS(
  SELECT 1
  FROM t1 INNER JOIN t0 ON t1.c0 <=> t0.c0
  WHERE t1.c0 IS NULL
  LIMIT 1
) AS null_safe_join_has_rows;

-- ================================================================
-- Main reproduction: optimizer_prune_level=0
-- Expected: impossible/empty plan.
-- Actual: normal scan/join/filesort plan.
-- ================================================================
SHOW VARIABLES LIKE 'optimizer_prune_level';

EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;

ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;

-- Hinted variant under optimizer_prune_level=0.
-- This is secondary evidence only: it shows that a higher-cost plan can run
-- faster, but the main bug is the missed impossible condition.
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;

ANALYZE FORMAT=JSON
SELECT /*+ JOIN_ORDER(t1, t0) */ t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;

-- Remove ORDER BY/LIMIT. If this still is not optimized as impossible,
-- the report is not just about ORDER BY/LIMIT planning.
EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL;

-- ================================================================
-- Additional comparison: optimizer_prune_level=1
-- This is the default-style comparison. It may choose a better join order,
-- but the impossible condition is still not detected.
-- ================================================================
SET SESSION optimizer_prune_level = 1;
SHOW VARIABLES LIKE 'optimizer_prune_level';

EXPLAIN FORMAT=JSON
SELECT t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;

ANALYZE FORMAT=JSON
SELECT t1.c1 AS ref0, t0.c0
FROM t1 INNER JOIN t0 ON t1.c0 = t0.c0
WHERE t1.c0 IS NULL
ORDER BY t0.c3 ASC, t0.c0 DESC
LIMIT 2;
