Changeset: 76af7753bd2e for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=76af7753bd2e Added Files: sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.sql sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.err sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.out sql/test/BugTracker-2016/Tests/convert-function-test.Bug-3460.sql sql/test/BugTracker-2016/Tests/convert-function-test.Bug-3460.stable.err sql/test/BugTracker-2016/Tests/convert-function-test.Bug-3460.stable.err.int128 sql/test/BugTracker-2016/Tests/convert-function-test.Bug-3460.stable.out sql/test/BugTracker-2016/Tests/convert-function-test.Bug-3460.stable.out.int128 Modified Files: sql/test/BugTracker-2016/Tests/All Branch: Jun2016 Log Message:
Test script to test scalar functions convert(fromType, toType) and cast(fromType as toType) for most MonetDB SQL data types and data values See also Bug 3460. diffs (truncated from 22989 to 300 lines): diff --git a/sql/test/BugTracker-2016/Tests/All b/sql/test/BugTracker-2016/Tests/All --- a/sql/test/BugTracker-2016/Tests/All +++ b/sql/test/BugTracker-2016/Tests/All @@ -1,3 +1,5 @@ +convert-function-test.Bug-3460 +HAVE_HGE?convert-function-test-hge.Bug-3460 HAVE_GEOM?storagemodel LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908 incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909 diff --git a/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.sql b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.sql @@ -0,0 +1,97 @@ +-- test SQL functions: convert(fromType, toType) and cast(fromType as toType) for all SQL data types and data values +-- See also https://www.monetdb.org/bugzilla/show_bug.cgi?id=3460 + +-- HUGEINT (for int128 only) +CREATE TABLE T_hugeint (v hugeint); +INSERT into T_hugeint VALUES (1), (0), (-1), (-127), (127), (-32767), (32767), (-2147483647), (2147483647); +INSERT into T_hugeint VALUES (null); +SELECT v FROM T_hugeint ORDER BY v; + +-- test convert() +SELECT v, convert(v, boolean) from T_hugeint; +SELECT v, convert(v, bit) from T_hugeint; -- BIT not valid data type +SELECT v, convert(v, tinyint) from T_hugeint where v between -127 and 127; +SELECT v, convert(v, smallint) from T_hugeint where v between -32767 and 32767; +SELECT v, convert(v, integer) from T_hugeint; +SELECT v, convert(v, bigint) from T_hugeint; +SELECT v, convert(v, hugeint) from T_hugeint; + +SELECT v, convert(v, float) from T_hugeint; +SELECT v, convert(v, float(24)) from T_hugeint; +SELECT v, convert(v, real) from T_hugeint; +SELECT v, convert(v, double) from T_hugeint; +SELECT v, convert(v, double precision) from T_hugeint; + +SELECT v, convert(v, numeric) from T_hugeint; +SELECT v, convert(v, decimal) from T_hugeint; +SELECT v, convert(v, numeric(10)) from T_hugeint; +SELECT v, convert(v, decimal(11)) from T_hugeint; +SELECT v, convert(v, numeric(12,0)) from T_hugeint; +SELECT v, convert(v, decimal(15,3)) from T_hugeint; + +SELECT v, convert(v, char) from T_hugeint where v between 0 and 1; +SELECT v, convert(v, varchar) from T_hugeint; -- missing length specification +SELECT v, convert(v, varchar(16)) from T_hugeint; +SELECT v, convert(v, longvarchar) from T_hugeint; -- LONGVARCHAR not valid data type +SELECT v, convert(v, long varchar) from T_hugeint; -- LONG VARCHAR not valid data type +SELECT v, convert(v, CHARACTER LARGE OBJECT) from T_hugeint; +SELECT v, convert(v, Clob) from T_hugeint; + +SELECT v, convert(v, Binary) from T_hugeint; -- BINARY not valid data type +SELECT v, convert(v, varBinary) from T_hugeint; -- VARBINARY not valid data type +SELECT v, convert(v, longvarBinary) from T_hugeint; -- LONGVARBINARY not valid data type +SELECT v, convert(v, Blob) from T_hugeint; -- conversion not supported + +SELECT v, convert(v, date) from T_hugeint; -- conversion not supported +SELECT v, convert(v, time) from T_hugeint; -- conversion not supported +SELECT v, convert(v, timestamp) from T_hugeint; -- conversion not supported +SELECT v, convert(v, time with timezone) from T_hugeint; -- data type not supported (parse error) +SELECT v, convert(v, timestamp with timezone) from T_hugeint; -- data type not supported (parse error) +SELECT v, convert(v, timetz) from T_hugeint; -- conversion not supported +SELECT v, convert(v, timestamptz) from T_hugeint; -- conversion not supported + +-- test cast() +SELECT v, cast(v as boolean) from T_hugeint; +SELECT v, cast(v as bit) from T_hugeint; -- BIT not valid data type +SELECT v, cast(v as tinyint) from T_hugeint where v between -127 and 127; +SELECT v, cast(v as smallint) from T_hugeint where v between -32767 and 32767; +SELECT v, cast(v as integer) from T_hugeint; +SELECT v, cast(v as bigint) from T_hugeint; +SELECT v, cast(v as hugeint) from T_hugeint; + +SELECT v, cast(v as float) from T_hugeint; +SELECT v, cast(v as float(24)) from T_hugeint; +SELECT v, cast(v as real) from T_hugeint; +SELECT v, cast(v as double) from T_hugeint; +SELECT v, cast(v as double precision) from T_hugeint; + +SELECT v, cast(v as numeric) from T_hugeint; +SELECT v, cast(v as decimal) from T_hugeint; +SELECT v, cast(v as numeric(10)) from T_hugeint; +SELECT v, cast(v as decimal(11)) from T_hugeint; +SELECT v, cast(v as numeric(12,0)) from T_hugeint; +SELECT v, cast(v as decimal(15,3)) from T_hugeint; + +SELECT v, cast(v as char) from T_hugeint where v between 0 and 1; +SELECT v, cast(v as varchar) from T_hugeint; -- missing length specification +SELECT v, cast(v as varchar(16)) from T_hugeint; +SELECT v, cast(v as longvarchar) from T_hugeint; -- LONGVARCHAR not valid data type +SELECT v, cast(v as long varchar) from T_hugeint; -- LONG VARCHAR not valid data type +SELECT v, cast(v as CHARACTER LARGE OBJECT) from T_hugeint; +SELECT v, cast(v as Clob) from T_hugeint; + +SELECT v, cast(v as Binary) from T_hugeint; -- BINARY not valid data type +SELECT v, cast(v as varBinary) from T_hugeint; -- VARBINARY not valid data type +SELECT v, cast(v as longvarBinary) from T_hugeint; -- LONGVARBINARY not valid data type +SELECT v, cast(v as Blob) from T_hugeint; -- conversion not supported + +SELECT v, cast(v as date) from T_hugeint; -- conversion not supported +SELECT v, cast(v as time) from T_hugeint; -- conversion not supported +SELECT v, cast(v as timestamp) from T_hugeint; -- conversion not supported +SELECT v, cast(v as time with timezone) from T_hugeint; -- data type not supported (parse error) +SELECT v, cast(v as timestamp with timezone) from T_hugeint; -- data type not supported (parse error) +SELECT v, cast(v as timetz) from T_hugeint; -- conversion not supported +SELECT v, cast(v as timestamptz) from T_hugeint; -- conversion not supported + +DROP TABLE T_hugeint; + diff --git a/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.err b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.err @@ -0,0 +1,137 @@ +stderr of test 'convert-function-test-hge.Bug-3460` in directory 'sql/test/BugTracker-2016` itself: + + +# 19:15:21 > +# 19:15:21 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=36439" "--set" "mapi_usock=/var/tmp/mtest-11655/.s.monetdb.36439" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2016" "--set" "embedded_r=yes" +# 19:15:21 > + +# builtin opt gdk_dbpath = /export/scratch2/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 36439 +# cmdline opt mapi_usock = /var/tmp/mtest-11655/.s.monetdb.36439 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2016 +# cmdline opt embedded_r = yes +# cmdline opt gdk_debug = 536870922 + +# 19:15:22 > +# 19:15:22 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-11655" "--port=36439" +# 19:15:22 > + +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, bit) from T_hugeint; -- BIT not valid data type +ERROR = !type (bit) unknown in: "select v, convert(v, bit)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, varchar) from T_hugeint; -- missing length specification +ERROR = !CHARACTER VARYING needs a mandatory length specification in: "select v, convert(v, varchar)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, longvarchar) from T_hugeint; -- LONGVARCHAR not valid data type +ERROR = !type (longvarchar) unknown in: "select v, convert(v, longvarchar)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, long varchar) from T_hugeint; -- LONG VARCHAR not valid data type +ERROR = !type (long) unknown in: "select v, convert(v, long varchar" + !syntax error, unexpected ')' in: ")" +MAPI = (monetdb) /var/tmp/mtest-10564/.s.monetdb.32160 +QUERY = SELECT v, convert(v, Binary) from T_hugeint; -- BINARY not valid data type +ERROR = !syntax error, unexpected ')', expecting LARGE in: "select v, convert(v, binary)" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, varBinary) from T_hugeint; -- VARBINARY not valid data type +ERROR = !type (varbinary) unknown in: "select v, convert(v, varbinary)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, longvarBinary) from T_hugeint; -- LONGVARBINARY not valid data type +ERROR = !type (longvarbinary) unknown in: "select v, convert(v, longvarbinary)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, Blob) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and blob(0,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, date) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and date(0,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, time) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and time(1,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, timestamp) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timestamp(7,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, time with timezone) from T_hugeint; -- data type not supported (parse error) +ERROR = !syntax error, unexpected IDENT, expecting TIME in: "select v, convert(v, time with timezone" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, timestamp with timezone) from T_hugeint; -- data type not supported (parse error) +ERROR = !syntax error, unexpected IDENT, expecting TIME in: "select v, convert(v, timestamp with timezone" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, timetz) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timetz(7,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, convert(v, timestamptz) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timestamptz(7,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as bit) from T_hugeint; -- BIT not valid data type +ERROR = !type (bit) unknown in: "select v, cast(v as bit)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as varchar) from T_hugeint; -- missing length specification +ERROR = !CHARACTER VARYING needs a mandatory length specification in: "select v, cast(v as varchar)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as longvarchar) from T_hugeint; -- LONGVARCHAR not valid data type +ERROR = !type (longvarchar) unknown in: "select v, cast(v as longvarchar)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as long varchar) from T_hugeint; -- LONG VARCHAR not valid data type +ERROR = !type (long) unknown in: "select v, cast(v as long varchar" + !syntax error, unexpected ')' in: ")" +MAPI = (monetdb) /var/tmp/mtest-10564/.s.monetdb.32160 +QUERY = SELECT v, cast(v as Binary) from T_hugeint; -- BINARY not valid data type +ERROR = !syntax error, unexpected ')', expecting LARGE in: "select v, cast(v as binary)" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as varBinary) from T_hugeint; -- VARBINARY not valid data type +ERROR = !type (varbinary) unknown in: "select v, cast(v as varbinary)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as longvarBinary) from T_hugeint; -- LONGVARBINARY not valid data type +ERROR = !type (longvarbinary) unknown in: "select v, cast(v as longvarbinary)" + !syntax error, unexpected FROM in: "from" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as Blob) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and blob(0,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as date) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and date(0,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as time) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and time(1,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as timestamp) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timestamp(7,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as time with timezone) from T_hugeint; -- data type not supported (parse error) +ERROR = !syntax error, unexpected IDENT, expecting TIME in: "select v, cast(v as time with timezone" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as timestamp with timezone) from T_hugeint; -- data type not supported (parse error) +ERROR = !syntax error, unexpected IDENT, expecting TIME in: "select v, cast(v as timestamp with timezone" +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as timetz) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timetz(7,0) are not equal for column 'v' +MAPI = (monetdb) /var/tmp/mtest-11655/.s.monetdb.36439 +QUERY = SELECT v, cast(v as timestamptz) from T_hugeint; -- conversion not supported +ERROR = !types hugeint(128,0) and timestamptz(7,0) are not equal for column 'v' + +# 19:15:22 > +# 19:15:22 > "Done." +# 19:15:22 > + diff --git a/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.out b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2016/Tests/convert-function-test-hge.Bug-3460.stable.out @@ -0,0 +1,690 @@ +stdout of test 'convert-function-test-hge.Bug-3460` in directory 'sql/test/BugTracker-2016` itself: + + +# 19:15:21 > +# 19:15:21 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=36439" "--set" "mapi_usock=/var/tmp/mtest-11655/.s.monetdb.36439" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2016" "--set" "embedded_r=yes" +# 19:15:21 > + +# MonetDB 5 server v11.23.0 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2016', using 8 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked +# Found 15.589 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://toulouse.da.cwi.nl:36439/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-11655/.s.monetdb.36439 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded +# MonetDB/R module loaded + +Ready. +# SQL catalog created, loading sql scripts once +# loading sql script: 09_like.sql +# loading sql script: 10_math.sql +# loading sql script: 11_times.sql +# loading sql script: 12_url.sql +# loading sql script: 13_date.sql +# loading sql script: 14_inet.sql +# loading sql script: 15_querylog.sql +# loading sql script: 16_tracelog.sql +# loading sql script: 17_temporal.sql +# loading sql script: 20_vacuum.sql +# loading sql script: 21_dependency_functions.sql +# loading sql script: 22_clients.sql +# loading sql script: 23_skyserver.sql +# loading sql script: 24_zorder.sql +# loading sql script: 25_debug.sql +# loading sql script: 26_sysmon.sql +# loading sql script: 27_rejects.sql +# loading sql script: 39_analytics.sql +# loading sql script: 39_analytics_hge.sql _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
