Changeset: 765c320e69bf for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=765c320e69bf
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/sql_parser.y
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
sql/test/BugTracker-2017/Tests/wrong_aggregation_count.Bug-6257.stable.out
sql/test/subquery/Tests/correlated.stable.out
Branch: subquery
Log Message:
more fixes for the in queries
diffs (truncated from 339 to 300 lines):
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -1946,7 +1946,7 @@ rel2bin_join(backend *be, sql_rel *rel,
/* only handle simple joins here */
if ((exp_has_func(e) && get_cmp(e) != cmp_filter) ||
- (get_cmp(e) == cmp_or)) {
+ get_cmp(e) == cmp_or || e->f) {
if (!join && !list_length(lje)) {
stmt *l = bin_first_column(be, left);
stmt *r = bin_first_column(be, right);
@@ -2338,7 +2338,7 @@ rel2bin_distinct(backend *be, stmt *s, s
return s;
/* Use 'all' tid columns */
- if ((tids = bin_find_columns(be, s, TID)) != NULL) {
+ if (0 && (tids = bin_find_columns(be, s, TID)) != NULL) {
for (n = tids->h; n; n = n->next) {
stmt *t = n->data;
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -985,7 +985,7 @@ order_joins(mvc *sql, list *rels, list *
}
if (list_length(exps)) { /* more expressions (add selects) */
node *n;
- set_processed(top);
+ //set_processed(top);
top = rel_select(sql->sa, top, NULL);
for(n=exps->h; n; n = n->next) {
sql_exp *e = n->data;
@@ -1000,9 +1000,13 @@ order_joins(mvc *sql, list *rels, list *
if (l && r)
*/
- if (exp_is_join_exp(e) == 0)
- rel_join_add_exp(sql->sa, top->l, e);
- else
+ if (exp_is_join_exp(e) == 0) {
+ sql_rel *nr = NULL;
+ if (e->flag == cmp_equal)
+ nr = rel_push_join(sql, top->l, e->l,
e->r, NULL, e);
+ if (!nr)
+ rel_join_add_exp(sql->sa, top->l, e);
+ } else
rel_select_add_exp(sql->sa, top, e);
}
}
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
@@ -596,7 +596,6 @@ rel_named_table_function(sql_query *quer
set_dependent(sq);
}
}
-
/* reset error */
sql->session->status = 0;
sql->errstr[0] = '\0';
@@ -976,6 +975,8 @@ rel_column_ref(sql_query *query, sql_rel
for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++)
exp = rel_bind_column(sql, outer, name, f);
+ if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
+ return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s' in query results without an
aggregate function", name);
if (exp) {
set_freevar(exp);
exp->card = CARD_ATOM;
@@ -1004,14 +1005,14 @@ rel_column_ref(sql_query *query, sql_rel
while(gb->l && !is_groupby(gb->op))
gb = gb->l;
if (gb && gb->l && rel_bind_column(sql, gb->l,
name, f))
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
}
if (is_sql_having(f))
- return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s' in query results without an
aggregate function", name);
+ return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s' in query results without an
aggregate function", name);
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
identifier '%s' unknown", name);
}
if (exp && rel && *rel && (*rel)->card <= CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f)) {
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", name);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", name);
}
} else if (dlist_length(l) == 2) {
@@ -1026,6 +1027,8 @@ rel_column_ref(sql_query *query, sql_rel
for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++)
exp = rel_bind_column2(sql, outer, tname,
cname, f);
+ if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
+ return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", tname, cname);
if (exp) {
set_freevar(exp);
exp->card = CARD_ATOM;
@@ -1052,14 +1055,14 @@ rel_column_ref(sql_query *query, sql_rel
while(gb->l && !is_groupby(gb->op) &&
is_project(gb->op))
gb = gb->l;
if (gb && is_groupby(gb->op) && gb->l &&
rel_bind_column2(sql, gb->l, tname, cname, f))
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
}
if (is_sql_having(f))
- return sql_error(sql, 02, SQLSTATE(42S22)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", tname, cname);
+ return sql_error(sql, 05, SQLSTATE(42S22)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", tname, cname);
return sql_error(sql, 02, SQLSTATE(42S22) "SELECT: no
such column '%s.%s'", tname, cname);
}
if (exp && rel && *rel && (*rel)->card == CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f)) {
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s.%s' in query results without an aggregate
function", tname, cname);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s.%s' in query results without an aggregate
function", tname, cname);
}
} else if (dlist_length(l) >= 3) {
return sql_error(sql, 02, SQLSTATE(42000) "TODO: column names
of level >= 3");
@@ -1453,16 +1456,16 @@ rel_filter(mvc *sql, sql_rel *rel, list
if (exps_card(l) > rel->card) {
sql_exp *ls = l->h->data;
if (ls->name)
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", ls->name);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", ls->name);
else
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
}
if (exps_card(r) > rel->card) {
sql_exp *rs = l->h->data;
if (rs->name)
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", rs->name);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", rs->name);
else
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
}
if (exps_card(r) <= CARD_ATOM && exps_are_atoms(r)) {
if (exps_card(l) == exps_card(r) || rel->processed) /* bin
compare op */
@@ -1561,15 +1564,15 @@ rel_compare_exp_(sql_query *query, sql_r
/* atom or row => select */
if (ls->card > rel->card) {
if (ls->name)
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", ls->name);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", ls->name);
else
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
}
if (rs->card > rel->card || (rs2 && rs2->card > rel->card)) {
if (rs->name)
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", rs->name);
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", rs->name);
else
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column in query results without an aggregate function");
}
if (rs->card <= CARD_ATOM && (exp_is_atom(rs) || exp_has_freevar(rs)) &&
(!rs2 || (rs2->card <= CARD_ATOM && (exp_is_atom(rs2) ||
exp_has_freevar(rs2))))) {
@@ -2677,7 +2680,6 @@ rel_in_exp(sql_query *query, sql_rel *re
sql_rel *z = NULL;
r = rel_value_exp(query, &z, n->data.sym, f /* ie no
result project */, ek);
-
if (!r) {
/* reset error */
sql->session->status = 0;
@@ -4772,9 +4774,9 @@ rel_order_by(sql_query *query, sql_rel *
}
} else if (e && e->card != rel->card) {
if (e && e->name) {
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", e->name);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", e->name);
} else {
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
}
}
}
@@ -6111,9 +6113,9 @@ rel_select_exp(sql_query *query, sql_rel
if (ce && exp_subtype(ce)) {
if (rel->card < ce->card) {
if (ce->name) {
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", ce->name);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", ce->name);
} else {
- return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
}
}
/*
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -85,7 +85,7 @@ merge_freevar(list *l, list *r)
return r;
if (!r)
return l;
- return list_merge(l, r, (fdup)NULL);
+ return list_distinct(list_merge(l, r, (fdup)NULL), (fcmp)exp_equal,
(fdup)NULL);
}
static list * exps_freevar(mvc *sql, list *exps);
@@ -337,7 +337,7 @@ static sql_rel *
push_up_project(mvc *sql, sql_rel *rel)
{
/* input rel is dependent outerjoin with on the right a project, we
first try to push inner side expressions down (because these cannot be pushed
up) */
- if (rel && is_outerjoin(rel->op) && is_dependent(rel) && 0) {
+ if (rel && is_outerjoin(rel->op) && is_dependent(rel)) {
sql_rel *r = rel->r;
/* find constant expressions and move these down */
@@ -362,12 +362,16 @@ push_up_project(mvc *sql, sql_rel *rel)
}
}
if (cexps) {
- sql_rel *n = l->l = rel_project(
sql->sa, l->l,
+ sql_rel *p = l->l = rel_project(
sql->sa, l->l,
rel_projections(sql, l->l,
NULL, 1, 1));
- n->exps = list_merge(n->exps, cexps,
(fdup)NULL);
+ p->exps = list_merge(p->exps, cexps,
(fdup)NULL);
if (list_empty(nexps)) {
rel->r = l; /* remove empty
project */
} else {
+ for (n = cexps->h; n; n =
n->next) { /* add pushed down renamed expressions */
+ sql_exp *e = n->data;
+ append(nexps,
exp_ref(sql->sa, e));
+ }
r->exps = nexps;
}
}
@@ -495,10 +499,11 @@ push_up_groupby(mvc *sql, sql_rel *rel)
col = exp_ref(sql->sa, col);
col = exp_unop(sql->sa, col,
sql_bind_func(sql->sa, NULL, "identity", exp_subtype(col), NULL, F_FUNC));
col = exp_label(sql->sa, col,
++sql->label);
- if (!exps_find_exp(r->exps,
col))
- append(r->exps, col);
+ append(p->exps, col);
+ //if (!exps_find_exp(r->exps,
col))
+ // append(r->exps, col);
}
- exp_ref(sql->sa, col);
+ col = exp_ref(sql->sa, col);
append(e->l=sa_list(sql->sa), col);
set_no_nil(e);
}
@@ -614,7 +619,8 @@ push_up_join(mvc *sql, sql_rel *rel)
append(nr->exps, pe);
pe = exp_ref(sql->sa, pe);
e = exp_ref(sql->sa, e);
- je = exp_compare(sql->sa, e, pe,
cmp_equal);
+ //je = exp_compare(sql->sa, e, pe,
cmp_equal);
+ je = exp_compare(sql->sa, e, pe,
cmp_equal_nil);
append(n->exps, je);
}
return n;
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -6744,9 +6744,9 @@ void *sql_error( mvc * sql, int error_co
va_list ap;
va_start (ap,format);
- if (sql->errstr[0] == '\0')
+ if (sql->errstr[0] == '\0' || error_code == 5)
vsnprintf(sql->errstr, ERRSIZE-1, _(format), ap);
- if (!sql->session->status)
+ if (!sql->session->status || error_code == 5)
sql->session->status = -error_code;
va_end (ap);
return NULL;
diff --git a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
--- a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
+++ b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
@@ -100,7 +100,7 @@ project (
| | | | | | ) [ "b3a"."open_auction_id" NOT NULL = "o"."open_auction_id" NOT
NULL ]
| | | | | ) [ "o"."open_auction_id" NOT NULL ] [ sys.min no nil ("b3a"."id"
NOT NULL HASHCOL ) NOT NULL as "L3"."L3", "o"."open_auction_id" NOT NULL ]
| | | | ) [ "L3"."L3" NOT NULL, "o"."open_auction_id" NOT NULL as "L25"."L25" ]
-| | | ) [ "b3"."id" NOT NULL HASHCOL = "L3"."L3" NOT NULL,
"o"."open_auction_id" NOT NULL = "L25"."L25" NOT NULL ]
+| | | ) [ "b3"."id" NOT NULL HASHCOL = "L3"."L3" NOT NULL,
"o"."open_auction_id" NOT NULL =* "L25"."L25" NOT NULL ]
| | ) [ "b3"."increase" NOT NULL, "o"."open_auction_id" NOT NULL as
"L20"."L20" ]
| ) [ "o"."open_auction_id" NOT NULL = "L20"."L20" NOT NULL ]
) [ "o"."id" NOT NULL HASHCOL , "o"."open_auction_id" NOT NULL, "o"."initial"
NOT NULL, "o"."reserve" NOT NULL, "o"."aktuell" NOT NULL, "o"."privacy" NOT
NULL, "o"."itemref" NOT NULL, "o"."seller" NOT NULL, "o"."quantity" NOT NULL,
"o"."type" NOT NULL, "o"."start" NOT NULL, "o"."ende" NOT NULL, "o"."%TID%" NOT
NULL, "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date"
NOT NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT
NULL, "b"."%TID%" NOT NULL, "b3"."increase" NOT NULL ]
@@ -129,7 +129,7 @@ project (
| | | | | | ) [ "b2a"."open_auction_id" NOT NULL = "o"."open_auction_id" NOT
NULL ]
| | | | | ) [ "o"."open_auction_id" NOT NULL ] [ sys.max no nil ("b2a"."id"
NOT NULL HASHCOL ) NOT NULL as "L7"."L7", "o"."open_auction_id" NOT NULL ]
| | | | ) [ "L7"."L7" NOT NULL, "o"."open_auction_id" NOT NULL as "L37"."L37" ]
-| | | ) [ "b2"."id" NOT NULL HASHCOL = "L7"."L7" NOT NULL,
"o"."open_auction_id" NOT NULL = "L37"."L37" NOT NULL ]
+| | | ) [ "b2"."id" NOT NULL HASHCOL = "L7"."L7" NOT NULL,
"o"."open_auction_id" NOT NULL =* "L37"."L37" NOT NULL ]
| | ) [ "b2"."increase" NOT NULL, "o"."open_auction_id" NOT NULL as
"L32"."L32" ]
| ) [ sys.sql_mul("b3"."increase" NOT NULL, double "2") <= "b2"."increase" NOT
NULL, "o"."open_auction_id" NOT NULL = "L32"."L32" NOT NULL ]
) [ "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date" NOT
NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT NULL ]
diff --git
a/sql/test/BugTracker-2017/Tests/wrong_aggregation_count.Bug-6257.stable.out
b/sql/test/BugTracker-2017/Tests/wrong_aggregation_count.Bug-6257.stable.out
--- a/sql/test/BugTracker-2017/Tests/wrong_aggregation_count.Bug-6257.stable.out
+++ b/sql/test/BugTracker-2017/Tests/wrong_aggregation_count.Bug-6257.stable.out
@@ -73,7 +73,7 @@ Ready.
#, (SELECT CAST(COUNT(*) as int) FROM sys.tables t WHERE t.schema_id = s.id
AND NOT t.system AND t.query is NULL) AS "# user tables"
#, (SELECT CAST(COUNT(*) as int) FROM sys.tables t WHERE t.schema_id = s.id
AND t.system AND t.query is NOT NULL) AS "# system views"
#SELECT * FROM sys.schema_stats;
-% .schema_stats, .schema_stats, .schema_stats, .schema_stats,
.schema_stats, .schema_stats, .schema_stats, .schema_stats, .schema_stats #
table_name
+% sys.schema_stats, sys.schema_stats, sys.schema_stats,
sys.schema_stats, .schema_stats, .schema_stats, .schema_stats,
.schema_stats, .schema_stats # table_name
% name, authorization, owner, system, "# tables/views", "#
system tables", "# user tables", "# system views", "# user
views" # name
% varchar, int, int, boolean, int, int, int, int,
int # type
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
