Skip to content

Instantly share code, notes, and snippets.

@gfngfn

gfngfn/birds-integration-test-en.md

Last active August 1, 2022 02:02
Show Gist options
  • Save gfngfn/b3777fa4175615280a2b00ab29ad7b19 to your computer and use it in GitHub Desktop.
Save gfngfn/b3777fa4175615280a2b00ab29ad7b19 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
birds-integration-test-en.md

Integration Tests for Operation-based Conversion of BIRDS

This is an operation check of the implementation given as 10ab5fd in the following PR:

Note 1: The tests below actually use 6dfa1d3 in the following PR, where the newly added feature can be used via CLI:

Note 2: All operations were performed inside a Docker image:

How to perform integration tests and their results

1 Compile and install the extended version of BIRDS

Just invoke the following:

$ make
$ make install

2 Generate SQL queries from a Datalog program

Use benchmarks/pods06/tracks1.dl to SQL queries:

source tracks('TRACK':string,'DATE':int,'RATING':int,'ALBUM':string).
source albums('ALBUM':string,'QUANTITY':int).
view tracks1('TRACK':string,'DATE':int,'RATING':int,'ALBUM':string,'QUANTITY':int).

% constraints:

⊥ :- tracks1(T,D,R,A,_), tracks1(_,_,_,A,Q), not tracks1(T,D,R,A,Q).

% view definition:
tracks1(T,D,R,A,Q) :- tracks(T,D,R,A), albums(A,Q).

% caculate delta relation of tracks for the modification in the view
+tracks(TRACK,DATE,RATING,ALBUM) :- tracks1(TRACK,DATE,RATING,ALBUM,_), not tracks(TRACK,DATE,RATING,ALBUM).
-tracks(TRACK,DATE,RATING,ALBUM) :- tracks(TRACK,DATE,RATING,ALBUM),not tracks1(TRACK,DATE,RATING,ALBUM,_), albums(ALBUM,_).
-tracks(TRACK,DATE,RATING,ALBUM) :- tracks(TRACK,DATE,RATING,ALBUM),not tracks1(TRACK,DATE,RATING,ALBUM,_), tracks1(_,_,_,ALBUM,_).

% caculate delta relation of albums for the modification in the view
+albums(ALBUM, QUANTITY) :- tracks1(_,_,_,ALBUM,QUANTITY), not albums(ALBUM,QUANTITY).
-albums(ALBUM, QUANTITY) :- albums(ALBUM,QUANTITY), not tracks1(_,_,_,ALBUM,QUANTITY), tracks(TRACK,DATE,RATING,ALBUM), not -tracks(TRACK,DATE,RATING,ALBUM).
-albums(ALBUM, QUANTITY) :- albums(ALBUM,QUANTITY), not tracks1(_,_,_,ALBUM,QUANTITY), +tracks(TRACK,DATE,RATING,ALBUM), not -tracks(TRACK,DATE,RATING,ALBUM).
-albums(ALBUM, QUANTITY) :- albums(ALBUM,QUANTITY), not tracks1(_,_,_,ALBUM,QUANTITY), tracks1(_,_,_,ALBUM,_), not -tracks(_,_,_,ALBUM).

The source file above contains seven rules for delta predicates. Let us name them (a)-(g), respectively.

The following command converts the Datalog source file into the corresponding SQL queries and prints them on stdout:

$ birds -f benchmarks/pods06/tracks1.dl

This will generate SQL queries that contain definitions of temporary tables corresponding to +tracks (i.e. (a)), -tracks (i.e. (b)-(c)), +albums (i.e. (d)), and -albums (i.e. (e)-(g)).

3 Apply the generated queries to test cases on PostgreSQL

Use the six test cases provided as TestCase1.txt-TestCase6.txt, respectively; see the results for detail.

Results

1 Compile and install the extended version of BIRDS

