Re: MonetDB: subquery - return single column on in queries (with one...

On Sun, Mar 17, 2019 at 05:07:57PM +0100, Martin Kersten wrote:

making progres ;) zeker

niels

...

On 17 Mar 2019, at 11:58, Niels Nes wrote:

Changeset: 4ca5e0d20906 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4ca5e0d20906 Modified Files: sql/server/rel_select.c sql/server/rel_unnest.c sql/test/BugTracker-2014/Tests/in_incorrect_multi.Bug-3462.stable.err Branch: subquery Log Message:

return single column on in queries (with one column on the left)

diffs (127 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 @@ -1832,7 +1832,7 @@ rel_compare(sql_query *query, sql_rel *r } else { /* first try without current relation, too see if there are correlations with the outer relation */ - sql_rel *r = rel_subquery(query, NULL, ro, ek, 0);//APPLY_JOIN); + sql_rel *r = rel_subquery(query, NULL, ro, ek, 0);

/* NOT handled filter case */ if (ro2) @@ -1842,7 +1842,7 @@ rel_compare(sql_query *query, sql_rel *r sql->session->status = 0; sql->errstr[0] = 0; query_push_outer(query, rel); - r = rel_subquery(query, NULL, ro, ek, 0);// is_sql_sel(f)?APPLY_LOJ:APPLY_JOIN); + r = rel_subquery(query, NULL, ro, ek, 0); query_pop_outer(query);

/* get inner queries result value, ie @@ -2137,7 +2137,7 @@ rel_logical_value_exp(sql_query *query, } else { /* first try without current relation, too see if there are correlations with the outer relation */ - sql_rel *r = rel_subquery(query, NULL, ro, ek, 0);//APPLY_JOIN); + sql_rel *r = rel_subquery(query, NULL, ro, ek, 0); sql_exp *rs2 = NULL;

/* correlation, ie return new relation */ @@ -2147,8 +2147,10 @@ rel_logical_value_exp(sql_query *query, /* reset error */ sql->session->status = 0; sql->errstr[0] = 0; - if (!r) + if (!r) { + assert(0); r = rel_subquery(query, *rel, ro, ek, is_sql_sel(f)?APPLY_LOJ:APPLY_JOIN); + }

/* get inner queries result value, ie get last expression of r */ @@ -2477,7 +2479,7 @@ rel_logical_value_exp(sql_query *query, if ((!orel || (is_project(orel->op) && !is_processed(orel) && !orel->l && list_empty(orel->exps))) && !query_has_outer(query)) orel = *rel = rel_project_exp(sql->sa, exp_atom_bool(sql->sa, 1));

- ek.card = card_set; + ek.card = card_column; if (is_sql_sel(f) && orel && is_project(orel->op) && !is_processed(orel)) { needproj = 1; pexps = orel->exps; @@ -2811,13 +2813,13 @@ rel_logical_exp(sql_query *query, sql_re if (!rel) return NULL; } + return rel; /* rel = rel_logical_exp(query, rel, lo, f); if (!rel) return NULL; return rel_logical_exp(query, rel, ro, f); */ - return rel; } case SQL_FILTER: /* [ x,..] filter [ y,..] */ @@ -2922,6 +2924,8 @@ rel_logical_exp(sql_query *query, sql_re }

} + if (list_length(ll) > 1) + ek.card = card_relation;

/* list of values or subqueries */ if (n->type == type_list) { @@ -3021,7 +3025,7 @@ rel_logical_exp(sql_query *query, sql_re } if (l_used) rel_join_add_exp(sql->sa, left, e); - else if (!is_select(left->op)) + else if (!is_select(left->op) || rel_is_ref(left)) left = rel_select(sql->sa, left, e); else rel_select_add_exp(sql->sa, left, e); @@ -3100,7 +3104,7 @@ rel_logical_exp(sql_query *query, sql_re list *pexps = NULL; int needproj = 0, exists=(sc->token == SQL_EXISTS);

- ek.card = card_set; + ek.card = card_column; if (orel && is_project(orel->op) && !is_processed(orel)) { needproj = 1; pexps = orel->exps; @@ -5777,7 +5781,7 @@ rel_value_exp2(sql_query *query, sql_rel if (r) { sql_exp *e;

- if (ek.card <= card_column && is_project(r->op) && list_length(r->exps) > 1) + if (ek.card <= card_set && is_project(r->op) && list_length(r->exps) > 1) return sql_error(sql, 02, SQLSTATE(42000) "SELECT: subquery must return only one column"); e = _rel_lastexp(sql, r);

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 @@ -679,6 +679,9 @@ rel_unnest_dependent(mvc *sql, sql_rel * return rel_unnest_dependent(sql, rel); }

+ if (r && is_base(r->op)) /* table functions need dependent implementation */ + return rel; + /* fallback */ if ((ad = rel_dependent_var(sql, rel->l, rel->r)) != NULL) rel = rel_general_unnest(sql, rel, ad); diff --git a/sql/test/BugTracker-2014/Tests/in_incorrect_multi.Bug-3462.stable.err b/sql/test/BugTracker-2014/Tests/in_incorrect_multi.Bug-3462.stable.err --- a/sql/test/BugTracker-2014/Tests/in_incorrect_multi.Bug-3462.stable.err +++ b/sql/test/BugTracker-2014/Tests/in_incorrect_multi.Bug-3462.stable.err @@ -31,7 +31,7 @@ stderr of test 'in_incorrect_multi.Bug-3 MAPI = (monetdb) /var/tmp/mtest-27483/.s.monetdb.35395 QUERY = SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS); -ERROR = !IN: inner query should return a single column +ERROR = !SELECT: subquery must return only one column CODE = 42000

_______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/716 e-mail: Niels.Nes@cwi.nl