Complete discovery process

-- ============================================================
-- Original query that first showed the inconsistency
-- ============================================================
-- While running a batch of TPC-H generated SQL statements through a
-- cross-database comparison tool, this query returned 0 rows on MySQL
-- but 3 rows on SQLite, PostgreSQL, and Dameng DB.

SELECT t2.a2, t4.ps_supplycost, t4.ps_availqty
FROM (SELECT t1.o_shippriority, t1.o_comment, MAX(t1.o_totalprice) AS a1, COUNT(*) AS a2, MIN(t1.o_orderstatus) AS a3
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, 1406.15 + 9743 - 277688.37 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8
WHERE NOT EXISTS(SELECT 1 FROM nation AS t5
                 WHERE t4.e1 = 7 AND t4.e1 <= 15 AND t4.e1 > 21
                    OR t4.e1 <= 9
                    OR t4.e1 >= 4
                    OR t4.e1 <> 19
                    OR t4.e1 <> 23 AND t2.o_shippriority <= t5.n_regionkey
                    OR t4.e1 = 20
                    OR t4.e1 = 14 AND t4.e1 = 19);

-- Result:
--   SQLite     : 3 rows
--   PostgreSQL : 3 rows
--   MySQL      : 0 rows   <-- WRONG
--   Dameng     : 3 rows

-- ============================================================
-- Setup: TPC-H schema (relevant tables)
-- ============================================================
CREATE TABLE nation (
    n_nationkey  INTEGER,
    n_name       VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
    n_regionkey  INTEGER,
    n_comment    VARCHAR(152) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin
);

CREATE TABLE partsupp (
    ps_partkey     INTEGER,
    ps_suppkey     INTEGER,
    ps_availqty    INTEGER,
    ps_supplycost  DECIMAL(15,2),
    ps_comment     VARCHAR(199) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin
);

CREATE TABLE orders (
    o_orderkey       INTEGER,
    o_custkey        INTEGER,
    o_orderstatus    VARCHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
    o_totalprice     DECIMAL(15,2),
    o_orderdate      DATE,
    o_orderpriority  VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
    o_clerk          VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
    o_shippriority   INTEGER,
    o_comment        VARCHAR(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin
);

INSERT INTO nation (n_nationkey, n_name, n_regionkey, n_comment) VALUES
(0, 'ALGERIA', 0, ' haggle. carefully final deposits detect slyly agai'),
(1, 'ARGENTINA', 1, 'al foxes promise slyly according to the regular accounts. bold requests alon'),
(2, 'BRAZIL', 1, 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '),
(3, 'CANADA', 1, 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'),
(4, 'EGYPT', 4, 'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'),
(5, 'FRANCE', 3, NULL),
(6, 'GERMANY', 3, 'regular requests'),
(7, 'INDIA', 2, NULL),
(8, 'JAPAN', 2, 'carefully final deposits'),
(9, 'CHINA', 2, NULL);

INSERT INTO partsupp (ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment) VALUES
(1, 2, 3325, 771.64, ', even theodolites. regular, final theodolites eat after the carefully pending foxes. furiously regular deposits sleep slyly. care'),
(2, 3, NULL, 993.49, ' slyly final packages. fluffily final deposits wake blithely ideas. carefully silent accounts nag fur'),
(3, 1, 1054, 337.09, NULL),
(4, 5, NULL, 357.84, 'kly regular pinto beans. carefully unusual waters cajole never. carefully regular courts cajole quickly slyly pending'),
(5, 4, 273, 48.47, NULL),
(6, 1, 5000, 500.00, 'regular deposits'),
(7, 2, NULL, 600.00, NULL),
(8, 3, 7000, 700.00, 'final requests'),
(9, 4, NULL, 800.00, NULL),
(10, 5, 9000, 900.00, 'carefully final');

INSERT INTO orders (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment) VALUES
(1, 1, 'O', 173665.47, '1996-01-02', '5-LOW', 'Clerk#000000951', 0, 'nstructions sleep furiously among '),
(2, 2, 'O', 46929.18, '1996-12-01', '1-URGENT', 'Clerk#000000880', NULL, ' foxes. pending accounts at the pending, silent asymptot'),
(3, 3, 'F', 193846.25, '1993-10-14', '5-LOW', NULL, 0, 'slyly regular accounts. bold deposits nag blithely. carefully unusual packag'),
(4, 4, 'O', 32151.78, '1995-10-11', '5-LOW', 'Clerk#000000124', NULL, NULL),
(5, 5, 'F', 144659.20, '1994-07-30', '5-LOW', 'Clerk#000000925', 0, 'quickly. bold deposits sleep slyly. packages use slyly'),
(6, 6, 'O', 50000.00, '1995-06-15', '2-HIGH', NULL, NULL, 'regular requests'),
(7, 7, 'F', 75000.00, '1993-12-20', '3-MEDIUM', 'Clerk#000000111', 0, NULL),
(8, 8, 'O', 100000.00, '1996-08-01', '4-NOT SPECIFIED', NULL, NULL, 'final deposits'),
(9, 9, 'F', 125000.00, '1994-03-10', '5-LOW', 'Clerk#000000222', 0, NULL),
(10, 10, 'O', 150000.00, '1997-01-15', '1-URGENT', NULL, NULL, 'carefully final');

-- ============================================================
-- Step 1: Remove the NOT EXISTS clause entirely
-- ============================================================
SELECT t2.a2, t4.ps_supplycost, t4.ps_availqty, t4.e1, t2.o_shippriority
FROM (SELECT t1.o_shippriority, t1.o_comment, MAX(t1.o_totalprice) AS a1, COUNT(*) AS a2, MIN(t1.o_orderstatus) AS a3
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, 1406.15 + 9743 - 277688.37 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8;

-- Result:
--   SQLite     : 3 rows, t4.e1 = NULL
--   PostgreSQL : 3 rows, t4.e1 = NULL
--   MySQL      : 3 rows, t4.e1 = NULL
--   Dameng     : 3 rows, t4.e1 = NULL
-- All four databases agree. The LEFT JOIN part itself is fine.

-- ============================================================
-- Step 2: Disable MySQL semijoin
-- ============================================================
SET optimizer_switch = 'semijoin=off';
-- then run the original query

-- Result:
--   Original query (default optimizer) : 0 rows
--   Original query (semijoin=off)      : 0 rows
-- Disabling semijoin does not change the result. Not a semijoin issue.

-- ============================================================
-- Step 3: Rewrite NOT EXISTS as LEFT JOIN ... WHERE ... IS NULL
-- ============================================================
SELECT t2.a2, t4.ps_supplycost, t4.ps_availqty
FROM (SELECT t1.o_shippriority, t1.o_comment, MAX(t1.o_totalprice) AS a1, COUNT(*) AS a2, MIN(t1.o_orderstatus) AS a3
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, 1406.15 + 9743 - 277688.37 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8
LEFT JOIN nation AS t5
  ON (t4.e1 = 7 AND t4.e1 <= 15 AND t4.e1 > 21
      OR t4.e1 <= 9
      OR t4.e1 >= 4
      OR t4.e1 <> 19
      OR t4.e1 <> 23 AND t2.o_shippriority <= t5.n_regionkey
      OR t4.e1 = 20
      OR t4.e1 = 14 AND t4.e1 = 19)
WHERE t5.n_regionkey IS NULL;

-- Result:
--   MySQL : 0 rows
-- Still 0 rows.

-- ============================================================
-- Step 4: NULL comparison semantics
-- ============================================================
SELECT NULL <> 19;
SELECT NULL = 19;
SELECT NOT (NULL = 19);
SELECT NULL <> 19 OR 1=1;
SELECT NULL <> 19 OR 1=0;
SELECT NULL <> 19 AND 1=1;
SELECT NULL <> 19 AND 1=0;

-- Result:
--   Expression              | SQLite | PostgreSQL | MySQL | Dameng
--   NULL <> 19              | NULL   | NULL       | NULL  | NULL
--   NULL = 19               | NULL   | NULL       | NULL  | NULL
--   NOT (NULL = 19)         | NULL   | NULL       | NULL  | NULL
--   NULL <> 19 OR 1=1       | 1      | True       | 1     | 1
--   NULL <> 19 OR 1=0       | NULL   | NULL       | NULL  | NULL
--   NULL <> 19 AND 1=1      | NULL   | NULL       | NULL  | NULL
--   NULL <> 19 AND 1=0      | 0      | False      | 0     | 0
-- All four databases agree on NULL semantics.

-- ============================================================
-- Step 5: Select t4.e1 both outside and inside the NOT EXISTS subquery
-- ============================================================
SELECT t2.a2, t4.ps_supplycost,
       (SELECT t4.e1 FROM nation AS t5 LIMIT 1) AS e1_in_exists
FROM (SELECT t1.o_shippriority, t1.o_comment, MAX(t1.o_totalprice) AS a1, COUNT(*) AS a2, MIN(t1.o_orderstatus) AS a3
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, 1406.15 + 9743 - 277688.37 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8;

-- Result:
--   MySQL : 3 rows, t4.e1 = NULL, e1_in_exists = NULL
-- t4.e1 is NULL on MySQL in both cases.

-- ============================================================
-- Step 6: Change e1 from a pure constant to a column reference
-- ============================================================

-- Query A: pure constant expression column
SELECT t2.a2, t4.ps_supplycost
FROM (SELECT t1.o_shippriority, t1.o_comment, COUNT(*) AS a2
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, 1406.15 + 9743 - 277688.37 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8
WHERE NOT EXISTS(SELECT 1 FROM nation AS t5 WHERE t4.e1 <= 9);

-- Query B: column reference expression column
SELECT t2.a2, t4.ps_supplycost
FROM (SELECT t1.o_shippriority, t1.o_comment, COUNT(*) AS a2
      FROM orders AS t1
      WHERE CASE WHEN t1.o_clerk <= 'Clerk#000002030' THEN t1.o_totalprice = 143621.52
                 WHEN t1.o_orderdate >= '1993-10-29' THEN t1.o_totalprice <= 143621.52
                 ELSE True END
      GROUP BY t1.o_shippriority, t1.o_comment) AS t2
LEFT JOIN (SELECT t3.ps_supplycost, t3.ps_availqty, t3.ps_supplycost + 0 AS e1
           FROM partsupp AS t3
           WHERE CASE WHEN t3.ps_suppkey = 264388 THEN t3.ps_partkey > 1793748 ELSE True END) AS t4
  ON t4.ps_supplycost = 883.8
WHERE NOT EXISTS(SELECT 1 FROM nation AS t5 WHERE t4.e1 <= 9);

-- Result:
--   Query A (pure constant 1406.15 + 9743 - 277688.37) : MySQL 0 rows
--   Query B (column reference t3.ps_supplycost + 0)    : MySQL 3 rows
-- The only difference between A and B is whether e1 is a pure constant
-- (foldable) or references a column (not foldable). This isolates the
-- problem to constant folding.

-- ============================================================
-- Step 7: Minimal reproduction
-- ============================================================
CREATE TABLE a(x INT);
CREATE TABLE b(y INT);
CREATE TABLE c(z INT);

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1);
INSERT INTO c VALUES (1);

-- Query A: pure constant expression column
SELECT a.x
FROM a
LEFT JOIN (SELECT b.y, 1 + 2 AS e1 FROM b WHERE b.y = 999) AS t
  ON t.y = 1
WHERE NOT EXISTS(SELECT 1 FROM c WHERE t.e1 <= 9);

-- Query B: column reference expression column
SELECT a.x
FROM a
LEFT JOIN (SELECT b.y, b.y + 2 AS e1 FROM b WHERE b.y = 999) AS t
  ON t.y = 1
WHERE NOT EXISTS(SELECT 1 FROM c WHERE t.e1 <= 9);

-- Result:
--   Query A (pure constant "1 + 2"):
--     SQLite     : 1 row
--     PostgreSQL : 1 row
--     MySQL      : 0 rows   <-- WRONG
--     Dameng     : 1 row
--   Query B (column reference "b.y + 2"):
--     SQLite     : 1 row
--     PostgreSQL : 1 row
--     MySQL      : 1 row    <-- correct
--     Dameng     : 1 row

-- ============================================================
-- Conclusion
-- ============================================================
-- The key observation: the only difference between the failing and
-- working versions is whether the derived table's expression column is
-- a pure constant (foldable) or references a column (not foldable).
-- This points directly at constant folding ignoring the NULL-ability of
-- the outer join's nullable side.