$ docker run --rm -it -v $(pwd):/workspace proofninja/birds-cui:v1 bash
root@6d502981f680:~# cd /workspace/
root@6d502981f680:/workspace# opam init
(omitted)
root@6d502981f680:/workspace# eval `opam config env`
[WARNING] Running as root is not recommended
root@6d502981f680:/workspace# make
Makefile:144: warning: overriding recipe for target 'release/parser.cmi'
Makefile:136: warning: ignoring old recipe for target 'release/parser.cmi'
Makefile:152: warning: overriding recipe for target 'release/parser.cmx'
Makefile:139: warning: ignoring old recipe for target 'release/parser.cmx'
ocamlfind ocamldep -I src -I src/logic -I src/test src/logic/lib.ml src/logic/intro.ml src/logic/formulas.ml src/logic/prop.ml src/logic/fol.ml src/logic/skolem.ml src/logic/fol_ex.ml src/expr.ml src/utils.ml src/parser.ml src/lexer.ml src/conn_ops.ml src/rule_preprocess.ml src/stratification.ml src/derivation.ml src/bottom_up.ml src/evaluation.ml src/ast2fol.ml src/ast2sql.ml src/ast2ros.ml src/ast2theorem.ml src/bx.ml src/debugger.ml src/lexer.mll src/parser.mli |sed -e 's/src/bin/g' > depend
ocamlfind ocamlc -bin-annot -w -26  -I src -I src/logic -I src/test -package postgresql,str,num -thread -o src/ast2sql -c src/ast2sql.ml
ocamlfind ocamlc -bin-annot -w -26  -I src -I src/logic -I src/test -package postgresql,str,num -thread -o src/main -c src/main.ml
ocamlfind ocamlc -bin-annot -w -26  -I src -I src/logic -I src/test -package postgresql,str,num -thread -linkpkg src/logic/lib.cmo src/logic/intro.cmo src/logic/formulas.cmo src/logic/prop.cmo src/logic/fol.cmo src/logic/skolem.cmo src/logic/fol_ex.cmo src/expr.cmo src/utils.cmo src/parser.cmo src/lexer.cmo src/conn_ops.cmo src/rule_preprocess.cmo src/stratification.cmo src/derivation.cmo src/bottom_up.cmo src/evaluation.cmo src/ast2fol.cmo src/ast2sql.cmo src/ast2ros.cmo src/ast2theorem.cmo src/bx.cmo src/debugger.cmo src/main.cmo -o bin/birds
root@6d502981f680:/workspace# make install
Makefile:144: warning: overriding recipe for target 'release/parser.cmi'
Makefile:136: warning: ignoring old recipe for target 'release/parser.cmi'
Makefile:152: warning: overriding recipe for target 'release/parser.cmx'
Makefile:139: warning: ignoring old recipe for target 'release/parser.cmx'
ocamlfind ocamldep -I src -I src/logic -I src/test src/logic/lib.ml src/logic/intro.ml src/logic/formulas.ml src/logic/prop.ml src/logic/fol.ml src/logic/skolem.ml src/logic/fol_ex.ml src/expr.ml src/utils.ml src/parser.ml src/lexer.ml src/conn_ops.ml src/rule_preprocess.ml src/stratification.ml src/derivation.ml src/bottom_up.ml src/evaluation.ml src/ast2fol.ml src/ast2sql.ml src/ast2ros.ml src/ast2theorem.ml src/bx.ml src/debugger.ml src/lexer.mll src/parser.mli |sed -e 's/src/bin/g' > depend
make release
make[1]: Entering directory '/workspace'
Makefile:144: warning: overriding recipe for target 'release/parser.cmi'
Makefile:136: warning: ignoring old recipe for target 'release/parser.cmi'
Makefile:152: warning: overriding recipe for target 'release/parser.cmx'
Makefile:139: warning: ignoring old recipe for target 'release/parser.cmx'
ocamlfind ocamldep -I src -I src/logic -I src/test src/logic/lib.ml src/logic/intro.ml src/logic/formulas.ml src/logic/prop.ml src/logic/fol.ml src/logic/skolem.ml src/logic/fol_ex.ml src/expr.ml src/utils.ml src/parser.ml src/lexer.ml src/conn_ops.ml src/rule_preprocess.ml src/stratification.ml src/derivation.ml src/bottom_up.ml src/evaluation.ml src/ast2fol.ml src/ast2sql.ml src/ast2ros.ml src/ast2theorem.ml src/bx.ml src/debugger.ml src/lexer.mll src/parser.mli |sed -e 's/src/bin/g' > depend
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/lib -c src/logic/lib.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/lib -c src/logic/lib.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/intro -c src/logic/intro.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/intro -c src/logic/intro.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/formulas -c src/logic/formulas.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/formulas -c src/logic/formulas.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/prop -c src/logic/prop.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/prop -c src/logic/prop.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/fol -c src/logic/fol.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/fol -c src/logic/fol.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/skolem -c src/logic/skolem.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/skolem -c src/logic/skolem.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/logic/fol_ex -c src/logic/fol_ex.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/logic/fol_ex -c src/logic/fol_ex.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/expr -c src/expr.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/expr -c src/expr.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/utils -c src/utils.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/parser -c src/parser.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/parser -c src/parser.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/lexer -c src/lexer.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/conn_ops -c src/conn_ops.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/rule_preprocess -c src/rule_preprocess.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/stratification -c src/stratification.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/derivation -c src/derivation.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/bottom_up -c src/bottom_up.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/evaluation -c src/evaluation.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/ast2fol -c src/ast2fol.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/ast2sql -c src/ast2sql.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/ast2sql -c src/ast2sql.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/ast2ros -c src/ast2ros.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -o release/ast2theorem -c src/ast2theorem.mli
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/ast2theorem -c src/ast2theorem.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/bx -c src/bx.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/debugger -c src/debugger.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -o release/main -c src/main.ml
ocamlfind ocamlopt -bin-annot -w -26 -I release -I release/logic -package postgresql,str,num -thread -linkpkg release/logic/lib.cmx release/logic/intro.cmx release/logic/formulas.cmx release/logic/prop.cmx release/logic/fol.cmx release/logic/skolem.cmx release/logic/fol_ex.cmx release/expr.cmx release/utils.cmx release/parser.cmx release/lexer.cmx release/conn_ops.cmx release/rule_preprocess.cmx release/stratification.cmx release/derivation.cmx release/bottom_up.cmx release/evaluation.cmx release/ast2fol.cmx release/ast2sql.cmx release/ast2ros.cmx release/ast2theorem.cmx release/bx.cmx release/debugger.cmx release/main.cmx -o release/birds
rm -f release/*.cmx release/*.cmi release/*.o release/logic/*.cmx release/logic/*.cmi release/logic/*.o
make[1]: Leaving directory '/workspace'
mv release/birds /usr/local/bin/

2 Generate SQL queries from a Datalog program

The following SQL queries were generated:

$ birds -f benchmarks/pods06/tracks1.dl
CREATE VIEW tracks1 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM, albums_1.QUANTITY AS QUANTITY FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
INSERT INTO temp1 SELECT * FROM temp1 AS inst;
DELETE FROM temp2 WHERE EXISTS ( SELECT * FROM temp2 AS inst );
INSERT INTO temp3 SELECT * FROM temp3 AS inst;
DELETE FROM temp4 WHERE EXISTS ( SELECT * FROM temp4 AS inst );

They contain the definitions of four temporary tables.

  • temp1 corresponds to +tracks, i.e., (a).
  • temp2 corresponds to -tracks, i.e., (b)-(c).
  • temp3 corresponds to +albums, i.e., (d).
  • temp4 corresponds to -albums, i.e., (e)-(g).

3 Apply the generated queries to test cases on PostgreSQL

TestCase1.txt

First, create integration_tests/init1.sql with the following contents, which perform initialization:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris'),
('Trust', 1992, 4, 'Wish'),
('Another song', 1999, 4, 'Tokyo');

INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4),
('Wish',5);

CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

Then run the following commands:

gfnmac@gfnMBP BIRDS % docker run --rm -it -v $(pwd):/workspace proofninja/birds-cui:v1 bash
root@55e1f0c1b55a:~# cd /workspace/
root@55e1f0c1b55a:/workspace# pg_ctlcluster 9.6 main start
root@55e1f0c1b55a:/workspace# psql -U postgres postgres -f integration_tests/init1.sql
CREATE TABLE
CREATE TABLE
INSERT 0 6
INSERT 0 5
CREATE TABLE
INSERT 0 5

Apply the query that will make the temporary table corresponding to +tracks, i.e., (a):

root@55e1f0c1b55a:/workspace# psql -U postgres
psql (9.6.22)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 0

Apply the query that will make the temporary table corresponding to -tracks, i.e., (b)-(c):

postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 0

Apply the query that will make the temporary table corresponding to +albums, i.e., (d):

postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 0

Apply the query that will make the temporary table corresponding to -albums, i.e., (e)-(g):

postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 0

All of the four queries gave expected results.

TestCase2.txt

integration_tests/init2.sql:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris'),
('Trust', 1992, 4, 'Wish'),
('Another song', 1999, 4, 'Tokyo');

INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4),
('Wish',5);

CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

INSERT INTO tracks1 VALUES('Mysong', 2018, 10, 'Myalbum', 20);
root@6d502981f680:/workspace# psql -U postgres postgres -f integration_tests/init2.sql
CREATE TABLE
CREATE TABLE
INSERT 0 6
INSERT 0 5
CREATE TABLE
INSERT 0 5
INSERT 0 1
postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 1
postgres=# select * from temp1;
 track  | date | rating |  album
--------+------+--------+---------
 Mysong | 2018 |     10 | Myalbum
(1 row)

postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 1
postgres=# select * from temp3;
  album  | quantity
---------+----------
 Myalbum |       20
(1 row)

postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 0

All of the four queries gave expected results.

TestCase3.txt

integration_tests/init3.sql:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris'),
('Trust', 1992, 4, 'Wish'),
('Another song', 1999, 4, 'Tokyo');

INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4),
('Wish',5);

CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

delete from tracks1 where track='Lovesong';
root@0655575b17cb:/workspace# psql -U postgres postgres -f integration_tests/init3.sql
CREATE TABLE
CREATE TABLE
INSERT 0 6
INSERT 0 5
CREATE TABLE
INSERT 0 5
DELETE 2
root@0655575b17cb:/workspace# psql -U postgres
psql (9.6.22)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 2
postgres=# select * from temp2;
  track   | date | rating | album
----------+------+--------+--------
 Lovesong | 1989 |      5 | Paris
 Lovesong | 1989 |      5 | Galore
(2 rows)

postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 0

TestCase4.txt

integration_tests/init4.sql:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris'),
('Trust', 1992, 4, 'Wish'),
('Another song', 1999, 4, 'Tokyo');

INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4),
('Wish',5);

CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

INSERT INTO tracks1 VALUES('Mysong', 2018, 10, 'Tokyo', 5);
root@b67f1d956157:/workspace# psql -U postgres postgres -f integration_tests/init4.sql
CREATE TABLE
CREATE TABLE
INSERT 0 6
INSERT 0 5
CREATE TABLE
INSERT 0 5
INSERT 0 1
root@b67f1d956157:/workspace# psql -U postgres
psql (9.6.22)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 1
postgres=# select * from temp1;
 track  | date | rating | album
--------+------+--------+-------
 Mysong | 2018 |     10 | Tokyo
(1 row)

postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 1
postgres=# select * from temp2;
    track     | date | rating | album
--------------+------+--------+-------
 Another song | 1999 |      4 | Tokyo
(1 row)

postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 1
postgres=# select * from temp3;
 album | quantity
-------+----------
 Tokyo |        5
(1 row)

postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 0

TestCase5.txt

integration_tests/init5.sql:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris'),
('Another song', 1999, 4, 'Tokyo');

INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4),
('Wish',5);

CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

INSERT INTO tracks1 VALUES('Yours', 2021, 8, 'Wish', 8);
root@fd8e242340fc:/workspace# psql -U postgres postgres -f integration_tests/init5.sql
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 5
CREATE TABLE
INSERT 0 4
INSERT 0 1
root@fd8e242340fc:/workspace# psql -U postgres
psql (9.6.22)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 1
postgres=# select * from temp1;
 track | date | rating | album
-------+------+--------+-------
 Yours | 2021 |      8 | Wish
(1 row)

postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 1
postgres=# select * from temp3;
 album | quantity
-------+----------
 Wish  |        8
(1 row)

postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 1
postgres=# select * from temp4;
 album | quantity
-------+----------
 Wish  |        5
(1 row)

postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM );
 album | quantity
-------+----------
(0 rows)

postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM );
 album | quantity
-------+----------
 Wish  |        5
(1 row)

postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
 album | quantity
-------+----------
 Wish  |        5
(1 row)

TestCase6.txt

integration_tests/init6.sql:

CREATE TABLE tracks
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR
);

CREATE TABLE albums
(
  album VARCHAR NOT NULL,
  quantity INT
);

INSERT INTO tracks VALUES
('Lullaby', 1989, 3, 'Galore'),
('Lullaby', 1989, 3, 'Show'),
('Lovesong', 1989, 5, 'Galore'),
('Lovesong', 1989, 5, 'Paris');


INSERT INTO albums VALUES
('Disintegration',6),
('Show',3),
('Galore',1),
('Paris',4);


CREATE TABLE tracks1
(
  track VARCHAR NOT NULL,
  date int,
  rating int,
  album VARCHAR,
  quantity INT
);

INSERT INTO tracks1
SELECT track, date, rating, tracks.album, quantity
FROM tracks, albums
WHERE tracks.album=albums.album;

UPDATE tracks1 SET quantity=5 WHERE album='Paris';
root@06e7d37a1124:/workspace# psql -U postgres postgres -f integration_tests/init6.sql
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 4
CREATE TABLE
INSERT 0 4
UPDATE 1
root@06e7d37a1124:/workspace# select * from tracks limit 10;
bash: syntax error near unexpected token `from'
root@06e7d37a1124:/workspace# psql -U postgres
psql (9.6.22)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE temp1 AS SELECT tracks1_0.TRACK AS TRACK, tracks1_0.DATE AS DATE, tracks1_0.RATING AS RATING, tracks1_0.ALBUM AS ALBUM FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM tracks AS t WHERE t.TRACK = tracks1_0.TRACK AND t.DATE = tracks1_0.DATE AND t.RATING = tracks1_0.RATING AND t.ALBUM = tracks1_0.ALBUM );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp2 AS SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, albums AS albums_1 WHERE albums_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM ) UNION SELECT tracks_0.TRACK AS TRACK, tracks_0.DATE AS DATE, tracks_0.RATING AS RATING, tracks_0.ALBUM AS ALBUM FROM tracks AS tracks_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = tracks_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.TRACK = tracks_0.TRACK AND t.DATE = tracks_0.DATE AND t.RATING = tracks_0.RATING AND t.ALBUM = tracks_0.ALBUM );
SELECT 0
postgres=# CREATE TEMPORARY TABLE temp3 AS SELECT tracks1_0.ALBUM AS ALBUM, tracks1_0.QUANTITY AS QUANTITY FROM tracks1 AS tracks1_0 WHERE NOT EXISTS ( SELECT * FROM albums AS t WHERE t.ALBUM = tracks1_0.ALBUM AND t.QUANTITY = tracks1_0.QUANTITY );
SELECT 1
postgres=# select * from temp3;
 album | quantity
-------+----------
 Paris |        5
(1 row)

postgres=# CREATE TEMPORARY TABLE temp4 AS SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM ) UNION SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
SELECT 1
postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks AS tracks_1 WHERE tracks_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = tracks_1.TRACK AND t.DATE = tracks_1.DATE AND t.RATING = tracks_1.RATING AND t.ALBUM = albums_0.ALBUM );;
 album | quantity
-------+----------
 Paris |        4
(1 row)

postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, temp1 AS temp1 WHERE temp1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.TRACK = temp1.TRACK AND t.DATE = temp1.DATE AND t.RATING = temp1.RATING AND t.ALBUM = albums_0.ALBUM );
 album | quantity
-------+----------
(0 rows)

postgres=# SELECT albums_0.ALBUM AS ALBUM, albums_0.QUANTITY AS QUANTITY FROM albums AS albums_0, tracks1 AS tracks1_1 WHERE tracks1_1.ALBUM = albums_0.ALBUM AND NOT EXISTS ( SELECT * FROM tracks1 AS t WHERE t.ALBUM = albums_0.ALBUM AND t.QUANTITY = albums_0.QUANTITY ) AND NOT EXISTS ( SELECT * FROM temp2 AS t WHERE t.ALBUM = albums_0.ALBUM );
 album | quantity
-------+----------
 Paris |        4
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment