Changeset: a0e687e83898 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a0e687e83898
Modified Files:
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_select.c
sql/test/miscellaneous/Tests/simple_plans.sql
sql/test/miscellaneous/Tests/simple_plans.stable.out
Branch: Jun2020
Log Message:
Performance fix, when attempting to add an order by column expression, test
first if it exists on the list of projections. If so, return a reference to the
matched projection
diffs (121 lines):
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
@@ -1311,6 +1311,19 @@ exp_match_exp( sql_exp *e1, sql_exp *e2)
return 0;
}
+sql_exp *
+exps_any_match(list *l, sql_exp *e)
+{
+ if (!l)
+ return NULL;
+ for (node *n = l->h; n ; n = n->next) {
+ sql_exp *ne = (sql_exp *) n->data;
+ if (exp_match_exp(ne, e))
+ return ne;
+ }
+ return NULL;
+}
+
static int
exps_are_joins( list *l )
{
diff --git a/sql/server/rel_exp.h b/sql/server/rel_exp.h
--- a/sql/server/rel_exp.h
+++ b/sql/server/rel_exp.h
@@ -131,6 +131,7 @@ extern int exp_refers( sql_exp *p, sql_e
extern int exp_match( sql_exp *e1, sql_exp *e2);
extern sql_exp* exps_find_exp( list *l, sql_exp *e);
extern int exp_match_exp( sql_exp *e1, sql_exp *e2);
+extern sql_exp* exps_any_match(list *l, sql_exp *e);
/* match just the column (cmp equality) expressions */
extern int exp_match_col_exps( sql_exp *e, list *l);
extern int exps_match_col_exps( sql_exp *e1, sql_exp *e2);
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
@@ -4471,7 +4471,7 @@ rel_order_by_column_exp(sql_query *query
{
mvc *sql = query->sql;
sql_rel *r = *R, *p = NULL;
- sql_exp *e = NULL;
+ sql_exp *e = NULL, *found = NULL;
exp_kind ek = {type_value, card_column, FALSE};
if (!r)
@@ -4489,15 +4489,19 @@ rel_order_by_column_exp(sql_query *query
else if (r)
p->l = r;
if (e && p) {
- e = rel_project_add_exp(sql, p, e);
- for (node *n = p->exps->h ; n ; n = n->next) {
- sql_exp *ee = n->data;
-
- if (ee->card > r->card) {
- if (exp_name(ee))
- return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", exp_name(ee));
- else
- return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
+ 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 {
+ e = rel_project_add_exp(sql, p, e);
+ for (node *n = p->exps->h ; n ; n = n->next) {
+ sql_exp *ee = n->data;
+
+ if (ee->card > r->card) {
+ if (exp_name(ee))
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in
query results without an aggregate function", exp_name(ee));
+ else
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query
results without an aggregate function");
+ }
}
}
return e;
diff --git a/sql/test/miscellaneous/Tests/simple_plans.sql
b/sql/test/miscellaneous/Tests/simple_plans.sql
--- a/sql/test/miscellaneous/Tests/simple_plans.sql
+++ b/sql/test/miscellaneous/Tests/simple_plans.sql
@@ -1,5 +1,10 @@
start transaction;
+create table myy (col1 int, col2 int);
+insert into myy values (1, 1), (2, 0), (3,3), (4,2);
+select distinct col1 + col2 from myy order by col1 + col2;
+plan select distinct col1 + col2 from myy order by col1 + col2;
+
create table myx (x uuid, y uuid);
plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y =
'1aea00e5db6e0810b554fde31d961965';
plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y is
null;
diff --git a/sql/test/miscellaneous/Tests/simple_plans.stable.out
b/sql/test/miscellaneous/Tests/simple_plans.stable.out
--- a/sql/test/miscellaneous/Tests/simple_plans.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_plans.stable.out
@@ -6,6 +6,28 @@ stdout of test 'simple_plans` in directo
# 11:57:38 >
#start transaction;
+#create table myy (col1 int, col2 int);
+#insert into myy values (1, 1), (2, 0), (3,3), (4,2);
+[ 4 ]
+#select distinct col1 + col2 from myy order by col1 + col2;
+% sys.%2 # table_name
+% %2 # name
+% bigint # type
+% 1 # length
+[ 2 ]
+[ 6 ]
+#plan select distinct col1 + col2 from myy order by col1 + col2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 110 # length
+project (
+| group by (
+| | project (
+| | | table(sys.myy) [ "myy"."col1", "myy"."col2" ] COUNT
+| | ) [ sys.sql_add(bigint["myy"."col1"] as "myy"."col1", bigint["myy"."col2"]
as "myy"."col2") as "%2"."%2" ]
+| ) [ "%2"."%2" ] [ "%2"."%2" ]
+) [ "%2"."%2" ] [ "%2"."%2" ASC ]
#create table myx (x uuid, y uuid);
#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y =
'1aea00e5db6e0810b554fde31d961965';
% .plan # table_name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
