Changeset: 1fc3c8aa1f5c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1fc3c8aa1f5c
Added Files:
sql/test/BugTracker-2021/Tests/distinct-with-orderby.Bug-7202.test
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2021/Tests/All
sql/test/analytics/Tests/analytics10.test
sql/test/emptydb/Tests/check.SQL.py
sql/test/miscellaneous/Tests/groupby_expressions.test
sql/test/prepare/Tests/sqlancer_prepare.stable.err
sql/test/prepare/Tests/sqlancer_prepare.stable.err.int128
Branch: Jan2022
Log Message:
Added test and fix for bug #7202 With distinct the ordering columns must match
the projection list, otherwise it is not computable
diffs (188 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
@@ -4380,7 +4380,7 @@ rel_partition_groupings(sql_query *query
/* find selection expressions matching the order by column expression */
/* complex columns only */
static sql_exp *
-rel_order_by_column_exp(sql_query *query, sql_rel **R, symbol *column_r, int f)
+rel_order_by_column_exp(sql_query *query, sql_rel **R, symbol *column_r, int
needs_distinct, int f)
{
mvc *sql = query->sql;
sql_rel *r = *R, *p = NULL;
@@ -4390,7 +4390,7 @@ rel_order_by_column_exp(sql_query *query
if (!r)
return e;
- if (r && is_simple_project(r->op) && is_processed(r)) {
+ if (is_simple_project(r->op) && is_processed(r)) {
p = r;
r = r->l;
}
@@ -4405,6 +4405,8 @@ rel_order_by_column_exp(sql_query *query
if (is_project(p->op) && (found = exps_any_match(p->exps, e)))
{ /* if one of the projections matches, return a reference to it */
e = exp_ref(sql, found);
} else {
+ if (needs_distinct)
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: with DISTINCT ORDER BY expressions must appear in select list");
e = rel_project_add_exp(sql, p, e);
for (node *n = p->exps->h ; n ; n = n->next) {
sql_exp *ee = n->data;
@@ -4422,6 +4424,8 @@ rel_order_by_column_exp(sql_query *query
sql_exp *found = exps_find_exp(r->exps, e);
if (!found) {
+ if (needs_distinct)
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: with DISTINCT ORDER BY expressions must appear in select list");
append(r->exps, e);
} else {
e = found;
@@ -4446,7 +4450,7 @@ simple_selection(symbol *sq)
}
static list *
-rel_order_by(sql_query *query, sql_rel **R, symbol *orderby, int f)
+rel_order_by(sql_query *query, sql_rel **R, symbol *orderby, int
needs_distinct, int f)
{
mvc *sql = query->sql;
sql_rel *rel = *R, *or = rel; /* the order by relation */
@@ -4504,6 +4508,8 @@ rel_order_by(sql_query *query, sql_rel *
sql_exp *found =
exps_find_exp(rel->exps, e);
if (!found) {
+ if (needs_distinct)
+ return sql_error(sql,
02, SQLSTATE(42000) "SELECT: with DISTINCT ORDER BY expressions must appear in
select list");
append(rel->exps, e);
} else {
e = found;
@@ -4517,8 +4523,7 @@ rel_order_by(sql_query *query, sql_rel *
sql->session->status = 0;
sql->errstr[0] = '\0';
- if (!e)
- e = rel_order_by_column_exp(query,
&rel, col, sql_sel | sql_orderby | (f & sql_group_totals));
+ e = rel_order_by_column_exp(query, &rel, col,
needs_distinct, sql_sel | sql_orderby | (f & sql_group_totals));
}
if (!e)
return NULL;
@@ -4794,7 +4799,7 @@ rel_rankop(sql_query *query, sql_rel **r
/* Order By */
if (order_by_clause) {
- obe = rel_order_by(query, &p, order_by_clause, nf | sql_window);
+ obe = rel_order_by(query, &p, order_by_clause, 0, nf |
sql_window);
if (!obe)
return NULL;
}
@@ -5484,7 +5489,7 @@ rel_having_limits_nodes(sql_query *query
}
rel = rel_orderby(sql, rel);
set_processed(rel);
- obe = rel_order_by(query, &rel, sn->orderby, sql_orderby |
group_totals);
+ obe = rel_order_by(query, &rel, sn->orderby, sn->distinct !=
NULL, sql_orderby | group_totals);
if (!obe)
return NULL;
rel->r = obe;
diff --git a/sql/test/BugTracker-2021/Tests/All
b/sql/test/BugTracker-2021/Tests/All
--- a/sql/test/BugTracker-2021/Tests/All
+++ b/sql/test/BugTracker-2021/Tests/All
@@ -35,3 +35,4 @@ grouping-sets-aliases.Bug-7185
copyinto-copyfrom.Bug-7186
concurrent-add-column.Bug-7196
unique-idx.Bug-7201
+distinct-with-orderby.Bug-7202
diff --git a/sql/test/BugTracker-2021/Tests/distinct-with-orderby.Bug-7202.test
b/sql/test/BugTracker-2021/Tests/distinct-with-orderby.Bug-7202.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2021/Tests/distinct-with-orderby.Bug-7202.test
@@ -0,0 +1,20 @@
+statement ok
+START TRANSACTION
+
+statement ok
+CREATE TABLE T (t1 int, t2 int)
+
+statement ok rowcount 2
+INSERT INTO t VALUES (1, 1), (1, 2)
+
+query I nosort
+SELECT DISTINCT t1 FROM T ORDER BY t1
+----
+1
+
+statement error 42000!SELECT: with DISTINCT ORDER BY expressions must appear
in select list
+SELECT DISTINCT t1 FROM T ORDER BY t1, t2
+
+statement ok
+ROLLBACK
+
diff --git a/sql/test/analytics/Tests/analytics10.test
b/sql/test/analytics/Tests/analytics10.test
--- a/sql/test/analytics/Tests/analytics10.test
+++ b/sql/test/analytics/Tests/analytics10.test
@@ -959,7 +959,7 @@ query I rowsort
SELECT
DISTINCT CAST(SUM(TotalSales) as BIGINT) AS TotalSales
FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
-ORDER BY SUM(TotalSales)
+ORDER BY CAST(SUM(TotalSales) as BIGINT)
----
100
1200
diff --git a/sql/test/emptydb/Tests/check.SQL.py
b/sql/test/emptydb/Tests/check.SQL.py
--- a/sql/test/emptydb/Tests/check.SQL.py
+++ b/sql/test/emptydb/Tests/check.SQL.py
@@ -387,7 +387,7 @@ with process.client('sql', format='csv',
clt.stdin.write("select E'\\\\dSv ' || s.name || '.' || t.name from
sys._tables t, sys.schemas s where t.schema_id = s.id and t.query is not null
order by s.name, t.name;\n")
- clt.stdin.write("select distinct E'\\\\dSf ' || s.name || '.\"' || f.name
|| '\"' from sys.functions f, sys.schemas s where f.language between 1 and 2
and f.schema_id = s.id and s.name = 'sys' order by s.name, f.name;\n")
+ clt.stdin.write("select proj from (select distinct E'\\\\dSf ' || s.name
|| '.\"' || f.name || '\"' as proj from sys.functions f, sys.schemas s where
f.language between 1 and 2 and f.schema_id = s.id and s.name = 'sys') as
proj(proj) order by proj;\n")
out, err = clt.communicate()
out = re.sub('^"(.*)"$', r'\1', out, flags=re.MULTILINE).replace('"\n',
'\n').replace('\n"', '\n').replace('""', '"').replace(r'\\', '\\')
diff --git a/sql/test/miscellaneous/Tests/groupby_expressions.test
b/sql/test/miscellaneous/Tests/groupby_expressions.test
--- a/sql/test/miscellaneous/Tests/groupby_expressions.test
+++ b/sql/test/miscellaneous/Tests/groupby_expressions.test
@@ -232,15 +232,10 @@ select cast(sum("aa"+"bb") as bigint) fr
----
10
-query II rowsort
-select distinct "aa", "bb" from "groupings" order by "aa" > "bb"
+query T rowsort
+select distinct "aa" > "bb" from "groupings" order by "aa" > "bb"
----
-1
-1
-1
-3
-2
-2
+False
query I rowsort
select count(*) from "groupings" having count("aa") <= count("aa" + "bb")
order by sum("aa") > sum("bb")
diff --git a/sql/test/prepare/Tests/sqlancer_prepare.stable.err
b/sql/test/prepare/Tests/sqlancer_prepare.stable.err
--- a/sql/test/prepare/Tests/sqlancer_prepare.stable.err
+++ b/sql/test/prepare/Tests/sqlancer_prepare.stable.err
@@ -22,7 +22,7 @@ MAPI = (monetdb) /var/tmp/mtest-363257/
QUERY = prepare with cte0(c0) as (select 1), cte1(c0,c1,c2) as (select
distinct 1, 2, false)
select distinct least('y', (values (''), (''))), 1, (select ? from
(select 1) as l1v0(x))
from cte0 as l0cte0, cte1 as l0cte1 order by l0cte0.c0 desc nulls
last, l0cte1.c2 desc nulls first; -- ? can't be defined, so error
-ERROR = !Could not determine type for argument number 1
+ERROR = !SELECT: with DISTINCT ORDER BY expressions must appear in select list
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-185783/.s.monetdb.32587
QUERY = prepare with cte0(c0) as (select 2) select 1 <> all(select 2 from
(values (1),(2)) as t1) from cte0 as l0cte0 group by ?; --error, cannot have a
parameter for group by column
diff --git a/sql/test/prepare/Tests/sqlancer_prepare.stable.err.int128
b/sql/test/prepare/Tests/sqlancer_prepare.stable.err.int128
--- a/sql/test/prepare/Tests/sqlancer_prepare.stable.err.int128
+++ b/sql/test/prepare/Tests/sqlancer_prepare.stable.err.int128
@@ -26,7 +26,7 @@ MAPI = (monetdb) /var/tmp/mtest-363257/
QUERY = prepare with cte0(c0) as (select 1), cte1(c0,c1,c2) as (select
distinct 1, 2, false)
select distinct least('y', (values (''), (''))), 1, (select ? from
(select 1) as l1v0(x))
from cte0 as l0cte0, cte1 as l0cte1 order by l0cte0.c0 desc nulls
last, l0cte1.c2 desc nulls first; -- ? can't be defined, so error
-ERROR = !Could not determine type for argument number 1
+ERROR = !SELECT: with DISTINCT ORDER BY expressions must appear in select list
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-185783/.s.monetdb.32587
QUERY = prepare with cte0(c0) as (select 2) select 1 <> all(select 2 from
(values (1),(2)) as t1) from cte0 as l0cte0 group by ?; --error, cannot have a
parameter for group by column
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
