Changeset: f201bf96179f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f201bf96179f
Branch: default
Log Message:
merge with returning
diffs (truncated from 855 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,6 +1,24 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Tue Oct 8 2024 Yunus Koning <yunus.kon...@monetdbsolutions.com>
+- Introduce the RETURNING clause for INSERT, UPDATE and DELETE statements.
+ Specifying a RETURNING clause causes the SQL statement to return the
+ modified records which can be queried using SELECT like expressions
+ in the RETURNING clause. Aggregate functions are allowed.
+ This is a common non-standard SQL extension.
+
+ Examples:
+
+ INSERT INTO foo values (1,10), (-1,-10) RETURNING i+2*j AS bar
+ ----
+ 21
+ -21
+
+ UPDATE foo SET i = -i WHERE i >0 RETURNING sum(j), count(j)
+ ----
+ -60|3
+
* Tue Sep 10 2024 Lucas Pereira <lucas.pere...@monetdbsolutions.com>
- Introduce division_min_scale SQL environment variable for specifying
minimum scale of the division result. The default value is 3.
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -5238,7 +5238,7 @@ rel2bin_insert(backend *be, sql_rel *rel
{
mvc *sql = be->mvc;
list *l;
- stmt *inserts = NULL, *insert = NULL, *ddl = NULL, *pin = NULL,
**updates, *ret = NULL, *cnt = NULL, *pos = NULL;
+ stmt *inserts = NULL, *insert = NULL, *ddl = NULL, *pin = NULL,
**updates, *ret = NULL, *cnt = NULL, *pos = NULL, *returning = NULL;
int idx_ins = 0, len = 0;
node *n, *m, *idx_m = NULL;
sql_rel *tr = rel->l, *prel = rel->r;
@@ -5352,6 +5352,20 @@ rel2bin_insert(backend *be, sql_rel *rel
if (!insert)
return NULL;
+ if (rel->returning) {
+ list* il = sa_list(sql->sa);
+ sql_rel* inner = rel->l;
+ assert(inner->op == op_basetable);
+ for (n = inner->exps->h, m = inserts->op4.lval->h; n && m; n =
n->next, m = m->next) {
+ sql_exp* ce = n->data;
+ stmt* ins = m->data;
+ stmt* s = stmt_rename(be, ce, ins);// label
each insert statement with the corresponding col exp label
+ append(il, s);
+ }
+ returning = stmt_list(be, il);
+ sql->type = Q_TABLE;
+ }
+
if (!sql_insert_triggers(be, t, updates, 1))
return sql_error(sql, 10, SQLSTATE(27000) "INSERT INTO:
triggers failed for table '%s'", t->base.name);
/* update predicate list */
@@ -5368,7 +5382,7 @@ rel2bin_insert(backend *be, sql_rel *rel
return sql_error(sql, 10, SQLSTATE(HY013)
MAL_MALLOC_FAIL);
if (t->s && isGlobal(t) && !isGlobalTemp(t))
stmt_add_dependency_change(be, t, ret);
- return ret;
+ return returning?returning:ret;
}
}
@@ -6398,6 +6412,18 @@ rel2bin_update(backend *be, sql_rel *rel
append(l, stmt_update_col(be, c, tids,
updates[c->colnr]));
}
+ stmt* returning = NULL;
+ if (rel->returning) {
+ sql_rel* b = rel->l;
+ int refcnt = b->ref.refcnt; // HACK: forces recalculation of
base columns since they are assumed to be updated
+ b->ref.refcnt = 1;
+ returning = subrel_bin(be, b, refs);
+ b->ref.refcnt = refcnt;
+ returning->cand = tids;
+ returning = subrel_project(be, returning, refs, b);
+ sql->type = Q_TABLE;
+ }
+
if (cascade_updates(be, t, tids, updates)) {
if (sql->cascade_action)
sql->cascade_action = NULL;
@@ -6426,7 +6452,7 @@ rel2bin_update(backend *be, sql_rel *rel
sql->cascade_action = NULL;
if (rel->r && !rel_predicates(be, rel->r))
return NULL;
- return cnt;
+ return returning?returning:cnt;
}
static int
@@ -6644,7 +6670,7 @@ static stmt *
rel2bin_delete(backend *be, sql_rel *rel, list *refs)
{
mvc *sql = be->mvc;
- stmt *stdelete = NULL, *tids = NULL;
+ stmt *stdelete = NULL, *tids = NULL, *returning = NULL;
sql_rel *tr = rel->l;
sql_table *t = NULL;
@@ -6661,6 +6687,14 @@ rel2bin_delete(backend *be, sql_rel *rel
assert(rows->type == st_list);
tids = rows->op4.lval->h->data; /* TODO this should be the
candidate list instead */
}
+
+ if (rel->returning) {
+ returning = subrel_bin(be, rel->l, refs);
+ returning->cand = tids;
+ returning = subrel_project(be, returning, refs, rel->l);
+ sql->type = Q_TABLE;
+ }
+
stdelete = sql_delete(be, t, tids);
if (sql->cascade_action)
sql->cascade_action = NULL;
@@ -6669,7 +6703,7 @@ rel2bin_delete(backend *be, sql_rel *rel
if (rel->r && !rel_predicates(be, rel->r))
return NULL;
- return stdelete;
+ return returning?returning:stdelete;
}
struct tablelist {
@@ -7507,17 +7541,17 @@ subrel_bin(backend *be, sql_rel *rel, li
break;
case op_insert:
s = rel2bin_insert(be, rel, refs);
- if (sql->type == Q_TABLE)
+ if (!(rel->returning) && sql->type == Q_TABLE)
sql->type = Q_UPDATE;
break;
case op_update:
s = rel2bin_update(be, rel, refs);
- if (sql->type == Q_TABLE)
+ if (!(rel->returning) && sql->type == Q_TABLE)
sql->type = Q_UPDATE;
break;
case op_delete:
s = rel2bin_delete(be, rel, refs);
- if (sql->type == Q_TABLE)
+ if (!(rel->returning) && sql->type == Q_TABLE)
sql->type = Q_UPDATE;
break;
case op_truncate:
diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c
--- a/sql/backends/monet5/sql.c
+++ b/sql/backends/monet5/sql.c
@@ -5522,6 +5522,61 @@ SQLcheck(Client cntxt, MalBlkPtr mb, Mal
return MAL_SUCCEED;
}
+static str
+SQLread_dump_rel(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ mvc *m = NULL;
+ str msg = NULL;
+ buffer *b = NULL;
+ stream *s = NULL;
+ char *res = NULL;
+ str *r = getArgReference_str(stk, pci, 0);
+ char *input = *getArgReference_str(stk, pci, 1);
+
+ if ((msg = getSQLContext(cntxt, mb, &m, NULL)) != NULL)
+ return msg;
+ if ((msg = checkSQLContext(cntxt)) != NULL)
+ return msg;
+
+ list *refs = sa_list(m->sa);
+ if (refs == NULL)
+ goto bailout;
+
+ int pos = 0;
+ sql_rel* rel = rel_read(m, input, &pos, refs);
+ if (!rel)
+ throw(SQL, "SQLread_dump_rel", SQLSTATE(42000) "failed to read
relational plan");
+
+ b = buffer_create(1024);
+ if(b == NULL)
+ goto bailout;
+ s = buffer_wastream(b, "exp_dump");
+ if(s == NULL)
+ goto bailout;
+
+ refs = sa_list(m->sa);
+ if (refs == NULL)
+ goto bailout;
+
+ rel_print_refs(m, s, rel, 0, refs, 0);
+ rel_print_(m, s, rel, 0, refs, 0);
+ res = buffer_get_buf(b);
+
+ if (!(*r = GDKstrdup(res)))
+ goto bailout;
+
+ mnstr_destroy(s);
+ return MAL_SUCCEED;
+
+bailout:
+ if (s)
+ mnstr_destroy(s);
+ else if (b)
+ buffer_destroy(b);
+ throw(SQL, "SQLread_dump_rel", SQLSTATE(HY013) MAL_MALLOC_FAIL);
+}
+
+
static mel_func sql_init_funcs[] = {
pattern("sql", "shutdown", SQLshutdown_wrap, true, "", args(1,3,
arg("",str),arg("delay",bte),arg("force",bit))),
pattern("sql", "shutdown", SQLshutdown_wrap, true, "", args(1,3,
arg("",str),arg("delay",sht),arg("force",bit))),
@@ -6454,6 +6509,7 @@ static mel_func sql_init_funcs[] = {
pattern("sql", "vacuum", SQLstr_auto_vacuum, true, "auto vacuum string column
of given table with interval(sec)", args(0,3,
arg("sname",str),arg("tname",str),arg("interval", int))),
pattern("sql", "stop_vacuum", SQLstr_stop_vacuum, true, "stop auto vacuum",
args(0,2, arg("sname",str),arg("tname",str))),
pattern("sql", "check", SQLcheck, false, "Return sql string of check
constraint.", args(1,3, arg("sql",str), arg("sname", str), arg("name", str))),
+ pattern("sql", "read_dump_rel", SQLread_dump_rel, false, "Reads sql_rel
string into sql_rel object and then writes it to the return value", args(1,2,
arg("sql",str), arg("sql_rel", str))),
{ .imp=NULL }
};
#include "mal_import.h"
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
@@ -312,7 +312,8 @@ typedef struct relation {
processed:1, /* fully processed or still in the process of building
*/
outer:1, /* used as outer (ungrouped) */
grouped:1, /* groupby processed all the group by exps */
- single:1;
+ single:1,
+ returning:1; /*update|delete|insert relations return modified records*/
/*
* Used by rewriters at rel_unnest, rel_optimizer and rel_distribute so
a relation is not modified twice
* The list is kept at rel_optimizer_private.h Please update it
accordingly
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
@@ -1894,10 +1894,11 @@ rel_read(mvc *sql, char *r, int *pos, li
(void)readInt(r,pos);
skipWS(r, pos);
(*pos)++; /* ( */
- (void)readInt(r,pos); /* skip nr refs */
+ int cnt = readInt(r,pos);
(*pos)++; /* ) */
if (!(rel = rel_read(sql, r, pos, refs)))
return NULL;
+ rel->ref.refcnt = cnt;
append(refs, rel);
skipWS(r,pos);
}
@@ -1908,7 +1909,7 @@ rel_read(mvc *sql, char *r, int *pos, li
*pos += (int) strlen("REF");
skipWS(r, pos);
nr = readInt(r,pos); /* skip nr refs */
- return rel_dup(list_fetch(refs, nr-1));
+ return list_fetch(refs, nr-1);
}
if (r[*pos] == 'i' && r[*pos+1] == 'n' && r[*pos+2] == 's') {
@@ -1931,6 +1932,7 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_insert(sql, lrel, rrel)) || !(rel =
read_rel_properties(sql, rel, r, pos)))
return NULL;
+ return rel;
}
if (r[*pos] == 'd' && r[*pos+1] == 'e' && r[*pos+2] == 'l') {
@@ -1953,6 +1955,8 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_delete(sql->sa, lrel, rrel)) || !(rel =
read_rel_properties(sql, rel, r, pos)))
return NULL;
+
+ return rel;
}
if (r[*pos] == 't' && r[*pos+1] == 'r' && r[*pos+2] == 'u') {
@@ -2006,10 +2010,11 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!update_allowed(sql, t, t->base.name, "UPDATE", "update",
0) )
return NULL;
+ skipWS(r, pos);
if (!(exps = read_exps(sql, lrel, rrel, NULL, r, pos, '[', 0,
1))) /* columns to be updated */
return NULL;
- for (node *n = rel->exps->h ; n ; n = n->next) {
+ for (node *n = exps->h ; n ; n = n->next) {
sql_exp *e = (sql_exp *) n->data;
const char *cname = exp_name(e);
@@ -2026,6 +2031,8 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_update(sql, lrel, rrel, NULL, nexps)) || !(rel
= read_rel_properties(sql, rel, r, pos)))
return NULL;
+
+ return rel;
}
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
