dl2uの改修

[yoshihiro503]

$ dune exec dl2u tmp3.dl

を実行すると

 Unexpected Equation: Q > 2

tmp3.dl:

source tracks2('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
view tracks3('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
+tracks2(T, R, A, Q) :- tracks2(T, GENV1, A, Q) , not tracks2(T, R, A, Q) , A = 'Show' , not GENV1 = 4 , Q > 2 , R = 4.
-tracks2(T, R, A, Q) :- tracks2(T, R, A, Q) , A = 'Show' , Q > 2 , not R = 4.

tmp3.txtを参照:

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, 'Show' AS ALBUM, 2 AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4;

の2 AS QUANTITYをtracks2_0.QUANTIRY AS QUANTITYにする必要がある

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, 'Show' AS ALBUM, tracks2_0.QUANTIRY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4;

temp1についても同様のことをする必要がある。

[yoshihiro503]

dune exec dl2u input3.dl > output3.sql
dune exec dl2u input4.dl > output4.sql

input3.dl:

source tracks2('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
view tracks3('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
+tracks2(T, R, A, Q) :- tracks2(T, GENV1, A, Q) , not tracks2(T, R, A, Q) , A = 'Show' , not GENV1 = 4 , Q > 2 , R = 4.
-tracks2(T, R, A, Q) :- tracks2(T, R, A, Q) , A = 'Show' , Q > 2 , not R = 4.

output3.sql:

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING  AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4 AND NOT EXISTS ( SELECT * FROM tracks2 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.RATING = 4 AND t.ALBUM = 'Show' AND t.QUANTITY > 2 );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;

input4.dl

source tracks2('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
view tracks3('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
-tracks2(T, R1, A, Q) :- tracks2(T, R1, A, Q) , tracks2(T, R2, GENV3, GENV4) , GENV4 = 2 , R1 <> R2.
+tracks2(T, R2, A, Q) :- tracks2(T, R1, A, Q) , not -tracks2(T, R1, A, Q) , tracks2(T, R2, GENV1, GENV2) , GENV2 = 2 , R1 <> R2.

output4.sql:

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_0.RATING <> tracks2_1.RATING AND tracks2_1.TRACK = tracks2_0.TRACK;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_0.RATING <> tracks2_1.RATING AND tracks2_1.TRACK = tracks2_0.TRACK AND NOT EXISTS ( SELECT * FROM temp0 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.ALBUM = tracks2_0.ALBUM AND t.QUANTITY = tracks2_0.QUANTITY );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;

[cedretaber]

input3 について

元の datalog

source tracks2('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
view tracks3('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
+tracks2(T, R, A, Q) :- tracks2(T, GENV1, A, Q) , not tracks2(T, R, A, Q) , A = 'Show' , not GENV1 = 4 , Q > 2 , R = 4.
-tracks2(T, R, A, Q) :- tracks2(T, R, A, Q) , A = 'Show' , Q > 2 , not R = 4.

期待値

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING  AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.QUANTITY > 2 AND tracks2_0.RATING <> 4 AND NOT EXISTS ( SELECT * FROM tracks2 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.RATING = 4 AND t.ALBUM = 'Show' AND t.QUANTITY > 2 );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;

現状の出力値

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, 'Show' AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.RATING <> 4 AND tracks2_0.QUANTITY > 2;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, 4 AS RATING, 'Show' AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0 WHERE tracks2_0.ALBUM = 'Show' AND tracks2_0.RATING <> 4 AND tracks2_0.QUANTITY > 2 AND NOT EXISTS ( SELECT * FROM tracks2 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.RATING = 4 AND t.ALBUM = 'Show' AND t.QUANTITY = tracks2_0.QUANTITY );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;
[WARN] Unsupported equation: Q > 2
[WARN] Unsupported equation: Q > 2

考察

temp0 について

tracks2_0.ALBUM AS ALBUM と 'Show' AS ALBUM が異なるが、 tracks2_0.ALBUM = 'Show' という条件があるので意味論的には同じ。

temp1 について

tracks2_0.RATING AS RATING と 4 AS RATING が異なる。
期待値の書き方だと元々の RATING を保持する形になるが、元の datalog 式 tracks2(T, R, A, Q) :- tracks2(T, GENV1, A, Q) , not tracks2(T, R, A, Q) , A = 'Show' , not GENV1 = 4 , Q > 2 , R = 4. を考えると、ここは現状の出力である 4 AS RATING が正しそう。

tracks2_0.ALBUM AS ALBUM と 'Show' AS ALBUM の違いについては temp0 と同じ。

NOT EXISTS の中の t.QUANTITY > 2 と t.QUANTITY = tracks2_0.QUANTITY が異なる。
これも、元の datalog 式 tracks2(T, R, A, Q) :- tracks2(T, GENV1, A, Q) , not tracks2(T, R, A, Q) , A = 'Show' , not GENV1 = 4 , Q > 2 , R = 4. を考えると、同じ Q を指定するべきなので、期待値の t.QUANTITY > 2 ではなく現状の出力の t.QUANTITY = tracks2_0.QUANTITY が正しそう。

結論

現状の出力で問題なく、期待値として与えられた SQL が間違っていそう？

[cedretaber]

input4 について

元の datalog

source tracks2('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
view tracks3('TRACK':string, 'RATING':int, 'ALBUM':string, 'QUANTITY':int).
-tracks2(T, R1, A, Q) :- tracks2(T, R1, A, Q) , tracks2(T, R2, GENV3, GENV4) , GENV4 = 2 , R1 <> R2.
+tracks2(T, R2, A, Q) :- tracks2(T, R1, A, Q) , not -tracks2(T, R1, A, Q) , tracks2(T, R2, GENV1, GENV2) , GENV2 = 2 , R1 <> R2.

期待値

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_0.RATING <> tracks2_1.RATING AND tracks2_1.TRACK = tracks2_0.TRACK;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_0.RATING <> tracks2_1.RATING AND tracks2_1.TRACK = tracks2_0.TRACK AND NOT EXISTS ( SELECT * FROM temp0 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.ALBUM = tracks2_0.ALBUM AND t.QUANTITY = tracks2_0.QUANTITY );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;

現在の出力値

CREATE TEMPORARY TABLE temp0 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_0.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_1.TRACK = tracks2_0.TRACK AND tracks2_0.RATING <> tracks2_1.RATING;
CREATE TEMPORARY TABLE temp1 AS SELECT tracks2_0.TRACK AS TRACK, tracks2_1.RATING AS RATING, tracks2_0.ALBUM AS ALBUM, tracks2_0.QUANTITY AS QUANTITY FROM tracks2 AS tracks2_0, tracks2 AS tracks2_1 WHERE tracks2_1.QUANTITY = 2 AND tracks2_1.TRACK = tracks2_0.TRACK AND tracks2_0.RATING <> tracks2_1.RATING AND NOT EXISTS ( SELECT * FROM temp0 AS t WHERE t.TRACK = tracks2_0.TRACK AND t.RATING = tracks2_0.RATING AND t.ALBUM = tracks2_0.ALBUM AND t.QUANTITY = tracks2_0.QUANTITY );
DELETE FROM tracks2 USING temp0 WHERE tracks2.TRACK = temp0.TRACK AND tracks2.RATING = temp0.RATING AND tracks2.ALBUM = temp0.ALBUM AND tracks2.QUANTITY = temp0.QUANTITY;
INSERT INTO tracks2 SELECT * FROM temp1;
[WARN] Unsupported equation: R1 <> R2
[WARN] Unsupported equation: R1 <> R2

考察

temp0 について

WHERE 内の順番が違うだけで同じ

temp1 について

tracks2_0.RATING AS RATING と tracks2_1.RATING AS RATING が異なる。
元の datalog 式は +tracks2(T, R2, A, Q) :- tracks2(T, R1, A, Q) , not -tracks2(T, R1, A, Q) , tracks2(T, R2, GENV1, GENV2) , GENV2 = 2 , R1 <> R2. で、ヘッドは R2 になっている。 R2 は tracks2(T, R2, GENV1, GENV2) , GENV2 = 2 の条件で指定されているので、 SQL では WHERE tracks2_1.QUANTITY = 2 という条件の入った tracks2_1 が正しいはず。
よって、現在の出力の方が正しい。

NOT EXISTS の t.RATING = tracks2_0.RATING が期待値にはないが現在の出力値には存在する。
元の datalog 式は +tracks2(T, R2, A, Q) :- tracks2(T, R1, A, Q) , not -tracks2(T, R1, A, Q) , tracks2(T, R2, GENV1, GENV2) , GENV2 = 2 , R1 <> R2. で、 not -tracks2(T, R1, A, Q) の R1 は tracks2(T, R1, A, Q) で利用されているのでその制約が SQL にも反映される必要がある。
よって、現在の出力の方が正しい。

結論

これも現状の出力で問題なく、期待値として与えられた SQL が間違っていそう？

[hiroyukikato]

丁寧に考察して頂きありがとうございます。
確かに私の提示した期待値に間違いがあったようで、現在の出力値で問題なさそうです。