Changeset: 36b7be236d87 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=36b7be236d87
Added Files:
sql/test/VOC/Tests/median.Bug-3096.sql
sql/test/VOC/Tests/median.Bug-3096.stable.err
sql/test/VOC/Tests/median.Bug-3096.stable.out
Modified Files:
sql/backends/monet5/sql.mx
sql/scripts/39_analytics.sql
sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
sql/test/Tests/systemfunctions.stable.out
sql/test/VOC/Tests/All
sql/test/leaks/Tests/check0.stable.out
sql/test/leaks/Tests/check1.stable.out
sql/test/leaks/Tests/check2.stable.out
sql/test/leaks/Tests/check3.stable.out
sql/test/leaks/Tests/check4.stable.out
sql/test/leaks/Tests/check5.stable.out
sql/test/mapi/Tests/php_monetdb.stable.out
Branch: Apr2012
Log Message:
fixed bug 3096
1) added missinge sql create functions for date/time/timestamp
2) fixed problem with median on empty column
diffs (truncated from 588 to 300 lines):
diff --git a/sql/backends/monet5/sql.mx b/sql/backends/monet5/sql.mx
--- a/sql/backends/monet5/sql.mx
+++ b/sql/backends/monet5/sql.mx
@@ -7165,6 +7165,45 @@ freeVariables(Client c, MalBlkPtr mb, Ma
* Assume val is id,oid sorted.
*/
@h
+sql5_export str SQLmiddle_val(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+@c
+str
+SQLmiddle_val(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ BAT *v;
+ BUN cnt = 0, pos;
+ ValPtr res = getArgReference(stk, pci, 0);
+ int *type = (int*) getArgReference(stk, pci, 0);
+ bat valid = *(bat*) getArgReference(stk, pci, 1);
+ BATiter vi;
+
+ (void)cntxt;
+ (void)mb;
+ if( (v = BATdescriptor(valid)) == NULL )
+ throw(SQL, "sql.middle", "Cannot access descriptor");
+
+ *type = v->ttype;
+ cnt = BATcount(v);
+ if (cnt == 0) {
+ VALset(res, v->ttype, ATOMnilptr(v->ttype));
+ BBPunfix(v->batCacheid);
+ return MAL_SUCCEED;
+ }
+ pos = cnt/2;
+ vi = bat_iterator(v);
+ VALset(res, v->ttype, BUNtail(vi, pos));
+ BBPunfix(v->batCacheid);
+ return MAL_SUCCEED;
+}
+@mal
+pattern sql.middle_val( val:bat[:any_1,:any_2]):any_2
+address SQLmiddle_val;
+
+@h
+/*
+ * Compute the median.
+ * Assume val is id,oid sorted.
+ */
sql5_export str SQLmiddle(bat *res, bat *val, bat *hist);
@c
str
@@ -7228,10 +7267,7 @@ address SQLmiddle;
function aggr.median(a:bat[:oid,:any_1]):any_1;
v := algebra.sortTail(a);
- c := aggr.count(v);
- c := calc./(c,2);
- p := calc.lng(c);
- return algebra.fetch(v, p);
+ return sql.middle_val(a);
end aggr.median;
function
aggr.median(a:bat[:oid,:any_1],g:bat[:oid,:oid],e:bat[:oid,:any_2]):bat[:oid,:any_1];
diff --git a/sql/scripts/39_analytics.sql b/sql/scripts/39_analytics.sql
--- a/sql/scripts/39_analytics.sql
+++ b/sql/scripts/39_analytics.sql
@@ -12,6 +12,13 @@ create aggregate median(val REAL) return
create aggregate median(val DOUBLE) returns DOUBLE
external name "aggr"."median";
+create aggregate median(val DATE) returns DATE
+ external name "aggr"."median";
+create aggregate median(val TIME) returns TIME
+ external name "aggr"."median";
+create aggregate median(val TIMESTAMP) returns TIMESTAMP
+ external name "aggr"."median";
+
create aggregate corr(e1 TINYINT, e2 TINYINT) returns TINYINT
external name "aggr"."corr";
create aggregate corr(e1 SMALLINT, e2 SMALLINT) returns SMALLINT
diff --git
a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
--- a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
+++ b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
@@ -57,8 +57,8 @@ Ready.
[ 5195, "queryhistory", 2000, NULL, 0, true, 0, false,
0 ]
[ 5209, "callhistory", 2000, NULL, 0, true, 0, false,
0 ]
[ 5226, "querylog", 2000, "create view querylog as\nselect qd.*,
ql.ctime, ql.arguments, ql.exec, ql.result, ql.foot, ql.memory, ql.tuples,
ql.inblock, ql.oublock from queryhistory qd, callhistory ql\nwhere qd.id =
ql.id;", 1, true, 0, false, 0 ]
-[ 5824, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
-[ 5930, "r", 2000, NULL, 0, false, 0, false, 0
]
+[ 5836, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
+[ 5942, "r", 2000, NULL, 0, false, 0, false, 0
]
#select * from tables where name;
% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables # table_name
% id, name, schema_id, query, type, system, commit_action,
readonly, temporary # name
@@ -93,8 +93,8 @@ Ready.
[ 5195, "queryhistory", 2000, NULL, 0, true, 0, false,
0 ]
[ 5209, "callhistory", 2000, NULL, 0, true, 0, false,
0 ]
[ 5226, "querylog", 2000, "create view querylog as\nselect qd.*,
ql.ctime, ql.arguments, ql.exec, ql.result, ql.foot, ql.memory, ql.tuples,
ql.inblock, ql.oublock from queryhistory qd, callhistory ql\nwhere qd.id =
ql.id;", 1, true, 0, false, 0 ]
-[ 5824, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
-[ 5930, "r", 2000, NULL, 0, false, 0, false, 0
]
+[ 5836, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
+[ 5942, "r", 2000, NULL, 0, false, 0, false, 0
]
# 18:53:13 >
# 18:53:13 > "Done."
diff --git a/sql/test/Tests/systemfunctions.stable.out
b/sql/test/Tests/systemfunctions.stable.out
--- a/sql/test/Tests/systemfunctions.stable.out
+++ b/sql/test/Tests/systemfunctions.stable.out
@@ -254,6 +254,12 @@ Ready.
[ "sys", "median", 1, "real", "" ]
[ "sys", "median", 0, "double", "create aggregate
median(val double) returns double\n\texternal name \"aggr\".\"median\";" ]
[ "sys", "median", 1, "double", "" ]
+[ "sys", "median", 0, "date", "create aggregate median(val
date) returns date\n\texternal name \"aggr\".\"median\";" ]
+[ "sys", "median", 1, "date", "" ]
+[ "sys", "median", 0, "time", "create aggregate median(val
time) returns time\n\texternal name \"aggr\".\"median\";" ]
+[ "sys", "median", 1, "time", "" ]
+[ "sys", "median", 0, "timestamp", "create aggregate
median(val timestamp) returns timestamp\n\texternal name \"aggr\".\"median\";"
]
+[ "sys", "median", 1, "timestamp", "" ]
[ "sys", "mlinefromtext", 0, "multilinestring", "create
function mlinefromtext(wkt string, srid smallint) returns multilinestring
external name geom.\"MultiLineFromText\";" ]
[ "sys", "mlinefromtext", 1, "clob", "" ]
[ "sys", "mlinefromtext", 2, "smallint", "" ]
diff --git a/sql/test/VOC/Tests/All b/sql/test/VOC/Tests/All
--- a/sql/test/VOC/Tests/All
+++ b/sql/test/VOC/Tests/All
@@ -1,1 +1,2 @@
VOC
+median.Bug-3096
diff --git a/sql/test/VOC/Tests/median.Bug-3096.sql
b/sql/test/VOC/Tests/median.Bug-3096.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/VOC/Tests/median.Bug-3096.sql
@@ -0,0 +1,30 @@
+
+CREATE TABLE "voyages" (
+ "number" integer NOT NULL,
+ "number_sup" char(1) NOT NULL,
+ "trip" integer,
+ "trip_sup" char(1),
+ "boatname" varchar(50),
+ "master" varchar(50),
+ "tonnage" integer,
+ "type_of_boat" varchar(30),
+ "built" varchar(15),
+ "bought" varchar(15),
+ "hired" varchar(15),
+ "yard" char(1),
+ "chamber" char(1),
+ "departure_date" date,
+ "departure_harbour" varchar(30),
+ "cape_arrival" date,
+ "cape_departure" date,
+ "cape_call" boolean,
+ "arrival_date" date,
+ "arrival_harbour" varchar(30),
+ "next_voyage" integer,
+ "particulars" varchar(530)
+);
+
+select median(departure_date) - min(departure_date) from voyages;
+select max(departure_date) - min(departure_date) from voyages;
+
+drop table voyages;
diff --git a/sql/test/VOC/Tests/median.Bug-3096.stable.err
b/sql/test/VOC/Tests/median.Bug-3096.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/VOC/Tests/median.Bug-3096.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'median.Bug-3096` in directory 'test/VOC` itself:
+
+
+# 16:07:06 >
+# 16:07:06 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB" "--set"
"mapi_open=true" "--set" "mapi_port=31863" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_VOC" "--set"
"mal_listing=0"
+# 16:07:06 >
+
+# builtin opt gdk_dbname = demo
+# builtin opt gdk_dbfarm =
/home/niels/scratch/rc-clean/Linux-x86_64/var/monetdb5/dbfarm
+# builtin opt gdk_debug = 0
+# builtin opt gdk_alloc_map = no
+# builtin opt gdk_vmtrim = yes
+# 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 gdk_dbfarm =
/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 31863
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_test_VOC
+# cmdline opt mal_listing = 0
+
+# 16:07:06 >
+# 16:07:06 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=31863"
+# 16:07:06 >
+
+
+# 16:07:06 >
+# 16:07:06 > "Done."
+# 16:07:06 >
+
diff --git a/sql/test/VOC/Tests/median.Bug-3096.stable.out
b/sql/test/VOC/Tests/median.Bug-3096.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/VOC/Tests/median.Bug-3096.stable.out
@@ -0,0 +1,59 @@
+stdout of test 'median.Bug-3096` in directory 'test/VOC` itself:
+
+
+# 16:07:06 >
+# 16:07:06 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB" "--set"
"mapi_open=true" "--set" "mapi_port=31863" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_VOC" "--set"
"mal_listing=0"
+# 16:07:06 >
+
+# MonetDB 5 server v11.9.2
+# This is an unreleased version
+# Serving database 'mTests_test_VOC', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 3.778 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on
mapi:monetdb://niels.nesco.mine.nu:31863/
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 16:07:06 >
+# 16:07:06 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=31863"
+# 16:07:06 >
+
+#CREATE TABLE "voyages" (
+# "number" integer NOT NULL,
+# "number_sup" char(1) NOT NULL,
+# "trip" integer,
+# "trip_sup" char(1),
+# "boatname" varchar(50),
+# "master" varchar(50),
+# "tonnage" integer,
+# "type_of_boat" varchar(30),
+# "built" varchar(15),
+# "bought" varchar(15),
+# "hired" varchar(15),
+# "yard" char(1),
+# "chamber" char(1),
+# "departure_date" date,
+# "departure_harbour" varchar(30),
+#select median(departure_date) - min(departure_date) from voyages;
+% sys. # table_name
+% sql_sub_L1 # name
+% int # type
+% 11 # length
+[ NULL ]
+#select max(departure_date) - min(departure_date) from voyages;
+% sys. # table_name
+% sql_sub_L1 # name
+% int # type
+% 11 # length
+[ NULL ]
+#drop table voyages;
+
+# 16:07:06 >
+# 16:07:06 > "Done."
+# 16:07:06 >
+
diff --git a/sql/test/leaks/Tests/check0.stable.out
b/sql/test/leaks/Tests/check0.stable.out
--- a/sql/test/leaks/Tests/check0.stable.out
+++ b/sql/test/leaks/Tests/check0.stable.out
@@ -53,18 +53,18 @@ Ready.
[ "int", "str", 142 ]
[ "oid", "int", 19 ]
[ "oid", "int", 19 ]
-[ "oid", "int", 1145 ]
-[ "oid", "int", 3159 ]
-[ "oid", "int", 3159 ]
-[ "oid", "int", 3159 ]
-[ "oid", "int", 3159 ]
-[ "oid", "int", 3159 ]
+[ "oid", "int", 1148 ]
+[ "oid", "int", 3165 ]
+[ "oid", "int", 3165 ]
+[ "oid", "int", 3165 ]
+[ "oid", "int", 3165 ]
+[ "oid", "int", 3165 ]
[ "oid", "lng", 19 ]
[ "oid", "str", 1 ]
[ "oid", "str", 1 ]
[ "oid", "str", 19 ]
-[ "oid", "str", 3159 ]
-[ "oid", "str", 3159 ]
+[ "oid", "str", 3165 ]
+[ "oid", "str", 3165 ]
#select 'transient', count(*) from bbp() as bbp where kind like 'tran%';
% .L1, .bbp # table_name
% L1, L2 # name
diff --git a/sql/test/leaks/Tests/check1.stable.out
b/sql/test/leaks/Tests/check1.stable.out
--- a/sql/test/leaks/Tests/check1.stable.out
+++ b/sql/test/leaks/Tests/check1.stable.out
@@ -35,8 +35,8 @@ Ready.
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
