Changeset: 3e2295c5b3b6 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3e2295c5b3b6
Modified Files:
sql/test/pg_regress/Tests/All
sql/test/pg_regress/Tests/create_view.sql
sql/test/pg_regress/Tests/create_view.stable.err
sql/test/pg_regress/Tests/create_view.stable.out
sql/test/pg_regress/Tests/drop.sql
sql/test/pg_regress/Tests/drop.stable.err
sql/test/pg_regress/Tests/select_views.sql
sql/test/pg_regress/Tests/select_views.stable.out
Branch: default
Log Message:
Adapted and enabled tests: create_view.sql and slect_views.sql
MonetDB does not (yet) support CREATE OR REPLACE VIEW syntax (see 3574) or
CREATE VIEW ... ORDER BY .. (see 3465).
Those statements will fail currently, but I kept them in for future.
diffs (truncated from 1773 to 300 lines):
diff --git a/sql/test/pg_regress/Tests/All b/sql/test/pg_regress/Tests/All
--- a/sql/test/pg_regress/Tests/All
+++ b/sql/test/pg_regress/Tests/All
@@ -57,8 +57,9 @@ loadwisconsin #[MK] copied from benchmar
create_table
create_misc
create_operator
-# create_index # pg specific index syntax (USING btree() or rtree() or hash()
and functional and conditional indexes) are not supported in MonetDB. skipping
this test
-#ToDo create_view
+# create_index # pg specific index syntax (USING btree() or rtree() or hash()
and functional or partial indexes) are not supported in MonetDB. skipping this
test
+create_view
+select_views
create_aggregate
#ToDo triggers
#ToDo inherit
@@ -86,7 +87,6 @@ arrays
#ToDo namespace
#ToDo privileges
#ToDo rules
-#ToDo select_views
#ToDo portals_p2 [MK] MonetDB does not support SQL cursor/fetch operations
#ToDo foreign_key
#cluster [MK]MonetDB does not support clustering of tables
diff --git a/sql/test/pg_regress/Tests/create_view.sql
b/sql/test/pg_regress/Tests/create_view.sql
--- a/sql/test/pg_regress/Tests/create_view.sql
+++ b/sql/test/pg_regress/Tests/create_view.sql
@@ -10,8 +10,9 @@ CREATE VIEW street AS
WHERE c.outline = r.thepath;
CREATE VIEW iexit AS
- SELECT ih.name, ih.thepath,
- interpt_pp(ih.thepath, r.thepath) AS exit
+ SELECT ih.name, ih.thepath /* ,
+ funtion interpt_pp() does not exist, so exclude it
+ interpt_pp(ih.thepath, r.thepath) AS exit */
FROM ihighway ih, ramp r
WHERE ih.thepath = r.thepath;
@@ -26,40 +27,65 @@ CREATE VIEW toyemp AS
--
-- CREATE OR REPLACE VIEW
+-- MonetDB does not (yet) support CREATE OR REPLACE VIEW, see
https://www.monetdb.org/bugzilla/show_bug.cgi?id=3574
+-- MonetDB does not (yet) support ORDER BY in CREATE VIEW, see
https://www.monetdb.org/bugzilla/show_bug.cgi?id=3465
--
CREATE TABLE viewtest_tbl (a int, b int);
+/*
COPY INTO viewtest_tbl FROM stdin;
5 10
10 15
15 20
20 25
\.
+*/
+INSERT INTO viewtest_tbl VALUES (5, 10), (10, 15), (15, 20), (20, 25);
-CREATE OR REPLACE VIEW viewtest AS
+CREATE /* OR REPLACE */ VIEW viewtest AS
SELECT * FROM viewtest_tbl;
CREATE OR REPLACE VIEW viewtest AS
SELECT * FROM viewtest_tbl WHERE a > 10;
+-- MonetDB does not (yet) support CREATE OR REPLACE. test with DROP VIEW and
CREATE VIEW instead
+DROP VIEW viewtest;
+CREATE VIEW viewtest AS
+ SELECT * FROM viewtest_tbl WHERE a > 10;
SELECT * FROM viewtest;
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+DROP VIEW viewtest;
+CREATE VIEW viewtest AS
+ SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+-- MonetDB does not (yet) support ORDER BY in CREATE VIEW,
+CREATE VIEW viewtest AS
+ SELECT a, b FROM viewtest_tbl WHERE a > 5;
+
SELECT * FROM viewtest;
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a FROM viewtest_tbl WHERE a <> 20;
+DROP VIEW viewtest;
+CREATE VIEW viewtest AS
+ SELECT a FROM viewtest_tbl WHERE a <> 20;
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT 1, * FROM viewtest_tbl;
+DROP VIEW viewtest;
+CREATE VIEW viewtest AS
+ SELECT 1, * FROM viewtest_tbl;
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a, cast(b as numeric) FROM viewtest_tbl;
+DROP VIEW viewtest;
+CREATE VIEW viewtest AS
+ SELECT a, cast(b as numeric) FROM viewtest_tbl;
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;
diff --git a/sql/test/pg_regress/Tests/create_view.stable.err
b/sql/test/pg_regress/Tests/create_view.stable.err
--- a/sql/test/pg_regress/Tests/create_view.stable.err
+++ b/sql/test/pg_regress/Tests/create_view.stable.err
@@ -29,30 +29,31 @@ stderr of test 'create_view` in director
# 17:11:19 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1142" "--port=38959"
# 17:11:19 >
-#--
-#-- CREATE_VIEW
-#-- Virtual class definitions
-#-- (this also tests the query rewrite system)
-#--
-#-- Test comments
-#--
-#-- CREATE OR REPLACE VIEW
-#--
-#-- should fail
-MAPI = (monetdb) /var/tmp/mtest-12345/.s.monetdb.54321
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
+QUERY = CREATE OR REPLACE VIEW viewtest AS
+ SELECT * FROM viewtest_tbl WHERE a > 10;
+ERROR = !syntax error, unexpected OR, expecting INDEX in: "create or"
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
+QUERY = CREATE OR REPLACE VIEW viewtest AS
+ SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+ERROR = !syntax error, unexpected OR, expecting INDEX in: "create or"
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
+QUERY = CREATE VIEW viewtest AS
+ SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+ERROR = !CREATE VIEW: ORDER BY not supported
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = CREATE OR REPLACE VIEW viewtest AS
SELECT a FROM viewtest_tbl WHERE a <> 20;
-ERROR = cannot change number of columns in view
-#-- should fail
-MAPI = (monetdb) /var/tmp/mtest-12345/.s.monetdb.54321
+ERROR = !syntax error, unexpected OR, expecting INDEX in: "create or"
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = CREATE OR REPLACE VIEW viewtest AS
SELECT 1, * FROM viewtest_tbl;
-ERROR = cannot change number of columns in view
-#-- should fail
-MAPI = (monetdb) /var/tmp/mtest-12345/.s.monetdb.54321
+ERROR = !syntax error, unexpected OR, expecting INDEX in: "create or"
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = CREATE OR REPLACE VIEW viewtest AS
SELECT a, cast(b as numeric) FROM viewtest_tbl;
-ERROR = cannot change data type of view column "b"
+ERROR = !syntax error, unexpected OR, expecting INDEX in: "create or"
+
# 17:11:19 >
# 17:11:19 > "Done."
diff --git a/sql/test/pg_regress/Tests/create_view.stable.out
b/sql/test/pg_regress/Tests/create_view.stable.out
--- a/sql/test/pg_regress/Tests/create_view.stable.out
+++ b/sql/test/pg_regress/Tests/create_view.stable.out
@@ -24,13 +24,6 @@ Ready.
# 17:11:19 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1142" "--port=38959"
# 17:11:19 >
-= ! Correct / expected output still needs to be provided / verified / approved
! =
-
-#--
-#-- CREATE_VIEW
-#-- Virtual class definitions
-#-- (this also tests the query rewrite system)
-#--
#CREATE VIEW street AS
# SELECT r.name, r.thepath, c.cname AS cname
# FROM road r, real_city c
@@ -48,41 +41,42 @@ Ready.
#-- CREATE OR REPLACE VIEW
#--
#CREATE TABLE viewtest_tbl (a int, b int);
-COPY viewtest_tbl FROM stdin;
-#CREATE OR REPLACE VIEW viewtest AS
+#INSERT INTO viewtest_tbl VALUES (5, 10), (10, 15), (15, 20), (20, 25);
+[ 4 ]
+#CREATE /* OR REPLACE */ VIEW viewtest AS
# SELECT * FROM viewtest_tbl;
#CREATE OR REPLACE VIEW viewtest AS
# SELECT * FROM viewtest_tbl WHERE a > 10;
#SELECT * FROM viewtest;
-% sys.L0, sys.L0 # table_name
+% sys.viewtest, sys.viewtest # table_name
% a, b # name
-% ?, ? # type
-% ?, ? # length
+% int, int # type
+% 2, 2 # length
[ 15, 20 ]
[ 20, 25 ]
#CREATE OR REPLACE VIEW viewtest AS
# SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
#SELECT * FROM viewtest;
-% sys.L0, sys.L0 # table_name
+% sys.viewtest, sys.viewtest # table_name
% a, b # name
-% ?, ? # type
-% ?, ? # length
-[ 20, 25 ]
-[ 15, 20 ]
+% int, int # type
+% 2, 2 # length
[ 10, 15 ]
-
-#-- should fail
-#
-#-- should fail
-#
-#-- should fail
-#
+[ 15, 20 ]
+[ 20, 25 ]
+#DROP VIEW viewtest;
+#CREATE VIEW viewtest AS
+# SELECT a FROM viewtest_tbl WHERE a <> 20;
+#DROP VIEW viewtest;
+#CREATE VIEW viewtest AS
+# SELECT 1, * FROM viewtest_tbl;
+#DROP VIEW viewtest;
+#CREATE VIEW viewtest AS
+# SELECT a, cast(b as numeric) FROM viewtest_tbl;
#DROP VIEW viewtest;
#DROP TABLE viewtest_tbl;
-= ! Correct / expected output still needs to be provided / verified / approved
! =
-
# 17:11:19 >
# 17:11:19 > "Done."
# 17:11:19 >
diff --git a/sql/test/pg_regress/Tests/drop.sql
b/sql/test/pg_regress/Tests/drop.sql
--- a/sql/test/pg_regress/Tests/drop.sql
+++ b/sql/test/pg_regress/Tests/drop.sql
@@ -140,6 +140,16 @@ DROP INDEX bt_txt_index;
DROP INDEX bt_f8_index;
+--
+-- VIRTUAL CLASS REMOVAL
+-- (also tests removal of rewrite rules)
+--
+DROP VIEW street;
+
+DROP VIEW iexit;
+
+DROP VIEW toyemp;
+
DROP TABLE onek;
@@ -234,14 +244,3 @@ DROP TABLE bt_f8_heap;
-- DROP TABLE RELTIME_TBL;
-- DROP TABLE TINTERVAL_TBL;
-
---
--- VIRTUAL CLASS REMOVAL
--- (also tests removal of rewrite rules)
---
-DROP VIEW street;
-
-DROP VIEW iexit;
-
-DROP VIEW toyemp;
-
diff --git a/sql/test/pg_regress/Tests/drop.stable.err
b/sql/test/pg_regress/Tests/drop.stable.err
--- a/sql/test/pg_regress/Tests/drop.stable.err
+++ b/sql/test/pg_regress/Tests/drop.stable.err
@@ -30,53 +30,53 @@ stderr of test 'drop` in directory 'sql/
# 16:35:18 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-6420" "--port=36808"
# 16:35:18 >
-MAPI = (monetdb) /var/tmp/mtest-29915/.s.monetdb.37487
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = UPDATE pg_user
SET usesuper = 't'::bool
WHERE usename = 'postgres';
ERROR = !unexpected symbol (:)
-MAPI = (monetdb) /var/tmp/mtest-29915/.s.monetdb.37487
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = DROP FUNCTION hobbies(person);
ERROR = !type (person) unknown in: "drop function hobbies(person)"
-MAPI = (monetdb) /var/tmp/mtest-29915/.s.monetdb.37487
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = DROP FUNCTION hobby_construct(text,text);
ERROR = !DROP FUNCTION: no such function 'hobby_construct' (clob, clob)
-MAPI = (monetdb) /var/tmp/mtest-29915/.s.monetdb.37487
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = DROP FUNCTION equipment(hobbies_r);
ERROR = !type (hobbies_r) unknown in: "drop function equipment(hobbies_r)"
-MAPI = (monetdb) /var/tmp/mtest-29915/.s.monetdb.37487
+MAPI = (monetdb) /var/tmp/mtest-11947/.s.monetdb.35880
QUERY = DROP FUNCTION user_relns();
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
