\set ECHO all


CREATE TABLE ten_percent_null AS
SELECT generate_series(1, 10000)::int AS a;
UPDATE ten_percent_null SET a = NULL WHERE a <= 1000;
ANALYZE ten_percent_null;

CREATE TABLE no_null AS
SELECT generate_series(1, 10000)::int AS a;
ANALYZE no_null;


-- Test semi-joins

-- join_is_reversed = false
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM no_null t2 WHERE t1.a <> t2.a);

-- join_is_reversed = true
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM no_null t2 WHERE t2.a <> t1.a);

-- join_is_reversed = true
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM no_null t2 WHERE t1.a + t2.a <> t1.a);

-- join_is_reversed = false
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM no_null t2 WHERE t1.a <> t1.a + t2.a);

-- join_is_reversed = false
-- this is the only case where the estimate is wrong (estimated sel 1, actually 0)
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM no_null t2 WHERE t1.a + t2.a <> t1.a + t2.a);


-- Test anti-joins

-- join_is_reversed = false
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM no_null t2 WHERE t1.a <> t2.a);

-- join_is_reversed = true
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM no_null t2 WHERE t2.a <> t1.a);

-- join_is_reversed = true
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM no_null t2 WHERE t1.a + t2.a <> t1.a);

-- join_is_reversed = false
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM no_null t2 WHERE t1.a <> t1.a + t2.a);

-- join_is_reversed = false
-- this is the only case where the estimate is wrong (estimated sel 0, actually 1)
EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM no_null t2 WHERE t1.a + t2.a <> t1.a + t2.a);




-- Special case for empty RHS: we could in theory detect empty RHS and handle this,
-- but that seems like a kind of extreme sensitivity to bad stats.  Best not to try
-- to do this?

CREATE TABLE empty (a int);
ANALYZE empty;

EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM empty t2 WHERE t1.a <> t2.a);

EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM empty t2 WHERE t1.a <> t2.a);


-- Special case for single value RHS:  we could in theory check the MCV of a
-- a RHS table that has only a single value, and then estimate how many rows
-- in the LHS would match: we estimate incorrectly by that number in this case.
-- Should we try to do that?

CREATE TABLE single_value (a int);
INSERT INTO single_value (a) VALUES (1042);
ANALYZE single_value;

EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE EXISTS (SELECT * FROM single_value t2 WHERE t1.a <> t2.a);

EXPLAIN ANALYZE
  SELECT * FROM ten_percent_null t1
  WHERE NOT EXISTS (SELECT * FROM single_value t2 WHERE t1.a <> t2.a);
