Changeset: dbe6130a56c9 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dbe6130a56c9
Added Files:
sql/test/orderidx/Tests/oidx_hge_type.sql
sql/test/orderidx/Tests/oidx_hge_type.stable.err
sql/test/orderidx/Tests/oidx_hge_type.stable.out.int128
Modified Files:
sql/test/orderidx/Tests/All
Branch: Dec2016
Log Message:
Add test to create ordered index on hugeint and large decimal/numeric data types
diffs (truncated from 408 to 300 lines):
diff --git a/sql/test/orderidx/Tests/All b/sql/test/orderidx/Tests/All
--- a/sql/test/orderidx/Tests/All
+++ b/sql/test/orderidx/Tests/All
@@ -1,3 +1,4 @@
simpletable
smalltable
oidx_all_types
+HAVE_HGE?oidx_hge_type
diff --git a/sql/test/orderidx/Tests/oidx_hge_type.sql
b/sql/test/orderidx/Tests/oidx_hge_type.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/orderidx/Tests/oidx_hge_type.sql
@@ -0,0 +1,78 @@
+create table hge_types (
+ "hugeint" hugeint,
+ "decimal" decimal, -- is synonym for decimal(18,3)
+ "decimal38" decimal(38),
+ "decimal37_22" decimal(37,22),
+ "numeric" numeric, -- is synonym for decimal(18,3)
+ "numeric38" numeric(38, 0), -- is synonym for decimal(38)
+ "numeric37_9" numeric(37, 9) -- is synonym for decimal(37,9)
+);
+
+select number, name, type, type_digits, type_scale, "null", "default" from
sys._columns where table_id in (select id from sys._tables where name =
'hge_types') order by number;
+
+select * from hge_types;
+
+insert into hge_types ("hugeint") values (12345678900987654321);
+insert into hge_types ("decimal") values (123456789012345.678);
+insert into hge_types ("numeric") values (123456789012345.678);
+insert into hge_types ("decimal38") values (12345678900987654321);
+insert into hge_types ("numeric38") values (12345678900987654321);
+insert into hge_types ("decimal37_22") values
(123456789012345.1234567890123456789012);
+insert into hge_types ("numeric37_9") values
(1234567890123456789012345.123456789);
+
+insert into hge_types values (123456789012345, 123456789012345,
123456789012345, 123456789012345, 123456789012345, 123456789012345,
123456789012345);
+insert into hge_types values (123456789012345, 123456789012345,
123456789012345, 123456789012345, 123456789012345, 123456789012345,
123456789012345);
+
+insert into hge_types select -"hugeint", -"decimal", -"decimal38",
-"decimal37_22", -"numeric", -"numeric38", -"numeric37_9" from hge_types;
+
+select * from hge_types order by 1,2,3,4,5,6,7;
+
+
+-- select 'create ordered index "hge_oidx_'||name||'" on hge_types
("'||name||'");' as stmt from sys._columns where table_id in (select id from
sys._tables where name = 'hge_types') order by number;
+create ordered index "hge_oidx_hugeint" on hge_types ("hugeint");
+create ordered index "hge_oidx_decimal" on hge_types ("decimal");
+create ordered index "hge_oidx_decimal38" on hge_types ("decimal38");
+create ordered index "hge_oidx_decimal37_22" on hge_types ("decimal37_22");
+create ordered index "hge_oidx_numeric" on hge_types ("numeric");
+create ordered index "hge_oidx_numeric38" on hge_types ("numeric38");
+create ordered index "hge_oidx_numeric37_9" on hge_types ("numeric37_9");
+
+select type, name from sys.idxs where table_id in (select id from sys._tables
where name = 'hge_types') order by name;
+
+select * from hge_types
+ where "hugeint" between 1 and 12345678900987654322
+ or "decimal" between 2 and 123456789012345.679
+ or "numeric" between 2 and 123456789012345.679
+ or "decimal38" between 3 and 123456789009876543212
+ or "numeric38" between 3 and 123456789009876543212
+ or "decimal37_22" between 4 and 123456789012345.1234567890123456789013
+ or "numeric37_9" between 4 and 1234567890123456789012345.123456790
+ order by 1,2,3,4,5,6,7;
+
+select * from hge_types
+ where "hugeint" >= -12345678900987654322
+ or "decimal" >= -123456789012345.679
+ or "numeric" >= -123456789012345.679
+ or "decimal38" >= -123456789009876543212
+ or "numeric38" >= -123456789009876543212
+ or "decimal37_22" >= -123456789012345.1234567890123456789013
+ or "numeric37_9" >= -1234567890123456789012345.123456790
+ order by 7 desc, 6 desc, 5 desc, 4 desc, 3 desc, 2 desc, 1 desc;
+
+
+insert into hge_types select -"hugeint" + 123, -"decimal" + 123, -"decimal38"
+ 123, -"decimal37_22" + 123, -"numeric" + 123, -"numeric38" + 123,
-"numeric37_9" + 123 from hge_types;
+
+select * from hge_types
+ where "hugeint" >= -12345678900987654322
+ or "decimal" >= -123456789012345.679
+ or "numeric" >= -123456789012345.679
+ or "decimal38" >= -123456789009876543212
+ or "numeric38" >= -123456789009876543212
+ or "decimal37_22" >= -123456789012345.1234567890123456789013
+ or "numeric37_9" >= -1234567890123456789012345.123456790
+ order by 7 desc, 6 desc, 5 desc, 4 desc, 3 desc, 2 desc, 1 desc;
+
+drop table hge_types;
+
+select type, name from sys.idxs where table_id in (select id from sys._tables
where name = 'hge_types') order by name;
+
diff --git a/sql/test/orderidx/Tests/oidx_hge_type.stable.err
b/sql/test/orderidx/Tests/oidx_hge_type.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/orderidx/Tests/oidx_hge_type.stable.err
@@ -0,0 +1,36 @@
+stderr of test 'oidx_hge_type` in directory 'sql/test/orderidx` itself:
+
+
+# 16:56:56 >
+# 16:56:56 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36531" "--set"
"mapi_usock=/var/tmp/mtest-28625/.s.monetdb.36531" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_orderidx"
"--set" "embedded_r=yes" "--set" "embedded_py=true"
+# 16:56:56 >
+
+# 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 = 36531
+# cmdline opt mapi_usock = /var/tmp/mtest-28625/.s.monetdb.36531
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_orderidx
+# cmdline opt embedded_r = yes
+# cmdline opt embedded_py = true
+# cmdline opt gdk_debug = 536870922
+
+# 16:56:57 >
+# 16:56:57 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-28625" "--port=36531"
+# 16:56:57 >
+
+
+# 16:56:57 >
+# 16:56:57 > "Done."
+# 16:56:57 >
+
diff --git a/sql/test/orderidx/Tests/oidx_hge_type.stable.out.int128
b/sql/test/orderidx/Tests/oidx_hge_type.stable.out.int128
new file mode 100644
--- /dev/null
+++ b/sql/test/orderidx/Tests/oidx_hge_type.stable.out.int128
@@ -0,0 +1,271 @@
+stdout of test 'oidx_hge_type` in directory 'sql/test/orderidx` itself:
+
+
+# 16:56:56 >
+# 16:56:56 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36531" "--set"
"mapi_usock=/var/tmp/mtest-28625/.s.monetdb.36531" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_orderidx"
"--set" "embedded_r=yes" "--set" "embedded_py=true"
+# 16:56:56 >
+
+# MonetDB 5 server v11.25.12
+# This is an unreleased version
+# Serving database 'mTests_sql_test_orderidx', using 8 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers
+# Found 15.589 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2017 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:36531/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-28625/.s.monetdb.36531
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+# MonetDB/Python 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: 18_index.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: 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
+# loading sql script: 40_geom.sql
+# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
+# loading sql script: 41_md5sum.sql
+# loading sql script: 45_uuid.sql
+# loading sql script: 46_gsl.sql
+# loading sql script: 46_profiler.sql
+# loading sql script: 51_sys_schema_extension.sql
+# loading sql script: 72_fits.sql
+# loading sql script: 74_netcdf.sql
+# loading sql script: 75_lidar.sql
+# loading sql script: 75_shp.sql
+# loading sql script: 75_storagemodel.sql
+# loading sql script: 80_statistics.sql
+# loading sql script: 80_udf.sql
+# loading sql script: 80_udf_hge.sql
+# loading sql script: 85_bam.sql
+# loading sql script: 90_generator.sql
+# loading sql script: 90_generator_hge.sql
+# loading sql script: 99_system.sql
+
+# 16:56:57 >
+# 16:56:57 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-28625" "--port=36531"
+# 16:56:57 >
+
+#create table hge_types (
+# "hugeint" hugeint,
+# "decimal" decimal, -- is synonym for decimal(18,3)
+# "decimal38" decimal(38),
+# "decimal37_22" decimal(37,22),
+# "numeric" numeric, -- is synonym for decimal(18,3)
+# "numeric38" numeric(38, 0), -- is synonym for decimal(38)
+# "numeric37_9" numeric(37, 9) -- is synonym for decimal(37,9)
+#);
+#select number, name, type, type_digits, type_scale, "null", "default" from
sys._columns where table_id in (select id from sys._tables where name =
'hge_types') order by number;
+% sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns, sys._columns, sys._columns # table_name
+% number, name, type, type_digits, type_scale, null, default
# name
+% int, varchar, varchar, int, int, boolean, varchar
# type
+% 1, 12, 7, 3, 2, 5, 0 # length
+[ 0, "hugeint", "hugeint", 128, 0, true, NULL ]
+[ 1, "decimal", "decimal", 18, 3, true, NULL ]
+[ 2, "decimal38", "decimal", 38, 0, true, NULL ]
+[ 3, "decimal37_22", "decimal", 37, 22, true, NULL ]
+[ 4, "numeric", "decimal", 18, 3, true, NULL ]
+[ 5, "numeric38", "decimal", 38, 0, true, NULL ]
+[ 6, "numeric37_9", "decimal", 37, 9, true, NULL ]
+#select * from hge_types;
+% sys.hge_types, sys.hge_types, sys.hge_types, sys.hge_types,
sys.hge_types, sys.hge_types, sys.hge_types # table_name
+% hugeint, decimal, decimal38, decimal37_22, numeric,
numeric38, numeric37_9 # name
+% hugeint, decimal, decimal, decimal, decimal,
decimal, decimal # type
+% 1, 20, 39, 39, 20, 39, 39 # length
+#insert into hge_types ("hugeint") values (12345678900987654321);
+[ 1 ]
+#insert into hge_types ("decimal") values (123456789012345.678);
+[ 1 ]
+#insert into hge_types ("numeric") values (123456789012345.678);
+[ 1 ]
+#insert into hge_types ("decimal38") values (12345678900987654321);
+[ 1 ]
+#insert into hge_types ("numeric38") values (12345678900987654321);
+[ 1 ]
+#insert into hge_types ("decimal37_22") values
(123456789012345.1234567890123456789012);
+[ 1 ]
+#insert into hge_types ("numeric37_9") values
(1234567890123456789012345.123456789);
+[ 1 ]
+#insert into hge_types values (123456789012345, 123456789012345,
123456789012345, 123456789012345, 123456789012345, 123456789012345,
123456789012345);
+[ 1 ]
+#insert into hge_types values (123456789012345, 123456789012345,
123456789012345, 123456789012345, 123456789012345, 123456789012345,
123456789012345);
+[ 1 ]
+#insert into hge_types select -"hugeint", -"decimal", -"decimal38",
-"decimal37_22", -"numeric", -"numeric38", -"numeric37_9" from hge_types;
+[ 9 ]
+#select * from hge_types order by 1,2,3,4,5,6,7;
+% sys.hge_types, sys.hge_types, sys.hge_types, sys.hge_types,
sys.hge_types, sys.hge_types, sys.hge_types # table_name
+% hugeint, decimal, decimal38, decimal37_22, numeric,
numeric38, numeric37_9 # name
+% hugeint, decimal, decimal, decimal, decimal,
decimal, decimal # type
+% 21, 20, 39, 39, 20, 39, 39 # length
+[ NULL, NULL, NULL, NULL, NULL, NULL,
-1234567890123456789012345.123456789 ]
+[ NULL, NULL, NULL, NULL, NULL, NULL,
1234567890123456789012345.123456789 ]
+[ NULL, NULL, NULL, NULL, NULL, -12345678900987654321, NULL
]
+[ NULL, NULL, NULL, NULL, NULL, 12345678900987654321, NULL
]
+[ NULL, NULL, NULL, NULL, -123456789012345.678, NULL, NULL
]
+[ NULL, NULL, NULL, NULL, 123456789012345.678, NULL, NULL
]
+[ NULL, NULL, NULL, -123456789012345.1234567890123456789012,
NULL, NULL, NULL ]
+[ NULL, NULL, NULL, 123456789012345.1234567890123456789012, NULL,
NULL, NULL ]
+[ NULL, NULL, -12345678900987654321, NULL, NULL, NULL, NULL
]
+[ NULL, NULL, 12345678900987654321, NULL, NULL, NULL, NULL
]
+[ NULL, -123456789012345.678, NULL, NULL, NULL, NULL, NULL
]
+[ NULL, 123456789012345.678, NULL, NULL, NULL, NULL, NULL
]
+[ -12345678900987654321, NULL, NULL, NULL, NULL, NULL, NULL
]
+[ -123456789012345, -123456789012345.000, -123456789012345,
-123456789012345.0000000000000000000000, -123456789012345.000,
-123456789012345, -123456789012345.000000000 ]
+[ -123456789012345, -123456789012345.000, -123456789012345,
-123456789012345.0000000000000000000000, -123456789012345.000,
-123456789012345, -123456789012345.000000000 ]
+[ 123456789012345, 123456789012345.000, 123456789012345,
123456789012345.0000000000000000000000, 123456789012345.000,
123456789012345, 123456789012345.000000000 ]
+[ 123456789012345, 123456789012345.000, 123456789012345,
123456789012345.0000000000000000000000, 123456789012345.000,
123456789012345, 123456789012345.000000000 ]
+[ 12345678900987654321, NULL, NULL, NULL, NULL, NULL, NULL
]
+#create ordered index "hge_oidx_hugeint" on hge_types ("hugeint");
+#create ordered index "hge_oidx_decimal" on hge_types ("decimal");
+#create ordered index "hge_oidx_decimal38" on hge_types ("decimal38");
+#create ordered index "hge_oidx_decimal37_22" on hge_types ("decimal37_22");
+#create ordered index "hge_oidx_numeric" on hge_types ("numeric");
+#create ordered index "hge_oidx_numeric38" on hge_types ("numeric38");
+#create ordered index "hge_oidx_numeric37_9" on hge_types ("numeric37_9");
+#select type, name from sys.idxs where table_id in (select id from sys._tables
where name = 'hge_types') order by name;
+% sys.idxs, sys.idxs # table_name
+% type, name # name
+% int, varchar # type
+% 1, 21 # length
+[ 5, "hge_oidx_decimal" ]
+[ 5, "hge_oidx_decimal37_22" ]
+[ 5, "hge_oidx_decimal38" ]
+[ 5, "hge_oidx_hugeint" ]
+[ 5, "hge_oidx_numeric" ]
+[ 5, "hge_oidx_numeric37_9" ]
+[ 5, "hge_oidx_numeric38" ]
+#select * from hge_types
+# where "hugeint" between 1 and 12345678900987654322
+# or "decimal" between 2 and 123456789012345.679
+# or "numeric" between 2 and 123456789012345.679
+# or "decimal38" between 3 and 123456789009876543212
+# or "numeric38" between 3 and 123456789009876543212
+# or "decimal37_22" between 4 and 123456789012345.1234567890123456789013
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
