Changeset: d4c04f17a9d6 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/d4c04f17a9d6
Added Files:
sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.reqtests
sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.test
Modified Files:
sql/test/rel-optimizers/Tests/All
sql/test/rel-optimizers/Tests/merge-ors-base.test
Branch: cmp-or-patterns
Log Message:
Adds test for multi-col cmp_eq to cmp_in case by merge_ors opt
diffs (175 lines):
diff --git a/sql/test/rel-optimizers/Tests/All
b/sql/test/rel-optimizers/Tests/All
--- a/sql/test/rel-optimizers/Tests/All
+++ b/sql/test/rel-optimizers/Tests/All
@@ -14,3 +14,4 @@ merge-unions-base
merge-unions
merge-ors-base
merge-ors-single-col-eq-to-cmp_in
+merge-ors-multi-col-eq-to-cmp_in
diff --git a/sql/test/rel-optimizers/Tests/merge-ors-base.test
b/sql/test/rel-optimizers/Tests/merge-ors-base.test
--- a/sql/test/rel-optimizers/Tests/merge-ors-base.test
+++ b/sql/test/rel-optimizers/Tests/merge-ors-base.test
@@ -2,5 +2,19 @@ statement ok
create table f (n int, m int)
statement ok
-insert into f values (1, 20), (2, 40), (2, 0), (3, 60), (9, 180)
+insert into f values (1, 20),
+ (2, 40),
+ (2, 0 ),
+ (3, 60),
+ (9, 180)
+statement ok
+create table b (n int, m int, k int)
+
+statement ok
+insert into b values (1, 20, 200),
+ (2, 40, 400),
+ (2, 0, 0),
+ (3, 60, 600),
+ (9, 180, 1800)
+
diff --git
a/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.reqtests
b/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.reqtests
new file mode 100644
--- /dev/null
+++ b/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.reqtests
@@ -0,0 +1,1 @@
+merge-ors-base
diff --git
a/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.test
b/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.test
new file mode 100644
--- /dev/null
+++ b/sql/test/rel-optimizers/Tests/merge-ors-multi-col-eq-to-cmp_in.test
@@ -0,0 +1,132 @@
+query III rowsort
+select * from b where n > 3 or (m = 20 and k = 200) or (m = 60 and k = 600)
+----
+1
+20
+200
+3
+60
+600
+9
+180
+1800
+
+query T nosort non-eq-two-col-2-value
+plan select * from b where n > 3 or (m = 20 and k = 200) or (m = 60 and k =
600)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") > (int(4) "3")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query T nosort non-eq-two-col-2-value
+plan select * from b where (m = 20 and k = 200) or n > 3 or (m = 60 and k =
600)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") > (int(4) "3")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+# order of m,k equality statements are swapped in the last AND expression
+query T nosort non-eq-two-col-2-value
+plan select * from b where (m = 20 and k = 200) or n > 3 or (k = 600 and m =
60)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") > (int(4) "3")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query T nosort non-eq-two-col-2-value
+plan select * from b where (m = 20 and k = 200) or (m = 60 and k = 600) or n >
3
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") > (int(4) "3")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query III rowsort
+select * from b where (n = 1 and m = 20) or (m = 20 and k = 200) or (m = 60
and k = 600)
+----
+1
+20
+200
+3
+60
+600
+
+query T nosort two-col-1-value-two-col-2-value
+plan select * from b where (n = 1 and m = 20) or (m = 20 and k = 200) or (m =
60 and k = 600)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") = (int(4) "1"), ("b"."m") = (int(8)
"20")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query T nosort two-col-1-value-two-col-2-value
+plan select * from b where (m = 20 and k = 200) or (n = 1 and m = 20) or (m =
60 and k = 600)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") = (int(4) "1"), ("b"."m") = (int(8)
"20")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query T nosort two-col-1-value-two-col-2-value
+plan select * from b where (m = 20 and k = 200) or (m = 60 and k = 600) or (n
= 1 and m = 20)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."n") = (int(4) "1"), ("b"."m") = (int(8)
"20")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query III rowsort
+select * from b where (m = 40 and k > 0) or (m = 20 and k = 200) or (m = 60
and k = 600)
+----
+1
+20
+200
+2
+40
+400
+3
+60
+600
+
+query T nosort
+plan select * from b where (m = 40 and k > 0) or (m = 20 and k = 200) or (m =
60 and k = 600)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."n", "b"."m", "b"."k" ]
+| ) [ (( [ "b"."m", "b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8)
"60", int(11) "600" ])) or (("b"."m") = (int(8) "40"), ("b"."k") > (int(11)
"0")) ]
+) [ "b"."n", "b"."m", "b"."k" ]
+
+query II rowsort
+select m, k from b where (m = 40) or (m = 20 and k = 200) or (m = 0) or (m =
60 and k = 600) or (m = 180)
+----
+0
+0
+180
+1800
+20
+200
+40
+400
+60
+600
+
+query T nosort single-col-3-val-2-col-2-val
+plan select m, k from b where (m = 40) or (m = 20 and k = 200) or (m = 0) or
(m = 60 and k = 600) or (m = 180)
+----
+project (
+| select (
+| | table("sys"."b") [ "b"."m", "b"."k" ]
+| ) [ (("b"."m") in (int(8) "40", int(8) "0", int(8) "180")) or (( [ "b"."m",
"b"."k" ]) in ( [ int(8) "20", int(11) "200" ], [ int(8) "60", int(11) "600"
])) ]
+) [ "b"."m", "b"."k" ]
+
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
