Niels, can you create a Changlog entry, please? On 2010-06-22 15:25, Niels Nes wrote:

Changeset: 600a35cbf6c0 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=600a35cbf6c0 Modified Files: sql/src/server/bin_optimizer.mx sql/src/server/rel_bin.mx sql/src/server/rel_schema.mx sql/src/server/rel_select.mx sql/src/server/sql_parser.mx sql/src/server/sql_statement.mx sql/src/server/sql_symbol.mx sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out sql/src/test/Dependencies/Tests/Dependencies.stable.out Branch: Jun2010 Log Message:

make it possible to use '?' in offset and limit (transplanted from 61c37c0d76f8ee3359bda98cc25e750b5201ae56)

diffs (truncated from 482 to 300 lines):

diff -r d83c56c4bba7 -r 600a35cbf6c0 sql/src/server/bin_optimizer.mx --- a/sql/src/server/bin_optimizer.mx Tue Jun 22 14:39:09 2010 +0200 +++ b/sql/src/server/bin_optimizer.mx Mon Jun 21 23:35:09 2010 +0200 @@ -288,9 +288,9 @@ stmt *r = stmt_dup(j->op2.stval);

l = stmt_limit(l, - s->op2.stval->op1.aval->data.val.wval, - s->op3.stval->op1.aval->data.val.wval, - s->flag); + stmt_dup(s->op2.stval), + stmt_dup(s->op3.stval), + s->flag); s = stmt_join(l, r, cmp_equal); ns = bin_optimizer(c, s); stmt_destroy(s); @@ -311,9 +311,9 @@ stmt *r = stmt_dup(j->op2.stval);

r = stmt_limit(r, - s->op2.stval->op1.aval->data.val.wval, - s->op3.stval->op1.aval->data.val.wval, - s->flag); + stmt_dup(s->op2.stval), + stmt_dup(s->op3.stval), + s->flag); s = stmt_join(l, r, cmp_equal); ns = bin_optimizer(c, s); stmt_destroy(s); @@ -325,8 +325,8 @@ /* try to push the limit through the order */ if (j->type == st_order) { s = stmt_order(stmt_limit(stmt_dup(j->op1.stval), - s->op2.stval->op1.aval->data.val.wval, - s->op3.stval->op1.aval->data.val.wval, + stmt_dup(s->op2.stval), + stmt_dup(s->op3.stval), LIMIT_DIRECTION(j->flag,1,0)), j->flag); ns = bin_optimizer(c, s); stmt_destroy(s); @@ -338,8 +338,8 @@ /* try to push the limit through the reverse */ if (!s->flag && j->type == st_reverse) { s = stmt_reverse(stmt_limit(stmt_dup(j->op1.stval), - s->op2.stval->op1.aval->data.val.wval, - s->op3.stval->op1.aval->data.val.wval, + stmt_dup(s->op2.stval), + stmt_dup(s->op3.stval), 0)); ns = bin_optimizer(c, s); stmt_destroy(s); @@ -351,8 +351,9 @@ /* try to push the limit through the mark (only if there is no offset) */ if (!s->op2.stval->op1.aval->data.val.wval && j->type == st_mark) { s = stmt_mark_tail(stmt_limit(stmt_dup(j->op1.stval), - s->op2.stval->op1.aval->data.val.wval, - s->op3.stval->op1.aval->data.val.wval, s->flag), + stmt_dup(s->op2.stval), + stmt_dup(s->op3.stval), + s->flag), j->op2.stval->op1.aval->data.val.ival); ns = bin_optimizer(c, s); stmt_destroy(s); diff -r d83c56c4bba7 -r 600a35cbf6c0 sql/src/server/rel_bin.mx --- a/sql/src/server/rel_bin.mx Tue Jun 22 14:39:09 2010 +0200 +++ b/sql/src/server/rel_bin.mx Mon Jun 21 23:35:09 2010 +0200 @@ -1478,32 +1478,26 @@ return stmt_list(l); }

-static wrd +static sql_exp* topn_limit( sql_rel *rel ) { if (rel->exps) { sql_exp *limit = rel->exps->h->data; - atom *a = limit->l; - - return a->data.val.wval; + + return limit; } - return -1; + return NULL; }

-static wrd +static sql_exp* topn_offset( sql_rel *rel ) { - wrd o = 0; - if (rel->exps && list_length(rel->exps) > 1) { sql_exp *offset = rel->exps->h->next->data; - atom *a = offset->l; - - o = a->data.val.wval; - if (o <= 0) - o = 0; + + return offset; } - return o; + return NULL; }

static sql_table * @@ -1524,15 +1518,24 @@ list *pl; node *en, *n; stmt *sub = NULL, *psub = NULL; - wrd l = -1; + stmt *l = NULL;

if (topn) { - wrd o = topn_offset(topn); - l = topn_limit(topn); - if (l < 0) /* for now only handle topn + sql_exp *le = topn_limit(topn); + sql_exp *oe = topn_offset(topn); + + if (!le) { /* for now only handle topn including limit, ie not just offset */ topn = NULL; - l += o; + } else { + l = exp_bin(sql, le, NULL, NULL, NULL, NULL); + if (oe) { + sql_subtype *wrd = sql_bind_localtype("wrd"); + sql_subfunc *add = sql_bind_func_result(sql->session->schema, "sql_add", wrd, wrd, wrd); + stmt *o = exp_bin(sql, oe, NULL, NULL, NULL, NULL); + l = stmt_binop(l, o, add); + } + } }

