Changeset: 92b8635ee100 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/92b8635ee100
Modified Files:
sql/backends/monet5/rel_bin.c
sql/rel.txt
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/test/miscellaneous/Tests/group_by_all.test
Branch: Dec2025
Log Message:
fix for the default ascending order on order by all, issue 7805
fixed cardinality after group by all (solving other issue in 7805)
added more type casting of NULL and named placeholders, solving issue with
sqlalchemy tests
diffs (262 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
@@ -932,9 +932,10 @@ exp2bin_named_placeholders(backend *be,
if (list_empty(args))
return NULL;
- for (node *n = args->h; n; n = n->next, argc++) {
+ for (node *n = args->h, *m = be->mvc->params->h; n && m; n = n->next, m
= m->next, argc++) {
sql_exp *a = n->data;
- sql_subtype *t = exp_subtype(a);
+ sql_arg *p = m->data;
+ sql_subtype *t = &p->type;
stmt *s = exp_bin(be, a, NULL, NULL, NULL, NULL, NULL, NULL, 1,
0, 1);
InstrPtr q = newAssignment(be->mb);
diff --git a/sql/rel.txt b/sql/rel.txt
--- a/sql/rel.txt
+++ b/sql/rel.txt
@@ -138,7 +138,7 @@ e_cmp
cmp_filter = 6, filters ->l/r
are both lists
- cmp_in = 8, in list handling
->r is a list of values
+ cmp_in = 8, in list handling ->r is
a list of values
cmp_notin = 9, not in list handling ->r is
a list of values
cmp_conjunctive = 10, and handling
-> l is a list
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4193,7 +4193,7 @@ rel_cast(sql_query *query, sql_rel **rel
if (tpe->type->eclass == EC_DEC) {
sql_subtype *et = exp_subtype(e);
if (e->type == e_atom && !tpe->digits) {
- if (et->type->eclass == EC_NUM || et->type->eclass ==
EC_DEC) {
+ if (e->l && (et->type->eclass == EC_NUM ||
et->type->eclass == EC_DEC)) {
tpe->digits = atom_num_digits(e->l);
tpe = sql_bind_subtype(sql->sa, "decimal",
tpe->digits, et->scale);
} else if (EC_VARCHAR(et->type->eclass)) {
@@ -4657,7 +4657,8 @@ rel_order_by(sql_query *query, sql_rel *
assert(is_project(rel->op));
for(node *n = rel->exps->h; n; n = n->next) {
sql_exp *e = n->data;
- append(exps, exp_ref(sql, e));
+ append(exps, e=exp_ref(sql, e));
+ set_ascending(e);
}
return exps;
}
@@ -5993,9 +5994,12 @@ rel_select_exp(sql_query *query, sql_rel
*/
pexps = list_merge(pexps, exps_copy(sql, te), (fdup)NULL);
}
+ int card = inner->card;
if (rel && is_groupby(rel->op) && rel->flag) {
list *gbe = rel->r;
if (!list_empty(gbe)) {
+ inner->card = CARD_AGGR;
+ card = CARD_MULTI;
for (node *n=gbe->h; n; n = n->next) {
sql_exp *e = n->data;
if (rel->flag == 1 && is_atom(e->type) &&
!e->alias.name) {
@@ -6020,6 +6024,7 @@ rel_select_exp(sql_query *query, sql_rel
set_processed(rel);
}
rel = rel_project(sql->sa, rel, pexps);
+ rel->card = card;
rel = rel_having_limits_nodes(query, rel, sn, ek, group_totals);
return rel;
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -2385,6 +2385,32 @@ rewrite_empty_project(visitor *v, sql_re
#define is_division(sf) (strcmp(sf->func->base.name, "sql_div") == 0)
#define is_multiplication(sf) (strcmp(sf->func->base.name, "sql_mul") == 0)
+static sql_exp *
+exp_atom_set_type_null(visitor *v, sql_exp *el, sql_subtype *t)
+{
+ if (el->type == e_atom && !el->f && !el->r) {
+ sql_subtype *f = exp_subtype(el);
+ if ((!f || f->type->eclass == EC_ANY) && !el->l) { /*
parameter, set type, or return ERR?? */
+ sql_arg *a = sql_bind_paramnr(v->sql, el->flag);
+ if (!a->type.type || a->type.type->eclass == EC_ANY) {
+ a->type = *t;
+ el->tpe = a->type;
+ v->changes++;
+ return el;
+ }
+ } else if ((!f || f->type->eclass == EC_ANY) && el->l) { /*
NULL? */
+ atom *a = el->l;
+ if (atom_null(a)) {
+ atom_cast_inplace(v->sql->sa, a, t);
+ el->tpe = *t;
+ v->changes++;
+ return el;
+ }
+ }
+ }
+ return NULL;
+}
+
static inline sql_exp *
exp_physical_types(visitor *v, sql_rel *rel, sql_exp *e, int depth)
{
@@ -2571,7 +2597,54 @@ exp_reset_card_and_freevar_set_physical_
}
static sql_exp *
-exp_set_type(mvc *sql, sql_exp *te, sql_exp *e)
+exp_set_type(visitor *v, sql_rel *rel, sql_exp *e, int depth)
+{
+ (void)rel;
+ (void)depth;
+ if (!e)
+ return e;
+
+ if (e->type == e_atom && !e->f && !e->l && !e->r) {
+ sql_subtype *t = exp_subtype(e);
+ if ((!t || t->type->eclass == EC_ANY)) { /* parameter, set
type, or return ERR?? */
+ sql_arg *a = sql_bind_paramnr(v->sql, e->flag);
+ if (!a->type.type)
+ return sql_error(v->sql, 10, SQLSTATE(42000)
"Could not determine type for argument number %d", e->flag+1);
+ e->tpe = a->type;
+ }
+ } else if (e->type == e_cmp && !e->f) {
+ if (e->flag == cmp_in || e->flag == cmp_notin) {
+ sql_subtype *lt = exp_subtype(e->l);
+ e->r = exps_check_type(v->sql, lt, e->r);
+ } else if (e->flag == cmp_equal || e->flag == cmp_notequal) {
+ sql_subtype *lt = exp_subtype(e->l);
+ sql_subtype *rt = exp_subtype(e->r);
+ if (!lt || lt->type->eclass == EC_ANY) {
+ sql_exp *ne = exp_atom_set_type_null(v, e->l,
rt);
+ if (ne)
+ e->l = ne;
+ } else if (!rt || rt->type->eclass == EC_ANY) {
+ sql_exp *ne = exp_atom_set_type_null(v, e->r,
lt);
+ if (ne)
+ e->r = ne;
+ }
+ }
+ } else if (e->type == e_convert && !e->f) {
+ sql_exp *el = e->l;
+ sql_subtype *t = exp_totype(e);
+ if (el->type == e_atom && !el->f && !el->l && !el->r) {
+ el = exp_atom_set_type_null(v, el, t);
+ if (el) {
+ e->l = el;
+ return e;
+ }
+ }
+ }
+ return e;
+}
+
+static sql_exp *
+exp_set_type_as(mvc *sql, sql_exp *te, sql_exp *e)
{
if (te->type == e_convert) {
if (e->type == e_column) {
@@ -2603,7 +2676,7 @@ rel_set_type(visitor *v, sql_rel *rel)
sql_subtype *t = exp_subtype(e);
if (t && !t->type->localtype) {
- n->data = exp_set_type(v->sql,
m->data, e);
+ n->data =
exp_set_type_as(v->sql, m->data, e);
clear_hash = true;
}
}
@@ -2625,7 +2698,7 @@ rel_set_type(visitor *v, sql_rel *rel)
sql_subtype *t = exp_subtype(e);
if (t && !t->type->localtype) {
- n->data = exp_set_type(v->sql,
m->data, e);
+ n->data =
exp_set_type_as(v->sql, m->data, e);
clear_hash = true;
}
}
@@ -2676,6 +2749,7 @@ rel_set_type(visitor *v, sql_rel *rel)
} else if (te->type == e_atom &&
!te->f) {
sql_subtype *t =
exp_subtype(te);
if (!t && !te->l && !te->r) {
/* parameter, set type, or return ERR?? */
+ assert(0);
sql_arg *a =
sql_bind_paramnr(v->sql, te->flag);
if (!a->type.type)
return
sql_error(v->sql, 10, SQLSTATE(42000) "Could not determine type for argument
number %d", te->flag+1);
@@ -4652,7 +4726,7 @@ rel_simplify_exp_and_rank(visitor *v, sq
}
static inline sql_rel *
-run_exp_rewriter(visitor *v, sql_rel *rel, exp_rewrite_fptr rewriter, bool
direction, const char *name)
+run_exp_rewriter(visitor *v, sql_rel *rel, exp_rewrite_fptr rewriter, bool
direction, const char *name, bool continue_on_changes)
{
(void)name;
v->changes = 0;
@@ -4665,8 +4739,13 @@ run_exp_rewriter(visitor *v, sql_rel *re
return rel;
#else
*/
- return rel_exp_visitor_bottomup(v, rel, rewriter, direction);
+ rel = rel_exp_visitor_bottomup(v, rel, rewriter, direction);
+ if (continue_on_changes) {
+ v->changes = 0;
+ rel = rel_exp_visitor_bottomup(v, rel, rewriter, direction);
+ }
//#endif
+ return rel;
}
static inline sql_rel *
@@ -4692,17 +4771,18 @@ rel_unnest(mvc *sql, sql_rel *rel)
{
visitor v = { .sql = sql };
- rel = run_exp_rewriter(&v, rel, &rel_simplify_exp_and_rank, false,
"simplify_exp_and_rank");
+ rel = run_exp_rewriter(&v, rel, &rel_simplify_exp_and_rank, false,
"simplify_exp_and_rank", false);
rel = run_rel_rewriter(&v, rel, &rel_unnest_simplify,
"unnest_simplify");
- rel = run_exp_rewriter(&v, rel, &rewrite_complex, true,
"rewrite_complex");
- rel = run_exp_rewriter(&v, rel, &rewrite_ifthenelse, false,
"rewrite_ifthenelse"); /* add isnull handling */
- rel = run_exp_rewriter(&v, rel, &rewrite_exp_rel, true,
"rewrite_exp_rel");
+ rel = run_exp_rewriter(&v, rel, &rewrite_complex, true,
"rewrite_complex", false);
+ rel = run_exp_rewriter(&v, rel, &rewrite_ifthenelse, false,
"rewrite_ifthenelse", false); /* add isnull handling */
+ rel = run_exp_rewriter(&v, rel, &rewrite_exp_rel, true,
"rewrite_exp_rel", false);
rel = run_rel_rewriter(&v, rel, &rel_unnest_comparison_rewriters,
"unnest_comparison_rewriters");
rel = run_rel_rewriter(&v, rel, &_rel_unnest, "unnest");
rel = run_rel_rewriter(&v, rel, &rewrite_fix_count, "fix_count");
/* fix count inside a left join (adds a project (if (cnt IS null) then (0) else
(cnt)) */
rel = run_rel_rewriter(&v, rel, &rel_unnest_projects,
"unnest_projects");
- rel = run_exp_rewriter(&v, rel,
&exp_reset_card_and_freevar_set_physical_type, false,
"exp_reset_card_and_freevar_set_physical_type");
+ rel = run_exp_rewriter(&v, rel,
&exp_reset_card_and_freevar_set_physical_type, false,
"exp_reset_card_and_freevar_set_physical_type", false);
+ rel = run_exp_rewriter(&v, rel, &exp_set_type, false, "exp_set_type",
true);
rel = rel_visitor_topdown(&v, rel, &rel_set_type);
return rel;
}
diff --git a/sql/test/miscellaneous/Tests/group_by_all.test
b/sql/test/miscellaneous/Tests/group_by_all.test
--- a/sql/test/miscellaneous/Tests/group_by_all.test
+++ b/sql/test/miscellaneous/Tests/group_by_all.test
@@ -32,17 +32,17 @@ SELECT g, SUM(i) FROM integers GROUP BY
query II nosort
SELECT g, SUM(i) FROM integers GROUP BY 1 ORDER BY ALL
----
-1
+0
3
-0
+1
3
query II nosort
SELECT g, SUM(i) FROM integers GROUP BY 1 ORDER BY *
----
-1
+0
3
-0
+1
3
# multiple aggregates
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
