Changeset: 248aa8f21695 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=248aa8f21695
Added Files:
sql/test/BugTracker-2018/Tests/insert-null-second-interval-0.Bug-6515.stable.err
sql/test/BugTracker-2018/Tests/insert-null-second-interval-0.Bug-6515.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-select-range-null.Bug-6514.stable.err
sql/test/BugTracker-2018/Tests/sqlitelogictest-select-range-null.Bug-6514.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-wrong-aggregation-count.Bug-6510.stable.err
sql/test/BugTracker-2018/Tests/sqlitelogictest-wrong-aggregation-count.Bug-6510.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-wrong-select-not-between.Bug-6511.stable.err
sql/test/BugTracker-2018/Tests/sqlitelogictest-wrong-select-not-between.Bug-6511.stable.out
Modified Files:
sql/backends/monet5/rel_bin.c
sql/backends/monet5/sql.c
sql/backends/monet5/sql_statement.c
sql/backends/monet5/sql_statement.h
sql/include/sql_relation.h
sql/server/rel_dump.c
sql/server/rel_exp.c
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_select.c
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
sql/test/VOC/Tests/VOC.stable.out
Branch: Jul2017
Log Message:
Fixes for bugs 6510, 6512, 6514 and 6515
6510 start sum's at 0 instead of nil in case of count/sum 2 step aggregation
6512 make sure we only call bat.single on constants (ie not on bats).
6514 handle NULL (type void) on both sides of an compare operator, ie cast to a
valid type.
6515 handle null in calc.second_interval
diffs (truncated from 890 to 300 lines):
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
@@ -546,7 +546,7 @@ exp_bin(backend *be, sql_exp *e, stmt *l
as = const_column(be, as);
}
}
- s = stmt_aggr(be, as, grp, ext, a, 1, need_no_nil(e) /* ignore
nil*/ );
+ s = stmt_aggr(be, as, grp, ext, a, 1, need_no_nil(e) /* ignore
nil*/, !zero_if_empty(e) );
if (find_prop(e->p, PROP_COUNT)) /* propagate count == 0 ipv
NULL in outer joins */
s->flag |= OUTER_ZERO;
} break;
@@ -2479,7 +2479,7 @@ rel2bin_project(backend *be, sql_rel *re
if (!s) /* error */
return NULL;
/* single value with limit */
- if (topn && rel->r && sub && sub->nrcols == 0)
+ if (topn && rel->r && sub && sub->nrcols == 0 && s->nrcols == 0)
s = const_column(be, s);
else if (sub && sub->nrcols >= 1 && s->nrcols == 0)
s = stmt_const(be, bin_first_column(be, sub), s);
@@ -3051,7 +3051,7 @@ insert_check_ukey(backend *be, list *ins
s = releqjoin(be, lje, rje, 1 /* hash used */,
cmp_equal, 0);
s = stmt_result(be, s, 0);
}
- s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1, 0),
stmt_atom_lng(be, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1),
stmt_atom_lng(be, 0), ne);
/* 2e stage: find out if inserted are unique */
if ((!idx_inserts && ins->nrcols) || (idx_inserts &&
idx_inserts->nrcols)) { /* insert columns not atoms */
@@ -3076,7 +3076,7 @@ insert_check_ukey(backend *be, list *ins
return NULL;
sum = sql_bind_aggr(sql->sa, sql->session->schema,
"not_unique", tail_type(orderby_grp));
- ssum = stmt_aggr(be, orderby_grp, NULL, NULL, sum, 1,
0);
+ ssum = stmt_aggr(be, orderby_grp, NULL, NULL, sum, 1,
0, 1);
/* combine results */
s = stmt_binop(be, s, ssum, or);
}
@@ -3100,11 +3100,11 @@ insert_check_ukey(backend *be, list *ins
s = stmt_join(be, s, h, 0, cmp_equal);
/* s should be empty */
s = stmt_result(be, s, 0);
- s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0);
+ s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
} else {
s = stmt_uselect(be, s, h, cmp_equal, NULL, 0);
/* s should be empty */
- s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0);
+ s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
}
/* s should be empty */
s = stmt_binop(be, s, stmt_atom_lng(be, 0), ne);
@@ -3128,9 +3128,9 @@ insert_check_ukey(backend *be, list *ins
ss = stmt_result(be, g, 2); /* use count */
/* (count(ss) <> sum(ss)) */
sum = sql_bind_aggr(sql->sa, sql->session->schema,
"sum", lng);
- ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0);
+ ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0, 1);
ssum = sql_Nop_(be, "ifthenelse", sql_unop_(be, NULL,
"isnull", ssum), stmt_atom_lng(be, 0), ssum, NULL);
- count_sum = stmt_binop(be, check_types(be,
tail_type(ssum), stmt_aggr(be, ss, NULL, NULL, cnt, 1, 0), type_equal), ssum,
ne);
+ count_sum = stmt_binop(be, check_types(be,
tail_type(ssum), stmt_aggr(be, ss, NULL, NULL, cnt, 1, 0, 1), type_equal),
ssum, ne);
/* combine results */
s = stmt_binop(be, s, count_sum, or);
@@ -3159,10 +3159,10 @@ insert_check_fkey(backend *be, list *ins
if (pin && list_length(pin->op4.lval))
s = pin->op4.lval->h->data;
if (s->key && s->nrcols == 0) {
- s = stmt_binop(be, stmt_aggr(be, idx_inserts, NULL, NULL, cnt,
1, 0), stmt_atom_lng(be, 1), ne);
+ s = stmt_binop(be, stmt_aggr(be, idx_inserts, NULL, NULL, cnt,
1, 0, 1), stmt_atom_lng(be, 1), ne);
} else {
/* releqjoin.count <> inserts[col1].count */
- s = stmt_binop(be, stmt_aggr(be, idx_inserts, NULL, NULL, cnt,
1, 0), stmt_aggr(be, s, NULL, NULL, cnt, 1, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, idx_inserts, NULL, NULL, cnt,
1, 0, 1), stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1), ne);
}
/* s should be empty */
@@ -3268,7 +3268,7 @@ sql_insert_check_null(backend *be, sql_t
if (!(s->key && s->nrcols == 0)) {
s = stmt_selectnil(be, i);
- s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0);
+ s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
} else {
sql_subfunc *isnil = sql_bind_func(sql->sa,
sql->session->schema, "isnull", &c->type, NULL, F_FUNC);
@@ -3391,7 +3391,7 @@ rel2bin_insert(backend *be, sql_rel *rel
if (insert->op1->nrcols == 0) {
s = stmt_atom_lng(be, 1);
} else {
- s = stmt_aggr(be, insert->op1, NULL, NULL,
sql_bind_aggr(sql->sa, sql->session->schema, "count", NULL), 1, 0);
+ s = stmt_aggr(be, insert->op1, NULL, NULL,
sql_bind_aggr(sql->sa, sql->session->schema, "count", NULL), 1, 0, 1);
}
return s;
}
@@ -3475,7 +3475,7 @@ update_check_ukey(backend *be, stmt **up
}
s = releqjoin(be, lje, rje, 1 /* hash used */,
cmp_equal, 0);
s = stmt_result(be, s, 0);
- s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1,
0), stmt_atom_lng(be, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1,
0, 1), stmt_atom_lng(be, 0), ne);
}
/* 2e stage: find out if the updated are unique */
@@ -3548,9 +3548,9 @@ update_check_ukey(backend *be, stmt **up
ss = Cnt; /* use count */
/* (count(ss) <> sum(ss)) */
sum = sql_bind_aggr(sql->sa, sql->session->schema,
"sum", lng);
- ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0);
+ ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0, 1);
ssum = sql_Nop_(be, "ifthenelse", sql_unop_(be, NULL,
"isnull", ssum), stmt_atom_lng(be, 0), ssum, NULL);
- count_sum = stmt_binop(be, stmt_aggr(be, ss, NULL,
NULL, cnt, 1, 0), check_types(be, lng, ssum, type_equal), ne);
+ count_sum = stmt_binop(be, stmt_aggr(be, ss, NULL,
NULL, cnt, 1, 0, 1), check_types(be, lng, ssum, type_equal), ne);
/* combine results */
if (s)
@@ -3579,7 +3579,7 @@ update_check_ukey(backend *be, stmt **up
o = stmt_col(be, c->c, nu_tids);
s = stmt_join(be, o, h, 0, cmp_equal);
s = stmt_result(be, s, 0);
- s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1,
0), stmt_atom_lng(be, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1,
0, 1), stmt_atom_lng(be, 0), ne);
}
/* 2e stage: find out if updated are unique */
@@ -3608,9 +3608,9 @@ update_check_ukey(backend *be, stmt **up
/* (count(ss) <> sum(ss)) */
sum = sql_bind_aggr(sql->sa, sql->session->schema,
"sum", lng);
- ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0);
+ ssum = stmt_aggr(be, ss, NULL, NULL, sum, 1, 0, 1);
ssum = sql_Nop_(be, "ifthenelse", sql_unop_(be, NULL,
"isnull", ssum), stmt_atom_lng(be, 0), ssum, NULL);
- count_sum = stmt_binop(be, check_types(be,
tail_type(ssum), stmt_aggr(be, ss, NULL, NULL, cnt, 1, 0), type_equal), ssum,
ne);
+ count_sum = stmt_binop(be, check_types(be,
tail_type(ssum), stmt_aggr(be, ss, NULL, NULL, cnt, 1, 0, 1), type_equal),
ssum, ne);
/* combine results */
if (s)
@@ -3681,7 +3681,7 @@ update_check_fkey(backend *be, stmt **up
assert(0);
cur = stmt_col(be, c->c, dels);
}
- s = stmt_binop(be, stmt_aggr(be, idx_updates, NULL, NULL, cnt, 1, 0),
stmt_aggr(be, cur, NULL, NULL, cnt, 1, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, idx_updates, NULL, NULL, cnt, 1, 0,
1), stmt_aggr(be, cur, NULL, NULL, cnt, 1, 0, 1), ne);
for (m = k->columns->h; m; m = m->next) {
sql_kc *c = m->data;
@@ -3709,12 +3709,12 @@ update_check_fkey(backend *be, stmt **up
}
}
if (null) {
- cntnulls = stmt_aggr(be, null, NULL, NULL, cnt, 1, 0);
+ cntnulls = stmt_aggr(be, null, NULL, NULL, cnt, 1, 0, 1);
} else {
cntnulls = stmt_atom_lng(be, 0);
}
s = stmt_binop(be, s,
- stmt_binop(be, stmt_aggr(be, stmt_selectnil(be, idx_updates),
NULL, NULL, cnt, 1, 0), cntnulls , ne), or);
+ stmt_binop(be, stmt_aggr(be, stmt_selectnil(be, idx_updates),
NULL, NULL, cnt, 1, 0, 1), cntnulls , ne), or);
/* s should be empty */
msg = sa_message(sql->sa, "UPDATE: FOREIGN KEY constraint '%s.%s'
violated", k->t->base.name, k->base.name);
@@ -3772,14 +3772,14 @@ join_updated_pkey(backend *be, sql_key *
s = stmt_result(be, s, 0);
/* add missing nulls */
- cnteqjoin = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0);
+ cnteqjoin = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
if (nulls) {
sql_subfunc *add = sql_bind_func_result(sql->sa,
sql->session->schema, "sql_add", lng, lng, lng);
- cnteqjoin = stmt_binop(be, cnteqjoin, stmt_aggr(be, null, NULL,
NULL, cnt, 1, 0), add);
+ cnteqjoin = stmt_binop(be, cnteqjoin, stmt_aggr(be, null, NULL,
NULL, cnt, 1, 0, 1), add);
}
/* releqjoin.count <> updates[updcol].count */
- s = stmt_binop(be, cnteqjoin, stmt_aggr(be, rows, NULL, NULL, cnt, 1,
0), ne);
+ s = stmt_binop(be, cnteqjoin, stmt_aggr(be, rows, NULL, NULL, cnt, 1,
0, 1), ne);
/* s should be empty */
msg = sa_message(sql->sa, "UPDATE: FOREIGN KEY constraint '%s.%s'
violated", k->t->base.name, k->base.name);
@@ -4198,7 +4198,7 @@ sql_update_check_null(backend *be, sql_t
if (!(s->key && s->nrcols == 0)) {
s = stmt_selectnil(be, updates[c->colnr]);
- s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0);
+ s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
} else {
sql_subfunc *isnil = sql_bind_func(sql->sa,
sql->session->schema, "isnull", &c->type, NULL, F_FUNC);
@@ -4350,7 +4350,7 @@ rel2bin_update(backend *be, sql_rel *rel
list_prepend(l, ddl);
cnt = stmt_list(be, l);
} else {
- s = stmt_aggr(be, tids, NULL, NULL, sql_bind_aggr(sql->sa,
sql->session->schema, "count", NULL), 1, 0);
+ s = stmt_aggr(be, tids, NULL, NULL, sql_bind_aggr(sql->sa,
sql->session->schema, "count", NULL), 1, 0, 1);
cnt = s;
}
@@ -4463,7 +4463,7 @@ sql_delete_ukey(backend *be, stmt *utids
break;
default: /*RESTRICT*/
/* The overlap between deleted
primaries and foreign should be empty */
- s = stmt_binop(be, stmt_aggr(be, tids,
NULL, NULL, cnt, 1, 0), stmt_atom_lng(be, 0), ne);
+ s = stmt_binop(be, stmt_aggr(be, tids,
NULL, NULL, cnt, 1, 0, 1), stmt_atom_lng(be, 0), ne);
msg = sa_message(sql->sa, "DELETE:
FOREIGN KEY constraint '%s.%s' violated", fk->t->base.name, fk->base.name);
s = stmt_exception(be, s, msg, 00001);
list_prepend(l, s);
@@ -4535,7 +4535,7 @@ sql_delete(backend *be, sql_table *t, st
if (!sql_delete_triggers(be, t, v, 1))
return sql_error(sql, 02, "DELETE: triggers failed for table
'%s'", t->base.name);
if (rows)
- s = stmt_aggr(be, rows, NULL, NULL, sql_bind_aggr(sql->sa,
sql->session->schema, "count", NULL), 1, 0);
+ s = stmt_aggr(be, rows, NULL, NULL, sql_bind_aggr(sql->sa,
sql->session->schema, "count", NULL), 1, 0, 1);
return s;
}
@@ -4599,7 +4599,7 @@ rel2bin_output(backend *be, sql_rel *rel
}
list_append(slist, stmt_export(be, s, tsep, rsep, ssep, ns, fns));
if (s->type == st_list && ((stmt*)s->op4.lval->h->data)->nrcols != 0) {
- stmt *cnt = stmt_aggr(be, s->op4.lval->h->data, NULL, NULL,
sql_bind_aggr(sql->sa, sql->session->schema, "count", NULL), 1, 0);
+ stmt *cnt = stmt_aggr(be, s->op4.lval->h->data, NULL, NULL,
sql_bind_aggr(sql->sa, sql->session->schema, "count", NULL), 1, 0, 1);
return cnt;
} else {
return stmt_atom_lng(be, 1);
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
@@ -3225,32 +3225,40 @@ str
second_interval(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
lng *ret = getArgReference_lng(stk, pci, 0), r;
- int k = digits2ek(*getArgReference_int(stk, pci, 2)), scale = 0;
+ int k = digits2ek(*getArgReference_int(stk, pci, 2)), scale = 0, isnil
= 0;
(void) cntxt;
if (pci->argc > 3)
scale = *getArgReference_int(stk, pci, 3);
+ *ret = lng_nil;
switch (getArgType(mb, pci, 1)) {
case TYPE_bte:
r = stk->stk[getArg(pci, 1)].val.btval;
+ isnil = (stk->stk[getArg(pci, 1)].val.btval == bte_nil);
break;
case TYPE_sht:
r = stk->stk[getArg(pci, 1)].val.shval;
+ isnil = (stk->stk[getArg(pci, 1)].val.shval == sht_nil);
break;
case TYPE_int:
r = stk->stk[getArg(pci, 1)].val.ival;
+ isnil = (stk->stk[getArg(pci, 1)].val.ival == int_nil);
break;
case TYPE_lng:
r = stk->stk[getArg(pci, 1)].val.lval;
+ isnil = (stk->stk[getArg(pci, 1)].val.lval == lng_nil);
break;
#ifdef HAVE_HGE
case TYPE_hge:
r = (lng) stk->stk[getArg(pci, 1)].val.hval;
+ isnil = (stk->stk[getArg(pci, 1)].val.hval == hge_nil);
break;
#endif
default:
throw(ILLARG, "calc.sec_interval", "illegal argument");
}
+ if (isnil)
+ return MAL_SUCCEED;
switch (k) {
case iday:
r *= 24;
diff --git a/sql/backends/monet5/sql_statement.c
b/sql/backends/monet5/sql_statement.c
--- a/sql/backends/monet5/sql_statement.c
+++ b/sql/backends/monet5/sql_statement.c
@@ -2969,7 +2969,7 @@ stmt_func(backend *be, stmt *ops, const
}
stmt *
-stmt_aggr(backend *be, stmt *op1, stmt *grp, stmt *ext, sql_subaggr *op, int
reduce, int no_nil)
+stmt_aggr(backend *be, stmt *op1, stmt *grp, stmt *ext, sql_subaggr *op, int
reduce, int no_nil, int nil_if_empty)
{
MalBlkPtr mb = be->mb;
InstrPtr q = NULL;
@@ -3049,6 +3049,8 @@ stmt_aggr(backend *be, stmt *op1, stmt *
q = pushBit(mb, q, TRUE);
} else if (no_nil && strncmp(aggrfunc, "count", 5) == 0) {
q = pushBit(mb, q, no_nil);
+ } else if (!nil_if_empty && strncmp(aggrfunc, "sum", 3) == 0) {
+ q = pushBit(mb, q, FALSE);
}
if (q) {
stmt *s = stmt_create(be->mvc->sa, st_aggr);
diff --git a/sql/backends/monet5/sql_statement.h
b/sql/backends/monet5/sql_statement.h
--- a/sql/backends/monet5/sql_statement.h
+++ b/sql/backends/monet5/sql_statement.h
@@ -216,7 +216,7 @@ extern stmt *stmt_unop(backend *be, stmt
extern stmt *stmt_binop(backend *be, stmt *op1, stmt *op2, sql_subfunc *op);
extern stmt *stmt_Nop(backend *be, stmt *ops, sql_subfunc *op);
extern stmt *stmt_func(backend *be, stmt *ops, const char *name, sql_rel *imp,
int f_union);
-extern stmt *stmt_aggr(backend *be, stmt *op1, stmt *grp, stmt *ext,
sql_subaggr *op, int reduce, int no_nil);
+extern stmt *stmt_aggr(backend *be, stmt *op1, stmt *grp, stmt *ext,
sql_subaggr *op, int reduce, int no_nil, int nil_if_empty);
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