if (!rel->exps) @@ -1591,7 +1594,7 @@ stmt_destroy(sub); return NULL; } - limit = stmt_limit(orderbycols, 0, l, LIMIT_DIRECTION(is_ascending(orderbycole), 1, before_project)); + limit = stmt_limit(orderbycols, stmt_atom_wrd(0), l, LIMIT_DIRECTION(is_ascending(orderbycole), 1, before_project)); for ( n=pl->h ; n; n = n->next) { list_append(npl, stmt_semijoin(column(stmt_dup(n->data)), @@ -1902,8 +1905,8 @@ rel2bin_topn( mvc *sql, sql_rel *rel, list *refs) { list *newl; - wrd l = -1, o = 0; - stmt *sub = NULL, *order = NULL; + sql_exp *oe = NULL, *le = NULL; + stmt *sub = NULL, *order = NULL, *l = NULL, *o = NULL; node *n;

if (rel->l) { /* first construct the sub relation */ @@ -1918,8 +1921,8 @@ if (!sub) return NULL;

- l = topn_limit(rel); - o = topn_offset(rel); + le = topn_limit(rel); + oe = topn_offset(rel);

if (sub->type == st_ordered) { stmt *s = stmt_dup(sub->op2.stval); @@ -1932,18 +1935,25 @@

if (n) { stmt *limit = NULL, *p, *j; - wrd lmt = l; - - if (l < 0) { + sql_exp *lmt = le; + + if (le) + l = exp_bin(sql, le, NULL, NULL, NULL, NULL); + if (oe) + o = exp_bin(sql, oe, NULL, NULL, NULL, NULL); + + if (!le) { l = o; - o = 0; + o = stmt_atom_wrd(0); } + if (!o) + o = stmt_atom_wrd(0);

if (order) { limit = stmt_limit(stmt_dup(order), o, l, 0); } else { limit = stmt_limit(column(stmt_dup(n->data)), o, l, 0); - if (lmt >= 0) { + if (lmt) { n = n->next; list_append(newl, limit); } @@ -1953,7 +1963,7 @@ j = find_projection_join(limit); if (j && 0) { p = find_pivot(j); - if (lmt < 0) + if (!lmt) p = stmt_diff(stmt_dup(p), stmt_dup(limit)); else p = stmt_semijoin(stmt_dup(p), stmt_dup(limit)); @@ -1971,7 +1981,7 @@ for ( ; n; n = n->next) { stmt *s;

- if (lmt < 0) + if (!lmt) s = stmt_diff(column(stmt_dup(n->data)), stmt_dup(limit)); else s = stmt_semijoin(column(stmt_dup(n->data)), stmt_dup(limit)); @@ -1979,7 +1989,7 @@ } } if (order) { - if (lmt < 0) { + if (!lmt) { order = stmt_diff(order, limit); } else { stmt_destroy(order); diff -r d83c56c4bba7 -r 600a35cbf6c0 sql/src/server/rel_schema.mx --- a/sql/src/server/rel_schema.mx Tue Jun 22 14:39:09 2010 +0200 +++ b/sql/src/server/rel_schema.mx Mon Jun 21 23:35:09 2010 +0200 @@ -859,7 +859,7 @@ if (query->token == SQL_SELECT) { SelectNode *sn = (SelectNode *) query;

- if (sn->limit >= 0) + if (sn->limit) return sql_error(sql, 01, "CREATE VIEW: LIMIT not supported"); if (sn->orderby) return sql_error(sql, 01, "CREATE VIEW: ORDER BY not supported"); diff -r d83c56c4bba7 -r 600a35cbf6c0 sql/src/server/rel_select.mx --- a/sql/src/server/rel_select.mx Tue Jun 22 14:39:09 2010 +0200 +++ b/sql/src/server/rel_select.mx Mon Jun 21 23:35:09 2010 +0200 @@ -4644,12 +4644,31 @@ if (!rel) return NULL;

- if (sn->limit > 0 || sn->offset > 0) { + if (sn->limit || sn->offset) { + sql_subtype *wrd = sql_bind_localtype("wrd"); list *exps = new_exp_list();

- append(exps, exp_atom_wrd(sn->limit)); - if (sn->offset > 0) - append(exps, exp_atom_wrd(sn->offset)); + if (sn->limit) { + sql_exp *l = rel_value_exp( sql, NULL, sn->limit, 0, ek); + + if (!l || !(l=rel_check_type(sql, wrd, l, type_equal))) + return NULL; + if ((ek.card != card_relation && sn->limit) && + (ek.card == card_value && sn->limit)) { + sql_subaggr *zero_or_one = sql_bind_aggr(sql->session->schema, "zero_or_one", exp_subtype(l)); + + l = exp_aggr1(l, zero_or_one, 0, 0, CARD_ATOM, 0); + } + // return sql_error(sql, 01, "SELECT: LIMIT only allowed on outermost SELECT"); + append(exps, l); + } else + append(exps, NULL); + if (sn->offset) { + sql_exp *o = rel_value_exp( sql, NULL, sn->offset, 0, ek); + if (!o || !(o=rel_check_type(sql, wrd, o, type_equal))) + return NULL; + append(exps, o); + } rel = rel_topn(rel, exps); } return rel; @@ -4670,10 +4689,6 @@ return table_ref(sql, rel, sq); assert(sn->s.token == SQL_SELECT);

- if ((ek.card != card_relation && sn->limit >= 0) && - (ek.card == card_value && sn->limit != 1)) - return sql_error(sql, 01, "SELECT: LIMIT only allowed on outermost SELECT"); - if (ek.card != card_relation && sn->orderby) return sql_error(sql, 01, "SELECT: ORDER BY only allowed on outermost SELECT");

diff -r d83c56c4bba7 -r 600a35cbf6c0 sql/src/server/sql_parser.mx --- a/sql/src/server/sql_parser.mx Tue Jun 22 14:39:09 2010 +0200 +++ b/sql/src/server/sql_parser.mx Mon Jun 21 23:35:09 2010 +0200 @@ -388,6 +388,9 @@ ordering_spec simple_table table_ref + opt_limit + opt_offset + param case_exp case_scalar_exp _______________________________________________ Checkin-list mailing list Checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list

-- Sjoerd Mullender