Changeset: c38110da1907 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c38110da1907
Modified Files:
sql/backends/monet5/sql_gencode.c
sql/include/sql_relation.h
sql/server/bin_optimizer.c
sql/server/rel_bin.c
sql/server/rel_dump.c
sql/server/rel_optimizer.c
sql/server/rel_schema.c
sql/server/rel_select.c
sql/server/rel_select.h
sql/server/sql_parser.y
sql/server/sql_rel2bin.c
sql/server/sql_scan.c
sql/server/sql_statement.c
sql/server/sql_statement.h
sql/server/sql_symbol.c
sql/server/sql_symbol.h
Branch: default
Log Message:
I added an sql operator called sample. It goes through all the sql stack with
a new relation operator called op_sample (and the equiv. st_sample etc.
ofcourse). Currently there is no m5 module sample commited yet so if you call
SAMPLE you will get an MAL exception. However explain and plan should work
fine. Some known issues right now are some mismatches on the optimising level,
(failing to apply dce in some cases) but they will be fixed soon.
The following text is a more formal explanation which can also be used for the
"new features" text. Another commit in m5 to come later will discuss the
algorithmic aspects.
-------------------------------------------------------------------------------
A new SQL operator has been added to support sampling the result of a query.
The syntax for sampling is:
SELECT ... FROM ... WHERE ... SAMPLE sqlINT
where sqlINT is the number of rows in the sample. SAMPLE is been treated the
same as LIMIT, ORDER BY, etc., that means that it can only be in the outer most
SELECT clause, i.e., SAMPLE cannot appear in a subquery. However, if this is
needed, then one may define a function, for example
create function mysample ()
returns table(col a,...)
begin
return
select a,...
from name_table
limit sqlINT;
end;
and then use function mysample() for example to populate a new table with the
sample. E.g.,
INSERT INTO sample_table (select * FROM mysample());
------------------------------------------------------------------------------
diffs (truncated from 723 to 300 lines):
diff --git a/sql/backends/monet5/sql_gencode.c
b/sql/backends/monet5/sql_gencode.c
--- a/sql/backends/monet5/sql_gencode.c
+++ b/sql/backends/monet5/sql_gencode.c
@@ -835,6 +835,14 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
}
s->nr = l;
} break;
+ case st_sample:{
+ int l = _dumpstmt(sql, mb, s->op1);
+ int r = _dumpstmt(sql, mb, s->op2);
+ q = newStmt1(mb, "sample", "uniform");
+ q = pushArgument(mb, q, l);
+ q = pushArgument(mb, q, r);
+ s->nr = getDestVar(q);
+ } break;
case st_order:{
int l = _dumpstmt(sql, mb, s->op1);
diff --git a/sql/include/sql_relation.h b/sql/include/sql_relation.h
--- a/sql/include/sql_relation.h
+++ b/sql/include/sql_relation.h
@@ -137,6 +137,7 @@ typedef enum operator_type {
op_except,
op_groupby,
op_topn,
+ op_sample,
op_insert, /* insert(l=table, r insert expressions) */
op_update, /* update(l=table, r update expressions) */
op_delete /* delete(l=table, r delete expression) */
@@ -182,6 +183,8 @@ typedef enum operator_type {
(op == op_topn)
#define is_modify(op) \
(op == op_insert || op == op_update || op == op_delete)
+#define is_sample(op) \
+ (op == op_sample)
/* NO NIL semantics of aggr operations */
#define need_no_nil(e) \
diff --git a/sql/server/bin_optimizer.c b/sql/server/bin_optimizer.c
--- a/sql/server/bin_optimizer.c
+++ b/sql/server/bin_optimizer.c
@@ -444,6 +444,7 @@ _bin_optimizer(mvc *c, stmt *s)
case st_reorder:
case st_ordered:
case st_limit2:
+ case st_sample:
case st_alias:
case st_append:
diff --git a/sql/server/rel_bin.c b/sql/server/rel_bin.c
--- a/sql/server/rel_bin.c
+++ b/sql/server/rel_bin.c
@@ -1900,6 +1900,50 @@ rel2bin_topn( mvc *sql, sql_rel *rel, li
}
static stmt *
+rel2bin_sample( mvc *sql, sql_rel *rel, list *refs)
+{
+ list *newl;
+ stmt *sub = NULL, *s = NULL, *sample = NULL;
+ node *n;
+
+ if (rel->l) { /* first construct the sub relation */
+ sub = subrel_bin(sql, rel->l, refs);
+ if (!sub)
+ return NULL;
+ }
+
+ n = sub->op4.lval->h;
+ newl = list_new(sql->sa);
+
+ if (n) {
+ stmt *sc = n->data;
+ char *cname = column_name(sql->sa, sc);
+ char *tname = table_name(sql->sa, sc);
+
+ s = exp_bin(sql, rel->exps->h->data, NULL, NULL, NULL, NULL);
+
+ if (!s)
+ s = stmt_atom_wrd_nil(sql->sa);
+
+ sc = column(sql->sa, sc);
+ sample = stmt_sample(sql->sa, stmt_alias(sql->sa, sc, tname,
cname),s);
+
+ sample = stmt_mirror(sql->sa, sample);
+ for ( ; n; n = n->next) {
+ stmt *sc = n->data;
+ char *cname = column_name(sql->sa, sc);
+ char *tname = table_name(sql->sa, sc);
+
+ sc = column(sql->sa, sc);
+ sc = stmt_project(sql->sa, sample, sc);
+ list_append(newl, stmt_alias(sql->sa, sc, tname,
cname));
+ }
+ }
+ sub = stmt_list(sql->sa, newl);
+ return sub;
+}
+
+static stmt *
nth( list *l, int n)
{
int i;
@@ -3571,6 +3615,10 @@ subrel_bin(mvc *sql, sql_rel *rel, list
s = rel2bin_topn(sql, rel, refs);
sql->type = Q_TABLE;
break;
+ case op_sample:
+ s = rel2bin_sample(sql, rel, refs);
+ sql->type = Q_TABLE;
+ break;
case op_insert:
s = rel2bin_insert(sql, rel, refs);
if (sql->type == Q_TABLE)
diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c
--- a/sql/server/rel_dump.c
+++ b/sql/server/rel_dump.c
@@ -256,6 +256,8 @@ op2string(operator_type op)
return "group by";
case op_topn:
return "topn";
+ case op_sample:
+ return "sample";
case op_insert:
case op_update:
case op_delete:
@@ -379,6 +381,7 @@ rel_print_(mvc *sql, stream *fout, sql_
case op_select:
case op_groupby:
case op_topn:
+ case op_sample:
r = "project";
if (rel->op == op_select)
r = "select";
@@ -386,6 +389,8 @@ rel_print_(mvc *sql, stream *fout, sql_
r = "group by";
if (rel->op == op_topn)
r = "top N";
+ if (rel->op == op_sample)
+ r = "sample";
print_indent(sql, fout, depth);
if (rel->l) {
if (need_distinct(rel))
@@ -479,6 +484,7 @@ rel_print_refs(mvc *sql, stream* fout, s
case op_select:
case op_groupby:
case op_topn:
+ case op_sample:
rel_print_refs(sql, fout, rel->l, depth, refs);
if (rel->l && rel_is_ref(rel->l) && !find_ref(refs, rel->l)) {
rel_print_(sql, fout, rel->l, depth, refs);
@@ -988,7 +994,7 @@ rel_read(mvc *sql, char *r, int *pos)
skipWS(r, pos);
nrel = rel_read(sql, r, pos);
if (r[*pos] != ')')
- return sql_error(sql, -1, "project: missing
')'\n");
+ return sql_error(sql, -1, "top N: missing
')'\n");
(*pos)++;
skipWS(r, pos);
exps = read_exps(sql, nrel, NULL, r, pos, '[', 0);
@@ -1043,6 +1049,20 @@ rel_read(mvc *sql, char *r, int *pos)
rel->exps = exps;
return rel;
case 's':
+ *pos += strlen("sample");
+ skipWS(r, pos);
+ if (r[*pos] != '(')
+ return sql_error(sql, -1, "sample: missing '('\n");
+ (*pos)++;
+ skipWS(r, pos);
+ nrel = rel_read(sql, r, pos);
+ if (r[*pos] != ')')
+ return sql_error(sql, -1, "sample: missing ')'\n");
+ (*pos)++;
+ skipWS(r, pos);
+ exps = read_exps(sql, nrel, NULL, r, pos, '[', 0);
+ rel = rel_sample(sql->sa, nrel, exps);
+ return rel;
case 'a':
if (r[*pos+2] == 'l') {
*pos += strlen("select");
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -105,12 +105,13 @@ name_find_column( sql_rel *rel, char *rn
if (!c)
c = name_find_column( rel->l, rname, name, pnr, bt);
return c;
- case op_select:
- case op_topn:
+ case op_select:
+ case op_topn:
+ case op_sample:
return name_find_column( rel->l, rname, name, pnr, bt);
- case op_union:
- case op_inter:
- case op_except:
+ case op_union:
+ case op_inter:
+ case op_except:
if (pnr >= 0 || pnr == -2) {
/* first right (possible subquery) */
@@ -264,11 +265,12 @@ rel_properties(mvc *sql, global_props *g
rel_properties(sql, gp, rel->r);
break;
case op_project:
- case op_select:
- case op_groupby:
- case op_topn:
+ case op_select:
+ case op_groupby:
+ case op_topn:
+ case op_sample:
case op_ddl:
- if (rel->l)
+ if (rel->l)
rel_properties(sql, gp, rel->l);
break;
case op_insert:
@@ -300,9 +302,10 @@ rel_properties(mvc *sql, global_props *g
break;
case op_project:
- case op_groupby:
- case op_topn:
- case op_select:
+ case op_groupby:
+ case op_topn:
+ case op_sample:
+ case op_select:
break;
case op_insert:
@@ -2642,7 +2645,7 @@ rel_remove_empty_select(int *changes, mv
{
(void)sql;
- if ((is_join(rel->op) || is_semi(rel->op) || is_select(rel->op) ||
is_project(rel->op) || rel->op == op_topn) && rel->l) {
+ if ((is_join(rel->op) || is_semi(rel->op) || is_select(rel->op) ||
is_project(rel->op) || is_topn(rel->op) || is_sample(rel->op)) && rel->l) {
sql_rel *l = rel->l;
if (is_select(l->op) && !(rel_is_ref(l)) &&
(!l->exps || list_length(l->exps) == 0)) {
@@ -3118,7 +3121,8 @@ rel_remove_empty_join(mvc *sql, sql_rel
(*changes)++;
return rel_inplace_project(sql->sa, rel, rel_dup(l),
rel->exps);
}
- } else if ((is_project(rel->op) || is_topn(rel->op) ||
is_select(rel->op)) && rel->l) {
+ } else if ((is_project(rel->op) || is_topn(rel->op) ||
is_select(rel->op)
+ || is_sample(rel->op)) && rel->l) {
rel->l = rel_remove_empty_join(sql, rel->l, changes);
} else if (is_join(rel->op)) {
rel->l = rel_remove_empty_join(sql, rel->l, changes);
@@ -3915,7 +3919,7 @@ positional_exps_mark_used( sql_rel *rel,
if (!rel->exps)
assert(0);
- if (is_topn(subrel->op) && subrel->l)
+ if ((is_topn(subrel->op) || is_sample(subrel->op)) && subrel->l)
subrel = subrel->l;
if (rel->exps && subrel->exps) {
node *n, *m;
@@ -3994,7 +3998,7 @@ rel_used(sql_rel *rel)
rel_used(rel->l);
if (rel->r)
rel_used(rel->r);
- } else if (is_topn(rel->op) || is_select(rel->op)) {
+ } else if (is_topn(rel->op) || is_select(rel->op) ||
is_sample(rel->op)) {
rel_used(rel->l);
rel = rel->l;
}
@@ -4018,7 +4022,8 @@ rel_mark_used(mvc *sql, sql_rel *rel, in
case op_table:
break;
- case op_topn:
+ case op_topn:
+ case op_sample:
if (proj) {
rel = rel ->l;
rel_mark_used(sql, rel, proj);
@@ -4037,7 +4042,7 @@ rel_mark_used(mvc *sql, sql_rel *rel, in
case op_ddl:
break;
- case op_select:
+ case op_select:
if (rel->l) {
exps_mark_used(rel, rel->l);
rel_mark_used(sql, rel->l, 0);
@@ -4119,7 +4124,8 @@ rel_remove_unused(mvc *sql, sql_rel *rel
}
return rel;
- case op_topn:
+ case op_topn:
+ case op_sample:
if (rel->l)
rel->l = rel_remove_unused(sql, rel->l);
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
