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

Niels Nes Niels.Nes at cwi.nl
Sun Mar 17 17:49:55 CET 2019


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 <commits+niels=cwi.nl at monetdb.org> 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 at monetdb.org
> > https://www.monetdb.org/mailman/listinfo/checkin-list
> 
> _______________________________________________
> developers-list mailing list
> developers-list at 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 at sip.cwi.nl
url: https://www.cwi.nl/people/716	e-mail: Niels.Nes at cwi.nl


More information about the developers-list mailing list