Changeset: 1982862ae69b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1982862ae69b
Modified Files:
sql/server/rel_statistics.c
sql/test/BugTracker-2026/Tests/8001-select-count-where-having-bug.test
sql/test/BugTracker-2026/Tests/All
sql/test/rel-optimizers/Tests/const-aggr-elim.test
Branch: default
Log Message:
removed over eager optimization, even if aggregates skip nils and input has no
nil, expected count is > 0, aggregate could still return NULL (on empty inputs)
Solves issue # 8001
diffs (79 lines):
diff --git a/sql/server/rel_statistics.c b/sql/server/rel_statistics.c
--- a/sql/server/rel_statistics.c
+++ b/sql/server/rel_statistics.c
@@ -465,7 +465,6 @@ rel_propagate_statistics(visitor *v, sql
}
case e_aggr:
case e_func: {
- BUN lv;
sql_subfunc *f = e->f;
if (!f->func->s) {
@@ -482,10 +481,6 @@ rel_propagate_statistics(visitor *v, sql
if (look)
look(sql, e);
}
- /* for global aggregates with no semantics, if the left
relation has values, then the output won't be NULL */
- if (!is_semantics(e) && e->l && !have_nil(e->l) &&
- (e->type != e_aggr || (is_groupby(rel->op) &&
list_length(rel->r)) || ((lv = get_rel_count(rel->l)) != BUN_NONE && lv > 0)))
- set_has_no_nil(e);
/* set properties for global aggregates */
if (e->type == e_aggr && is_groupby(rel->op) &&
list_empty(rel->r)) {
if (!find_prop(e->p, PROP_NUNIQUES)) {
diff --git
a/sql/test/BugTracker-2026/Tests/8001-select-count-where-having-bug.test
b/sql/test/BugTracker-2026/Tests/8001-select-count-where-having-bug.test
--- a/sql/test/BugTracker-2026/Tests/8001-select-count-where-having-bug.test
+++ b/sql/test/BugTracker-2026/Tests/8001-select-count-where-having-bug.test
@@ -9,17 +9,16 @@ INSERT INTO t4 VALUES
(10, NULL),
(20, NULL)
--- Direct query over the empty filtered input.
+-- Direct query over the empty filtered input, avg(c1)==NULL and NULL <> 0.03
=> NULL.
query I nosort
SELECT COUNT(*) AS cnt
FROM t4
WHERE c10 IS NOT NULL
HAVING AVG(c1) <> 0.03
----
-0
-- CTAS form.
-statement ok rowcount 0
+statement ok
CREATE TABLE tt AS
SELECT *
FROM t4
@@ -30,7 +29,6 @@ SELECT COUNT(*) AS cnt
FROM tt
HAVING AVG(c1) <> 0.03
----
-0
statement ok
DROP TABLE IF EXISTS tt
diff --git a/sql/test/BugTracker-2026/Tests/All
b/sql/test/BugTracker-2026/Tests/All
--- a/sql/test/BugTracker-2026/Tests/All
+++ b/sql/test/BugTracker-2026/Tests/All
@@ -183,7 +183,7 @@ KNOWNFAIL?7997-recursive-cte-number-over
KNOWNFAIL?7998-scalar-subquery-error-msg-improvement
7999-not-not-expr-bug
KNOWNFAIL?8000-select-count-distinct-assert-failure
-KNOWNFAIL?8001-select-count-where-having-bug
+8001-select-count-where-having-bug
KNOWNFAIL?8002-schema-renaming-makes-seq-invalid-bug
KNOWNFAIL?8003-windowed-group_concat-ignores-orderby-bug
KNOWNFAIL?8004-nth_value-windowfunction-bug
diff --git a/sql/test/rel-optimizers/Tests/const-aggr-elim.test
b/sql/test/rel-optimizers/Tests/const-aggr-elim.test
--- a/sql/test/rel-optimizers/Tests/const-aggr-elim.test
+++ b/sql/test/rel-optimizers/Tests/const-aggr-elim.test
@@ -24,8 +24,8 @@ project (
| group by (
| | table ("sys"."generate_series"(int(31) "0", int(4) "10", int(1) "1"),
| | ) [ "%1"."value" ]
-| ) [ ] [ "sys"."avg" no nil (tinyint(2) "3") NOT NULL UNIQUE as "%2"."%2" ]
-) [ "%2"."%2" NOT NULL UNIQUE ]
+| ) [ ] [ "sys"."avg" no nil (tinyint(2) "3") UNIQUE as "%2"."%2" ]
+) [ "%2"."%2" UNIQUE ]
query R rowsort
SELECT avg(3) FROM baz GROUP BY b
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
