Changeset: 669ddcefd233 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=669ddcefd233
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
Removed Files:
sql/test/miscellaneous/README
sql/test/miscellaneous/Tests/now.sql
sql/test/miscellaneous/Tests/now.stable.err
Branch: tracer
Log Message:
Merge with default
diffs (truncated from 636 to 300 lines):
diff --git a/sql/test/miscellaneous/README b/sql/test/miscellaneous/README
deleted file mode 100644
--- a/sql/test/miscellaneous/README
+++ /dev/null
@@ -1,2 +0,0 @@
-The tests in this director produce time/system/ dependent output.
-It is included to ensure some testing
diff --git a/sql/test/miscellaneous/Tests/now.sql
b/sql/test/miscellaneous/Tests/now.sql
deleted file mode 100644
--- a/sql/test/miscellaneous/Tests/now.sql
+++ /dev/null
@@ -1,1 +0,0 @@
-select now();
diff --git a/sql/test/miscellaneous/Tests/now.stable.err
b/sql/test/miscellaneous/Tests/now.stable.err
deleted file mode 100644
--- a/sql/test/miscellaneous/Tests/now.stable.err
+++ /dev/null
@@ -1,37 +0,0 @@
-stderr of test 'now` in directory 'sql/test/miscellaneous` itself:
-
-
-# 17:34:22 >
-# 17:34:22 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=33116" "--set"
"mapi_usock=/var/tmp/mtest-28433/.s.monetdb.33116" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/manegold/Monet/_/prefix/_/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
-# 17:34:22 >
-
-# builtin opt gdk_dbpath =
/home/manegold/Monet/_/prefix/_/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 = 33116
-# cmdline opt mapi_usock = /var/tmp/mtest-28433/.s.monetdb.33116
-# cmdline opt monet_prompt =
-# cmdline opt mal_listing = 2
-# cmdline opt gdk_dbpath =
/home/manegold/Monet/_/prefix/_/var/MonetDB/mTests_sql_test_miscellaneous
-# cmdline opt mal_listing = 0
-# cmdline opt embedded_r = yes
-# cmdline opt gdk_debug = 536870922
-
-# 17:34:23 >
-# 17:34:23 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-28433" "--port=33116"
-# 17:34:23 >
-
-
-# 17:34:23 >
-# 17:34:23 > "Done."
-# 17:34:23 >
-
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,186 @@
+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);
+
+create function "sys"."dummy"("col1" blob, "col2" blob, "col3" integer)
returns boolean external name "unknown"."idontexist"; --error, MAL
implementation of sys.dummy doesn't exist.
+SELECT "sys"."dummy"("fortieth-second", blob '', '0') AS "alias1",
"fortieth-third" FROM "myschema"."mytable"; --error, function doesn't exist
+
+create table "myschema"."mytable2"
+(
+"'col1'" date,
+"'col2'" int,
+"'col3'" varchar(256),
+"'col4'" clob,
+"'col5'" clob,
+"'col6'" decimal,
+"'col7'" decimal,
+"'col8'" date,
+"'col9'" date,
+"'col10'" int,
+"'col11'" int,
+"'col12'" decimal,
+"'col13'" int,
+"'col14'" varchar(256),
+"'col15'" varchar(256),
+"'col16'" int,
+"'col17'" varchar(256),
+"'col18'" int,
+"'col19'" varchar(256),
+"'col20'" bigint,
+"'col21'" varchar(256),
+"'col22'" varchar(256),
+"'col23'" varchar(256),
+"'col24'" varchar(256),
+"'col25'" int,
+"'col26'" varchar(256),
+"'col27'" clob,
+"'col28'" varchar(256),
+"'col29'" decimal,
+"'col30'" decimal,
+"'col31'" decimal,
+"'col32'" decimal,
+"'col33'" clob,
+"'col34'" clob,
+"'col35'" int,
+"'col36'" int
+);
+
+insert into "myschema"."mytable2" values (
+'1998-01-03', 2239, 'col3', 'col4', 'col5', 73.28, 68.29, '2005-05-12',
'2010-03-03', 563, 63, 56.3, 852, 'col14',
+'col15', 134, 'col17', 892, 'col19', 9348, 'col21', 'col22', 'col23', 'col24',
934, 'col26', 'col27', 'col28', 849.2739, 1742.718,
+395.824, 39.824, 'col33', 'col34', 395824, 3789);
+
+SELECT "'col26'" FROM "myschema"."mytable2" WHERE
+( ( ("'col19'"='Some information of my hobby')) AND
+( ("'col9'"='' AND "'col3'"='ABCDE' AND (EXTRACT(YEAR FROM "'col1'")*100 +
EXTRACT(MONTH FROM "'col1'"))/100.0='2011.040'
+AND "'col14'"='A description of something' AND "'col8'"='2015-08-07')) AND (
("'col34'"='home to')) AND ( ("'col36'"='2013'))) GROUP BY "'col26'"
+LIMIT 1001 OFFSET 0; --error, from the condition "'col9'"='', it fails to
covert '' to date
+
+PREPARE SELECT "'col26'" FROM "myschema"."mytable2" WHERE
+( ( ("'col19'"='Some information of my hobby')) AND
+( ("'col9'"='' AND "'col3'"='ABCDE' AND (EXTRACT(YEAR FROM "'col1'")*100 +
EXTRACT(MONTH FROM "'col1'"))/100.0='2011.040'
+AND "'col14'"='A description of something' AND "'col8'"='2015-08-07')) AND (
("'col34'"='home to')) AND ( ("'col36'"='2013'))) GROUP BY "'col26'"
+LIMIT 1001 OFFSET 0;
+
+exec ** (); --error, from the condition "'col9'"='', it fails to covert '' to
date
+
+SELECT "'col26'" FROM "myschema"."mytable2" WHERE
+( ( ("'col19'"='Some information of my hobby')) AND
+( ("'col9'"='2015-06-11' AND "'col3'"='ABCDE' AND (EXTRACT(YEAR FROM
"'col1'")*100 + EXTRACT(MONTH FROM "'col1'"))/100.0='2011.040'
+AND "'col14'"='A description of something' AND "'col8'"='2015-08-07')) AND (
("'col34'"='home to')) AND ( ("'col36'"='2013'))) GROUP BY "'col26'"
+LIMIT 1001 OFFSET 0;
+
+PREPARE SELECT "'col26'" FROM "myschema"."mytable2" WHERE
+( ( ("'col19'"='Some information of my hobby')) AND
+( ("'col9'"='2015-06-11' AND "'col3'"='ABCDE' AND (EXTRACT(YEAR FROM
"'col1'")*100 + EXTRACT(MONTH FROM "'col1'"))/100.0='2011.040'
+AND "'col14'"='A description of something' AND "'col8'"='2015-08-07')) AND (
("'col34'"='home to')) AND ( ("'col36'"='2013'))) GROUP BY "'col26'"
+LIMIT 1001 OFFSET 0;
+
+exec ** ();
+
+drop schema "myschema" cascade;
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,53 @@
+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 >
+
+MAPI = (monetdb) /var/tmp/mtest-155076/.s.monetdb.39205
+QUERY = create function "sys"."dummy"("col1" blob, "col2" blob, "col3"
integer) returns boolean external name "unknown"."idontexist"; --error, MAL
implementation of sys.dummy doesn't exist.
+ERROR = !CREATE FUNCTION: external name unknown.idontexist not bound
(sys.dummy)
+CODE = 3F000
+MAPI = (monetdb) /var/tmp/mtest-155076/.s.monetdb.39205
+QUERY = SELECT "sys"."dummy"("fortieth-second", blob '', '0') AS "alias1",
"fortieth-third" FROM "myschema"."mytable"; --error, function doesn't exist
+ERROR = !SELECT: no such operator 'dummy'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-328803/.s.monetdb.34130
+QUERY = SELECT "'col26'" FROM "myschema"."mytable2" WHERE
+ ( ( ("'col19'"='Some information of my hobby')) AND
+ ( ("'col9'"='' AND "'col3'"='ABCDE' AND (EXTRACT(YEAR FROM
"'col1'")*100 + EXTRACT(MONTH FROM "'col1'"))/100.0='2011.040'
+ AND "'col14'"='A description of something' AND "'col8'"='2015-08-07'))
AND ( ("'col34'"='home to')) AND ( ("'col36'"='2013'))) GROUP BY "'col26'"
+ LIMIT 1001 OFFSET 0; --error, from the condition "'col9'"='', it fails
to covert '' to date
+ERROR = !Conversion of string '' failed
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-329283/.s.monetdb.38369
+QUERY = exec 31 (); --error, from the condition "'col9'"='', it fails to
covert '' to date
+ERROR = !Conversion of string '' failed
+CODE = 42000
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
