Changeset: 0bcaf50aff65 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/0bcaf50aff65
Added Files:
sql/test/BugTracker-2025/Tests/7653_incorrect_join_results.test
Modified Files:
monetdb5/modules/atoms/Tests/xml10.maltest
monetdb5/modules/atoms/batxml.c
sql/server/rel_exp.c
sql/server/rel_select.c
sql/server/rel_statistics.c
sql/test/2024/Tests/returning.test
sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.test
sql/test/BugTracker-2015/Tests/quantile_function_resolution.Bug-3773.test
sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-having-avg.Bug-6428.test
sql/test/BugTracker-2025/Tests/All
sql/test/Tests/select_window_pushdown.test
sql/test/miscellaneous/Tests/simple_plans.test
sql/test/miscellaneous/Tests/simple_selects.test
sql/test/rel-optimizers/Tests/groupby-cse.test
sql/test/rel-optimizers/Tests/remote-replica-plan.test
sql/test/sql_xml/Tests/funcs.test
Branch: default
Log Message:
fix for bug 7653, make sure unique-ness isn't propagated into the relational
tree incorrectly.
diffs (truncated from 440 to 300 lines):
diff --git a/monetdb5/modules/atoms/Tests/xml10.maltest
b/monetdb5/modules/atoms/Tests/xml10.maltest
--- a/monetdb5/modules/atoms/Tests/xml10.maltest
+++ b/monetdb5/modules/atoms/Tests/xml10.maltest
@@ -65,7 +65,7 @@ statement error
io.print(l)
statement ok
-ag:= xml.subaggr(te,g,e,true)
+ag:= aggr.subxmlaggr(te,g,e,true)
statement error
io.print(ag)
@@ -93,7 +93,7 @@ io.print("book construction")
"book construction"
statement ok
-be:= xml.aggr(ae)
+be:= aggr.xmlaggr(ae)
query T rowsort
io.print(be)
diff --git a/monetdb5/modules/atoms/batxml.c b/monetdb5/modules/atoms/batxml.c
--- a/monetdb5/modules/atoms/batxml.c
+++ b/monetdb5/modules/atoms/batxml.c
@@ -1183,10 +1183,10 @@ BATXMLgroup(xml *ret, const bat *bid)
const char *err = NULL;
if (buf == NULL)
- throw(MAL, "xml.aggr", SQLSTATE(HY013) MAL_MALLOC_FAIL);
+ throw(MAL, "aggr.xmlaggr", SQLSTATE(HY013) MAL_MALLOC_FAIL);
if ((b = BATdescriptor(*bid)) == NULL) {
GDKfree(buf);
- throw(MAL, "xml.aggr", SQLSTATE(HY002) RUNTIME_OBJECT_MISSING);
+ throw(MAL, "aggr.xmlaggr", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
}
strcpy(buf, str_nil);
@@ -1234,7 +1234,7 @@ BATXMLgroup(xml *ret, const bat *bid)
BBPunfix(b->batCacheid);
if (buf != NULL)
GDKfree(buf);
- throw(MAL, "xml.aggr", "%s", err);
+ throw(MAL, "aggr.xmlaggr", "%s", err);
}
static const char *
@@ -1467,7 +1467,7 @@ AGGRsubxmlcand(bat *retval, const bat *b
BBPreclaim(b);
BBPreclaim(g);
BBPreclaim(e);
- throw(MAL, "aggr.subxml", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
+ throw(MAL, "aggr.subxmlaggr", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
}
if (sid && !is_bat_nil(*sid)) {
s = BATdescriptor(*sid);
@@ -1475,7 +1475,7 @@ AGGRsubxmlcand(bat *retval, const bat *b
BBPunfix(b->batCacheid);
BBPreclaim(g);
BBPreclaim(e);
- throw(MAL, "aggr.subxml", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
+ throw(MAL, "aggr.subxmlaggr", SQLSTATE(HY002)
RUNTIME_OBJECT_MISSING);
}
} else {
s = NULL;
@@ -1486,7 +1486,7 @@ AGGRsubxmlcand(bat *retval, const bat *b
BBPreclaim(e);
BBPreclaim(s);
if (err !=NULL)
- throw(MAL, "aggr.subxml", "%s", err);
+ throw(MAL, "aggr.subxmlaggr", "%s", err);
*retval = bn->batCacheid;
BBPkeepref(bn);
@@ -1743,9 +1743,9 @@ mel_func batxml_init_funcs[] = {
pattern("batxml", "forest", BATXMLforest, false, "Construct an element
list.", args(1,2, batarg("",xml),batvararg("val",xml))),
command("batxml", "root", BATXMLroot, false, "Construct the root nodes.",
args(1,4,
batarg("",xml),batarg("val",xml),arg("version",str),arg("standalone",str))),
command("batxml", "isdocument", BATXMLisdocument, false, "Validate the string
as a XML document.", args(1,2, batarg("",bit),batarg("val",str))),
- command("xml", "aggr", BATXMLgroup, false, "Aggregate the XML values.",
args(1,2, arg("",xml),batarg("val",xml))),
- command("xml", "subaggr", AGGRsubxml, false, "Grouped aggregation of XML
values.", args(1,5,
batarg("",xml),batarg("val",xml),batarg("g",oid),batargany("e",1),arg("skip_nils",bit))),
- command("xml", "subaggr", AGGRsubxmlcand, false, "Grouped aggregation of XML
values with candidates list.", args(1,6,
batarg("",xml),batarg("val",xml),batarg("g",oid),batargany("e",1),batarg("s",oid),arg("skip_nils",bit))),
+ command("aggr", "xmlaggr", BATXMLgroup, false, "Aggregate the XML values.",
args(1,2, arg("",xml),batarg("val",xml))),
+ command("aggr", "subxmlaggr", AGGRsubxml, false, "Grouped aggregation of XML
values.", args(1,5,
batarg("",xml),batarg("val",xml),batarg("g",oid),batargany("e",1),arg("skip_nils",bit))),
+ command("aggr", "subxmlaggr", AGGRsubxmlcand, false, "Grouped aggregation of
XML values with candidates list.", args(1,6,
batarg("",xml),batarg("val",xml),batarg("g",oid),batargany("e",1),batarg("s",oid),arg("skip_nils",bit))),
command("batcalc", "xml", BATXMLstr2xml, false, "", args(1,2,
batarg("",xml),batarg("src",str))),
{ .imp=NULL }
};
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -777,8 +777,10 @@ exp_propagate(allocator *sa, sql_exp *ne
set_has_no_nil(ne);
if (has_nil(oe))
set_has_nil(ne);
+ /*
if (is_unique(oe))
set_unique(ne);
+ */
if (is_basecol(oe))
set_basecol(ne);
ne->p = prop_copy(sa, oe->p);
@@ -806,8 +808,10 @@ exp_ref_by_label(allocator *sa, sql_exp
set_has_no_nil(e);
if (has_nil(o))
set_has_nil(e);
+ /*
if (is_unique(o))
set_unique(e);
+ */
if (is_intern(o))
set_intern(e);
return exp_propagate(sa, e, o);
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
@@ -1585,6 +1585,7 @@ rel_column_ref(sql_query *query, sql_rel
else
exp->card = CARD_ATOM;
set_freevar(exp, i);
+ set_not_unique(exp);
if (!is_sql_where(of) && !is_sql_aggr(of) &&
!is_sql_aggr(f) && !outer->grouped)
set_outer(outer);
}
@@ -1683,6 +1684,7 @@ rel_column_ref(sql_query *query, sql_rel
else
exp->card = CARD_ATOM;
set_freevar(exp, i);
+ set_not_unique(exp);
if (!is_sql_where(of) && !is_sql_aggr(of) &&
!is_sql_aggr(f) && !outer->grouped)
set_outer(outer);
}
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
@@ -408,7 +408,7 @@ rel_propagate_statistics(visitor *v, sql
case op_project:
case op_groupby: {
sql_exp *found =
rel_propagate_column_ref_statistics(sql, rel->l, e); /* labels may be found on
the same projection, ugh */
- if (!found && is_simple_project(rel->op))
+ if (!found && (is_simple_project(rel->op) || rel->op ==
op_groupby))
(void) rel_propagate_column_ref_statistics(sql,
rel, e);
break;
}
@@ -1375,6 +1375,23 @@ rel_groupby_order(visitor *v, sql_rel *r
int *scores = NULL;
sql_exp **exps = NULL;
+ if (v->parent && !is_topn(v->parent->op) && !is_sample(v->parent->op) &&
+ is_groupby(rel->op) && exps_unique(v->sql, rel,
rel->r)) {
+ bool found = false;
+ for(node *n = rel->exps->h; n && !found; n = n->next) {
+ sql_exp *e = n->data;
+ if (e->type == e_aggr)
+ found = 1;
+ }
+ if (!found) {
+ /* no need to groupby on unique values */
+ rel->exps= list_merge(rel->r, rel->exps, (fdup)NULL);
+ rel->op = op_project;
+ rel->r = NULL;
+ return rel;
+ }
+ }
+
if (is_groupby(rel->op) && list_length(rel->r) > 1) {
node *n;
list *gbe = rel->r;
@@ -1421,7 +1438,7 @@ rel_final_optimization_loop_(visitor *v,
rel_distinct_project2groupby, rel_simplify_predicates,
rel_simplify_math,
rel_distinct_aggregate_on_unique_values */
- rel = rel_groupby_order(v, rel);
+ rel = rel_groupby_order(v, rel); /* also removes groupby's on unique
cols */
return rel;
}
diff --git a/sql/test/2024/Tests/returning.test
b/sql/test/2024/Tests/returning.test
--- a/sql/test/2024/Tests/returning.test
+++ b/sql/test/2024/Tests/returning.test
@@ -195,7 +195,7 @@ select (
) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE MIN "1" MAX "3"
NUNIQUES 3.000000) UNIQUE NUNIQUES 1.000000 as "%1"."%1" ] COUNT 1
) [ "%1"."%1" UNIQUE NUNIQUES 1.000000 ] COUNT 1')
----
-@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@group by (@update(@& REF 1
@project (@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10")
]@) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE)
NOT NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE ]@) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE) UNIQUE as
"%1"."%1" ]@) [ "%1"."%1" UNIQUE ]
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@group by (@update(@& REF 1
@project (@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10")
]@) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE)
NOT NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL
]@) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE) UNIQUE as "%1"."%1"
]@) [ "%1"."%1" UNIQUE ]
# delete from foo where j > 10 returning sum(i)
query T nosort
@@ -227,7 +227,7 @@ table("sys"."foo") [ "foo"."i" NOT NULL
)
) [ "foo"."i" NOT NULL NUNIQUES 3.000000 MAX "3" MIN "1" ]')
----
-@project (@insert(@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j"
NOT NULL, "foo"."%TID%" NOT NULL UNIQUE ]@ [ [ int(31) NULL, int(31) NULL ] as
"%4"."%4", [ int(31) "40", int(31) "50" ] as "%1"."%1" ]@) [ "%4"."%4" as
"foo"."i", "%1"."%1" as "foo"."j" ]@) [ "foo"."i" NOT NULL UNIQUE ]
+@project (@insert(@table("sys"."foo") [ "foo"."i" NOT NULL, "foo"."j" NOT
NULL, "foo"."%TID%" NOT NULL UNIQUE ]@ [ [ int(31) NULL, int(31) NULL ] as
"%4"."%4", [ int(31) "40", int(31) "50" ] as "%1"."%1" ]@) [ "%4"."%4" as
"foo"."i", "%1"."%1" as "foo"."j" ]@) [ "foo"."i" NOT NULL ]
# update foo set j = -j where j > 10 returning i
query T nosort
@@ -245,7 +245,7 @@ select (
) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL ]
) [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES 3.000000 ]')
----
-@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@update(@& REF 1 @project
(@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10") ]@) [
"foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE) NOT
NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE ]@) [ "foo"."i" NOT NULL UNIQUE ]
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@update(@& REF 1 @project
(@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10") ]@) [
"foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE) NOT
NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL ]@) [
"foo"."i" NOT NULL UNIQUE ]
# delete from foo where j > 10 returning i
query T nosort
diff --git
a/sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.test
b/sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.test
--- a/sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.test
+++ b/sql/test/BugTracker-2015/Tests/crash_in_reduce_groupby.Bug-3818.test
@@ -28,8 +28,8 @@ GROUP BY cods, elrik, ether, jaelen, sor
project (
| group by (
| | table("sys"."t2a") [ "t2a"."tib0" NOT NULL UNIQUE ]
-| ) [ tinyint(1) "0" as "cods" ] [ "cods" NOT NULL, "cods" NOT NULL as
"elrik", "cods" NOT NULL as "ether", "cods" NOT NULL as "jaelen", "cods" NOT
NULL as "sora", "sys"."min" no nil ("t2a"."tib0" NOT NULL UNIQUE) NOT NULL as
"%1"."%1" ]
-) [ "cods" NOT NULL UNIQUE, "elrik" NOT NULL, "ether" NOT NULL, "jaelen" NOT
NULL, "sora" NOT NULL, "%1"."%1" NOT NULL ]
+| ) [ tinyint(1) "0" as "cods" ] [ "cods" NOT NULL UNIQUE, "cods" NOT NULL
UNIQUE as "elrik", "cods" NOT NULL UNIQUE as "ether", "cods" NOT NULL UNIQUE as
"jaelen", "cods" NOT NULL UNIQUE as "sora", "sys"."min" no nil ("t2a"."tib0"
NOT NULL UNIQUE) NOT NULL as "%1"."%1" ]
+) [ "cods" NOT NULL UNIQUE, "elrik" NOT NULL UNIQUE, "ether" NOT NULL UNIQUE,
"jaelen" NOT NULL UNIQUE, "sora" NOT NULL UNIQUE, "%1"."%1" NOT NULL ]
statement ok
drop table t2a
diff --git
a/sql/test/BugTracker-2015/Tests/quantile_function_resolution.Bug-3773.test
b/sql/test/BugTracker-2015/Tests/quantile_function_resolution.Bug-3773.test
--- a/sql/test/BugTracker-2015/Tests/quantile_function_resolution.Bug-3773.test
+++ b/sql/test/BugTracker-2015/Tests/quantile_function_resolution.Bug-3773.test
@@ -11,8 +11,8 @@ project (
| group by (
| | project (
| | | table("sys"."x") [ "x"."y" NOT NULL UNIQUE ]
-| | ) [ "x"."y" NOT NULL UNIQUE ] [ "x"."y" ASC NOT NULL UNIQUE ]
-| ) [ ] [ "sys"."quantile" no nil ("x"."y" ASC NOT NULL UNIQUE, double(53)
"0") UNIQUE as "%1"."%1" ]
+| | ) [ "x"."y" NOT NULL ] [ "x"."y" ASC NOT NULL UNIQUE ]
+| ) [ ] [ "sys"."quantile" no nil ("x"."y" ASC NOT NULL, double(53) "0")
UNIQUE as "%1"."%1" ]
) [ "%1"."%1" UNIQUE ]
query T nosort
@@ -22,8 +22,8 @@ project (
| group by (
| | project (
| | | table("sys"."x") [ "x"."y" NOT NULL UNIQUE ]
-| | ) [ "x"."y" NOT NULL UNIQUE ] [ "x"."y" ASC NOT NULL UNIQUE ]
-| ) [ ] [ "sys"."quantile" no nil ("x"."y" ASC NOT NULL UNIQUE, double(53)
"0") UNIQUE as "%1"."%1" ]
+| | ) [ "x"."y" NOT NULL ] [ "x"."y" ASC NOT NULL UNIQUE ]
+| ) [ ] [ "sys"."quantile" no nil ("x"."y" ASC NOT NULL, double(53) "0")
UNIQUE as "%1"."%1" ]
) [ "%1"."%1" UNIQUE ]
statement ok
diff --git
a/sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-having-avg.Bug-6428.test
b/sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-having-avg.Bug-6428.test
---
a/sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-having-avg.Bug-6428.test
+++
b/sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-having-avg.Bug-6428.test
@@ -17,9 +17,11 @@ PLAN SELECT - col0 AS col1 FROM tab0 AS
project (
| select (
| | group by (
-| | | select (
-| | | | table("sys"."tab0") [ "tab0"."col0" UNIQUE as "cor0"."col0",
"tab0"."col1" UNIQUE as "cor0"."col1" ]
-| | | ) [ ("cor0"."col0" UNIQUE) * = (int(31) NULL) ]
+| | | project (
+| | | | select (
+| | | | | table("sys"."tab0") [ "tab0"."col0" UNIQUE as "cor0"."col0",
"tab0"."col1" UNIQUE as "cor0"."col1" ]
+| | | | ) [ ("cor0"."col0" UNIQUE) * = (int(31) NULL) ]
+| | | ) [ "cor0"."col1" UNIQUE, "cor0"."col0" UNIQUE, "cor0"."col1" UNIQUE,
"cor0"."col0" UNIQUE ]
| | ) [ "cor0"."col1" UNIQUE, "cor0"."col0" UNIQUE ] [ "cor0"."col1" UNIQUE,
"cor0"."col0" UNIQUE, "sys"."avg" no nil ("cor0"."col0" UNIQUE) as "%1"."%1" ]
| ) [ ("sys"."sql_add"(double(53)["sys"."sql_neg"("cor0"."col1" UNIQUE)],
"%1"."%1")) * != (double(53) NULL) ]
) [ "sys"."sql_neg"("cor0"."col0" UNIQUE) as "col1" ]
diff --git a/sql/test/BugTracker-2025/Tests/7653_incorrect_join_results.test
b/sql/test/BugTracker-2025/Tests/7653_incorrect_join_results.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7653_incorrect_join_results.test
@@ -0,0 +1,49 @@
+statement ok
+CREATE TABLE t0 (c0 INT , c1 INT)
+
+statement ok
+INSERT INTO t0(c1) VALUES(1), (2), (3)
+
+query III
+SELECT * FROM t0 JOIN LATERAL (SELECT CASE WHEN 0.7 <> ALL(SELECT t0.c1 FROM
t0 AS sub1) THEN 2 ELSE 1 END FROM t0 AS sub2) AS subQuery(col_1) ON NOT ((
EXISTS (SELECT 1)) AND 0);
+----
+NULL
+1
+2
+NULL
+1
+2
+NULL
+1
+2
+NULL
+2
+2
+NULL
+2
+2
+NULL
+2
+2
+NULL
+3
+2
+NULL
+3
+2
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
