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

making progres ;)

On 17 Mar 2019, at 11:58, Niels Nes commits+niels=cwi.nl@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@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list