Re: [Monetdb-developers] MonetDB: Jun2010 - make it possible to use '?' in offset and limit

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