Changeset: 66f0f6fece7a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=66f0f6fece7a
Added Files:
sql/test/Tests/mediumint.sql
sql/test/Tests/mediumint.stable.err
sql/test/Tests/mediumint.stable.out
sql/test/sys-schema/Tests/utilities.sql
sql/test/sys-schema/Tests/utilities.stable.err
sql/test/sys-schema/Tests/utilities.stable.out
Modified Files:
sql/test/Tests/All
sql/test/sys-schema/Tests/All
Branch: linear-hashing
Log Message:
Merged with Nov2019
diffs (truncated from 307 to 300 lines):
diff --git a/sql/test/Tests/All b/sql/test/Tests/All
--- a/sql/test/Tests/All
+++ b/sql/test/Tests/All
@@ -48,6 +48,7 @@ cast_str2num
big-example
count
+mediumint
decimal
decimal2
null
diff --git a/sql/test/Tests/mediumint.sql b/sql/test/Tests/mediumint.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/mediumint.sql
@@ -0,0 +1,21 @@
+-- test support for MySQL/MariaDB specific data type: MEDIUMINT
+-- Note: in MySQL/MariaDB a MEDIUMINT is stored in 3 bytes and has a valid
range of: -8388608 .. 8388607
+
+-- in MonetDB mediumint is accepted, but mapped to an int
+create table meditbl(medi MEDIUMINT);
+
+\d meditbl
+-- note: that the data type is now changed into: INTEGER
+select name, type, type_digits, type_scale, number from sys.columns where name
= 'medi' and table_id in (select id from sys.tables where name = 'meditbl');
+
+-- it accepts all 32-bit signed integer values which are also possible in an
int data type
+INsert into meditbl values (0), (1), (-1), (32767), (-32767), (8388607),
(-8388607), (2147483647), (-2147483647), (NULL);
+
+-- check for overflows (same as on an int)
+INsert into meditbl values (2147483648); -- Error: overflow in
conversion of 2147483648 to int.
+INsert into meditbl values (-2147483648); -- Error: overflow in
conversion of -2147483648 to int.
+
+select * from meditbl order by 1;
+
+drop table meditbl;
+
diff --git a/sql/test/Tests/mediumint.stable.err
b/sql/test/Tests/mediumint.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/mediumint.stable.err
@@ -0,0 +1,20 @@
+stderr of test 'mediumint` in directory 'sql/test` itself:
+
+
+# 16:18:53 >
+# 16:18:53 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-192912" "--port=32869"
+# 16:18:53 >
+
+MAPI = (monetdb) /var/tmp/mtest-192912/.s.monetdb.32869
+QUERY = INsert into meditbl values (2147483648); -- Error: overflow in
conversion of 2147483648 to int.
+ERROR = !overflow in conversion of 2147483648 to int.
+CODE = 22003
+MAPI = (monetdb) /var/tmp/mtest-192912/.s.monetdb.32869
+QUERY = INsert into meditbl values (-2147483648); -- Error: overflow in
conversion of -2147483648 to int.
+ERROR = !overflow in conversion of -2147483648 to int.
+CODE = 22003
+
+# 16:18:53 >
+# 16:18:53 > "Done."
+# 16:18:53 >
+
diff --git a/sql/test/Tests/mediumint.stable.out
b/sql/test/Tests/mediumint.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/mediumint.stable.out
@@ -0,0 +1,40 @@
+stdout of test 'mediumint` in directory 'sql/test` itself:
+
+
+# 16:18:53 >
+# 16:18:53 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-192912" "--port=32869"
+# 16:18:53 >
+
+#create table meditbl(medi MEDIUMINT);
+CREATE TABLE "sys"."meditbl" (
+ "medi" INTEGER
+);
+#select name, type, type_digits, type_scale, number from sys.columns where
name = 'medi' and table_id in (select id from sys.tables where name =
'meditbl');
+% .columns, .columns, .columns, .columns, .columns #
table_name
+% name, type, type_digits, type_scale, number # name
+% varchar, varchar, int, int, int # type
+% 4, 3, 2, 1, 1 # length
+[ "medi", "int", 32, 0, 0 ]
+#INsert into meditbl values (0), (1), (-1), (32767), (-32767), (8388607),
(-8388607), (2147483647), (-2147483647), (NULL);
+[ 10 ]
+#select * from meditbl order by 1;
+% sys.meditbl # table_name
+% medi # name
+% int # type
+% 11 # length
+[ NULL ]
+[ -2147483647 ]
+[ -8388607 ]
+[ -32767 ]
+[ -1 ]
+[ 0 ]
+[ 1 ]
+[ 32767 ]
+[ 8388607 ]
+[ 2147483647 ]
+#drop table meditbl;
+
+# 16:18:53 >
+# 16:18:53 > "Done."
+# 16:18:53 >
+
diff --git a/sql/test/sys-schema/Tests/All b/sql/test/sys-schema/Tests/All
--- a/sql/test/sys-schema/Tests/All
+++ b/sql/test/sys-schema/Tests/All
@@ -11,3 +11,5 @@ HAVE_NETCDF?netcdf_tables_checks
HAVE_SAMTOOLS?bam_tables_checks
HAVE_FITS&HAVE_GEOM&HAVE_LIDAR&HAVE_SHP&HAVE_NETCDF&HAVE_SAMTOOLS?systemfunctions
+
+utilities
diff --git a/sql/test/sys-schema/Tests/utilities.sql
b/sql/test/sys-schema/Tests/utilities.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.sql
@@ -0,0 +1,48 @@
+-- utility functions on sys schema objects
+
+-- utility function to find the id of an existing schema name.
+-- It will return NULL if schema name does not exist.
+CREATE OR REPLACE function schema_id(sname varchar(1024)) RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.schemas where name = sname;
+END;
+
+SELECT schema_id('sys');
+SELECT schema_id('json') > 2000;
+SELECT schema_id('hsfdjkhksf does not exist');
+
+
+-- utility function to find the id of an existing table name in a specific
schema.
+-- It will return NULL if table name does not exist in specified schema or
schema name does not exist.
+CREATE OR REPLACE function table_id(sname varchar(1024), tname varchar(1024))
RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = sname);
+END;
+
+SELECT table_id('sys','tables') > 2000;
+SELECT table_id(current_schema,'columns') > 2000;
+SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('sys','tables');
+SELECT table_id('sys','hsfdjkhksf does not exist');
+
+-- utility function to find the id of an existing table name in the current
schema.
+-- It will return NULL if table name does not exist in the current schema.
+CREATE OR REPLACE function table_id(tname varchar(1024)) RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = current_schema);
+END;
+
+SELECT current_schema;
+SELECT table_id('tables') > 2000;
+SELECT table_id('columns') > 2000;
+SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('tables');
+SELECT table_id('hsfdjkhksf does not exist');
+
+
+
+\dftv
+-- cleanup utilities
+DROP ALL function table_id;
+DROP function schema_id;
+
+\dftv
+
diff --git a/sql/test/sys-schema/Tests/utilities.stable.err
b/sql/test/sys-schema/Tests/utilities.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.stable.err
@@ -0,0 +1,12 @@
+stderr of test 'utilities` in directory 'sql/test/sys-schema` itself:
+
+
+# 17:20:48 >
+# 17:20:48 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-251920" "--port=33727"
+# 17:20:48 >
+
+
+# 17:20:48 >
+# 17:20:48 > "Done."
+# 17:20:48 >
+
diff --git a/sql/test/sys-schema/Tests/utilities.stable.out
b/sql/test/sys-schema/Tests/utilities.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.stable.out
@@ -0,0 +1,116 @@
+stdout of test 'utilities` in directory 'sql/test/sys-schema` itself:
+
+
+# 17:33:45 >
+# 17:33:45 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-253132" "--port=35072"
+# 17:33:45 >
+
+#CREATE OR REPLACE function schema_id(sname varchar(1024)) RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.schemas where name = sname;
+#END;
+#SELECT schema_id('sys');
+% . # table_name
+% %1 # name
+% int # type
+% 4 # length
+[ 2000 ]
+#SELECT schema_id('json') > 2000;
+% . # table_name
+% %1 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT schema_id('hsfdjkhksf does not exist');
+% . # table_name
+% %1 # name
+% int # type
+% 1 # length
+[ NULL ]
+#CREATE OR REPLACE function table_id(sname varchar(1024), tname varchar(1024))
RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = sname);
+#END;
+#SELECT table_id('sys','tables') > 2000;
+% . # table_name
+% %1 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT table_id(current_schema,'columns') > 2000;
+% . # table_name
+% %1 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('sys','tables');
+% .columns, .columns, .columns, .columns, .columns,
.columns # table_name
+% name, type, type_digits, type_scale, null, number # name
+% varchar, varchar, int, int, boolean, int # type
+% 13, 8, 7, 1, 5, 1 # length
+[ "id", "int", 32, 0, true, 0 ]
+[ "name", "varchar", 1024, 0, true, 1 ]
+[ "schema_id", "int", 32, 0, true, 2 ]
+[ "query", "varchar", 1048576, 0, true, 3 ]
+[ "type", "smallint", 16, 0, true, 4 ]
+[ "system", "boolean", 1, 0, true, 5 ]
+[ "commit_action", "smallint", 16, 0, true, 6 ]
+[ "access", "smallint", 16, 0, true, 7 ]
+[ "temporary", "smallint", 16, 0, true, 8 ]
+#SELECT table_id('sys','hsfdjkhksf does not exist');
+% . # table_name
+% %1 # name
+% int # type
+% 1 # length
+[ NULL ]
+#CREATE OR REPLACE function table_id(tname varchar(1024)) RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = current_schema);
+#END;
+#SELECT current_schema;
+% . # table_name
+% single_value # name
+% varchar # type
+% 3 # length
+[ "sys" ]
+#SELECT table_id('tables') > 2000;
+% . # table_name
+% %1 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT table_id('columns') > 2000;
+% . # table_name
+% %1 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('tables');
+% .columns, .columns, .columns, .columns, .columns,
.columns # table_name
+% name, type, type_digits, type_scale, null, number # name
+% varchar, varchar, int, int, boolean, int # type
+% 13, 8, 7, 1, 5, 1 # length
+[ "id", "int", 32, 0, true, 0 ]
+[ "name", "varchar", 1024, 0, true, 1 ]
+[ "schema_id", "int", 32, 0, true, 2 ]
+[ "query", "varchar", 1048576, 0, true, 3 ]
+[ "type", "smallint", 16, 0, true, 4 ]
+[ "system", "boolean", 1, 0, true, 5 ]
+[ "commit_action", "smallint", 16, 0, true, 6 ]
+[ "access", "smallint", 16, 0, true, 7 ]
+[ "temporary", "smallint", 16, 0, true, 8 ]
+#SELECT table_id('hsfdjkhksf does not exist');
+% . # table_name
+% %1 # name
+% int # type
+% 1 # length
+[ NULL ]
+FUNCTION sys.schema_id
+FUNCTION sys.table_id
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
