Changeset: af7bfd5c27da for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=af7bfd5c27da
Added Files:
sql/test/miscellaneous/Tests/select_groupby.sql
sql/test/miscellaneous/Tests/select_groupby.stable.err
sql/test/miscellaneous/Tests/select_groupby.stable.out
Modified Files:
sql/test/miscellaneous/Tests/All
Branch: Nov2019
Log Message:
Merge with Apr2019
diffs (truncated from 417 to 300 lines):
diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All
--- a/sql/test/miscellaneous/Tests/All
+++ b/sql/test/miscellaneous/Tests/All
@@ -10,3 +10,4 @@ with_update_stmts
groupby_error
alter_table_add_column
view-mul-digits
+select_groupby
diff --git a/sql/test/miscellaneous/Tests/select_groupby.sql
b/sql/test/miscellaneous/Tests/select_groupby.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/select_groupby.sql
@@ -0,0 +1,111 @@
+start transaction;
+create schema "myschema";
+create sequence "myschema"."myseq";
+create table myschema.mytable
+(
+ "first" char(100),
+ "second" double,
+ "third" double,
+ "fourth" clob,
+ "fifth" clob,
+ "sixth" double,
+ "seventh" clob,
+ "eighth" date,
+ "ninth" double,
+ "tenth" double,
+ "eleventh" char(100),
+ "tweelfth" char(4),
+ "thirteenth" char(50),
+ "fourteenth" char(50),
+ "fifteenth" clob,
+ "sixteenth" char(100),
+ "seventeenth" char(100),
+ "eighteenth" char(30),
+ "nineteenth" double,
+ "twentieth" char(100),
+ "twentieth-first" clob,
+ "twentieth-second" double,
+ "twentieth-third" double,
+ "twentieth-fourth" double,
+ "twentieth-fifth" double,
+ "twentieth-sixth" double,
+ "twentieth-seventh" char(100),
+ "twentieth-eighth" char(100),
+ "twentieth-ninth" char(100),
+ "thirtieth" char(14),
+ "thirtieth-first" bigint,
+ "thirtieth-second" bigint,
+ "thirtieth-third" bigint,
+ "thirtieth-fourth" bigint,
+ "thirtieth-fifth" bigint,
+ "thirtieth-sixth" bigint,
+ "thirtieth-seventh" bigint,
+ "thirtieth-eighth" bigint,
+ "thirtieth-ninth" bigint,
+ "fortieth" bigint,
+ "fortieth-first" tinyint,
+ "fortieth-second" blob,
+ "fortieth-third" int default next value for "myschema"."myseq"
+);
+INSERT INTO myschema.mytable ("first", "second", "third", "fourth", "fifth",
"sixth", "seventh", "eighth", "ninth", "tenth", "eleventh", "tweelfth",
"thirteenth", "fourteenth", "fifteenth", "sixteenth",
+ "seventeenth",
"eighteenth", "nineteenth", "twentieth", "twentieth-first", "twentieth-second",
"twentieth-third", "twentieth-fourth", "twentieth-fifth", "twentieth-sixth",
+ "twentieth-seventh",
"twentieth-eighth", "twentieth-ninth", "thirtieth", "thirtieth-first",
"thirtieth-second", "thirtieth-third", "thirtieth-fourth", "thirtieth-fifth",
+ "thirtieth-sixth",
"thirtieth-seventh", "thirtieth-eighth", "thirtieth-ninth", "fortieth",
"fortieth-first", "fortieth-second", "fortieth-third")
+ VALUES ('1234 / 0',
1234, 1, 'have', 'a', 850.85, 'very', '2019-03-18', 2, 16, 'nice', 'CC', 'DD',
'Day', 'somewhere', 'you', 'will', '3840',
+ 455, '11790 -
Something', 'Paper', 850.83, 344, 0, 506.83, 59.5689, 'Good', 'Food', 'hello',
'0000001234/00', 3000001215, 3000003378, 3000000346,
+ 3000003378,
3000000346, 3000000002, 3000000015, 3000000001, null, 3000000073, 0, null, 1);
+SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+
+PREPARE SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+exec ** ();
+
+PREPARE SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = ? AND "eleventh" = ? AND (EXTRACT(YEAR FROM mytable."eighth")
* cast(? as bigint) + EXTRACT(MONTH FROM mytable."eighth")) / cast(? as bigint)
= cast(? as bigint))
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT ?;
+exec ** ('a', 'b', 923, 51, 942, 544);
+
+TRUNCATE myschema.mytable;
+
+SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+
+PREPARE SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+exec ** ();
+
+PREPARE SELECT
+mytable."eighth" AS "first-projection",
+mytable."twentieth", mytable."twentieth-seventh",
+mytable."twentieth-third", mytable."twentieth-second"
+FROM myschema.mytable
+WHERE ("first" = ? AND "eleventh" = ? AND (EXTRACT(YEAR FROM mytable."eighth")
* cast(? as bigint) + EXTRACT(MONTH FROM mytable."eighth")) / cast(? as bigint)
= cast(? as bigint))
+GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT ?;
+exec ** ('a', 'b', 923, 51, 942, 544);
+
+rollback;
diff --git a/sql/test/miscellaneous/Tests/select_groupby.stable.err
b/sql/test/miscellaneous/Tests/select_groupby.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/select_groupby.stable.err
@@ -0,0 +1,33 @@
+stderr of test 'select_groupby` in directory 'sql/test/miscellaneous` itself:
+
+
+# 13:54:02 >
+# 13:54:02 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=35668" "--set"
"mapi_usock=/var/tmp/mtest-18773/.s.monetdb.35668" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "embedded_c=true"
+# 13:54:02 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/monetdb5/dbfarm/demo
+# 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 = 35668
+# cmdline opt mapi_usock = /var/tmp/mtest-18773/.s.monetdb.35668
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_miscellaneous
+# cmdline opt embedded_c = true
+#main thread:!ERROR:MALException:client.quit:Server stopped
+
+# 13:54:02 >
+# 13:54:02 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-18773" "--port=35668"
+# 13:54:02 >
+
+
+# 13:54:02 >
+# 13:54:02 > "Done."
+# 13:54:02 >
+
diff --git a/sql/test/miscellaneous/Tests/select_groupby.stable.out
b/sql/test/miscellaneous/Tests/select_groupby.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/select_groupby.stable.out
@@ -0,0 +1,250 @@
+stdout of test 'select_groupby` in directory 'sql/test/miscellaneous` itself:
+
+
+# 13:54:02 >
+# 13:54:02 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=35668" "--set"
"mapi_usock=/var/tmp/mtest-18773/.s.monetdb.35668" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-Apr2019/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "embedded_c=true"
+# 13:54:02 >
+
+# MonetDB 5 server v11.33.12 (hg id: b17dafcdef40)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_miscellaneous', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.504 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on
mapi:monetdb://localhost.localdomain:35668/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-18773/.s.monetdb.35668
+# MonetDB/GIS module loaded
+# 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_views.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_profiler.sql
+# loading sql script: 51_sys_schema_extension.sql
+# loading sql script: 60_wlcr.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
+# MonetDB/SQL module loaded
+
+# 15:11:54 >
+# 15:11:54 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-3271" "--port=37527"
+# 15:11:54 >
+
+#start transaction;
+#create schema "myschema";
+#create sequence "myschema"."myseq";
+#create table myschema.mytable
+#(
+# "first" char(100),
+# "second" double,
+# "third" double,
+# "fourth" clob,
+# "fifth" clob,
+# "sixth" double,
+# "seventh" clob,
+# "eighth" date,
+# "ninth" double,
+# "tenth" double,
+# "eleventh" char(100),
+# "tweelfth" char(4),
+# "thirteenth" char(50),
+# "fourteenth" char(50),
+# "fifteenth" clob,
+# "sixteenth" char(100),
+# "seventeenth" char(100),
+# "eighteenth" char(30),
+# "nineteenth" double,
+# "twentieth" char(100),
+# "twentieth-first" clob,
+# "twentieth-second" double,
+# "twentieth-third" double,
+#INSERT INTO myschema.mytable ("first", "second", "third", "fourth", "fifth",
"sixth", "seventh", "eighth", "ninth", "tenth", "eleventh", "tweelfth",
"thirteenth", "fourteenth", "fifteenth", "sixteenth",
+# "seventeenth",
"eighteenth", "nineteenth", "twentieth", "twentieth-first", "twentieth-second",
"twentieth-third", "twentieth-fourth", "twentieth-fifth", "twentieth-sixth",
+# "twentieth-seventh",
"twentieth-eighth", "twentieth-ninth", "thirtieth", "thirtieth-first",
"thirtieth-second", "thirtieth-third", "thirtieth-fourth", "thirtieth-fifth",
+[ 1 ]
+#SELECT
+#mytable."eighth" AS "first-projection",
+#mytable."twentieth", mytable."twentieth-seventh",
+#mytable."twentieth-third", mytable."twentieth-second"
+#FROM myschema.mytable
+#WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+#GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+% myschema.L1, myschema.mytable, myschema.mytable,
myschema.mytable, myschema.mytable # table_name
+% first-projection, twentieth, twentieth-seventh,
twentieth-third, twentieth-second # name
+% date, char, char, double, double # type
+% 10, 100, 100, 24, 24 # length
+#PREPARE SELECT
+#mytable."eighth" AS "first-projection",
+#mytable."twentieth", mytable."twentieth-seventh",
+#mytable."twentieth-third", mytable."twentieth-second"
+#FROM myschema.mytable
+#WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+#GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+#PREPARE SELECT
+#mytable."eighth" AS "first-projection",
+#mytable."twentieth", mytable."twentieth-seventh",
+#mytable."twentieth-third", mytable."twentieth-second"
+#FROM myschema.mytable
+#WHERE ("first" = '227 / 0' AND "eleventh" = 'Lekker' AND (EXTRACT(YEAR FROM
mytable."eighth")*100 + EXTRACT(MONTH FROM mytable."eighth"))/100.0 =
'2014.030')
+#GROUP BY "first-projection", "twentieth", "twentieth-seventh",
"twentieth-third", "twentieth-second" LIMIT 1001;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 6, 3, 1, 0, 7, 17 # length
+[ "date", 0, 0, "", "L1", "first-projection" ]
+[ "char", 100, 0, "", "mytable", "twentieth" ]
+[ "char", 100, 0, "", "mytable", "twentieth-seventh"
]
+[ "double", 53, 0, "", "mytable", "twentieth-third"
]
+[ "double", 53, 0, "", "mytable", "twentieth-second"
]
+#exec 13 ();
+% myschema.L1, myschema.mytable, myschema.mytable,
myschema.mytable, myschema.mytable # table_name
+% first-projection, twentieth, twentieth-seventh,
twentieth-third, twentieth-second # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
