Changeset: 06e2070cd7f2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/06e2070cd7f2
Added Files:
sql/test/BugTracker-2022/Tests/having-clauses.Bug-7278.test
sql/test/BugTracker-2022/Tests/having-filters.Bug
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2022/Tests/All
Branch: Jan2022
Log Message:
Added test and fix for bug #7278 When binding a column under an aggregate, look
for possible select relations generated by having clauses
diffs (228 lines):
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
@@ -1147,9 +1147,14 @@ rel_column_ref(sql_query *query, sql_rel
if (!exp && inner)
if (!(exp = rel_bind_column(sql, inner, name, f, 0)) &&
sql->session->status == -ERR_AMBIGUOUS)
return NULL;
- if (!exp && inner && is_sql_aggr(f) && is_groupby(inner->op))
- if (!(exp = rel_bind_column(sql, inner->l, name, f, 0))
&& sql->session->status == -ERR_AMBIGUOUS)
+ if (!exp && inner && is_sql_aggr(f) && (is_groupby(inner->op)
|| is_select(inner->op))) {
+ /* if inner is selection, ie having clause, get the
left relation to reach group by */
+ sql_rel *gp = inner;
+ while (gp && is_select(gp->op))
+ gp = gp->l;
+ if (gp && gp->l && !(exp = rel_bind_column(sql, gp->l,
name, f, 0)) && sql->session->status == -ERR_AMBIGUOUS)
return NULL;
+ }
if (!exp && query && query_has_outer(query)) {
int i;
sql_rel *outer;
@@ -1233,9 +1238,14 @@ rel_column_ref(sql_query *query, sql_rel
if (!exp && rel && inner)
if (!(exp = rel_bind_column2(sql, inner, tname, cname,
f)) && sql->session->status == -ERR_AMBIGUOUS)
return NULL;
- if (!exp && inner && is_sql_aggr(f) && is_groupby(inner->op))
- if (!(exp = rel_bind_column2(sql, inner->l, tname,
cname, f)) && sql->session->status == -ERR_AMBIGUOUS)
+ if (!exp && inner && is_sql_aggr(f) && (is_groupby(inner->op)
|| is_select(inner->op))) {
+ /* if inner is selection, ie having clause, get the
left relation to reach group by */
+ sql_rel *gp = inner;
+ while (gp && is_select(gp->op))
+ gp = gp->l;
+ if (gp && gp->l && !(exp = rel_bind_column2(sql, gp->l,
tname, cname, f)) && sql->session->status == -ERR_AMBIGUOUS)
return NULL;
+ }
if (!exp && query && query_has_outer(query)) {
int i;
sql_rel *outer;
diff --git a/sql/test/BugTracker-2022/Tests/All
b/sql/test/BugTracker-2022/Tests/All
--- a/sql/test/BugTracker-2022/Tests/All
+++ b/sql/test/BugTracker-2022/Tests/All
@@ -5,3 +5,4 @@ recreate-view.Bug-7241
HAVE_LIBPY3?python-udf-inside-udf.Bug-7252
pkey-restart.Bug-7263
delete-update.Bug-7267
+having-clauses.Bug-7278
diff --git a/sql/test/BugTracker-2022/Tests/having-clauses.Bug-7278.test
b/sql/test/BugTracker-2022/Tests/having-clauses.Bug-7278.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2022/Tests/having-clauses.Bug-7278.test
@@ -0,0 +1,176 @@
+statement ok
+START TRANSACTION
+
+statement ok
+create table x (x int, y int, z int)
+
+statement ok rowcount 3
+insert into x values (1,1,1),(2,2,2),(3,3,3)
+
+query I rowsort
+select y from x group by y having count(z) > 1 and count(x) > 1
+----
+
+query I nosort
+select y from x group by y order by count(z) > 1 and count(x) > 1
+----
+1
+2
+3
+
+query I nosort
+select y from x group by y order by count(z), count(x)
+----
+1
+2
+3
+
+query I nosort
+select y from x group by y having count(z) > 1 and count(x) > 1 order by
count(z), count(x)
+----
+
+query I nosort
+select y from x group by y having count(z) > 1 and count(x) > 1 order by
count(z) > 1 and count(x) > 1
+----
+
+query I rowsort
+select count(y) from x group by y having count(z) > 1 and count(x) > 1
+----
+
+query I nosort
+select count(y) from x group by y order by count(z) > 1 and count(x) > 1
+----
+1
+1
+1
+
+query I nosort
+select count(y) from x group by y order by count(z), count(x)
+----
+1
+1
+1
+
+query I nosort
+select count(y) from x group by y having count(z) > 1 and count(x) > 1 order
by count(z), count(x)
+----
+
+query I nosort
+select count(y) from x group by y having count(z) > 1 and count(x) > 1 order
by count(z) > 1 and count(x) > 1
+----
+
+query I rowsort
+select count(x) from x group by y having count(z) > 1 and count(x) > 1
+----
+
+query II nosort
+select count(x), count(z) from x group by y order by count(z) > 1 and count(x)
> 1
+----
+1
+1
+1
+1
+1
+1
+
+query II nosort
+select count(x), count(z) from x group by y order by count(z), count(x)
+----
+1
+1
+1
+1
+1
+1
+
+query II nosort
+select count(x), count(z) from x group by y having count(z) > 1 and count(x) >
1 order by count(z), count(x)
+----
+
+query II nosort
+select count(x), count(z) from x group by y having count(z) > 1 and count(x) >
1 order by count(z) > 1 and count(x) > 1
+----
+
+query I rowsort
+select 1 from x having count(z) > 1 and count(x) > 1
+----
+1
+
+query I nosort
+select 1 from x order by count(z) > 1 and count(x) > 1
+----
+1
+
+query I nosort
+select 1 from x order by count(z), count(x)
+----
+1
+
+query I nosort
+select 1 from x having count(z) > 1 and count(x) > 1 order by count(z),
count(x)
+----
+1
+
+query I nosort
+select 1 from x having count(z) > 1 and count(x) > 1 order by count(z) > 1 and
count(x) > 1
+----
+1
+
+query I rowsort
+select count(y) from x having count(z) > 1 and count(x) > 1
+----
+3
+
+query I nosort
+select count(y) from x order by count(z) > 1 and count(x) > 1
+----
+3
+
+query I nosort
+select count(y) from x order by count(z), count(x)
+----
+3
+
+query I nosort
+select count(y) from x having count(z) > 1 and count(x) > 1 order by count(z),
count(x)
+----
+3
+
+query I nosort
+select count(y) from x having count(z) > 1 and count(x) > 1 order by count(z)
> 1 and count(x) > 1
+----
+3
+
+query II rowsort
+select count(x), count(z) from x having count(z) > 1 and count(x) > 1
+----
+3
+3
+
+query II nosort
+select count(x), count(z) from x order by count(z) > 1 and count(x) > 1
+----
+3
+3
+
+query II nosort
+select count(x), count(z) from x order by count(z), count(x)
+----
+3
+3
+
+query II nosort
+select count(x), count(z) from x having count(z) > 1 and count(x) > 1 order by
count(z), count(x)
+----
+3
+3
+
+query II nosort
+select count(x), count(z) from x having count(z) > 1 and count(x) > 1 order by
count(z) > 1 and count(x) > 1
+----
+3
+3
+
+statement ok
+ROLLBACK
+
diff --git a/sql/test/BugTracker-2022/Tests/having-filters.Bug
b/sql/test/BugTracker-2022/Tests/having-filters.Bug
new file mode 100644
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
