Changeset: 5c8f8c6a1aa1 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5c8f8c6a1aa1
Modified Files:
sql/test/pg_regress/Tests/All
sql/test/pg_regress/Tests/interval.sql
sql/test/pg_regress/Tests/interval.stable.err
sql/test/pg_regress/Tests/interval.stable.out
sql/test/pg_regress/Tests/reltime.sql
sql/test/pg_regress/Tests/reltime.stable.err
sql/test/pg_regress/Tests/reltime.stable.out
sql/test/pg_regress/postgresql2sql99.sh
Branch: default
Log Message:
Adpated interval.sql and reltime.sql
to comply with MonetDB SQL compatible syntax
Added substitution rules to postgresql2sql99.sh
Disabled test for tinterval.sql
diffs (truncated from 950 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
@@ -41,7 +41,7 @@ timestamptz
interval
# abstime NOT supported ABSTIME, deprecated in Postgresql
reltime
-tinterval
+# tinterval NOT supported in MonetDB, deprecated in Postgresql and is also not
standard SQL so exclude this test
inet
comments
geometry
diff --git a/sql/test/pg_regress/Tests/interval.sql
b/sql/test/pg_regress/Tests/interval.sql
--- a/sql/test/pg_regress/Tests/interval.sql
+++ b/sql/test/pg_regress/Tests/interval.sql
@@ -2,18 +2,18 @@
-- INTERVAL
--
-SET DATESTYLE = 'ISO';
+/* SET DATESTYLE = 'ISO'; */
-- check acceptance of "time zone style"
-SELECT INTERVAL '01:00' AS "One hour";
-SELECT INTERVAL '+02:00' AS "Two hours";
-SELECT INTERVAL '-08:00' AS "Eight hours";
-SELECT INTERVAL '-05' AS "Five hours";
-SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
-SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
-SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+SELECT cast('01:00' as interval second) AS "One hour";
+SELECT cast('+02:00' as interval second) AS "Two hours";
+SELECT cast('-08:00' as interval second) AS "Eight hours";
+SELECT cast('-05' as interval second) AS "Five hours";
+SELECT cast('-1 +02:03' as interval second) AS "22 hours ago...";
+SELECT cast('-1 days +02:03' as interval second) AS "22 hours ago...";
+SELECT cast('10 years -11 month -12 days +13:14' as interval second) AS "9
years...";
-CREATE TABLE INTERVAL_TBL (f1 interval);
+CREATE TABLE INTERVAL_TBL (f1 interval second);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -32,37 +32,53 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('@
-- test interval operators
-SELECT '' AS ten, INTERVAL_TBL.*;
+SELECT '' AS ten, INTERVAL_TBL.* FROM INTERVAL_TBL;
-SELECT '' AS nine, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
+SELECT '' AS nine, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 <> cast('@ 10 days' as interval second);
-SELECT '' AS three, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
+SELECT '' AS three, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 <= cast('@ 5 hours' as interval second);
-SELECT '' AS three, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
+SELECT '' AS three, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 < cast('@ 1 day' as interval second);
-SELECT '' AS one, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
+SELECT '' AS one, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 = cast('@ 34 years' as interval second);
-SELECT '' AS five, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
+SELECT '' AS five, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 >= cast('@ 1 month' as interval second);
-SELECT '' AS nine, INTERVAL_TBL.*
- WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
+SELECT '' AS nine, INTERVAL_TBL.* FROM INTERVAL_TBL
+ WHERE INTERVAL_TBL.f1 > cast('@ 3 seconds ago' as interval second);
SELECT '' AS fortyfive, r1.*, r2.*
FROM INTERVAL_TBL r1, INTERVAL_TBL r2
WHERE r1.f1 > r2.f1
ORDER BY r1.f1, r2.f1;
-SET DATESTYLE = 'postgres';
+/* SET DATESTYLE = 'postgres'; */
-SELECT '' AS ten, INTERVAL_TBL.*;
+SELECT '' AS ten, INTERVAL_TBL.* FROM INTERVAL_TBL order by f1 desc;
-- test avg(interval), which is somewhat fragile since people have been
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
select avg(f1) from interval_tbl;
+
+select avg(cast(f1 as double)) from interval_tbl;
+select avg(cast(f1 as decimal)) from interval_tbl;
+select avg(cast(f1 as decimal(9,3))) from interval_tbl;
+select avg(cast(f1 as integer)) from interval_tbl;
+
+select min(f1) from interval_tbl;
+select max(f1) from interval_tbl;
+
+select sum(f1) from interval_tbl;
+select sum(cast(f1 as integer)) from interval_tbl;
+
+select count(f1) from interval_tbl;
+select count(distinct f1) from interval_tbl;
+
+DROP TABLE INTERVAL_TBL;
diff --git a/sql/test/pg_regress/Tests/interval.stable.err
b/sql/test/pg_regress/Tests/interval.stable.err
--- a/sql/test/pg_regress/Tests/interval.stable.err
+++ b/sql/test/pg_regress/Tests/interval.stable.err
@@ -25,25 +25,12 @@ stderr of test 'interval` in directory '
# cmdline opt mal_listing = 0
# cmdline opt gdk_debug = 536870922
-# 17:11:19 >
-# 17:11:19 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1142" "--port=38959"
-# 17:11:19 >
+# 14:52:52 >
+# 14:52:52 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8439" "--port=31752"
+# 14:52:52 >
-#--
-#-- INTERVAL
-#--
-#-- check acceptance of "time zone style"
-#-- badly formatted interval
-MAPI = (monetdb) /var/tmp/mtest-12345/.s.monetdb.54321
-QUERY = INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
-ERROR = invalid input syntax for type interval: "badly formatted interval"
-MAPI = (monetdb) /var/tmp/mtest-12345/.s.monetdb.54321
-QUERY = INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
-ERROR = invalid input syntax for type interval: "@ 30 eons ago"
-#-- test interval operators
-#-- test avg(interval), which is somewhat fragile since people have been
-#-- known to change the allowed input syntax for type interval without
-#-- updating pg_aggregate.agginitval
+
+
# 17:11:19 >
# 17:11:19 > "Done."
diff --git a/sql/test/pg_regress/Tests/interval.stable.out
b/sql/test/pg_regress/Tests/interval.stable.out
--- a/sql/test/pg_regress/Tests/interval.stable.out
+++ b/sql/test/pg_regress/Tests/interval.stable.out
@@ -20,67 +20,49 @@ stdout of test 'interval` in directory '
Ready.
-# 17:11:19 >
-# 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
! =
-
-#--
-#-- INTERVAL
-#--
-SET DATESTYLE = 'ISO';
-#-- check acceptance of "time zone style"
-#SELECT INTERVAL '01:00' AS "One hour";
-% sys.L0 # table_name
+#SELECT cast('01:00' as interval second) AS "One hour";
+% .L # table_name
% One hour # name
-% ? # type
-% ? # length
-[ "01:00:00" ]
-
-#SELECT INTERVAL '+02:00' AS "Two hours";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ 1.000 ]
+#SELECT cast('+02:00' as interval second) AS "Two hours";
+% .L # table_name
% Two hours # name
-% ? # type
-% ? # length
-[ "02:00:00" ]
-
-#SELECT INTERVAL '-08:00' AS "Eight hours";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ 2.000 ]
+#SELECT cast('-08:00' as interval second) AS "Eight hours";
+% .L # table_name
% Eight hours # name
-% ? # type
-% ? # length
-[ "-08:00:00" ]
-
-#SELECT INTERVAL '-05' AS "Five hours";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ -8.000 ]
+#SELECT cast('-05' as interval second) AS "Five hours";
+% .L # table_name
% Five hours # name
-% ? # type
-% ? # length
-[ "-05:00:00" ]
-
-#SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ -5.000 ]
+#SELECT cast('-1 +02:03' as interval second) AS "22 hours ago...";
+% .L # table_name
% 22 hours ago... # name
-% ? # type
-% ? # length
-[ "-21:57:00" ]
-
-#SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ -1.000 ]
+#SELECT cast('-1 days +02:03' as interval second) AS "22 hours ago...";
+% .L # table_name
% 22 hours ago... # name
-% ? # type
-% ? # length
-[ "-21:57:00" ]
-
-#SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
-% sys.L0 # table_name
+% sec_interval # type
+% 0 # length
+[ -1.000 ]
+#SELECT cast('10 years -11 month -12 days +13:14' as interval second) AS "9
years...";
+% .L # table_name
% 9 years... # name
-% ? # type
-% ? # length
-[ "9 years 1 mon -11 days -10:46:00" ]
-
-#CREATE TABLE INTERVAL_TBL (f1 interval);
+% sec_interval # type
+% 0 # length
+[ 10.000 ]
+#CREATE TABLE INTERVAL_TBL (f1 interval second);
#INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
[ 1 ]
#INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -101,183 +83,230 @@ SET DATESTYLE = 'ISO';
[ 1 ]
#INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
[ 1 ]
-#-- badly formatted interval
-#
-#
-#-- test interval operators
-#SELECT '' AS ten, INTERVAL_TBL.*;
-% sys.L0, sys.L0 # table_name
+#INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
+[ 1 ]
+#INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+[ 1 ]
+#SELECT '' AS ten, INTERVAL_TBL.* FROM INTERVAL_TBL;
+% .L, sys.interval_tbl # table_name
% ten, f1 # name
-% ?, ? # type
-% ?, ? # length
-[ "", "00:01:00" ]
-[ "", "05:00:00" ]
-[ "", "10 days" ]
-[ "", "34 years" ]
-[ "", "3 mons" ]
-[ "", "-00:00:14" ]
-[ "", "1 day 02:03:04" ]
-[ "", "6 years" ]
-[ "", "5 mons" ]
-[ "", "5 mons 12:00:00" ]
-
-#SELECT '' AS nine, INTERVAL_TBL.*
-# WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
-% sys.L0, sys.L0 # table_name
+% char, sec_interval # type
+% 0, 0 # length
+[ "", 0.000 ]
+[ "", 0.000 ]
+[ "", 0.000 ]
+[ "", 0.000 ]
+[ "", 0.000 ]
+[ "", 0.000 ]
+[ "", 1.000 ]
+[ "", 6.000 ]
+[ "", 5.000 ]
+[ "", 5.000 ]
+[ "", 0.000 ]
+[ "", 0.000 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
