Changeset: 1d8fdba00e27 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1d8fdba00e27
Modified Files:
sql/server/rel_select.c
sql/test/sciql/Tests/07_tiling_03.sql
sql/test/sciql/Tests/07_tiling_03.stable.err
sql/test/sciql/Tests/07_tiling_03.stable.out
sql/test/sciql/Tests/All
Branch: sciql
Log Message:
several improvements in the array tiling code and new test query that works
1. a more flexible way of finding the base array, since the groupby->l may not
always be a BASETABLE relation
2. treat the parameter of the AGGR function as an expression and add all
e_column-s in this expression to groupby->exps. this enables the AGGR function
to take an expression, e.g., avg(val * val), as its parameter, instead of only
column
3. only reduce the cardinality of the expressions if we have a normal SQL
groupby. with this change, the anchor point column is allowed in the SELECT
clause.
4. added a new test that covers the points 2 and 3 above
diffs (282 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -979,7 +979,9 @@ rel_groupby(sql_allocator *sa, sql_rel *
for (en = groupbyexps->h; en; en = en->next) {
sql_exp *e = en->data, *ne;
- /* after the group by the cardinality reduces */
+ /* the cardinality only reduces after a normal SQL
group by */
+ if (e->type == e_column && e->f)
+ rel->card = CARD_MULTI;
e->card = rel->card;
ne = exp_column(sa, exp_relname(e), exp_name(e),
exp_subtype(e), exp_card(e), has_nil(e), 0);
append(aggrs, ne);
@@ -2020,6 +2022,7 @@ rel_arraytiling(mvc *sql, sql_rel **rel,
exp->f = offsets;
append(exps, exp);
}
+ (*rel)->card = CARD_MULTI;
return exps;
}
@@ -3868,6 +3871,40 @@ rel_nop(mvc *sql, sql_rel **rel, symbol
return sql_error(sql, 02, "SELECT: no such operator '%s'", fname);
}
+/* Traverse all subexpressions of exp and add it to the exps list if an
+ * e_column is found
+ */
+static list *
+add_columns(mvc *sql, list *exps, sql_exp *exp)
+{
+ node *n;
+
+ switch(exp->type) {
+ case e_atom: /* nothing to add */
+ break;
+ case e_func:
+ case e_aggr:
+ for (n = ((list*)exp->l)->h; n; n = n->next) {
+ add_columns(sql, exps, (sql_exp*)n->data);
+ }
+ break;
+ case e_convert:
+ add_columns(sql, exps, exp->l);
+ break;
+ case e_cmp:
+ add_columns(sql, exps, exp->l);
+ add_columns(sql, exps, exp->r);
+ break;
+ case e_column:
+ append(exps, exp);
+ break;
+ default:
+ return sql_error(sql, 02, "SELECT: invalid expression type %d",
exp->type);
+ break;
+ }
+ return exps;
+}
+
/* E.g.:
* sql> plan select sum(v) from a group by a[x-1:x+2][y-1:y+2];
*
@@ -3904,15 +3941,34 @@ _rel_tiling_aggr(mvc *sql, sql_rel **rel
list *aggr_args = new_exp_list(sql->sa), *aggr_types =
new_subtype_list(sql->sa);
char *aggrstr2 = SA_NEW_ARRAY(sql->sa, char, strlen("array_") +
strlen(aggrstr) + 1);
node *cn = NULL;
- sql_table *t = (sql_table*)((sql_rel*)groupby->l)->l;
+ sql_table *t = NULL;
int i = 0, j = 0, *dsize = NULL;
sql_column *sc = NULL;
(void)distinct;
+ (void)f;
strcpy(aggrstr2, "array_\0");
strcat(aggrstr2, aggrstr);
+ /* find the base array */
+ if(((sql_rel*)groupby->l)->op == op_basetable) {
+ t = (sql_table*)((sql_rel*)groupby->l)->l;
+ } else {
+ char *aname = NULL;
+ sql_schema *s = NULL;
+
+ if (!(s = cur_schema(sql))) /* should not happen */
+ return sql_error(sql, 02, "SELECT: could not find
current schema");
+
+ if (!(aname =
exp_relname((sql_exp*)((list*)groupby->r)->h->data)))
+ return sql_error(sql, 02, "SELECT: cannot find base
array name in array tiling definitino");
+ if (!(t = mvc_bind_table(sql, s, aname)))
+ return sql_error(sql, 02, "SELECT: no such array
'%s.%s'", s->base.name, aname);
+ if (!t->ndims)
+ return sql_error(sql, 02, "SELECT: array tiling not
allowed over non-array '%s.%s'", s->base.name, aname);
+ }
+
if (!sym) { /* AGGR(*) case, but only "count" is allowed */
if (strcmp(aggrstr, "count") != 0) {
sql_error(sql, 02, "%s: unable to perform '%s(*)'",
toUpperCopy(aggrstr2, aggrstr), aggrstr);
@@ -3921,11 +3977,9 @@ _rel_tiling_aggr(mvc *sql, sql_rel **rel
sql_error(sql, 02, "%s: '%s(*)' not implemented for array
tiles", toUpperCopy(aggrstr2, aggrstr), aggrstr);
return NULL;
} else {
- if (sym->token != SQL_COLUMN) {
- return sql_error(sql, 02, "SELECT: expressions other
than <column name> not supported in tiling ranges");
- }
- exp = rel_column_ref(sql, rel, sym, f);
- append(groupby->exps, exp);
+ exp_kind ek = {type_value, card_value, FALSE};
+ exp = rel_value_exp(sql, rel, sym, sql_where, ek);
+ add_columns(sql, groupby->exps, exp);
append(aggr_args, exp);
append(aggr_types, exp_subtype(exp));
}
diff --git a/sql/test/sciql/Tests/07_tiling_03.sql
b/sql/test/sciql/Tests/07_tiling_03.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sciql/Tests/07_tiling_03.sql
@@ -0,0 +1,7 @@
+create array a ( x integer dimension[0:1:3], y integer dimension[0:1:3], f
float);
+insert into a values (0,0,0.2), (0,1,0.5), (0,2,0.2), (1,0,0.5), (1,1,1.0),
(1,2,0.5), (2,0,0.2), (2,1,0.5), (2,2,0.2);
+select x,y, avg(f * f) from a group by a[x:x+2][y:y+2];
+select x,y, f, avg(f * f) from a group by a[x:x+2][y:y+2];
+select x,y, f, avg(f * f) as f_avg from a group by a[x:x+2][y:y+2];
+drop array a;
+
diff --git a/sql/test/sciql/Tests/07_tiling_03.stable.err
b/sql/test/sciql/Tests/07_tiling_03.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/sciql/Tests/07_tiling_03.stable.err
@@ -0,0 +1,37 @@
+stderr of test '07_tiling_03` in directory 'test/sciql` itself:
+
+
+# 23:28:26 >
+# 23:28:26 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/export/scratch0/zhang/monet-install/sciql/debug/var/MonetDB"
"--set" "mapi_open=true" "--set" "mapi_port=33271" "--set" "monet_prompt="
"--trace" "--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_sciql"
"--set" "mal_listing=0"
+# 23:28:26 >
+
+# builtin opt gdk_dbname = demo
+# builtin opt gdk_dbfarm =
/export/scratch0/zhang/monet-install/sciql/debug/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 =
/export/scratch0/zhang/monet-install/sciql/debug/var/MonetDB
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 33271
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_test_sciql
+# cmdline opt mal_listing = 0
+
+# 23:28:26 >
+# 23:28:26 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=jarl"
"--port=33271"
+# 23:28:26 >
+
+
+# 23:28:26 >
+# 23:28:26 > "Done."
+# 23:28:26 >
+
diff --git a/sql/test/sciql/Tests/07_tiling_03.stable.out
b/sql/test/sciql/Tests/07_tiling_03.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/sciql/Tests/07_tiling_03.stable.out
@@ -0,0 +1,98 @@
+stdout of test '07_tiling_03` in directory 'test/sciql` itself:
+
+
+# 23:28:26 >
+# 23:28:26 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/export/scratch0/zhang/monet-install/sciql/debug/var/MonetDB"
"--set" "mapi_open=true" "--set" "mapi_port=33271" "--set" "monet_prompt="
"--trace" "--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_sciql"
"--set" "mal_listing=0"
+# 23:28:26 >
+
+# MonetDB 5 server v11.8.0
+# This is an unreleased version
+# Serving database 'mTests_test_sciql', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 7.749 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://jarl.ins.cwi.nl:33271/
+# MonetDB/GIS module loaded
+# MonetDB/SQL 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_history.sql
+# loading sql script: 16_tracelog.sql
+# loading sql script: 17_compress.sql
+# loading sql script: 18_dictionary.sql
+# loading sql script: 19_cluster.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: 29_array.sql
+# loading sql script: 40_geom.sql
+# loading sql script: 80_udf.sql
+# loading sql script: 99_system.sql
+
+# 23:28:26 >
+# 23:28:26 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=jarl"
"--port=33271"
+# 23:28:26 >
+
+#create array a ( x integer dimension[0:1:3], y integer dimension[0:1:3], f
float);
+#insert into a values (0,0,0.2), (0,1,0.5), (0,2,0.2), (1,0,0.5), (1,1,1.0),
(1,2,0.5), (2,0,0.2), (2,1,0.5), (2,2,0.2);
+[ 9 ]
+#select x,y, avg(f * f) from a group by a[x:x+2][y:y+2];
+% sys.a, sys.a, sys. # table_name
+% x, y, L34 # name
+% int, int, table # type
+% 1, 1, 0 # length
+[ 0, 0, 0.38500000000000001 ]
+[ 0, 1, 0.38500000000000001 ]
+[ 0, 2, 0.14500000000000002 ]
+[ 1, 0, 0.38500000000000001 ]
+[ 1, 1, 0.38500000000000001 ]
+[ 1, 2, 0.14500000000000002 ]
+[ 2, 0, 0.14500000000000002 ]
+[ 2, 1, 0.14500000000000002 ]
+[ 2, 2, 0.040000000000000008 ]
+#select x,y, f, avg(f * f) from a group by a[x:x+2][y:y+2];
+% sys.a, sys.a, sys.a, sys. # table_name
+% x, y, f, L35 # name
+% int, int, double, table # type
+% 1, 1, 24, 0 # length
+[ 0, 0, 0.2, 0.38500000000000001 ]
+[ 0, 1, 0.5, 0.38500000000000001 ]
+[ 0, 2, 0.2, 0.14500000000000002 ]
+[ 1, 0, 0.5, 0.38500000000000001 ]
+[ 1, 1, 1, 0.38500000000000001 ]
+[ 1, 2, 0.5, 0.14500000000000002 ]
+[ 2, 0, 0.2, 0.14500000000000002 ]
+[ 2, 1, 0.5, 0.14500000000000002 ]
+[ 2, 2, 0.2, 0.040000000000000008 ]
+#select x,y, f, avg(f * f) as f_avg from a group by a[x:x+2][y:y+2];
+% sys.a, sys.a, sys.a, sys. # table_name
+% x, y, f, f_avg # name
+% int, int, double, table # type
+% 1, 1, 24, 0 # length
+[ 0, 0, 0.2, 0.38500000000000001 ]
+[ 0, 1, 0.5, 0.38500000000000001 ]
+[ 0, 2, 0.2, 0.14500000000000002 ]
+[ 1, 0, 0.5, 0.38500000000000001 ]
+[ 1, 1, 1, 0.38500000000000001 ]
+[ 1, 2, 0.5, 0.14500000000000002 ]
+[ 2, 0, 0.2, 0.14500000000000002 ]
+[ 2, 1, 0.5, 0.14500000000000002 ]
+[ 2, 2, 0.2, 0.040000000000000008 ]
+#drop array a;
+
+# 23:28:26 >
+# 23:28:26 > "Done."
+# 23:28:26 >
+
diff --git a/sql/test/sciql/Tests/All b/sql/test/sciql/Tests/All
--- a/sql/test/sciql/Tests/All
+++ b/sql/test/sciql/Tests/All
@@ -50,6 +50,7 @@ 05_select_01
06_slice_01
07_tiling_01
+07_tiling_03
#texture
#smooth
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
