Changeset: 3278eaa13859 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3278eaa13859
Modified Files:
sql/common/sql_types.c
sql/test/miscellaneous/Tests/simple_selects.sql
sql/test/miscellaneous/Tests/simple_selects.stable.err
sql/test/miscellaneous/Tests/simple_selects.stable.out
Branch: Nov2019
Log Message:
Disallow casting month intervals to dates, and second intervals to timestamps
because there is no backend code for it and other databases don't support it
diffs (165 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -110,8 +110,8 @@ static int convert_matrix[EC_MAX][EC_MAX
/* EC_BLOB */ { 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
/* EC_POS */ { 0, 0, 2, 1, 1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0 },
/* EC_NUM */ { 0, 0, 2, 1, 1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0 },
-/* EC_MONTH*/ { 0, 0, 0, 1, 1, 0, 1, 1, 1, 0, 1, 1, 0, 1, 0, 0, 0 },
-/* EC_SEC*/ { 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 1, 0, 1, 0, 0 },
+/* EC_MONTH*/ { 0, 0, 0, 1, 1, 0, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0 },
+/* EC_SEC*/ { 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0 },
/* EC_DEC */ { 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0 },
/* EC_FLT */ { 0, 0, 0, 1, 1, 0, 1, 1, 0, 3, 1, 1, 0, 0, 0, 0, 0 },
/* EC_TIME */ { 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0 },
diff --git a/sql/test/miscellaneous/Tests/simple_selects.sql
b/sql/test/miscellaneous/Tests/simple_selects.sql
--- a/sql/test/miscellaneous/Tests/simple_selects.sql
+++ b/sql/test/miscellaneous/Tests/simple_selects.sql
@@ -17,19 +17,29 @@ select 1 having true;
create table x (x interval second, y interval month);
insert into x values (1, 1);
-select cast(x as date) from x;
+select cast(x as date) from x; --error, cannot cast
select cast(x as time) from x;
-select cast(x as timestamp) from x;
-select cast(y as date) from x;
-select cast(y as time) from x;
-select cast(y as timestamp) from x;
+select cast(x as timestamp) from x; --error, cannot cast
+select cast(y as date) from x; --error, cannot cast
+select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
+select cast(y as timestamp) from x; --error, cannot cast
insert into x values (null, null);
select cast(x as date) from x; --error, cannot cast
select cast(x as time) from x;
-select cast(x as timestamp) from x;
-select cast(y as date) from x;
-select cast(y as time) from x;
-select cast(y as timestamp) from x;
+select cast(x as timestamp) from x; --error, cannot cast
+select cast(y as date) from x; --error, cannot cast
+select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
+select cast(y as timestamp) from x; --error, cannot cast
+drop table x;
+
+create table x (x time, y date, z timestamp);
+insert into x values (null, null, null);
+select cast(x as interval second) from x; --We throw error, but PostgreSQL
doesn't
+select cast(x as interval month) from x; --We throw error, but PostgreSQL
doesn't
+select cast(y as interval second) from x; --error, cannot cast
+select cast(y as interval month) from x; --error, cannot cast
+select cast(z as interval second) from x; --error, cannot cast
+select cast(z as interval month) from x; --error, cannot cast
drop table x;
select "idontexist"."idontexist"(); --error, it doesn't exist
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.err
b/sql/test/miscellaneous/Tests/simple_selects.stable.err
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.err
+++ b/sql/test/miscellaneous/Tests/simple_selects.stable.err
@@ -5,7 +5,71 @@ stderr of test 'simple_selects` in direc
# 17:31:37 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=31760" "--set"
"mapi_usock=/var/tmp/mtest-27253/.s.monetdb.31760" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-Aug2018/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "embedded_c=true"
# 17:31:37 >
-MAPI = (monetdb) /var/tmp/mtest-6308/.s.monetdb.33199
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as date) from x; --error, cannot cast
+ERROR = !types sec_interval(13,0) and date(0,0) are not equal for column 'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as timestamp) from x; --error, cannot cast
+ERROR = !types sec_interval(13,0) and timestamp(7,0) are not equal for column
'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as date) from x; --error, cannot cast
+ERROR = !types month_interval(3,0) and date(0,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
+ERROR = !types month_interval(3,0) and time(1,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as timestamp) from x; --error, cannot cast
+ERROR = !types month_interval(3,0) and timestamp(7,0) are not equal for column
'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as date) from x; --error, cannot cast
+ERROR = !types sec_interval(13,0) and date(0,0) are not equal for column 'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as timestamp) from x; --error, cannot cast
+ERROR = !types sec_interval(13,0) and timestamp(7,0) are not equal for column
'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as date) from x; --error, cannot cast
+ERROR = !types month_interval(3,0) and date(0,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
+ERROR = !types month_interval(3,0) and time(1,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as timestamp) from x; --error, cannot cast
+ERROR = !types month_interval(3,0) and timestamp(7,0) are not equal for column
'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as interval second) from x; --We throw error, but
PostgreSQL doesn't
+ERROR = !types time(1,0) and sec_interval(13,0) are not equal for column 'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(x as interval month) from x; --We throw error, but
PostgreSQL doesn't
+ERROR = !types time(1,0) and month_interval(3,0) are not equal for column 'x'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as interval second) from x; --error, cannot cast
+ERROR = !types date(0,0) and sec_interval(13,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(y as interval month) from x; --error, cannot cast
+ERROR = !types date(0,0) and month_interval(3,0) are not equal for column 'y'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(z as interval second) from x; --error, cannot cast
+ERROR = !types timestamp(7,0) and sec_interval(13,0) are not equal for column
'z'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
+QUERY = select cast(z as interval month) from x; --error, cannot cast
+ERROR = !types timestamp(7,0) and month_interval(3,0) are not equal for column
'z'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-389745/.s.monetdb.32344
QUERY = select "idontexist"."idontexist"(); --error, it doesn't exist
ERROR = !SELECT: no such schema 'idontexist'
CODE = 3F000
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.out
b/sql/test/miscellaneous/Tests/simple_selects.stable.out
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_selects.stable.out
@@ -143,6 +143,29 @@ stdout of test 'simple_selects` in direc
% tinyint # type
% 1 # length
[ 1 ]
+#create table x (x interval second, y interval month);
+#insert into x values (1, 1);
+[ 1 ]
+#select cast(x as time) from x;
+% sys.L1 # table_name
+% L1 # name
+% time # type
+% 8 # length
+[ 00:00:01 ]
+#insert into x values (null, null);
+[ 1 ]
+#select cast(x as time) from x;
+% sys.L1 # table_name
+% L1 # name
+% time # type
+% 8 # length
+[ 00:00:01 ]
+[ NULL ]
+#drop table x;
+#create table x (x time, y date, z timestamp);
+#insert into x values (null, null, null);
+[ 1 ]
+#drop table x;
#select substring('abc' from 1 for null);
% .L2 # table_name
% L2 # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
