LCOV - code coverage report
Current view: top level - sql/server - rel_unnest.c (source / functions) Hit Total Coverage
Test: coverage.info Lines: 1961 2168 90.5 %
Date: 2021-10-13 02:24:04 Functions: 77 78 98.7 %

          Line data    Source code
       1             : /*
       2             :  * This Source Code Form is subject to the terms of the Mozilla Public
       3             :  * License, v. 2.0.  If a copy of the MPL was not distributed with this
       4             :  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
       5             :  *
       6             :  * Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V.
       7             :  */
       8             : 
       9             : /*#define DEBUG*/
      10             : 
      11             : #include "monetdb_config.h"
      12             : #include "sql_decimal.h"
      13             : #include "rel_unnest.h"
      14             : #include "rel_optimizer.h"
      15             : #include "rel_prop.h"
      16             : #include "rel_rel.h"
      17             : #include "rel_basetable.h"
      18             : #include "rel_exp.h"
      19             : #include "rel_select.h"
      20             : #include "rel_remote.h"
      21             : #include "rel_rewriter.h"
      22             : #include "sql_query.h"
      23             : #include "mal_errors.h" /* for SQLSTATE() */
      24             : 
      25             : /* some unnesting steps use the 'used' flag to avoid further rewrites. List them here, so only one reset flag iteration will be used */
      26             : #define rewrite_fix_count_used (1 << 0)
      27             : #define rewrite_values_used    (1 << 1)
      28             : 
      29             : #define is_rewrite_fix_count_used(X) ((X & rewrite_fix_count_used) == rewrite_fix_count_used)
      30             : #define is_rewrite_values_used(X)    ((X & rewrite_values_used) == rewrite_values_used)
      31             : 
      32             : static void
      33       32431 : exp_set_freevar(mvc *sql, sql_exp *e, sql_rel *r)
      34             : {
      35       34671 :         switch(e->type) {
      36           0 :         case e_cmp:
      37           0 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
      38           0 :                         exps_set_freevar(sql, e->l, r);
      39           0 :                         exps_set_freevar(sql, e->r, r);
      40           0 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
      41           0 :                         exp_set_freevar(sql, e->l, r);
      42           0 :                         exps_set_freevar(sql, e->r, r);
      43             :                 } else {
      44           0 :                         exp_set_freevar(sql, e->l, r);
      45           0 :                         exp_set_freevar(sql, e->r, r);
      46           0 :                         if (e->f)
      47             :                                 exp_set_freevar(sql, e->f, r);
      48             :                 }
      49             :                 break;
      50        2240 :         case e_convert:
      51        2240 :                 exp_set_freevar(sql, e->l, r);
      52        2240 :                 break;
      53       10064 :         case e_func:
      54             :         case e_aggr:
      55       10064 :                 if (e->l)
      56       10064 :                         exps_set_freevar(sql, e->l, r);
      57             :                 break;
      58       10506 :         case e_column:
      59       10506 :                 if ((e->l && rel_bind_column2(sql, r, e->l, e->r, 0)) ||
      60        7315 :                     (!e->l && rel_bind_column(sql, r, e->r, 0, 1)))
      61        3191 :                         return;
      62        7315 :                 set_freevar(e, 0);
      63        7315 :                 break;
      64       11861 :         case e_atom:
      65       11861 :                 if (e->f)
      66           0 :                         exps_set_freevar(sql, e->f, r);
      67             :                 break;
      68             :         case e_psm:
      69             :                 break;
      70             :         }
      71       32431 : }
      72             : 
      73             : void
      74       10064 : exps_set_freevar(mvc *sql, list *exps, sql_rel *r)
      75             : {
      76             :         node *n;
      77             : 
      78       10064 :         if (list_empty(exps))
      79             :                 return;
      80       29978 :         for(n = exps->h; n; n = n->next)
      81       19914 :                 exp_set_freevar(sql, n->data, r);
      82             : }
      83             : 
      84             : /* check if the set is distinct for the set of free variables */
      85             : static int
      86       17502 : is_distinct_set(mvc *sql, sql_rel *rel, list *ad)
      87             : {
      88             :         int distinct = 0;
      89       17663 :         if (ad && exps_unique(sql, rel, ad) && !have_nil(ad))
      90             :                 return 1;
      91       17632 :         if (ad && is_groupby(rel->op) && exp_match_list(rel->r, ad))
      92             :                 return 1;
      93       17606 :         distinct = need_distinct(rel);
      94       17606 :         if (is_project(rel->op) && rel->l && !distinct)
      95             :                 distinct = is_distinct_set(sql, rel->l, ad);
      96             :         return distinct;
      97             : }
      98             : 
      99             : int
     100    32450443 : exp_has_freevar(mvc *sql, sql_exp *e)
     101             : {
     102             :         if (mvc_highwater(sql)) {
     103           0 :                 (void) sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     104           0 :                 return 0;
     105             :         }
     106             : 
     107    33403925 :         if (is_freevar(e))
     108      259145 :                 return is_freevar(e);
     109    33144780 :         switch(e->type) {
     110     3741639 :         case e_cmp:
     111     3741639 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
     112       66474 :                         return (exps_have_freevar(sql, e->l) || exps_have_freevar(sql, e->r));
     113     3675165 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
     114      331829 :                         return (exp_has_freevar(sql, e->l) || exps_have_freevar(sql, e->r));
     115             :                 } else {
     116     3343336 :                         return (exp_has_freevar(sql, e->l) || exp_has_freevar(sql, e->r) ||
     117     3102923 :                             (e->f && exp_has_freevar(sql, e->f)));
     118             :                 }
     119             :                 break;
     120      953471 :         case e_convert:
     121      953471 :                 return exp_has_freevar(sql, e->l);
     122     2294020 :         case e_func:
     123             :         case e_aggr:
     124     2294020 :                 if (e->l)
     125     2264898 :                         return exps_have_freevar(sql, e->l);
     126             :                 /* fall through */
     127             :         case e_psm:
     128       32835 :                 if (exp_is_rel(e))
     129        3713 :                         return rel_has_freevar(sql, e->l);
     130             :                 break;
     131     3987507 :         case e_atom:
     132     3987507 :                 if (e->f)
     133       10663 :                         return exps_have_freevar(sql, e->f);
     134             :                 break;
     135             :         case e_column:
     136             :         default:
     137             :                 return 0;
     138             :         }
     139             :         return 0;
     140             : }
     141             : 
     142             : int
     143     8621761 : exps_have_freevar(mvc *sql, list *exps)
     144             : {
     145             :         if (mvc_highwater(sql)) {
     146           0 :                 (void) sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     147           0 :                 return 0;
     148             :         }
     149     8621761 :         if (!exps)
     150             :                 return 0;
     151    32684591 :         for (node *n = exps->h; n; n = n->next) {
     152             :                 int vf = 0;
     153    24750461 :                 sql_exp *e = n->data;
     154    24750461 :                 if ((vf =exp_has_freevar(sql, e)) != 0)
     155      405554 :                         return vf;
     156             :         }
     157             :         return 0;
     158             : }
     159             : 
     160             : int
     161     7279255 : rel_has_freevar(mvc *sql, sql_rel *rel)
     162             : {
     163             :         if (mvc_highwater(sql)) {
     164           0 :                 (void) sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     165           0 :                 return 0;
     166             :         }
     167             : 
     168     7279255 :         if (is_basetable(rel->op)) {
     169             :                 return 0;
     170     5339792 :         } else if (is_base(rel->op)) {
     171        9809 :                 return exps_have_freevar(sql, rel->exps) ||
     172        9809 :                         (rel->l && rel_has_freevar(sql, rel->l));
     173     5329983 :         } else if (is_simple_project(rel->op) || is_groupby(rel->op) || is_select(rel->op) || is_topn(rel->op) || is_sample(rel->op)) {
     174     3502278 :                 if ((is_simple_project(rel->op) || is_groupby(rel->op)) && rel->r && exps_have_freevar(sql, rel->r))
     175             :                         return 1;
     176     3502101 :                 return exps_have_freevar(sql, rel->exps) ||
     177     3265873 :                         (rel->l && rel_has_freevar(sql, rel->l));
     178     1827705 :         } else if (is_join(rel->op) || is_set(rel->op) || is_semi(rel->op) || is_modify(rel->op)) {
     179     3652834 :                 return exps_have_freevar(sql, rel->exps) ||
     180     3535652 :                         rel_has_freevar(sql, rel->l) || rel_has_freevar(sql, rel->r);
     181             :         }
     182             :         return 0;
     183             : }
     184             : 
     185             : static void exps_only_freevar(sql_query *query, list *exps, bool *arguments_correlated, bool *found_one_freevar, list **ungrouped_cols);
     186             : static void rel_only_freevar(sql_query *query, sql_rel *rel, bool *arguments_correlated, bool *found_one_freevar, list **ungrouped_cols);
     187             : 
     188             : void /* look for expressions with either only freevars or atoms */
     189       49383 : exp_only_freevar(sql_query *query, sql_exp *e, bool *arguments_correlated, bool *found_one_freevar, list **ungrouped_cols)
     190             : {
     191       52245 :         if (mvc_highwater(query->sql)) {
     192           0 :                 (void) sql_error(query->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     193           0 :                 return ;
     194             :         }
     195             : 
     196       52245 :         if (is_freevar(e)) {
     197             :                 sql_rel *outer;
     198             : 
     199         383 :                 *found_one_freevar = true;
     200         383 :                 if (e->type == e_column) {
     201         383 :                         if ((outer = query_fetch_outer(query, is_freevar(e)-1))) {
     202         367 :                                 sql_exp *a = rel_find_exp(outer, e);
     203         367 :                                 if (!a || !is_aggr(a->type)) {
     204         353 :                                         if (!*ungrouped_cols)
     205         329 :                                                 *ungrouped_cols = new_exp_list(query->sql->sa);
     206         353 :                                         list_append(*ungrouped_cols, e);
     207             :                                 }
     208             :                         }
     209             :                 }
     210         383 :                 return ;
     211             :         }
     212       51862 :         switch(e->type) {
     213          17 :         case e_cmp:
     214          17 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
     215           0 :                         exps_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     216           0 :                         exps_only_freevar(query, e->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     217          17 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
     218           2 :                         exp_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     219           2 :                         exps_only_freevar(query, e->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     220             :                 } else {
     221          15 :                         exp_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     222          15 :                         exp_only_freevar(query, e->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     223          15 :                         if (e->f)
     224             :                                 exp_only_freevar(query, e->f, arguments_correlated, found_one_freevar, ungrouped_cols);
     225             :                 }
     226             :                 break;
     227        2860 :         case e_convert:
     228        2860 :                 exp_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     229        2860 :                 break;
     230       12743 :         case e_func:
     231             :         case e_aggr:
     232       12743 :                 if (e->l)
     233       12743 :                         exps_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     234             :                 break;
     235          21 :         case e_psm:
     236          21 :                 if (exp_is_rel(e))
     237          21 :                         rel_only_freevar(query, e->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     238             :                 break;
     239        9371 :         case e_atom:
     240        9371 :                 if (e->f)
     241          18 :                         exps_only_freevar(query, e->f, arguments_correlated, found_one_freevar, ungrouped_cols);
     242             :                 break;
     243       26850 :         case e_column:
     244       26850 :                 *arguments_correlated = 0;
     245       26850 :                 break;
     246             :         }
     247       49383 : }
     248             : 
     249             : void
     250       12806 : exps_only_freevar(sql_query *query, list *exps, bool *arguments_correlated, bool *found_one_freevar, list **ungrouped_cols)
     251             : {
     252       12806 :         if (mvc_highwater(query->sql)) {
     253           0 :                 (void) sql_error(query->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     254           0 :                 return ;
     255             :         }
     256       12806 :         if (!exps)
     257             :                 return ;
     258       38619 :         for (node *n = exps->h; n ; n = n->next)
     259       25813 :                 exp_only_freevar(query, n->data, arguments_correlated, found_one_freevar, ungrouped_cols);
     260             : }
     261             : 
     262             : void
     263          21 : rel_only_freevar(sql_query *query, sql_rel *rel, bool *arguments_correlated, bool *found_one_freevar, list **ungrouped_cols)
     264             : {
     265          58 :         if (mvc_highwater(query->sql)) {
     266           0 :                 (void) sql_error(query->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     267           0 :                 return ;
     268             :         }
     269             : 
     270          58 :         if (is_basetable(rel->op)) {
     271             :                 return ;
     272          41 :         } else if (is_base(rel->op)) {
     273           0 :                 exps_only_freevar(query, rel->exps, arguments_correlated, found_one_freevar, ungrouped_cols);
     274           0 :                 if (rel->r)
     275             :                         rel_only_freevar(query, rel->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     276          41 :         } else if (is_simple_project(rel->op) || is_groupby(rel->op) || is_select(rel->op) || is_topn(rel->op) || is_sample(rel->op)) {
     277          41 :                 if ((is_simple_project(rel->op) || is_groupby(rel->op)) && rel->r)
     278           2 :                         exps_only_freevar(query, rel->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     279          41 :                 exps_only_freevar(query, rel->exps, arguments_correlated, found_one_freevar, ungrouped_cols);
     280          41 :                 if (rel->l)
     281             :                         rel_only_freevar(query, rel->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     282           0 :         } else if (is_join(rel->op) || is_set(rel->op) || is_semi(rel->op) || is_modify(rel->op)) {
     283           0 :                 exps_only_freevar(query, rel->exps, arguments_correlated, found_one_freevar, ungrouped_cols);
     284           0 :                 rel_only_freevar(query, rel->l, arguments_correlated, found_one_freevar, ungrouped_cols);
     285           0 :                 rel_only_freevar(query, rel->r, arguments_correlated, found_one_freevar, ungrouped_cols);
     286             :         }
     287             :         return ;
     288             : }
     289             : 
     290             : static int
     291      120329 : freevar_equal( sql_exp *e1, sql_exp *e2)
     292             : {
     293      120329 :         assert(e1 && e2 && is_freevar(e1) && is_freevar(e2));
     294      120329 :         if (e1 == e2)
     295             :                 return 0;
     296      119512 :         if (e1->type != e_column || e2->type != e_column)
     297             :                 return -1;
     298      119512 :         if (e1->l && e2->l && strcmp(e1->l, e2->l) == 0)
     299       77798 :                 return strcmp(e1->r, e2->r);
     300       41714 :         if (!e1->l && !e2->l)
     301           0 :                 return strcmp(e1->r, e2->r);
     302             :         return -1;
     303             : }
     304             : 
     305             : static list *
     306     1285297 : merge_freevar(list *l, list *r)
     307             : {
     308     1285297 :         if (!l)
     309             :                 return r;
     310      325630 :         if (!r)
     311             :                 return l;
     312       74505 :         return list_distinct(list_merge(l, r, (fdup)NULL), (fcmp)freevar_equal, (fdup)NULL);
     313             : }
     314             : 
     315             : static list * exps_freevar(mvc *sql, list *exps);
     316             : static list * rel_freevar(mvc *sql, sql_rel *rel);
     317             : 
     318             : static list *
     319     1111994 : exp_freevar(mvc *sql, sql_exp *e)
     320             : {
     321             :         if (mvc_highwater(sql))
     322           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     323             : 
     324     1146073 :         switch(e->type) {
     325      511943 :         case e_column:
     326      511943 :                 if (is_freevar(e))
     327      160807 :                         return append(sa_list(sql->sa), e);
     328             :                 break;
     329       34079 :         case e_convert:
     330       34079 :                 return exp_freevar(sql, e->l);
     331      186229 :         case e_aggr:
     332             :         case e_func:
     333      186229 :                 if (e->l)
     334      175538 :                         return exps_freevar(sql, e->l);
     335             :                 break;
     336      175980 :         case e_cmp:
     337      175980 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
     338       22115 :                         list *l = exps_freevar(sql, e->l);
     339       22115 :                         list *r = exps_freevar(sql, e->r);
     340       22115 :                         return merge_freevar(l, r);
     341      153865 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
     342           0 :                         list *l = exp_freevar(sql, e->l);
     343           0 :                         list *r = exps_freevar(sql, e->r);
     344           0 :                         return merge_freevar(l, r);
     345             :                 } else {
     346      153865 :                         list *l = exp_freevar(sql, e->l);
     347      153865 :                         list *r = exp_freevar(sql, e->r);
     348      153865 :                         l = merge_freevar(l, r);
     349      153865 :                         if (e->f) {
     350         113 :                                 r = exp_freevar(sql, e->f);
     351         113 :                                 return merge_freevar(l, r);
     352             :                         }
     353             :                         return l;
     354             :                 }
     355             :                 break;
     356          10 :         case e_psm:
     357          10 :                 if (exp_is_rel(e))
     358          10 :                         if (rel_has_freevar(sql, e->l))
     359           8 :                                 return rel_freevar(sql, e->l);
     360             :                 return NULL;
     361      237832 :         case e_atom:
     362      237832 :                 if (e->f)
     363         129 :                         return exps_freevar(sql, e->f);
     364             :                 return NULL;
     365             :         default:
     366             :                 return NULL;
     367             :         }
     368             :         return NULL;
     369             : }
     370             : 
     371             : static list *
     372      518245 : exps_freevar(mvc *sql, list *exps)
     373             : {
     374             :         node *n;
     375             :         list *c = NULL;
     376             : 
     377             :         if (mvc_highwater(sql))
     378           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     379      518245 :         if (!exps)
     380             :                 return NULL;
     381     1308497 :         for (n = exps->h; n; n = n->next) {
     382      791570 :                 sql_exp *e = n->data;
     383      791570 :                 list *var = exp_freevar(sql, e);
     384             : 
     385      791570 :                 c = merge_freevar(c,var);
     386             :         }
     387             :         return c;
     388             : }
     389             : 
     390             : static list *
     391      390631 : rel_freevar(mvc *sql, sql_rel *rel)
     392             : {
     393             :         list *lexps = NULL, *rexps = NULL, *exps = NULL;
     394             : 
     395             :         if (mvc_highwater(sql))
     396           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     397      390631 :         if (!rel)
     398             :                 return NULL;
     399      366962 :         switch(rel->op) {
     400        3782 :         case op_join:
     401             :         case op_left:
     402             :         case op_right:
     403             :         case op_full:
     404        3782 :                 exps = exps_freevar(sql, rel->exps);
     405        3782 :                 lexps = rel_freevar(sql, rel->l);
     406        3782 :                 rexps = rel_freevar(sql, rel->r);
     407        3782 :                 lexps = merge_freevar(lexps, rexps);
     408        3782 :                 exps = merge_freevar(exps, lexps);
     409        3782 :                 return exps;
     410             : 
     411             :         case op_basetable:
     412             :                 return NULL;
     413          75 :         case op_table: {
     414          75 :                 sql_exp *call = rel->r;
     415          75 :                 if (rel->flag != TRIGGER_WRAPPER && rel->l)
     416          68 :                         lexps = rel_freevar(sql, rel->l);
     417          75 :                 exps = (rel->flag != TRIGGER_WRAPPER && call)?exps_freevar(sql, call->l):NULL;
     418          75 :                 return merge_freevar(exps, lexps);
     419             :         }
     420       14957 :         case op_union:
     421             :         case op_except:
     422             :         case op_inter:
     423       14957 :                 exps = exps_freevar(sql, rel->exps);
     424       14957 :                 lexps = rel_freevar(sql, rel->l);
     425       14957 :                 rexps = rel_freevar(sql, rel->r);
     426       14957 :                 lexps = merge_freevar(lexps, rexps);
     427       14957 :                 exps = merge_freevar(exps, lexps);
     428       14957 :                 return exps;
     429      278768 :         case op_ddl:
     430             :         case op_semi:
     431             :         case op_anti:
     432             : 
     433             :         case op_select:
     434             :         case op_topn:
     435             :         case op_sample:
     436             : 
     437             :         case op_groupby:
     438             :         case op_project:
     439      278768 :                 exps = exps_freevar(sql, rel->exps);
     440      278768 :                 lexps = rel_freevar(sql, rel->l);
     441      278768 :                 if (rel->r) {
     442        1313 :                         if (is_groupby(rel->op) || is_simple_project(rel->op))
     443         766 :                                 rexps = exps_freevar(sql, rel->r);
     444             :                         else
     445         547 :                                 rexps = rel_freevar(sql, rel->r);
     446        1313 :                         lexps = merge_freevar(lexps, rexps);
     447             :                 }
     448      278768 :                 exps = merge_freevar(exps, lexps);
     449      278768 :                 return exps;
     450             :         default:
     451             :                 return NULL;
     452             :         }
     453             : 
     454             : }
     455             : 
     456             : static list *
     457       66173 : rel_dependent_var(mvc *sql, sql_rel *l, sql_rel *r)
     458             : {
     459             :         list *res = NULL;
     460             : 
     461       66173 :         if (rel_has_freevar(sql, r)){
     462       66010 :                 list *freevar = rel_freevar(sql, r);
     463       66010 :                 if (freevar) {
     464             :                         node *n;
     465       66010 :                         list *boundvar = rel_projections(sql, l, NULL, 1, 0);
     466             : 
     467      162475 :                         for(n = freevar->h; n; n = n->next) {
     468       96465 :                                 sql_exp *e = n->data, *ne = NULL;
     469             :                                 /* each freevar should be an e_column */
     470       96465 :                                 if (e->l) {
     471       96446 :                                         ne = exps_bind_column2(boundvar, e->l, e->r, NULL);
     472             :                                 } else {
     473          19 :                                         ne = exps_bind_column(boundvar, e->r, NULL, NULL, 1);
     474             :                                 }
     475       96465 :                                 if (ne) {
     476       96213 :                                         if (!res)
     477       65953 :                                                 res = sa_list(sql->sa);
     478       96213 :                                         append(res, ne);
     479             :                                 }
     480             :                         }
     481             :                 }
     482             :         }
     483       66173 :         return res;
     484             : }
     485             : 
     486             : /*
     487             :  * try to bind any freevar in the expression e
     488             :  */
     489             : void
     490       12581 : rel_bind_var(mvc *sql, sql_rel *rel, sql_exp *e)
     491             : {
     492       12581 :         list *fvs = exp_freevar(sql, e);
     493             : 
     494       12581 :         if (fvs) {
     495             :                 node *n;
     496             : 
     497       25138 :                 for(n = fvs->h; n; n=n->next) {
     498       12598 :                         sql_exp *e = n->data;
     499             : 
     500       12598 :                         if (is_freevar(e) && (exp_is_atom(e) || rel_find_exp(rel,e)))
     501       12528 :                                 reset_freevar(e);
     502             :                 }
     503             :         }
     504       12581 : }
     505             : 
     506             : static sql_exp * push_up_project_exp(mvc *sql, sql_rel *rel, sql_exp *e);
     507             : 
     508             : static list *
     509         938 : push_up_project_exps(mvc *sql, sql_rel *rel, list *exps)
     510             : {
     511             :         node *n;
     512             : 
     513         938 :         if (!exps)
     514             :                 return exps;
     515             : 
     516         168 :         for(n=exps->h; n; n=n->next) {
     517          99 :                 sql_exp *e = n->data;
     518             : 
     519          99 :                 n->data = push_up_project_exp(sql, rel, e);
     520             :         }
     521          69 :         list_hash_clear(exps);
     522          69 :         return exps;
     523             : }
     524             : 
     525             : static sql_exp *
     526         222 : push_up_project_exp(mvc *sql, sql_rel *rel, sql_exp *e)
     527             : {
     528             :         if (mvc_highwater(sql))
     529           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
     530             : 
     531         225 :         switch(e->type) {
     532          63 :         case e_cmp:
     533          63 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
     534           3 :                         e->l = push_up_project_exps(sql, rel, e->l);
     535           3 :                         e->r = push_up_project_exps(sql, rel, e->r);
     536           3 :                         return e;
     537          60 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
     538           0 :                         e->l = push_up_project_exp(sql, rel, e->l);
     539           0 :                         e->r = push_up_project_exps(sql, rel, e->r);
     540           0 :                         return e;
     541             :                 } else {
     542          60 :                         e->l = push_up_project_exp(sql, rel, e->l);
     543          60 :                         e->r = push_up_project_exp(sql, rel, e->r);
     544          60 :                         if (e->f)
     545           0 :                                 e->f = push_up_project_exp(sql, rel, e->f);
     546             :                 }
     547             :                 break;
     548           3 :         case e_convert:
     549           3 :                 e->l = push_up_project_exp(sql, rel, e->l);
     550           3 :                 break;
     551          18 :         case e_func:
     552             :         case e_aggr:
     553          18 :                 if (e->l)
     554          18 :                         e->l = push_up_project_exps(sql, rel, e->l);
     555             :                 break;
     556         105 :         case e_column:
     557             :                 {
     558             :                         sql_exp *ne;
     559             : 
     560             :                         /* include project or just lookup */
     561         105 :                         if (e->l) {
     562         105 :                                 ne = exps_bind_column2(rel->exps, e->l, e->r, NULL);
     563             :                         } else {
     564           0 :                                 ne = exps_bind_column(rel->exps, e->r, NULL, NULL, 1);
     565             :                         }
     566         105 :                         if (ne) {
     567          45 :                                 if (ne->type == e_column) {
     568             :                                         /* deref alias */
     569          42 :                                         e->l = ne->l;
     570          42 :                                         e->r = ne->r;
     571             :                                 } else {
     572             :                                         return push_up_project_exp(sql, rel, ne);
     573             :                                 }
     574             :                         }
     575             :                 } break;
     576          36 :         case e_atom:
     577          36 :                 if (e->f)
     578           0 :                         e->f = push_up_project_exps(sql, rel, e->f);
     579             :                 break;
     580             :         case e_psm:
     581             :                 break;
     582             :         }
     583             :         return e;
     584             : }
     585             : 
     586             : static sql_exp *exp_rewrite(mvc *sql, sql_rel *rel, sql_exp *e, list *ad);
     587             : 
     588             : static list *
     589        3183 : exps_rewrite(mvc *sql, sql_rel *rel, list *exps, list *ad)
     590             : {
     591             :         list *nexps;
     592             :         node *n;
     593             : 
     594        3183 :         if (list_empty(exps))
     595             :                 return exps;
     596        3168 :         nexps = sa_list(sql->sa);
     597        9820 :         for(n=exps->h; n; n = n->next)
     598        6652 :                 append(nexps, exp_rewrite(sql, rel, n->data, ad));
     599             :         return nexps;
     600             : }
     601             : 
     602             : /* recursively rewrite some functions */
     603             : static sql_exp *
     604       27938 : exp_rewrite(mvc *sql, sql_rel *rel, sql_exp *e, list *ad)
     605             : {
     606             :         sql_subfunc *sf;
     607             : 
     608       27938 :         if (e->type == e_convert) {
     609        2264 :                 e->l = exp_rewrite(sql, rel, e->l, ad);
     610        2264 :                 return e;
     611             :         }
     612       25674 :         if (e->type != e_func)
     613             :                 return e;
     614        3183 :         e->l = exps_rewrite(sql, rel, e->l, ad);
     615        3183 :         sf = e->f;
     616             :         /* window functions need to be run per freevars */
     617        3183 :         if (sf->func->type == F_ANALYTIC && strcmp(sf->func->base.name, "window_bound") != 0 && strcmp(sf->func->base.name, "diff") != 0 && ad) {
     618          40 :                 sql_subtype *bt = sql_bind_localtype("bit");
     619          40 :                 list *rankopargs = e->l, *gbe = ((list*)e->r)->h->data;
     620          40 :                 sql_exp *pe = list_empty(gbe) ? NULL : (sql_exp*)gbe->t->data, *last;
     621             :                 bool has_pe = pe != NULL;
     622             :                 int i = 0;
     623             : 
     624          16 :                 if (!pe || pe->type != e_func || strcmp(((sql_subfunc *)pe->f)->func->base.name, "diff") != 0)
     625             :                         pe = NULL;
     626             : 
     627          98 :                 for(node *d = ad->h; d; d=d->next) {
     628             :                         sql_subfunc *df;
     629          58 :                         sql_exp *de = d->data;
     630          58 :                         list *args = sa_list(sql->sa);
     631          58 :                         if (pe) {
     632          18 :                                 df = sql_bind_func(sql, NULL, "diff", bt, exp_subtype(de), F_ANALYTIC);
     633          18 :                                 append(args, pe);
     634             :                         } else {
     635          40 :                                 df = sql_bind_func(sql, NULL, "diff", exp_subtype(de), NULL, F_ANALYTIC);
     636             :                         }
     637          58 :                         assert(df);
     638          58 :                         append(args, de);
     639          58 :                         pe = exp_op(sql->sa, args, df);
     640             :                 }
     641             : 
     642          87 :                 for (node *n = rankopargs->h; n ; n = n->next, i++) { /* at rel_select pe is added right after the function's arguments */
     643          87 :                         if (i == list_length(sf->func->ops)) {
     644          40 :                                 n->data = pe;
     645          40 :                                 break;
     646             :                         }
     647             :                 }
     648          40 :                 last = rankopargs->t->data; /* if the window function has bounds calls, update them */
     649          40 :                 if (last && last->type == e_func && !strcmp(((sql_subfunc *)last->f)->func->base.name, "window_bound")) {
     650           9 :                         sql_exp *window1 = list_fetch(rankopargs, list_length(rankopargs) - 2), *window2 = list_fetch(rankopargs, list_length(rankopargs) - 1);
     651           9 :                         list *lw1 = window1->l, *lw2 = window2->l; /* the value functions require bound functions always */
     652             : 
     653           9 :                         if (has_pe) {
     654           7 :                                 assert(list_length(window1->l) == 6);
     655           7 :                                 lw1->h->data = exp_copy(sql, pe);
     656           7 :                                 lw2->h->data = exp_copy(sql, pe);
     657             :                         } else {
     658           2 :                                 window1->l = list_prepend(lw1, exp_copy(sql, pe));
     659           2 :                                 window2->l = list_prepend(lw2, exp_copy(sql, pe));
     660             :                         }
     661             :                 }
     662             :         }
     663             :         return e;
     664             : }
     665             : 
     666             : static sql_exp *
     667        1686 : rel_reduce2one_exp(mvc *sql, sql_rel *sq)
     668             : {
     669             :         sql_exp *e = NULL;
     670             : 
     671        1686 :         if (list_empty(sq->exps))
     672             :                 return NULL;
     673        1686 :         if (list_length(sq->exps) == 1)
     674        1598 :                 return sq->exps->t->data;
     675         230 :         for(node *n = sq->exps->h; n && !e; n = n->next) {
     676         142 :                 sql_exp *t = n->data;
     677             : 
     678         142 :                 if (!is_freevar(t))
     679             :                         e = t;
     680             :         }
     681          88 :         if (!e)
     682           6 :                 e = sq->exps->t->data;
     683          88 :         sq->exps = append(sa_list(sql->sa), e);
     684          88 :         return e;
     685             : }
     686             : 
     687             : static sql_exp *
     688          12 : rel_bound_exp(mvc *sql, sql_rel *rel )
     689             : {
     690          13 :         while (rel->l) {
     691             :                 rel = rel->l;
     692          13 :                 if (is_base(rel->op) || is_project(rel->op))
     693             :                         break;
     694             :         }
     695             : 
     696          12 :         if (rel) {
     697             :                 node *n;
     698          12 :                 for(n = rel->exps->h; n; n = n->next){
     699          12 :                         sql_exp *e = n->data;
     700             : 
     701          12 :                         if (exp_is_atom(e))
     702           1 :                                 return e;
     703          11 :                         if (!is_freevar(e))
     704          11 :                                 return exp_ref(sql, e);
     705             :                 }
     706             :         }
     707           0 :         if (rel && is_project(rel->op)) /* add dummy expression */
     708           0 :                 return rel_project_add_exp(sql, rel, exp_atom_bool(sql->sa, 1));
     709             :         return NULL;
     710             : }
     711             : 
     712             : /*
     713             :  * join j was just rewriten, but some join expressions may now
     714             :  * be too low in de relation rel. These need to move up.
     715             :  * */
     716             : static void
     717        6946 : move_join_exps(mvc *sql, sql_rel *j, sql_rel *rel)
     718             : {
     719             :         node *n;
     720        6946 :         list *exps = rel->exps;
     721             : 
     722        6946 :         if (!exps)
     723             :                 return;
     724          92 :         rel->exps = sa_list(sql->sa);
     725          92 :         if (!j->exps)
     726          40 :                 j->exps = sa_list(sql->sa);
     727         128 :         for(n = exps->h; n; n = n->next){
     728          36 :                 sql_exp *e = n->data;
     729             : 
     730          36 :                 if (rel_find_exp(rel, e)) {
     731           0 :                         if (exp_has_freevar(sql, e))
     732           0 :                                 rel_bind_var(sql, rel->l, e);
     733           0 :                         append(rel->exps, e);
     734             :                 } else {
     735          36 :                         if (exp_has_freevar(sql, e))
     736           1 :                                 rel_bind_var(sql, j->l, e);
     737          36 :                         append(j->exps, e);
     738             :                 }
     739             :         }
     740             : }
     741             : 
     742             : static sql_rel *
     743        6804 : rel_general_unnest(mvc *sql, sql_rel *rel, list *ad)
     744             : {
     745        6804 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel) && ad) {
     746             :                 list *fd;
     747             :                 node *n, *m;
     748             :                 int nr;
     749             : 
     750        6804 :                 sql_rel *l = rel->l, *r = rel->r, *inner_r;
     751             : 
     752             :                 /* cleanup empty selects (should be done before any rel_dup(l) */
     753        6804 :                 if (l && is_select(l->op) && list_empty(l->exps) && !rel_is_ref(l)) {
     754           0 :                         rel->l = l->l;
     755           0 :                         l->l = NULL;
     756           0 :                         rel_destroy(l);
     757           0 :                         l = rel->l;
     758             :                 }
     759             :                 /* rewrite T1 dependent join T2 -> T1 join D dependent join T2, where the T1/D join adds (equality) predicates (for the Domain (ad)) and D is are the distinct(projected(ad) from T1)  */
     760        6804 :                 sql_rel *D = rel_project(sql->sa, rel_dup(l), exps_copy(sql, ad));
     761        6804 :                 set_distinct(D);
     762             : 
     763        6804 :                 int single = is_single(r);
     764        6804 :                 reset_single(r);
     765             :                 sql_rel *or = r;
     766        6804 :                 r = rel_crossproduct(sql->sa, D, r, rel->op);
     767        6804 :                 if (single)
     768           2 :                         set_single(or);
     769        6804 :                 r->op = op_join;
     770        6804 :                 move_join_exps(sql, rel, r);
     771        6804 :                 set_dependent(r);
     772             :                 inner_r = r;
     773        6804 :                 r = rel_project(sql->sa, r, (is_semi(inner_r->op))?sa_list(sql->sa):rel_projections(sql, r->r, NULL, 1, 1));
     774             : 
     775        6804 :                 if (!is_semi(inner_r->op))  { /* skip the free vars */
     776        6804 :                         list *exps = sa_list(sql->sa);
     777             : 
     778       15017 :                         for(node *n=r->exps->h; n; n = n->next) {
     779        8213 :                                 sql_exp *e = n->data, *ne = NULL;
     780             : 
     781        8213 :                                 if (e->l) {
     782        8204 :                                         ne = exps_bind_column2(ad, e->l, e->r, NULL);
     783             :                                 } else {
     784           9 :                                         ne = exps_bind_column(ad, e->r, NULL, NULL, 1);
     785             :                                 }
     786        8213 :                                 if (!ne)
     787        8159 :                                         append(exps,e);
     788             :                         }
     789        6804 :                         r->exps = exps;
     790             :                 }
     791             : 
     792             :                 /* append ad + rename */
     793        6804 :                 nr = sql->label+1;
     794        6804 :                 sql->label += list_length(ad);
     795        6804 :                 fd = exps_label(sql->sa, exps_copy(sql, ad), nr);
     796       18787 :                 for (n = ad->h, m = fd->h; n && m; n = n->next, m = m->next) {
     797       11983 :                         sql_exp *l = n->data, *r = m->data, *e;
     798             : 
     799       11983 :                         l = exp_ref(sql, l);
     800       11983 :                         r = exp_ref(sql, r);
     801       11983 :                         e = exp_compare(sql->sa, l, r, cmp_equal);
     802       11983 :                         set_semantics(e);
     803       11983 :                         if (!rel->exps)
     804        6780 :                                 rel->exps = sa_list(sql->sa);
     805       11983 :                         append(rel->exps, e);
     806             :                 }
     807        6804 :                 list_merge(r->exps, fd, (fdup)NULL);
     808        6804 :                 rel->r = r;
     809        6804 :                 reset_dependent(rel);
     810        6804 :                 return rel;
     811             :         }
     812             :         return rel;
     813             : }
     814             : 
     815             : static sql_rel *
     816       25653 : push_up_project(mvc *sql, sql_rel *rel, list *ad)
     817             : {
     818       25653 :         sql_rel *r = rel->r;
     819             : 
     820       25653 :         if (rel_is_ref(r)) {
     821          71 :                 sql_rel *nr = rel_project(sql->sa, rel_dup(r->l), exps_copy(sql, r->exps));
     822          71 :                 rel_destroy(r);
     823          71 :                 rel->r = r = nr;
     824             :         }
     825             : 
     826             :         /* 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) */
     827       25653 :         if (rel && is_join(rel->op) && is_dependent(rel)) {
     828       24739 :                 sql_rel *r = rel->r;
     829             : 
     830             :                 /* find constant expressions and move these down */
     831       24739 :                 if (r && r->op == op_project) {
     832             :                         node *n;
     833             :                         list *nexps = NULL;
     834             :                         list *cexps = NULL;
     835       24739 :                         sql_rel *l = r->l;
     836             : 
     837       24739 :                         if (l && (is_select(l->op) || l->op == op_join) && !rel_is_ref(l)) {
     838       28826 :                                 for(n=r->exps->h; n; n=n->next) {
     839       16666 :                                         sql_exp *e = n->data;
     840             : 
     841       16666 :                                         if (exp_is_atom(e) || rel_find_exp(l->l,e)) { /* move down */
     842       16368 :                                                 if (!cexps)
     843       12113 :                                                         cexps = sa_list(sql->sa);
     844       16368 :                                                 append(cexps, e);
     845             :                                         } else {
     846         298 :                                                 if (!nexps)
     847         108 :                                                         nexps = sa_list(sql->sa);
     848         298 :                                                 append(nexps, e);
     849             :                                         }
     850             :                                 }
     851       12160 :                                 if (cexps) {
     852       12113 :                                         sql_rel *p = l->l = rel_project( sql->sa, l->l,
     853       12113 :                                                 rel_projections(sql, l->l, NULL, 1, 1));
     854       12113 :                                         p->exps = list_merge(p->exps, cexps, (fdup)NULL);
     855       12113 :                                         if (list_empty(nexps)) {
     856       12052 :                                                 rel->r = l; /* remove empty project */
     857             :                                         } else {
     858         282 :                                                 for (n = cexps->h; n; n = n->next) { /* add pushed down renamed expressions */
     859         221 :                                                         sql_exp *e = n->data;
     860         221 :                                                         append(nexps, exp_ref(sql, e));
     861             :                                                 }
     862          61 :                                                 r->exps = nexps;
     863             :                                         }
     864             :                                 }
     865             :                         }
     866             :                 }
     867             :         }
     868             :         /* input rel is dependent join with on the right a project */
     869       25653 :         if (rel && is_join(rel->op) && is_dependent(rel)) {
     870       24739 :                 sql_rel *r = rel->r;
     871             : 
     872       24739 :                 if (r && r->op == op_project) {
     873             :                         sql_exp *id = NULL;
     874             :                         node *m;
     875             :                         /* move project up, ie all attributes of left + the old expression list */
     876       12687 :                         sql_rel *n = rel_project( sql->sa, (r->l)?rel:rel->l,
     877       12687 :                                         rel_projections(sql, rel->l, NULL, 1, 1));
     878             : 
     879             :                         /* only pass bound variables */
     880       12687 :                         if (is_left(rel->op) && exps_have_freevar(sql, r->exps)) {
     881          12 :                                 id = rel_bound_exp(sql, r);
     882          12 :                                 id = rel_project_add_exp(sql, n, id);
     883             :                         }
     884       32647 :                         for (m=r->exps->h; m; m = m->next) {
     885       19960 :                                 sql_exp *e = m->data;
     886             : 
     887       19960 :                                 if (!is_freevar(e) || exp_name(e)) { /* only skip full freevars */
     888       19960 :                                         if (exp_has_freevar(sql, e)) {
     889        1508 :                                                 rel_bind_var(sql, rel->l, e);
     890        1508 :                                                 if (is_left(rel->op)) { /* add ifthenelse */
     891             :                                                         /* need bound var from r */
     892             :                                                         /* if id is NULL then NULL else e */
     893          12 :                                                         sql_exp *ne = rel_unop_(sql, NULL, exp_copy(sql, id), "sys", "isnull", card_value);
     894          12 :                                                         set_has_no_nil(ne);
     895          12 :                                                         ne = rel_nop_(sql, NULL, ne, exp_null(sql->sa, exp_subtype(e)), e, NULL, "sys", "ifthenelse", card_value);
     896          12 :                                                         exp_prop_alias(sql->sa, ne, e);
     897             :                                                         e = ne;
     898             :                                                 }
     899             :                                         }
     900             :                                 }
     901       19960 :                                 if (r->l)
     902       19022 :                                         e = exp_rewrite(sql, r->l, e, ad);
     903       19960 :                                 append(n->exps, e);
     904             :                         }
     905       12687 :                         if (r->r) {
     906          23 :                                 list *exps = r->r, *oexps = n->r = sa_list(sql->sa);
     907             : 
     908          61 :                                 for (m=exps->h; m; m = m->next) {
     909          38 :                                         sql_exp *e = m->data;
     910             : 
     911          38 :                                         if (!is_freevar(e) || exp_name(e)) { /* only skip full freevars */
     912          38 :                                                 if (exp_has_freevar(sql, e))
     913           0 :                                                         rel_bind_var(sql, rel->l, e);
     914             :                                         }
     915          38 :                                         append(oexps, e);
     916             :                                 }
     917             :                         }
     918             :                         /* remove old project */
     919       12687 :                         if (r->l) {
     920       11767 :                                 rel->r = r->l;
     921       11767 :                                 r->l = NULL;
     922             :                         }
     923       12687 :                         rel_destroy(r);
     924       12687 :                         return n;
     925             :                 }
     926             :         }
     927             :         /* a dependent semi/anti join with a project on the right side, could be removed */
     928       12966 :         if (rel && is_semi(rel->op) && is_dependent(rel)) {
     929         914 :                 sql_rel *r = rel->r;
     930             : 
     931             :                 /* merge project expressions into the join expressions  */
     932         914 :                 rel->exps = push_up_project_exps(sql, r, rel->exps);
     933             : 
     934         914 :                 if (r && r->op == op_project && r->l) {
     935             :                         /* remove old project */
     936         911 :                         rel->r = rel_dup(r->l);
     937         911 :                         rel_destroy(r);
     938         911 :                         return rel;
     939           3 :                 } else if (r && r->op == op_project) {
     940             :                         /* remove freevars from projection */
     941           3 :                         list *exps = r->exps, *nexps = sa_list(sql->sa);
     942             :                         node *m;
     943             : 
     944           6 :                         for (m=exps->h; m; m = m->next) {
     945           3 :                                 sql_exp *e = m->data;
     946             : 
     947           3 :                                 if (!exp_has_freevar(sql, e))
     948           0 :                                         append(nexps, e);
     949             :                         }
     950           3 :                         if (list_empty(nexps)) {
     951           3 :                                 assert(!r->l);
     952             :                                 /* remove old project and change outer into select */
     953           3 :                                 rel->r = NULL;
     954           3 :                                 rel_destroy(r);
     955           3 :                                 rel->op = op_select;
     956           3 :                                 return rel;
     957             :                         }
     958           0 :                         r->exps = nexps;
     959             :                 }
     960             :         }
     961             :         return rel;
     962             : }
     963             : 
     964             : static sql_rel *
     965           4 : push_up_topn_and_sample(mvc *sql, sql_rel *rel)
     966             : {
     967             :         /* a dependent semi/anti join with a project on the right side, could be removed */
     968           4 :         if (rel && (is_semi(rel->op) || is_join(rel->op)) && is_dependent(rel)) {
     969           4 :                 sql_rel *r = rel->r;
     970             : 
     971           4 :                 if (r && (is_topn(r->op) || is_sample(r->op))) {
     972             :                         /* remove old topn/sample */
     973           4 :                         sql_rel *(*func) (sql_allocator *, sql_rel *, list *) = is_topn(r->op) ? rel_topn : rel_sample;
     974           4 :                         rel->r = rel_dup(r->l);
     975           4 :                         rel = func(sql->sa, rel, r->exps);
     976           4 :                         rel_destroy(r);
     977           4 :                         return rel;
     978             :                 }
     979             :         }
     980             :         return rel;
     981             : }
     982             : 
     983             : static sql_rel *
     984       13707 : push_up_select(mvc *sql, sql_rel *rel, list *ad)
     985             : {
     986       13707 :         sql_rel *d = rel->l;
     987       13707 :         sql_rel *r = rel->r;
     988             :         int inner = 0;
     989             : 
     990       13707 :         if (rel && is_dependent(rel) && r && is_select(r->op)) {
     991       13707 :                 sql_rel *rl = r->l;
     992             : 
     993       13707 :                 if (rl && rel_has_freevar(sql, rl)) {
     994        4035 :                         list *inner_ad = rel_dependent_var(sql, d, rl);
     995             : 
     996        4035 :                         inner = !list_empty(inner_ad);
     997             :                 }
     998             :         }
     999        4035 :         if (inner && is_left(rel->op) && !need_distinct(d))
    1000          13 :                 return rel_general_unnest(sql, rel, ad);
    1001             :         /* input rel is dependent join with on the right a select */
    1002       13694 :         if ((!inner || is_semi(rel->op)) && rel && is_dependent(rel)) {
    1003        9687 :                 sql_rel *r = rel->r;
    1004             : 
    1005        9687 :                 if (r && is_select(r->op)) { /* move into join */
    1006             :                         node *n;
    1007             : 
    1008       20629 :                         for (n=r->exps->h; n; n = n->next) {
    1009       10942 :                                 sql_exp *e = n->data;
    1010             : 
    1011       10942 :                                 e = exp_copy(sql, e);
    1012       10942 :                                 if (exp_has_freevar(sql, e))
    1013       10924 :                                         rel_bind_var(sql, rel->l, e);
    1014       10942 :                                 rel_join_add_exp(sql->sa, rel, e);
    1015             :                         }
    1016             :                         /* remove select */
    1017        9687 :                         rel->r = rel_dup(r->l);
    1018        9687 :                         rel_destroy(r);
    1019        9687 :                         r = rel->r;
    1020        9687 :                         if (is_single(r)) {
    1021           0 :                                 set_single(rel);
    1022           0 :                                 rel->op = op_left;
    1023             :                         }
    1024        9687 :                         if (!inner)
    1025        9674 :                                 reset_dependent(rel);
    1026             :                 }
    1027        4007 :         } else if (rel && is_join(rel->op) && is_dependent(rel)) {
    1028        4007 :                 int cp = rel_is_ref(r);
    1029        4007 :                 sql_rel *r = rel->r;
    1030        4007 :                 list *exps = r->exps;
    1031             : 
    1032             :                 /* remove select */
    1033        4007 :                 rel->r = rel_dup(r->l);
    1034        4007 :                 rel = rel_select(sql->sa, rel, NULL);
    1035        4007 :                 rel->exps = !cp?exps:exps_copy(sql, exps);
    1036        4007 :                 rel_destroy(r);
    1037             :         }
    1038             :         return rel;
    1039             : }
    1040             : 
    1041             : static int
    1042          14 : exps_is_constant( list *exps )
    1043             : {
    1044             :         sql_exp *e;
    1045             : 
    1046          14 :         if (!exps || list_empty(exps))
    1047           0 :                 return 1;
    1048          14 :         if (list_length(exps) > 1)
    1049             :                 return 0;
    1050          14 :         e = exps->h->data;
    1051          14 :         return exp_is_atom(e);
    1052             : }
    1053             : 
    1054             : static int
    1055      713460 : exp_is_count(sql_exp *e, sql_rel *rel)
    1056             : {
    1057     1323665 :         if (!e || !rel)
    1058             :                 return 0;
    1059      878350 :         if (is_alias(e->type) && is_project(rel->op)) {
    1060      610105 :                 sql_exp *ne = rel_find_exp(rel->l, e);
    1061      610105 :                 return exp_is_count(ne, rel->l);
    1062             :         }
    1063      268245 :         if (is_convert(e->type))
    1064         100 :                 return exp_is_count(e->l, rel);
    1065      268145 :         if (is_aggr(e->type) && exp_aggr_is_count(e))
    1066        8954 :                 return 1;
    1067             :         return 0;
    1068             : }
    1069             : 
    1070             : static sql_rel *
    1071        4401 : push_up_groupby(mvc *sql, sql_rel *rel, list *ad)
    1072             : {
    1073             :         /* input rel is dependent join with on the right a groupby */
    1074        4401 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1075        4401 :                 sql_rel *l = rel->l, *r = rel->r;
    1076             : 
    1077             :                 /* left of rel should be a set */
    1078        4401 :                 if (l && is_distinct_set(sql, l, ad) && r && is_groupby(r->op)) {
    1079        4401 :                         list *sexps, *jexps, *a = rel_projections(sql, rel->l, NULL, 1, 1);
    1080             :                         node *n;
    1081        4401 :                         sql_exp *id = NULL;
    1082             : 
    1083             :                         /* move groupby up, ie add attributes of left + the old expression list */
    1084             : 
    1085        4401 :                         if (l && list_length(a) > 1 && !need_distinct(l)) { /* add identity call only if there's more than one column in the groupby */
    1086           6 :                                 rel->l = rel_add_identity(sql, l, &id); /* add identity call for group by */
    1087           6 :                                 assert(id);
    1088             :                         }
    1089             : 
    1090        4401 :                         assert(rel->op != op_anti);
    1091        4401 :                         if (rel->op == op_semi && !need_distinct(l))
    1092           0 :                                 rel->op = op_join;
    1093             : 
    1094        8953 :                         for (n = r->exps->h; n; n = n->next ) {
    1095        4552 :                                 sql_exp *e = n->data;
    1096             : 
    1097             :                                 /* count_nil(* or constant) -> count(t.TID) */
    1098        4552 :                                 if (exp_is_count(e, r) && (!e->l || exps_is_constant(e->l))) {
    1099             :                                         sql_exp *col;
    1100        2122 :                                         sql_rel *p = r->l; /* ugh */
    1101             : 
    1102        2122 :                                         if (!is_project(p->op))
    1103        2122 :                                                 r->l = p = rel_project(sql->sa, p, rel_projections(sql, p, NULL, 1, 1));
    1104        2122 :                                         col = p->exps->t->data;
    1105        2122 :                                         if (strcmp(exp_name(col), TID) != 0) {
    1106           6 :                                                 col = exp_ref(sql, col);
    1107           6 :                                                 col = exp_unop(sql->sa, col, sql_bind_func(sql, "sys", "identity", exp_subtype(col), NULL, F_FUNC));
    1108           6 :                                                 set_has_no_nil(col);
    1109           6 :                                                 set_unique(col);
    1110           6 :                                                 col = exp_label(sql->sa, col, ++sql->label);
    1111           6 :                                                 append(p->exps, col);
    1112             :                                         }
    1113        2122 :                                         col = exp_ref(sql, col);
    1114        2122 :                                         append(e->l=sa_list(sql->sa), col);
    1115        2122 :                                         set_no_nil(e);
    1116             :                                 }
    1117        4552 :                                 if (exp_has_freevar(sql, e))
    1118          10 :                                         rel_bind_var(sql, rel->l, e);
    1119             :                         }
    1120        4401 :                         r->exps = list_merge(r->exps, a, (fdup)NULL);
    1121        4401 :                         if (!r->r) {
    1122        4356 :                                 if (id)
    1123           4 :                                         r->r = list_append(sa_list(sql->sa), exp_ref(sql, id));
    1124             :                                 else
    1125        4352 :                                         r->r = exps_copy(sql, a);
    1126        4356 :                                 r->card = CARD_AGGR;
    1127             :                                 /* After the unnesting, the cardinality of the aggregate function becomes larger */
    1128       14945 :                                 for(node *n = r->exps->h; n; n = n->next) {
    1129       10589 :                                         sql_exp *e = n->data;
    1130             : 
    1131       10589 :                                         e->card = CARD_AGGR;
    1132             :                                 }
    1133             :                         } else {
    1134          97 :                                 for (n = ((list*)r->r)->h; n; n = n->next ) {
    1135          52 :                                         sql_exp *e = n->data;
    1136             : 
    1137          52 :                                         if (exp_has_freevar(sql, e))
    1138          15 :                                                 rel_bind_var(sql, rel->l, e);
    1139             :                                 }
    1140          45 :                                 if (id)
    1141           2 :                                         list_append(r->r, exp_ref(sql, id));
    1142             :                                 else
    1143          43 :                                         r->r = list_distinct(list_merge(r->r, exps_copy(sql, a), (fdup)NULL), (fcmp)exp_equal, (fdup)NULL);
    1144             :                         }
    1145             : 
    1146        4401 :                         if (!r->l) {
    1147           0 :                                 r->l = rel->l;
    1148           0 :                                 rel->l = NULL;
    1149           0 :                                 rel->r = NULL;
    1150           0 :                                 rel_destroy(rel);
    1151             :                                 /* merge (distinct) projects / group by (over the same group by cols) */
    1152           0 :                                 while (r->l && exps_have_freevar(sql, r->exps)) {
    1153           0 :                                         sql_rel *l = r->l;
    1154             : 
    1155           0 :                                         if (!is_project(l->op))
    1156             :                                                 break;
    1157           0 :                                         if (l->op == op_project && need_distinct(l)) { /* TODO: check if group by exps and distinct list are equal */
    1158           0 :                                                 r->l = rel_dup(l->l);
    1159           0 :                                                 rel_destroy(l);
    1160             :                                         }
    1161           0 :                                         if (is_groupby(l->op)) { /* TODO: check if group by exps and distinct list are equal */
    1162             :                                                 /* add aggr exps of r to l, replace r by l */
    1163             :                                                 node *n;
    1164           0 :                                                 for(n = r->exps->h; n; n = n->next) {
    1165           0 :                                                         sql_exp *e = n->data;
    1166             : 
    1167           0 :                                                         if (e->type == e_aggr)
    1168           0 :                                                                 append(l->exps, e);
    1169           0 :                                                         if (exp_has_freevar(sql, e))
    1170           0 :                                                                 rel_bind_var(sql, l, e);
    1171             :                                                 }
    1172           0 :                                                 r->l = NULL;
    1173           0 :                                                 rel_destroy(r);
    1174             :                                                 r = l;
    1175             :                                         }
    1176             :                                 }
    1177           0 :                                 return r;
    1178             :                         } else {
    1179        4401 :                                 rel->r = r->l;
    1180        4401 :                                 r->l = rel;
    1181             :                         }
    1182             :                         /* check if a join expression needs to be moved above the group by (into a select) */
    1183        4401 :                         sexps = sa_list(sql->sa);
    1184        4401 :                         jexps = sa_list(sql->sa);
    1185        4401 :                         if (rel->exps) {
    1186          11 :                                 for (n = rel->exps->h; n; n = n->next ) {
    1187           0 :                                         sql_exp *e = n->data;
    1188             : 
    1189           0 :                                         if (rel_find_exp(rel, e)) {
    1190           0 :                                                 append(jexps, e);
    1191             :                                         } else {
    1192           0 :                                                 append(sexps, e);
    1193             :                                         }
    1194             :                                 }
    1195             :                         }
    1196        4401 :                         rel->exps = jexps;
    1197        4401 :                         if (list_length(sexps)) {
    1198           0 :                                 r = rel_select(sql->sa, r, NULL);
    1199           0 :                                 r->exps = sexps;
    1200             :                         }
    1201        4401 :                         return r;
    1202             :                 }
    1203             :         }
    1204             :         return rel;
    1205             : }
    1206             : 
    1207             : static sql_rel *
    1208           0 : push_up_select_l(mvc *sql, sql_rel *rel)
    1209             : {
    1210             :         (void)sql;
    1211             :         /* input rel is dependent join with on the right a project */
    1212           0 :         if (rel && (is_join(rel->op) || is_semi(rel->op))) {
    1213           0 :                 sql_rel *l = rel->l;
    1214             : 
    1215           0 :                 if (is_select(l->op) && rel_has_freevar(sql, l) && !rel_is_ref(l) ) {
    1216             :                         /* push up select (above join) */
    1217           0 :                         rel->l = l->l;
    1218           0 :                         l->l = rel;
    1219           0 :                         return l;
    1220             :                 }
    1221             :         }
    1222             :         return rel;
    1223             : }
    1224             : 
    1225             : static sql_rel *
    1226         275 : push_up_join(mvc *sql, sql_rel *rel, list *ad)
    1227             : {
    1228         275 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1229         275 :                 sql_rel *j = rel->r;
    1230             : 
    1231         275 :                 if (j->op == op_join && !rel_is_ref(rel) && !rel_is_ref(j) && j->exps) {
    1232          58 :                         rel->exps =  rel->exps?list_merge(rel->exps, j->exps, (fdup)NULL):j->exps;
    1233          58 :                         j->exps = NULL;
    1234          58 :                         return rel;
    1235             :                 }
    1236             :         }
    1237             : 
    1238             :         /* input rel is dependent join with on the right a project */
    1239         217 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1240         217 :                 sql_rel *d = rel->l, *j = rel->r;
    1241             : 
    1242             :                 /* left of rel should be a set */
    1243         217 :                 if (d && is_distinct_set(sql, d, ad) && j && (is_join(j->op) || is_semi(j->op))) {
    1244         217 :                         sql_rel *jl = j->l, *jr = j->r;
    1245             :                         /* op_join if F(jl) intersect A(D) = empty -> jl join (D djoin jr)
    1246             :                          *            F(jr) intersect A(D) = empty -> (D djoin jl) join jr
    1247             :                          *       else (D djoin jl) natural join (D djoin jr)
    1248             :                          *
    1249             :                          * */
    1250             :                         list *rd = NULL, *ld = NULL;
    1251             : 
    1252         217 :                         if (is_semi(j->op) && is_select(jl->op) && rel_has_freevar(sql, jl) && !rel_is_ref(jl)) {
    1253           0 :                                 rel->r = j = push_up_select_l(sql, j);
    1254           0 :                                 return rel; /* ie try again */
    1255             :                         }
    1256         217 :                         rd = (j->op != op_full && j->op != op_right)?rel_dependent_var(sql, d, jr):(list*)1;
    1257         217 :                         ld = ((j->op == op_join || j->op == op_right))?rel_dependent_var(sql, d, jl):(list*)1;
    1258             : 
    1259         217 :                         if (ld && rd) {
    1260             :                                 node *m;
    1261             :                                 sql_rel *n, *nr, *nj;
    1262          75 :                                 list *inner_exps = exps_copy(sql, j->exps);
    1263          75 :                                 list *outer_exps = exps_copy(sql, rel->exps);
    1264             : 
    1265          75 :                                 rel->r = rel_dup(jl);
    1266          75 :                                 rel->exps = sa_list(sql->sa);
    1267          75 :                                 nj = rel_crossproduct(sql->sa, rel_dup(d), rel_dup(jr), j->op);
    1268          75 :                                 if (is_single(j))
    1269           7 :                                         set_single(nj);
    1270          75 :                                 rel_destroy(j);
    1271             :                                 j = nj;
    1272          75 :                                 set_dependent(j);
    1273          75 :                                 n = rel_crossproduct(sql->sa, rel, j, j->op);
    1274          75 :                                 n->exps = outer_exps;
    1275          75 :                                 if (!n->exps)
    1276          41 :                                         n->exps = inner_exps;
    1277             :                                 else
    1278          34 :                                         n->exps = list_merge(n->exps, inner_exps, (fdup)NULL);
    1279          75 :                                 j->op = rel->op;
    1280          75 :                                 if (is_semi(rel->op)) {
    1281           0 :                                         j->op = op_left;
    1282           0 :                                         rel->op = op_left;
    1283             :                                 }
    1284          75 :                                 n->l = rel_project(sql->sa, n->l, rel_projections(sql, n->l, NULL, 1, 1));
    1285          75 :                                 nr = n->r;
    1286          75 :                                 nr = n->r = rel_project(sql->sa, n->r, is_semi(nr->op)?sa_list(sql->sa):rel_projections(sql, nr->r, NULL, 1, 1));
    1287             :                                 /* add nr->l exps with labels */
    1288             :                                 /* create jexps */
    1289          75 :                                 if (!n->exps)
    1290          27 :                                         n->exps = sa_list(sql->sa);
    1291         211 :                                 for (m = d->exps->h; m; m = m->next) {
    1292         136 :                                         sql_exp *e = m->data, *pe, *je;
    1293             : 
    1294         136 :                                         pe = exp_ref(sql, e);
    1295         136 :                                         pe = exp_label(sql->sa, pe, ++sql->label);
    1296         136 :                                         append(nr->exps, pe);
    1297         136 :                                         pe = exp_ref(sql, pe);
    1298         136 :                                         e = exp_ref(sql, e);
    1299         136 :                                         je = exp_compare(sql->sa, e, pe, cmp_equal);
    1300         136 :                                         set_semantics(je);
    1301         136 :                                         append(n->exps, je);
    1302             :                                 }
    1303             :                                 return n;
    1304             :                         }
    1305             : 
    1306         142 :                         if (!rd) {
    1307         108 :                                 rel->r = rel_dup(jl);
    1308         108 :                                 sql_rel *nj = rel_crossproduct(sql->sa, rel, rel_dup(jr), j->op);
    1309         108 :                                 if (is_single(j))
    1310           0 :                                         set_single(nj);
    1311         108 :                                 nj->exps = exps_copy(sql, j->exps);
    1312         108 :                                 rel_destroy(j);
    1313             :                                 j = nj;
    1314         108 :                                 if (is_semi(rel->op)) {
    1315             :                                 //assert(!is_semi(rel->op));
    1316           2 :                                         rel->op = op_left;
    1317             :                                 }
    1318         108 :                                 move_join_exps(sql, j, rel);
    1319         108 :                                 return j;
    1320             :                         }
    1321          34 :                         if (!ld) {
    1322          34 :                                 rel->r = rel_dup(jr);
    1323          34 :                                 sql_rel *nj = rel_crossproduct(sql->sa, rel_dup(jl), rel, j->op);
    1324          34 :                                 if (is_single(j))
    1325           1 :                                         set_single(nj);
    1326          34 :                                 nj->exps = exps_copy(sql, j->exps);
    1327          34 :                                 rel_destroy(j);
    1328             :                                 j = nj;
    1329          34 :                                 if (is_semi(rel->op)) {
    1330             :                                 //assert(!is_semi(rel->op));
    1331           0 :                                         rel->op = op_left;
    1332             :                                 }
    1333          34 :                                 move_join_exps(sql, j, rel);
    1334          34 :                                 return j;
    1335             :                         }
    1336           0 :                         assert(0);
    1337             :                         return rel;
    1338             :                 }
    1339             :         }
    1340             :         return rel;
    1341             : }
    1342             : 
    1343             : static sql_rel *
    1344        3227 : push_up_set(mvc *sql, sql_rel *rel, list *ad)
    1345             : {
    1346        3227 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1347        3227 :                 sql_rel *d = rel->l, *s = rel->r;
    1348             : 
    1349             :                 /* left of rel should be a set */
    1350        3227 :                 if (d && is_distinct_set(sql, d, ad) && s && is_set(s->op)) {
    1351        3227 :                         sql_rel *sl = s->l, *sr = s->r, *n;
    1352             : 
    1353        3227 :                         sl = rel_project(sql->sa, sl, rel_projections(sql, sl, NULL, 1, 1));
    1354        6888 :                         for (node *n = sl->exps->h, *m = s->exps->h; n && m; n = n->next, m = m->next)
    1355        3661 :                                 exp_prop_alias(sql->sa, n->data, m->data);
    1356        3227 :                         sr = rel_project(sql->sa, sr, rel_projections(sql, sr, NULL, 1, 1));
    1357        6888 :                         for (node *n = sr->exps->h, *m = s->exps->h; n && m; n = n->next, m = m->next)
    1358        3661 :                                 exp_prop_alias(sql->sa, n->data, m->data);
    1359             :                         /* D djoin (sl setop sr) -> (D djoin sl) setop (D djoin sr) */
    1360        3227 :                         rel->r = sl;
    1361        3227 :                         n = rel_crossproduct(sql->sa, rel_dup(d), sr, rel->op);
    1362        3227 :                         n->exps = exps_copy(sql, rel->exps);
    1363        3227 :                         set_dependent(n);
    1364        3227 :                         s->l = rel;
    1365        3227 :                         s->r = n;
    1366        3227 :                         if (is_join(rel->op)) {
    1367        3223 :                                 list *sexps = sa_list(sql->sa), *dexps = rel_projections(sql, d, NULL, 1, 1);
    1368       11147 :                                 for (node *m = dexps->h; m; m = m->next) {
    1369        7924 :                                         sql_exp *e = m->data;
    1370             : 
    1371        7924 :                                         list_append(sexps, exp_ref(sql, e));
    1372             :                                 }
    1373        3223 :                                 s->exps = list_merge(sexps, s->exps, (fdup)NULL);
    1374             :                         }
    1375             :                         /* add/remove projections to inner parts of the union (as we push a join or semijoin down) */
    1376        3227 :                         s->l = rel_project(sql->sa, s->l, rel_projections(sql, s->l, NULL, 1, 1));
    1377        3227 :                         s->r = rel_project(sql->sa, s->r, rel_projections(sql, s->r, NULL, 1, 1));
    1378        3227 :                         if (is_semi(rel->op))
    1379           4 :                                 s->exps = rel_projections(sql, s->r, NULL, 1, 1);
    1380        3227 :                         return s;
    1381             :                 }
    1382             :         }
    1383             :         return rel;
    1384             : }
    1385             : 
    1386             : static sql_rel * rel_unnest_dependent(mvc *sql, sql_rel *rel);
    1387             : 
    1388             : static sql_rel *
    1389          17 : push_up_table(mvc *sql, sql_rel *rel, list *ad)
    1390             : {
    1391             :         (void)sql;
    1392          17 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1393          17 :                 sql_rel *d = rel->l, *tf = rel->r;
    1394          17 :                 sql_exp *id = NULL;
    1395             : 
    1396             :                 /* push d into function */
    1397          17 :                 if (d && is_distinct_set(sql, d, ad) && tf && is_base(tf->op)) {
    1398          17 :                         if (tf->l) {
    1399             :                                 sql_rel *l = tf->l;
    1400             : 
    1401          17 :                                 assert(tf->flag == TABLE_FROM_RELATION || !l->l);
    1402          17 :                                 if (l->l) {
    1403           8 :                                         sql_exp *tfe = tf->r;
    1404           8 :                                         list *ops = tfe->l;
    1405           8 :                                         rel->l = d = rel_add_identity(sql, d, &id);
    1406           8 :                                         id = exp_ref(sql, id);
    1407           8 :                                         l = tf->l = rel_crossproduct(sql->sa, rel_dup(d), l, op_join);
    1408           8 :                                         set_dependent(l);
    1409           8 :                                         tf->l = rel_unnest_dependent(sql, l);
    1410           8 :                                         list_append(ops, id);
    1411           8 :                                         id = exp_ref(sql, id);
    1412             :                                 } else {
    1413           9 :                                         l->l = rel_dup(d);
    1414             :                                 }
    1415             :                         } else {
    1416           0 :                                 tf->l = rel_dup(d);
    1417             :                         }
    1418             :                         /* we should add the identity in the resulting projection list */
    1419          17 :                         if (id) {
    1420           8 :                                 sql_exp *ne = exp_copy(sql, id);
    1421             : 
    1422           8 :                                 ne = exp_label(sql->sa, ne, ++sql->label);
    1423           8 :                                 ne = exp_ref(sql, ne);
    1424           8 :                                 list_prepend(tf->exps, ne);
    1425           8 :                                 ne = exp_ref(sql, ne);
    1426             : 
    1427           8 :                                 ne = exp_compare(sql->sa, id, ne, cmp_equal);
    1428             :                                 //set_semantics(e);
    1429           8 :                                 if (!rel->exps)
    1430           8 :                                         rel->exps = sa_list(sql->sa);
    1431           8 :                                 list_append(rel->exps, ne);
    1432             :                         }
    1433          17 :                         reset_dependent(rel);
    1434          17 :                         return rel;
    1435             :                 }
    1436             :         }
    1437             :         return rel;
    1438             : }
    1439             : 
    1440             : static sql_rel *
    1441    17724342 : rel_unnest_dependent(mvc *sql, sql_rel *rel)
    1442             : {
    1443             :         sql_rel *nrel = rel;
    1444             : 
    1445             :         if (mvc_highwater(sql))
    1446           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
    1447             : 
    1448             :         /* current unnest only possible for equality joins, <, <> etc needs more work */
    1449    17771613 :         if (rel && (is_join(rel->op) || is_semi(rel->op)) && is_dependent(rel)) {
    1450             :                 /* howto find out the left is a set */
    1451             :                 sql_rel *l, *r;
    1452             : 
    1453       55307 :                 l = rel->l;
    1454       55307 :                 r = rel->r;
    1455             : 
    1456       55307 :                 if (rel_has_freevar(sql, l)) {
    1457       21575 :                         rel->l = rel_unnest_dependent(sql, rel->l);
    1458       21575 :                         if (rel_has_freevar(sql, rel->l)) {
    1459       21575 :                                 if (rel->op == op_right) {
    1460           0 :                                         sql_rel *l = rel->l;
    1461             : 
    1462           0 :                                         rel->l = rel->r;
    1463           0 :                                         rel->r = l;
    1464           0 :                                         rel->op = op_left;
    1465           0 :                                         return rel_unnest_dependent(sql, rel);
    1466             :                                 }
    1467             :                         }
    1468             :                 }
    1469             : 
    1470       55307 :                 if (!rel_has_freevar(sql, r)) {
    1471        1223 :                         reset_dependent(rel);
    1472        1223 :                         return rel;
    1473             :                 }
    1474             : 
    1475             :                 /* try to push dependent join down */
    1476       54084 :                 if (rel_has_freevar(sql, r)) {
    1477       54084 :                         list *ad = rel_dependent_var(sql, rel->l, rel->r);
    1478             : 
    1479       54084 :                         if (r && is_select(r->op)) {
    1480       13713 :                                 sql_rel *l = r->l;
    1481             : 
    1482       13713 :                                 if (!rel_is_ref(r) && l && !rel_is_ref(l) && l->op == op_join && list_empty(l->exps)) {
    1483           4 :                                         l->exps = r->exps;
    1484           4 :                                         r->l = NULL;
    1485           4 :                                         rel_destroy(r);
    1486           4 :                                         rel->r = l;
    1487           4 :                                         return rel_unnest_dependent(sql, rel);
    1488             :                                 }
    1489             :                         }
    1490             : 
    1491       54080 :                         if (r && is_simple_project(r->op) && ((!exps_have_freevar(sql, r->exps) && !exps_have_unsafe(r->exps, 1)) || is_distinct_set(sql, l, ad))) {
    1492       25653 :                                 rel = push_up_project(sql, rel, ad);
    1493       25653 :                                 return rel_unnest_dependent(sql, rel);
    1494             :                         }
    1495             : 
    1496       28427 :                         if (r && (is_topn(r->op) || is_sample(r->op))) {
    1497           4 :                                 rel = push_up_topn_and_sample(sql, rel);
    1498           4 :                                 return rel_unnest_dependent(sql, rel);
    1499             :                         }
    1500             : 
    1501       28423 :                         if (r && is_select(r->op) && ad) {
    1502       13707 :                                 rel = push_up_select(sql, rel, ad);
    1503       13707 :                                 return rel_unnest_dependent(sql, rel);
    1504             :                         }
    1505             : 
    1506       14716 :                         if (r && is_groupby(r->op) && !is_left(rel->op) && is_distinct_set(sql, l, ad)) {
    1507        4401 :                                 rel = push_up_groupby(sql, rel, ad);
    1508        4401 :                                 return rel_unnest_dependent(sql, rel);
    1509             :                         }
    1510             : 
    1511       10315 :                         if (r && (is_join(r->op) || is_semi(r->op)) && is_distinct_set(sql, l, ad)) {
    1512         275 :                                 rel = push_up_join(sql, rel, ad);
    1513         275 :                                 return rel_unnest_dependent(sql, rel);
    1514             :                         }
    1515             : 
    1516       10040 :                         if (r && is_set(r->op) && !is_left(rel->op) && is_distinct_set(sql, l, ad)) {
    1517        3227 :                                 rel = push_up_set(sql, rel, ad);
    1518        3227 :                                 return rel_unnest_dependent(sql, rel);
    1519             :                         }
    1520             : 
    1521        6813 :                         if (r && is_base(r->op) && is_distinct_set(sql, l, ad)) {
    1522          17 :                                 rel = push_up_table(sql, rel, ad);
    1523          17 :                                 return rel;
    1524             :                         }
    1525             : 
    1526             :                         /* fallback */
    1527        6796 :                         if (ad != NULL)
    1528        6791 :                                 rel = rel_general_unnest(sql, rel, ad);
    1529             : 
    1530             :                         /* no dependent variables */
    1531        6796 :                         reset_dependent(rel);
    1532        6796 :                         rel->r = rel_unnest_dependent(sql, rel->r);
    1533             :                 } else {
    1534           0 :                         rel->l = rel_unnest_dependent(sql, rel->l);
    1535           0 :                         rel->r = rel_unnest_dependent(sql, rel->r);
    1536             :                 }
    1537             :         } else {
    1538    17716306 :                 if (rel && (is_simple_project(rel->op) || is_groupby(rel->op) || is_select(rel->op) || is_topn(rel->op) || is_sample(rel->op)))
    1539     7952081 :                         rel->l = rel_unnest_dependent(sql, rel->l);
    1540     9764225 :                 else if (rel && (is_join(rel->op) || is_semi(rel->op) ||  is_set(rel->op) || is_modify(rel->op) || is_ddl(rel->op))) {
    1541     4867634 :                         rel->l = rel_unnest_dependent(sql, rel->l);
    1542     4867634 :                         rel->r = rel_unnest_dependent(sql, rel->r);
    1543             :                 }
    1544             :         }
    1545             :         return nrel;
    1546             : }
    1547             : 
    1548             : static sql_rel *
    1549     3627220 : _rel_unnest(visitor *v, sql_rel *rel)
    1550             : {
    1551     3627220 :         if (is_dependent(rel)) {
    1552        8614 :                 rel = rel_unnest_dependent(v->sql, rel);
    1553        8614 :                 v->changes++;
    1554             :         }
    1555     3627220 :         return rel;
    1556             : }
    1557             : 
    1558             : static void exp_reset_props(sql_rel *rel, sql_exp *e, bool setnil);
    1559             : 
    1560             : static void
    1561          15 : exps_reset_props(sql_rel *rel, list *exps, bool setnil)
    1562             : {
    1563          15 :         if (!list_empty(exps))
    1564          50 :                 for(node *n=exps->h; n; n=n->next)
    1565          35 :                         exp_reset_props(rel, n->data, setnil);
    1566          15 : }
    1567             : 
    1568             : static void
    1569       23883 : exp_reset_props(sql_rel *rel, sql_exp *e, bool setnil)
    1570             : {
    1571       23883 :         switch (e->type) {
    1572       23810 :         case e_column: {
    1573       23810 :                 if (setnil && (((is_right(rel->op) || is_full(rel->op)) && rel_find_exp(rel->l, e) != NULL) ||
    1574       13378 :                         ((is_left(rel->op) || is_full(rel->op)) && rel_find_exp(rel->r, e) != NULL)))
    1575       13368 :                         set_has_nil(e);
    1576             :         } break;
    1577          48 :         case e_convert: {
    1578          48 :                 exp_reset_props(rel, e->l, setnil);
    1579          48 :                 if (setnil && has_nil((sql_exp*)e->l))
    1580          20 :                         set_has_nil(e);
    1581             :         } break;
    1582          15 :         case e_func: {
    1583          15 :                 sql_subfunc *f = e->f;
    1584             : 
    1585          15 :                 exps_reset_props(rel, e->l, setnil);
    1586          15 :                 if (setnil && !f->func->semantics && e->l && have_nil(e->l))
    1587           4 :                         set_has_nil(e);
    1588             :         } break;
    1589           0 :         case e_aggr: {
    1590           0 :                 sql_subfunc *a = e->f;
    1591             : 
    1592           0 :                 exps_reset_props(rel, e->l, setnil);
    1593           0 :                 if (setnil && (a->func->s || strcmp(a->func->base.name, "count") != 0) && !a->func->semantics && !has_nil(e) && e->l && have_nil(e->l))
    1594           0 :                         set_has_nil(e);
    1595             :         } break;
    1596           2 :         case e_cmp: {
    1597           2 :                 if (e->flag == cmp_or || e->flag == cmp_filter) {
    1598           0 :                         exps_reset_props(rel, e->l, setnil);
    1599           0 :                         exps_reset_props(rel, e->r, setnil);
    1600           0 :                         if (setnil && (have_nil(e->l) || have_nil(e->r)))
    1601           0 :                                 set_has_nil(e);
    1602           2 :                 } else if (e->flag == cmp_in || e->flag == cmp_notin) {
    1603           0 :                         exp_reset_props(rel, e->l, setnil);
    1604           0 :                         exps_reset_props(rel, e->r, setnil);
    1605           0 :                         if (setnil && (has_nil((sql_exp*)e->l) || have_nil(e->r)))
    1606           0 :                                 set_has_nil(e);
    1607             :                 } else {
    1608           2 :                         exp_reset_props(rel, e->l, setnil);
    1609           2 :                         exp_reset_props(rel, e->r, setnil);
    1610           2 :                         if (e->f)
    1611           2 :                                 exp_reset_props(rel, e->f, setnil);
    1612           2 :                         if (setnil && !is_semantics(e) && (((sql_exp*)e->l) || has_nil((sql_exp*)e->r) || (e->f && has_nil((sql_exp*)e->f))))
    1613           2 :                                 set_has_nil(e);
    1614             :                 }
    1615             :         } break;
    1616             :         default:
    1617             :                 break;
    1618             :         }
    1619       23883 :         set_not_unique(e);
    1620       23883 : }
    1621             : 
    1622             : static sql_exp *
    1623       24701 : rewrite_inner(mvc *sql, sql_rel *rel, sql_rel *inner, operator_type op, sql_rel **rewrite)
    1624             : {
    1625       24701 :         int single = is_single(inner);
    1626             :         sql_rel *d = NULL;
    1627             : 
    1628       24701 :         reset_single(inner);
    1629       24701 :         if (single && is_project(rel->op))
    1630             :                 op = op_left;
    1631             : 
    1632       24701 :         if (!is_project(inner->op))
    1633          13 :                 inner = rel_project(sql->sa, inner, rel_projections(sql, inner, NULL, 1, 1));
    1634             : 
    1635       24701 :         if (is_join(rel->op)){ /* TODO handle set operators etc */
    1636          44 :                 if (is_right(rel->op))
    1637           0 :                         d = rel->l = rel_crossproduct(sql->sa, rel->l, inner, op);
    1638             :                 else
    1639          44 :                         d = rel->r = rel_crossproduct(sql->sa, rel->r, inner, op);
    1640          44 :                 if (single)
    1641           9 :                         set_single(d);
    1642       24657 :         } else if (is_project(rel->op)){ /* projection -> op_left */
    1643       13216 :                 if (rel->l || single || op == op_left) {
    1644       13204 :                         if ((single || op == op_left) && !rel->l)
    1645         717 :                                 rel->l = rel_project(sql->sa, rel->l, append(sa_list(sql->sa), exp_atom_bool(sql->sa, 1)));
    1646       13204 :                         d = rel->l = rel_crossproduct(sql->sa, rel->l, inner, op_left);
    1647       13204 :                         if (single)
    1648        2903 :                                 set_single(d);
    1649             :                 } else {
    1650          12 :                         d = rel->l = inner;
    1651             :                 }
    1652             :         } else {
    1653       11441 :                 d = rel->l = rel_crossproduct(sql->sa, rel->l, inner, op);
    1654       11441 :                 if (single)
    1655        5034 :                         set_single(d);
    1656             :         }
    1657       24701 :         assert(d);
    1658       24701 :         if (rel_has_freevar(sql, inner)) {
    1659        7752 :                 list *dv = rel_dependent_var(sql, d, inner);
    1660        7752 :                 list *fv = rel_freevar(sql, inner);
    1661             :                 /* check if the inner depends on the new join (d) or one leve up */
    1662        7752 :                 if (list_length(dv))
    1663        7702 :                         set_dependent(d);
    1664        7752 :                 if (list_length(fv) != list_length(dv))
    1665         106 :                         set_dependent(rel);
    1666             :         }
    1667       24701 :         if (rewrite)
    1668       23778 :                 *rewrite = d;
    1669       24701 :         return inner->exps->t->data;
    1670             : }
    1671             : 
    1672             : static sql_exp *
    1673    19809591 : rewrite_exp_rel(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    1674             : {
    1675    19809591 :         if (exp_is_rel(e) && is_ddl(rel->op) && rel->flag == ddl_psm) {
    1676       16387 :                 sql_rel *inner = exp_rel_get_rel(v->sql->sa, e);
    1677       16387 :                 if (is_single(inner)) {
    1678             :                         /* use a dummy projection for the single join */
    1679           2 :                         sql_rel *nrel = rel_project(v->sql->sa, NULL, append(sa_list(v->sql->sa), exp_atom_bool(v->sql->sa, 1)));
    1680             : 
    1681           2 :                         if (!rewrite_inner(v->sql, nrel, inner, depth?op_left:op_join, NULL))
    1682             :                                 return NULL;
    1683             :                         /* has to apply recursively */
    1684           2 :                         if (!(e->l = rel_exp_visitor_bottomup(v, nrel, &rewrite_exp_rel, true)))
    1685             :                                 return NULL;
    1686           2 :                         v->changes++;
    1687             :                 }
    1688    19793205 :         } else if (exp_has_rel(e) && !is_ddl(rel->op)) {
    1689       12021 :                 sql_rel *rewrite = NULL;
    1690       18399 :                 sql_exp *ne = rewrite_inner(v->sql, rel, exp_rel_get_rel(v->sql->sa, e), depth?op_left:op_join, &rewrite);
    1691             : 
    1692       12021 :                 if (!ne)
    1693           0 :                         return ne;
    1694       12021 :                 if (exp_is_rel(e)) {
    1695       12021 :                         ne = exp_ref(v->sql, ne);
    1696       12021 :                         if (exp_name(e))
    1697       12021 :                                 exp_prop_alias(v->sql->sa, ne, e);
    1698       12021 :                         if (!exp_name(ne))
    1699           0 :                                 ne = exp_label(v->sql->sa, ne, ++v->sql->label);
    1700             :                         e = ne;
    1701             :                 } else {
    1702           0 :                         e = exp_rel_update_exp(v->sql, e);
    1703             :                 }
    1704       12021 :                 exp_reset_props(rewrite, e, is_left(rewrite->op));
    1705       12021 :                 v->changes++;
    1706             :         }
    1707             :         return e;
    1708             : }
    1709             : 
    1710             : /* add an dummy true projection column */
    1711             : static inline sql_rel *
    1712     3551897 : rewrite_empty_project(visitor *v, sql_rel *rel)
    1713             : {
    1714     3551897 :         if (is_simple_project(rel->op) && list_empty(rel->exps)) {
    1715           0 :                 v->changes++;
    1716           0 :                 append(rel->exps, exp_atom_bool(v->sql->sa, 1));
    1717     3551897 :         } else if (is_groupby(rel->op) && list_empty(rel->exps)) {
    1718          22 :                 v->changes++;
    1719          22 :                 append(rel->exps, exp_atom_bool(v->sql->sa, 1));
    1720             :         }
    1721     3551897 :         return rel;
    1722             : }
    1723             : 
    1724             : #define is_anyequal_func(sf) (strcmp((sf)->func->base.name, "sql_anyequal") == 0 || strcmp((sf)->func->base.name, "sql_not_anyequal") == 0)
    1725             : #define is_anyequal(sf) (strcmp((sf)->func->base.name, "sql_anyequal") == 0)
    1726             : #define is_not_anyequal(sf) (strcmp((sf)->func->base.name, "sql_not_anyequal") == 0)
    1727             : 
    1728             : static int exps_have_not_anyequal(list *exps);
    1729             : 
    1730             : static int
    1731     1217472 : exp_has_not_anyequal(sql_exp *e)
    1732             : {
    1733     1231532 :         if (!e)
    1734             :                 return 0;
    1735     1231532 :         switch(e->type){
    1736       24161 :         case e_func:
    1737             :         case e_aggr: {
    1738       24161 :                 list *args = e->l;
    1739       24161 :                 sql_subfunc *f = e->f;
    1740             : 
    1741       24161 :                 if (f && f->func && is_not_anyequal(f) && exps_have_rel_exp(args))
    1742             :                         return 1;
    1743       24158 :                 return exps_have_not_anyequal(e->l);
    1744             :         }
    1745      396582 :         case e_cmp:
    1746      396582 :                 if (e->flag == cmp_or || e->flag == cmp_filter)
    1747       10403 :                         return (exps_have_not_anyequal(e->l) || exps_have_not_anyequal(e->r));
    1748      386179 :                 if (e->flag == cmp_in || e->flag == cmp_notin)
    1749           0 :                         return (exp_has_not_anyequal(e->l) || exps_have_not_anyequal(e->r));
    1750      386179 :                 return (exp_has_not_anyequal(e->l) || exp_has_not_anyequal(e->r) || (e->f && exp_has_not_anyequal(e->f)));
    1751       14060 :         case e_convert:
    1752       14060 :                 return exp_has_not_anyequal(e->l);
    1753       44656 :         case e_atom:
    1754       44656 :                 return (e->f && exp_has_not_anyequal(e->f));
    1755             :         case e_psm:
    1756             :         case e_column:
    1757             :                 return 0;
    1758             :         }
    1759             :         return 0;
    1760             : }
    1761             : 
    1762             : static int
    1763      422587 : exps_have_not_anyequal(list *exps)
    1764             : {
    1765      422587 :         if (list_empty(exps))
    1766             :                 return 0;
    1767      794038 :         for(node *n=exps->h; n; n=n->next) {
    1768      445027 :                 sql_exp *e = n->data;
    1769             : 
    1770      445027 :                 if (exp_has_not_anyequal(e))
    1771             :                         return 1;
    1772             :         }
    1773             :         return 0;
    1774             : }
    1775             : 
    1776             : /* introduce extra selects for (not) anyequal + subquery */
    1777             : static sql_rel *
    1778     3573950 : not_anyequal_helper(visitor *v, sql_rel *rel)
    1779             : {
    1780     3573950 :         if (is_innerjoin(rel->op) && exps_have_not_anyequal(rel->exps)) {
    1781           3 :                 sql_rel *nrel = rel_select(v->sql->sa, rel, NULL);
    1782           3 :                 nrel->exps = rel->exps;
    1783           3 :                 rel->exps = NULL;
    1784             :                 rel = nrel;
    1785           3 :                 v->changes++;
    1786             :         }
    1787     3573950 :         return rel;
    1788             : }
    1789             : 
    1790             : /*
    1791             :  * For decimals and intervals we need to adjust the scale for some operations.
    1792             :  *
    1793             :  * TODO move the decimal scale handling to this function.
    1794             :  */
    1795             : #define is_division(sf) (strcmp(sf->func->base.name, "sql_div") == 0)
    1796             : #define is_multiplication(sf) (strcmp(sf->func->base.name, "sql_mul") == 0)
    1797             : 
    1798             : static inline sql_exp *
    1799    31809653 : exp_physical_types(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    1800             : {
    1801             :         (void)rel;
    1802             :         (void)depth;
    1803             :         sql_exp *ne = e;
    1804             : 
    1805    31809653 :         if (!e || (e->type != e_func && e->type != e_convert) || !e->l)
    1806             :                 return e;
    1807             : 
    1808     1910858 :         if (e->type == e_convert) {
    1809      632062 :                 sql_subtype *ft = exp_fromtype(e);
    1810      632062 :                 sql_subtype *tt = exp_totype(e);
    1811             : 
    1812             :                 /* complex conversion matrix */
    1813      632062 :                 if (ft->type->eclass == EC_SEC && tt->type->eclass == EC_SEC && ft->type->digits > tt->type->digits) {
    1814             :                         /* no conversion needed, just time adjustment */
    1815             :                         ne = e->l;
    1816           0 :                         ne->tpe = *tt; // ugh
    1817             :                 }
    1818             :         } else {
    1819             :                 list *args = e->l;
    1820     1278796 :                 sql_subfunc *f = e->f;
    1821             : 
    1822             :                 /* multiplication and division on decimals */
    1823     1278796 :                 if (is_multiplication(f) && list_length(args) == 2) {
    1824       20699 :                         sql_exp *le = args->h->data;
    1825       20699 :                         sql_subtype *lt = exp_subtype(le);
    1826             : 
    1827       20699 :                         if (lt->type->eclass == EC_SEC || lt->type->eclass == EC_MONTH) {
    1828          27 :                                 sql_exp *re = args->h->next->data;
    1829          27 :                                 sql_subtype *rt = exp_subtype(re);
    1830             : 
    1831          27 :                                 if (rt->type->eclass == EC_DEC && rt->scale) {
    1832           6 :                                         int scale = (int) rt->scale; /* shift with scale */
    1833           6 :                                         sql_subtype *it = sql_bind_localtype(lt->type->impl);
    1834           6 :                                         sql_subfunc *c = sql_bind_func(v->sql, "sys", "scale_down", lt, it, F_FUNC);
    1835             : 
    1836           6 :                                         if (!c) {
    1837           0 :                                                 TRC_CRITICAL(SQL_PARSER, "scale_down missing (%s)\n", lt->type->impl);
    1838           0 :                                                 return NULL;
    1839             :                                         }
    1840             : #ifdef HAVE_HGE
    1841           6 :                                         hge val = scale2value(scale);
    1842             : #else
    1843             :                                         lng val = scale2value(scale);
    1844             : #endif
    1845           6 :                                         atom *a = atom_int(v->sql->sa, it, val);
    1846           6 :                                         ne = exp_binop(v->sql->sa, e, exp_atom(v->sql->sa, a), c);
    1847             :                                 }
    1848             :                         }
    1849     1258097 :                 } else if (is_division(f) && list_length(args) == 2) {
    1850        2639 :                         sql_exp *le = args->h->data;
    1851        2639 :                         sql_subtype *lt = exp_subtype(le);
    1852             : 
    1853        2639 :                         if (lt->type->eclass == EC_SEC || lt->type->eclass == EC_MONTH) {
    1854          35 :                                 sql_exp *re = args->h->next->data;
    1855          35 :                                 sql_subtype *rt = exp_subtype(re);
    1856             : 
    1857          35 :                                 if (rt->type->eclass == EC_DEC && rt->scale) {
    1858          10 :                                         int scale = (int) rt->scale; /* shift with scale */
    1859             : #ifdef HAVE_HGE
    1860          10 :                                         hge val = scale2value(scale);
    1861             : #else
    1862             :                                         lng val = scale2value(scale);
    1863             : #endif
    1864             : 
    1865          10 :                                         if (lt->type->eclass == EC_SEC) {
    1866           5 :                                                 sql_subtype *it = sql_bind_localtype(lt->type->impl);
    1867           5 :                                                 sql_subfunc *c = sql_bind_func(v->sql, "sys", "scale_up", lt, it, F_FUNC);
    1868             : 
    1869           5 :                                                 if (!c) {
    1870           0 :                                                         TRC_CRITICAL(SQL_PARSER, "scale_up missing (%s)\n", lt->type->impl);
    1871           0 :                                                         return NULL;
    1872             :                                                 }
    1873           5 :                                                 atom *a = atom_int(v->sql->sa, it, val);
    1874           5 :                                                 ne = exp_binop(v->sql->sa, e, exp_atom(v->sql->sa, a), c);
    1875             :                                         } else { /* EC_MONTH */
    1876           5 :                                                 sql_subtype *it = sql_bind_localtype(rt->type->impl);
    1877           5 :                                                 sql_subfunc *c = sql_bind_func(v->sql, "sys", "scale_down", rt, it, F_FUNC);
    1878             : 
    1879           5 :                                                 if (!c) {
    1880           0 :                                                         TRC_CRITICAL(SQL_PARSER, "scale_down missing (%s)\n", lt->type->impl);
    1881           0 :                                                         return NULL;
    1882             :                                                 }
    1883           5 :                                                 atom *a = atom_int(v->sql->sa, it, val);
    1884           5 :                                                 args->h->next->data = exp_binop(v->sql->sa, args->h->next->data, exp_atom(v->sql->sa, a), c);
    1885             :                                         }
    1886             :                                 }
    1887             :                         }
    1888             :                 }
    1889             :         }
    1890     1910858 :         if (ne != e) {
    1891          11 :                 if (exp_name(e))
    1892           1 :                         exp_prop_alias(v->sql->sa, ne, e);
    1893          11 :                 v->changes++;
    1894             :         }
    1895             :         return ne;
    1896             : }
    1897             : 
    1898             : static sql_exp *
    1899    31809642 : exp_reset_card_and_freevar_set_physical_type(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    1900             : {
    1901    31809642 :         if (e->type == e_func && e->r) /* mark as normal (analytic) function now */
    1902        9657 :                 e->r = NULL;
    1903    31809642 :         reset_freevar(e); /* unnesting is done, we can remove the freevar flag */
    1904             : 
    1905    31809642 :         if (!(e = exp_physical_types(v, rel, e, depth))) /* for decimals and intervals we need to adjust the scale for some operations */
    1906             :                 return NULL;
    1907    31809694 :         if (!rel->l)
    1908             :                 return e;
    1909             : 
    1910    29212192 :         switch(rel->op){
    1911    21032282 :         case op_select:
    1912             :         case op_join:
    1913             :         case op_left:
    1914             :         case op_right:
    1915             :         case op_full:
    1916             :         case op_semi:
    1917             :         case op_anti:
    1918             :         case op_project: {
    1919    21032282 :                 switch(e->type) {
    1920     1219031 :                 case e_aggr:
    1921             :                 case e_func: {
    1922     1219031 :                         e->card = exps_card(e->l);
    1923     1219031 :                 } break;
    1924    14672429 :                 case e_column: {
    1925             :                         sql_exp *le = NULL, *re = NULL;
    1926    14672429 :                         bool underjoinl = false, underjoinr = false;
    1927             : 
    1928    14672429 :                         le = rel_find_exp_and_corresponding_rel(rel->l, e, NULL, &underjoinl);
    1929    14672434 :                         if (!is_simple_project(rel->op) && !is_inter(rel->op) && !is_except(rel->op) && !is_semi(rel->op) && rel->r) {
    1930     2508111 :                                 re = rel_find_exp_and_corresponding_rel(rel->r, e, NULL, &underjoinr);
    1931             :                                 /* if the expression is found under a join, the cardinality expands to multi */
    1932     2508111 :                                 e->card = MAX(le?underjoinl?CARD_MULTI:le->card:CARD_ATOM, re?underjoinr?CARD_MULTI:re->card:CARD_ATOM);
    1933    12164323 :                         } else if (e->card == CARD_ATOM) { /* unnested columns vs atoms */
    1934      313146 :                                 e->card = le?underjoinl?CARD_MULTI:le->card:CARD_ATOM;
    1935             :                         } else { /* general case */
    1936    11851177 :                                 e->card = (le && !underjoinl)?le->card:CARD_MULTI;
    1937             :                         }
    1938    14672434 :                         } break;
    1939      601488 :                 case e_convert: {
    1940      601488 :                         e->card = exp_card(e->l);
    1941      601488 :                 } break;
    1942     2165779 :                 case e_cmp: {
    1943     2165779 :                         if (e->flag == cmp_or || e->flag == cmp_filter) {
    1944       57013 :                                 e->card = MAX(exps_card(e->l), exps_card(e->r));
    1945     2108766 :                         } else if (e->flag == cmp_in || e->flag == cmp_notin) {
    1946      169081 :                                 e->card = MAX(exp_card(e->l), exps_card(e->r));
    1947             :                         } else {
    1948     1939685 :                                 e->card = MAX(exp_card(e->l), exp_card(e->r));
    1949     1939685 :                                 if (e->f)
    1950        4316 :                                         e->card = MAX(e->card, exp_card(e->f));
    1951             :                         }
    1952             :                 } break;
    1953             :                 case e_atom:
    1954             :                 case e_psm:
    1955             :                         break;
    1956             :                 }
    1957             :         } break;
    1958      723211 :         case op_inter:
    1959             :         case op_except:
    1960             :         case op_union: {
    1961      723211 :                 e->card = CARD_MULTI;
    1962      723211 :         } break;
    1963      613546 :         case op_groupby: {
    1964      613546 :                 switch(e->type) {
    1965      140825 :                 case e_aggr:
    1966      140825 :                         e->card = rel->card;
    1967      140825 :                         break;
    1968      445870 :                 case e_column: {
    1969      445870 :                         if (e->card == CARD_ATOM) { /* unnested columns vs atoms */
    1970        6017 :                                 sql_exp *le = rel_find_exp(rel->l, e);
    1971             :                                 /* if it's from the left relation, it's either a constant or column, so set to min between le->card and aggr */
    1972        6017 :                                 e->card = le?MIN(le->card, CARD_AGGR):CARD_ATOM;
    1973             :                         } else {
    1974      439853 :                                 e->card = rel->card;
    1975             :                         }
    1976             :                 } break;
    1977             :                 default:
    1978             :                         break;
    1979             :                 }
    1980             :         } break;
    1981             :         default:
    1982             :                 break;
    1983             :         }
    1984    29212197 :         if (is_simple_project(rel->op) && need_distinct(rel)) /* Need distinct, all expressions should have CARD_AGGR at max */
    1985      247014 :                 e->card = MIN(e->card, CARD_AGGR);
    1986    29212197 :         if (!is_set(rel->op) && (!is_groupby(rel->op) || !list_empty(rel->r))) /* global groupings have atomic cardinality */
    1987    28466221 :                 rel->card = MAX(e->card, rel->card); /* the relation cardinality may get updated too */
    1988             :         return e;
    1989             : }
    1990             : 
    1991             : static list*
    1992        4538 : aggrs_split_args(mvc *sql, list *aggrs, list *exps, int is_groupby_list)
    1993             : {
    1994             :         bool clear_hash = false;
    1995             : 
    1996        4538 :         if (list_empty(aggrs))
    1997             :                 return aggrs;
    1998       19656 :         for (node *n=aggrs->h; n; n = n->next) {
    1999       15985 :                 sql_exp *a = n->data;
    2000             : 
    2001       15985 :                 if (is_func(a->type) && !is_groupby_list)
    2002           1 :                         continue;
    2003       15984 :                 if (!is_aggr(a->type)) {
    2004             :                         sql_exp *e1 = a, *found = NULL;
    2005             : 
    2006       42590 :                         for (node *nn = exps->h; nn && !found; nn = nn->next) {
    2007       31015 :                                 sql_exp *e2 = nn->data;
    2008             : 
    2009       31015 :                                 if (!exp_equal(e1, e2))
    2010             :                                         found = e2;
    2011             :                         }
    2012       11575 :                         if (!found) {
    2013        5798 :                                 if (!exp_name(e1))
    2014           0 :                                         e1 = exp_label(sql->sa, e1, ++sql->label);
    2015        5798 :                                 append(exps, e1);
    2016             :                         } else {
    2017             :                                 e1 = found;
    2018             :                         }
    2019       11575 :                         e1 = exp_ref(sql, e1);
    2020       11575 :                         n->data = e1; /* replace by reference */
    2021             :                         clear_hash = true;
    2022       11575 :                         continue;
    2023             :                 }
    2024        4409 :                 list *args = a->l;
    2025             : 
    2026        4409 :                 if (!list_empty(args)) {
    2027       10289 :                         for (node *an = args->h; an; an = an->next) {
    2028        6165 :                                 sql_exp *e1 = an->data, *found = NULL, *eo = e1;
    2029             :                                 /* we keep converts as they reuse names of inner columns */
    2030        6165 :                                 int convert = is_convert(e1->type);
    2031             : 
    2032        6165 :                                 if (convert)
    2033         107 :                                         e1 = e1->l;
    2034       34989 :                                 for (node *nn = exps->h; nn && !found; nn = nn->next) {
    2035       28824 :                                         sql_exp *e2 = nn->data;
    2036             : 
    2037       28824 :                                         if (!exp_equal(e1, e2))
    2038             :                                                 found = e2;
    2039             :                                 }
    2040        6165 :                                 if (!found) {
    2041        6086 :                                         if (!exp_name(e1))
    2042        5656 :                                                 e1 = exp_label(sql->sa, e1, ++sql->label);
    2043        6086 :                                         append(exps, e1);
    2044             :                                 } else {
    2045             :                                         e1 = found;
    2046             :                                 }
    2047        6165 :                                 e1 = exp_ref(sql, e1);
    2048             :                                 /* replace by reference */
    2049        6165 :                                 if (convert) {
    2050         107 :                                         eo->l = e1;
    2051             :                                 } else {
    2052        6058 :                                         an->data = e1;
    2053             :                                         clear_hash = true;
    2054             :                                 }
    2055             :                         }
    2056             :                 }
    2057             :         }
    2058        3671 :         if (clear_hash)
    2059        3639 :                 list_hash_clear(aggrs);
    2060             :         return aggrs;
    2061             : }
    2062             : 
    2063             : static int
    2064      121523 : exps_complex(list *exps)
    2065             : {
    2066      121523 :         if (list_empty(exps))
    2067             :                 return 0;
    2068      187271 :         for(node *n = exps->h; n; n = n->next) {
    2069      107381 :                 sql_exp *e = n->data;
    2070             : 
    2071      107381 :                 if (e->type != e_column && e->type != e_atom)
    2072             :                         return 1;
    2073             :         }
    2074             :         return 0;
    2075             : }
    2076             : 
    2077             : static int
    2078       58606 : aggrs_complex(list *exps)
    2079             : {
    2080       58606 :         if (list_empty(exps))
    2081             :                 return 0;
    2082      171364 :         for(node *n = exps->h; n; n = n->next) {
    2083      114915 :                 sql_exp *e = n->data;
    2084             : 
    2085      114915 :                 if (e->type == e_aggr && exps_complex(e->l))
    2086             :                                 return 1;
    2087             :         }
    2088             :         return 0;
    2089             : }
    2090             : 
    2091             : /* simplify aggregates, ie push functions under the groupby relation */
    2092             : /* rel visitor */
    2093             : static inline sql_rel *
    2094     3551896 : rewrite_aggregates(visitor *v, sql_rel *rel)
    2095             : {
    2096     3551896 :         if (is_groupby(rel->op) && (exps_complex(rel->r) || aggrs_complex(rel->exps))) {
    2097        2269 :                 list *exps = sa_list(v->sql->sa);
    2098             : 
    2099        2269 :                 rel->r = aggrs_split_args(v->sql, rel->r, exps, 1);
    2100        2269 :                 rel->exps = aggrs_split_args(v->sql, rel->exps, exps, 0);
    2101        2269 :                 rel->l = rel_project(v->sql->sa, rel->l, exps);
    2102        2269 :                 v->changes++;
    2103        2269 :                 return rel;
    2104             :         }
    2105             :         return rel;
    2106             : }
    2107             : 
    2108             : /* remove or expressions with subqueries */
    2109             : static sql_rel *
    2110     3552664 : rewrite_or_exp(visitor *v, sql_rel *rel)
    2111             : {
    2112     3552664 :         if (mvc_highwater(v->sql))
    2113           0 :                 return sql_error(v->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
    2114             : 
    2115     3552664 :         if ((is_select(rel->op) || is_join(rel->op) || is_semi(rel->op)) && !list_empty(rel->exps)) {
    2116     1620326 :                 for(node *n=rel->exps->h; n; n=n->next) {
    2117      883855 :                         sql_exp *e = n->data, *id;
    2118             : 
    2119      883855 :                         if (is_compare(e->type) && e->flag == cmp_or) {
    2120             :                                 /* check for exp_is_rel */
    2121       20501 :                                 if (exps_have_rel_exp(e->l) || exps_have_rel_exp(e->r)) {
    2122             :                                         /* rewrite into setop */
    2123         384 :                                         list_remove_node(rel->exps, NULL, n); /* remove or expression */
    2124         384 :                                         if (is_select(rel->op) && list_empty(rel->exps) && !(rel_is_ref(rel))) { /* remove empty select if that's the case */
    2125         378 :                                                 sql_rel *l = rel->l;
    2126         378 :                                                 rel->l = NULL;
    2127         378 :                                                 rel_destroy(rel);
    2128             :                                                 rel = l;
    2129             :                                         }
    2130         384 :                                         rel = rel_add_identity(v->sql, rel, &id); /* identity function needed */
    2131         384 :                                         const char *idrname = exp_relname(id), *idname = exp_name(id);
    2132         384 :                                         list *tids = NULL, *exps = rel_projections(v->sql, rel, NULL, 1, 1);
    2133             : 
    2134        3232 :                                         for( node *n = exps->h ; n ; ) {
    2135        2848 :                                                 node *next = n->next;
    2136        2848 :                                                 sql_exp *e = n->data;
    2137             : 
    2138        2848 :                                                 if (strcmp(exp_name(e), TID) == 0) { /* remove TID references and later restore them with identity function references */
    2139         429 :                                                         if (!tids)
    2140         377 :                                                                 tids = sa_list(v->sql->sa);
    2141         429 :                                                         list_append(tids, exp_alias(v->sql->sa, exp_relname(e), TID, idrname, idname, sql_bind_localtype("oid"), CARD_MULTI, 0, 1, 1));
    2142         429 :                                                         list_remove_node(exps, NULL, n);
    2143             :                                                 }
    2144             :                                                 n = next;
    2145             :                                         }
    2146             : 
    2147         384 :                                         sql_rel *l = rel, *r = rel_dup(rel);
    2148         384 :                                         set_processed(rel);
    2149         384 :                                         l = rel_select(v->sql->sa, l, NULL);
    2150         384 :                                         l->exps = e->l;
    2151         384 :                                         if (!(l = rewrite_or_exp(v, l)))
    2152         384 :                                                 return NULL;
    2153         384 :                                         r = rel_select(v->sql->sa, r, NULL);
    2154         384 :                                         r->exps = e->r;
    2155         384 :                                         if (!(r = rewrite_or_exp(v, r)))
    2156             :                                                 return NULL;
    2157         384 :                                         if (!(rel = rel_setop_check_types(v->sql, l, r, exps_copy(v->sql, exps), exps_copy(v->sql, exps), op_union)))
    2158             :                                                 return NULL;
    2159         384 :                                         rel_setop_set_exps(v->sql, rel, exps, false);
    2160         384 :                                         set_processed(rel);
    2161         384 :                                         rel = rel_distinct(rel);
    2162         384 :                                         if (tids) /* restore TIDs with identity function references */
    2163         377 :                                                 rel = rel_project(v->sql->sa, rel, list_merge(rel_projections(v->sql, rel, NULL, 1, 1), tids, NULL));
    2164         384 :                                         v->changes++;
    2165         384 :                                         return rel;
    2166             :                                 }
    2167             :                         }
    2168             :                 }
    2169      736471 :                 if (is_join(rel->op) && list_empty(rel->exps))
    2170           0 :                         rel->exps = NULL; /* crossproduct */
    2171      736471 :                 return try_remove_empty_select(v, rel);
    2172             :         }
    2173             :         return rel;
    2174             : }
    2175             : 
    2176             : static inline sql_rel *
    2177     3551897 : rewrite_split_select_exps(visitor *v, sql_rel *rel)
    2178             : {
    2179     3551897 :         if (is_select(rel->op) && !list_empty(rel->exps)) {
    2180             :                 int i = 0;
    2181      324682 :                 bool has_complex_exps = false, has_simple_exps = false, *complex_exps = SA_NEW_ARRAY(v->sql->ta, bool, list_length(rel->exps));
    2182             : 
    2183      719034 :                 for (node *n = rel->exps->h ; n ; n = n->next) {
    2184      394352 :                         sql_exp *e = n->data;
    2185             : 
    2186      394352 :                         if (exp_has_rel(e) || exp_has_freevar(v->sql, e)) {
    2187       20797 :                                 complex_exps[i] = true;
    2188       20797 :                                 has_complex_exps = true;
    2189             :                         } else {
    2190      373555 :                                 complex_exps[i] = false;
    2191             :                                 has_simple_exps = true;
    2192             :                         }
    2193      394352 :                         i++;
    2194             :                 }
    2195             : 
    2196      324682 :                 if (has_complex_exps && has_simple_exps) {
    2197        5175 :                         sql_rel *nsel = rel_select_copy(v->sql->sa, rel->l, NULL);
    2198        5175 :                         rel->l = nsel;
    2199             : 
    2200             :                         i = 0;
    2201       16085 :                         for (node *n = rel->exps->h ; n ; ) {
    2202       10910 :                                 node *nxt = n->next;
    2203             : 
    2204       10910 :                                 if (!complex_exps[i]) {
    2205        5702 :                                         rel_select_add_exp(v->sql->sa, nsel, n->data);
    2206        5702 :                                         list_remove_node(rel->exps, NULL, n);
    2207             :                                 }
    2208             :                                 n = nxt;
    2209       10910 :                                 i++;
    2210             :                         }
    2211        5175 :                         v->changes++;
    2212             :                 }
    2213             :         }
    2214     3551897 :         return rel;
    2215             : }
    2216             : 
    2217             : static void /* replace diff arguments to avoid duplicate work. The arguments must be iterated in this order! */
    2218       34271 : diff_replace_arguments(mvc *sql, sql_exp *e, list *ordering, int *pos, int *i)
    2219             : {
    2220       34271 :         if (e->type == e_func && !strcmp(((sql_subfunc*)e->f)->func->base.name, "diff")) {
    2221       10406 :                 list *args = (list*)e->l;
    2222       10406 :                 sql_exp *first = args->h->data, *second = list_length(args) == 2 ? args->h->next->data : NULL;
    2223             : 
    2224       10406 :                 if (first->type == e_func && !strcmp(((sql_subfunc*)first->f)->func->base.name, "diff")) {
    2225          66 :                         diff_replace_arguments(sql, first, ordering, pos, i);
    2226             :                 } else {
    2227       10340 :                         sql_exp *ne = args->h->data = exp_ref(sql, list_fetch(ordering, pos[*i]));
    2228       10340 :                         set_descending(ne);
    2229       10340 :                         set_nulls_first(ne);
    2230       10340 :                         *i = *i + 1;
    2231             :                 }
    2232       10406 :                 if (second && second->type == e_func && !strcmp(((sql_subfunc*)second->f)->func->base.name, "diff")) {
    2233             :                         diff_replace_arguments(sql, second, ordering, pos, i);
    2234       10406 :                 } else if (second) {
    2235          66 :                         sql_exp *ne = args->h->next->data = exp_ref(sql, list_fetch(ordering, pos[*i]));
    2236          66 :                         set_descending(ne);
    2237          66 :                         set_nulls_first(ne);
    2238          66 :                         *i = *i + 1;
    2239             :                 }
    2240             :         }
    2241       34271 : }
    2242             : 
    2243             : /* exp visitor */
    2244             : static inline sql_exp *
    2245    16792265 : rewrite_rank(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    2246             : {
    2247             :         sql_rel *rell = NULL;
    2248             :         int needed = 0;
    2249             : 
    2250    16792265 :         if (!is_simple_project(rel->op) || e->type != e_func || list_length(e->r) < 2 /* e->r means window function */)
    2251    16782608 :                 return e;
    2252             : 
    2253             :         (void)depth;
    2254             :         /* ranks/window functions only exist in the projection */
    2255        9657 :         list *l = e->l, *r = e->r, *gbe = r->h->data, *obe = r->h->next->data;
    2256        9657 :         e->card = (rel->card == CARD_AGGR) ? CARD_AGGR : CARD_MULTI; /* After the unnesting, the cardinality of the window function becomes larger */
    2257             : 
    2258        9657 :         needed = (gbe || obe);
    2259        9657 :         if (l)
    2260       22889 :                 for (node *n = l->h; n && !needed; n = n->next) {
    2261       13232 :                         sql_exp *e = n->data;
    2262       13232 :                         needed = e->ref;
    2263             :                 }
    2264             : 
    2265        9657 :         if (needed) {
    2266        5569 :                 rell = rel->l = rel_project(v->sql->sa, rel->l, rel_projections(v->sql, rel->l, NULL, 1, 1));
    2267       40672 :                 for (node *n = l->h; n; n = n->next) {
    2268       35103 :                         sql_exp *e = n->data;
    2269             : 
    2270       35103 :                         if (e->ref) {
    2271        2822 :                                 e->ref = 0;
    2272        2822 :                                 append(rell->exps, e);
    2273        2822 :                                 n->data = exp_ref(v->sql, e);
    2274             :                         }
    2275             :                 }
    2276             :         }
    2277             : 
    2278             :         /* The following array remembers the original positions of gbe and obe expressions to replace them in order later at diff_replace_arguments */
    2279        9657 :         int gbeoffset = list_length(gbe), i = 0, added = 0;
    2280        9657 :         int *pos = SA_NEW_ARRAY(v->sql->ta, int, gbeoffset + list_length(obe));
    2281             : 
    2282        9657 :         if (gbe || obe) {
    2283        5515 :                 if (gbe)
    2284        9996 :                         for (i = 0 ; i < gbeoffset ; i++)
    2285        5012 :                                 pos[i] = i;
    2286             : 
    2287        5515 :                 if (gbe && obe) {
    2288        4825 :                         gbe = list_merge(sa_list(v->sql->sa), gbe, (fdup)NULL); /* make sure the p->r is a different list than the gbe list */
    2289        4825 :                         i = 0;
    2290        9668 :                         for(node *n = obe->h ; n ; n = n->next, i++) {
    2291        4843 :                                 sql_exp *e1 = n->data;
    2292             :                                 bool found = false;
    2293             :                                 int j = 0;
    2294             : 
    2295        9425 :                                 for(node *nn = gbe->h ; nn ; nn = nn->next, j++) {
    2296        4895 :                                         sql_exp *e2 = nn->data;
    2297             :                                         /* the partition expression order should be the same as the one in the order by clause (if it's in there as well) */
    2298        4895 :                                         if (exp_match(e1, e2)) {
    2299         313 :                                                 if (is_ascending(e1))
    2300         217 :                                                         set_ascending(e2);
    2301             :                                                 else
    2302          96 :                                                         set_descending(e2);
    2303         313 :                                                 if (nulls_last(e1))
    2304          96 :                                                         set_nulls_last(e2);
    2305             :                                                 else
    2306         217 :                                                         set_nulls_first(e2);
    2307             :                                                 found = true;
    2308             :                                                 break;
    2309             :                                         }
    2310             :                                 }
    2311             :                                 if (!found) {
    2312        4530 :                                         pos[gbeoffset + i] = gbeoffset + added;
    2313        4530 :                                         added++;
    2314        4530 :                                         append(gbe, e1);
    2315             :                                 } else {
    2316         313 :                                         pos[gbeoffset + i] = j;
    2317             :                                 }
    2318             :                         }
    2319         690 :                 } else if (obe) {
    2320         531 :                         assert(!gbe);
    2321         531 :                         i = 0;
    2322        1082 :                         for(node *n = obe->h ; n ; n = n->next, i++) {
    2323         551 :                                 sql_exp *oe = n->data;
    2324         551 :                                 if (!exps_find_exp(rell->exps, oe)) {
    2325           7 :                                         sql_exp *ne = exp_ref(v->sql, oe);
    2326             : 
    2327           7 :                                         if (is_ascending(oe))
    2328           7 :                                                 set_ascending(ne);
    2329           7 :                                         if (nulls_last(oe))
    2330           0 :                                                 set_nulls_last(ne);
    2331             :                                         /* disable sorting info (ie back to defaults) */
    2332           7 :                                         set_descending(oe);
    2333           7 :                                         set_nulls_first(oe);
    2334           7 :                                         n->data = ne;
    2335           7 :                                         append(rell->exps, oe);
    2336             :                                 }
    2337         551 :                                 pos[i] = i;
    2338             :                         }
    2339             :                         gbe = obe;
    2340             :                 }
    2341             : 
    2342        5515 :                 list *ordering = sa_list(v->sql->sa); /* add exps from gbe and obe as ordering expressions */
    2343       15608 :                 for(node *n = gbe->h ; n ; n = n->next) {
    2344       10093 :                         sql_exp *next = n->data;
    2345       10093 :                         sql_exp *found = exps_find_exp(rell->exps, next);
    2346       10144 :                         sql_exp *ref = exp_ref(v->sql, found ? found : next);
    2347             : 
    2348       10093 :                         if (is_ascending(next))
    2349        8194 :                                 set_ascending(ref);
    2350       10093 :                         if (nulls_last(next))
    2351        1908 :                                 set_nulls_last(ref);
    2352       10093 :                         set_descending(next);
    2353       10093 :                         set_nulls_first(next);
    2354       10093 :                         if (!found)
    2355          51 :                                 list_append(rell->exps, next);
    2356       10093 :                         list_append(ordering, ref);
    2357             :                 }
    2358        5515 :                 rell = rel_project(v->sql->sa, rell, rel_projections(v->sql, rell, NULL, 1, 1));
    2359        5515 :                 rell->r = ordering;
    2360        5515 :                 rel->l = rell;
    2361             : 
    2362             :                 /* remove obe argument, so this function won't be called again on this expression */
    2363        5515 :                 list_remove_node(r, NULL, r->t);
    2364             : 
    2365             :                 /* add project with rank */
    2366        5515 :                 rell = rel->l = rel_project(v->sql->sa, rel->l, rel_projections(v->sql, rell->l, NULL, 1, 1));
    2367        5515 :                 i = 0;
    2368             : 
    2369       40282 :                 for (node *n = l->h; n ; n = n->next) { /* replace the updated arguments */
    2370       34767 :                         sql_exp *e = n->data;
    2371             : 
    2372       34767 :                         if (e->type == e_func && !strcmp(((sql_subfunc*)e->f)->func->base.name, "window_bound"))
    2373         562 :                                 continue;
    2374       34205 :                         diff_replace_arguments(v->sql, e, ordering, pos, &i);
    2375             :                 }
    2376             : 
    2377        5515 :                 sql_exp *b1 = (sql_exp*) list_fetch(l, list_length(l) - 2); /* the 'window_bound' calls are added after the function arguments and frame type */
    2378        5515 :                 sql_exp *b2 = (sql_exp*) list_fetch(l, list_length(l) - 1);
    2379             : 
    2380        5515 :                 if (b1 && b1->type == e_func && !strcmp(((sql_subfunc*)b1->f)->func->base.name, "window_bound")) {
    2381         281 :                         list *ll = b1->l;
    2382         281 :                         rell = rel->l = rel_project(v->sql->sa, rell, rel_projections(v->sql, rell, NULL, 1, 1));
    2383             : 
    2384         281 :                         int pe_pos = list_length(l) - 5; /* append the new partition expression to the list of expressions */
    2385         281 :                         sql_exp *pe = (sql_exp*) list_fetch(l, pe_pos);
    2386         281 :                         list_append(rell->exps, pe);
    2387             : 
    2388         281 :                         if (list_length(ll) == 6) { /* update partition definition for window function input if that's the case */
    2389         181 :                                 ((list*)b1->l)->h->data = exp_ref(v->sql, pe);
    2390         181 :                                 ((list*)b2->l)->h->data = exp_ref(v->sql, pe);
    2391             :                         }
    2392         281 :                         i = 0; /* the partition may get a new reference, update it on the window function list of arguments as well */
    2393         674 :                         for (node *n = l->h; n ; n = n->next, i++) {
    2394         674 :                                 if (i == pe_pos) {
    2395         281 :                                         n->data = exp_ref(v->sql, pe);
    2396         281 :                                         break;
    2397             :                                 }
    2398             :                         }
    2399             : 
    2400         281 :                         sql_exp *frame_type = (sql_exp*) list_fetch(l, list_length(l) - 3);
    2401         281 :                         atom *a = frame_type->l;
    2402         281 :                         int nr = (int)atom_get_int(a);
    2403             : 
    2404         281 :                         if (nr == FRAME_RANGE && obe) { /* for range we pass the last order by column (otherwise it's either invalid or is a special case)*/
    2405         108 :                                 int oe_pos = list_length(ll) - 5;
    2406         108 :                                 sql_exp *oe = (sql_exp*) list_fetch(ll, oe_pos);
    2407         108 :                                 if (oe->type != e_column && oe->type != e_atom) {
    2408           4 :                                         sql_exp *common  = list_fetch(ordering, pos[gbeoffset + list_length(obe) - 1]);
    2409             : 
    2410           4 :                                         if (list_length(ll) == 5) {
    2411           2 :                                                 ((list*)b1->l)->h->data = exp_ref(v->sql, common);
    2412           2 :                                                 ((list*)b2->l)->h->data = exp_ref(v->sql, common);
    2413             :                                         } else {
    2414           2 :                                                 ((list*)b1->l)->h->next->data = exp_ref(v->sql, common);
    2415           2 :                                                 ((list*)b2->l)->h->next->data = exp_ref(v->sql, common);
    2416             :                                         }
    2417             :                                 }
    2418         173 :                         } else if (nr == FRAME_ROWS || nr == FRAME_GROUPS) {
    2419         159 :                                 int oe_pos = list_length(l) - 4; /* for groups and rows, we push the ordering diff call, reference it back */
    2420             :                                 /* now this is the tricky, part, the ordering expression, may be a column, or any projection, only the later requires the push down */
    2421         159 :                                 sql_exp *oe = (sql_exp*) list_fetch(l, oe_pos);
    2422         159 :                                 if (oe->type != e_column && oe->type != e_atom) {
    2423         150 :                                         list_append(rell->exps, oe);
    2424             : 
    2425         150 :                                         if (list_length(ll) == 5) {
    2426          37 :                                                 ((list*)b1->l)->h->data = exp_ref(v->sql, oe);
    2427          37 :                                                 ((list*)b2->l)->h->data = exp_ref(v->sql, oe);
    2428             :                                         } else {
    2429         113 :                                                 ((list*)b1->l)->h->next->data = exp_ref(v->sql, oe);
    2430         113 :                                                 ((list*)b2->l)->h->next->data = exp_ref(v->sql, oe);
    2431             :                                         }
    2432             :                                 }
    2433             :                         }
    2434             :                 }
    2435             : 
    2436             :                 /* move rank down add ref */
    2437        5515 :                 if (!exp_name(e))
    2438        1409 :                         e = exp_label(v->sql->sa, e, ++v->sql->label);
    2439        5515 :                 append(rell->exps, e);
    2440        5515 :                 e = exp_ref(v->sql, e);
    2441        5515 :                 v->changes++;
    2442             :         } else {
    2443             :                 /* remove obe argument, so this function won't be called again on this expression */
    2444        4142 :                 list_remove_node(r, NULL, r->t);
    2445        4142 :                 v->changes++;
    2446             :         }
    2447             :         return e;
    2448             : }
    2449             : 
    2450             : static sql_rel *
    2451        5501 : rel_union_exps(mvc *sql, sql_exp **l, list *vals, int is_tuple)
    2452             : {
    2453             :         sql_rel *u = NULL;
    2454             :         list *exps = NULL;
    2455             :         int freevar = 0;
    2456             : 
    2457             :         if (mvc_highwater(sql))
    2458           0 :                 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
    2459             : 
    2460       11021 :         for (node *n=vals->h; n; n = n->next) {
    2461        5520 :                 sql_exp *ve = n->data, *r, *s;
    2462             :                 sql_rel *sq = NULL;
    2463             : 
    2464        5520 :                 exp_label(sql->sa, ve, ++sql->label); /* an alias is needed */
    2465        5520 :                 if (exp_has_rel(ve)) {
    2466        5485 :                         sq = exp_rel_get_rel(sql->sa, ve); /* get subquery */
    2467        5485 :                         if (sq)
    2468        5485 :                                 freevar = rel_has_freevar(sql,sq);
    2469             :                 } else {
    2470          35 :                         sq = rel_project(sql->sa, NULL, append(sa_list(sql->sa), ve));
    2471          35 :                         if (!exp_is_atom(ve))
    2472             :                                 freevar = 1;
    2473          35 :                         set_processed(sq);
    2474             :                 }
    2475        5520 :                 if (is_tuple) { /* cast each one */
    2476        5890 :                         for (node *m=sq->exps->h, *o = ((list *)(*l)->f)->h; m && o; m = m->next, o = o->next) {
    2477        4062 :                                 r = m->data;
    2478        4062 :                                 s = o->data;
    2479        4062 :                                 if (rel_convert_types(sql, NULL, NULL, &s, &r, 1, type_equal) < 0)
    2480           0 :                                         return NULL;
    2481        4062 :                                 m->data = r;
    2482             :                         }
    2483             :                 } else {
    2484        3692 :                         sq->nrcols = list_length(sq->exps);
    2485             :                         /* union a project[(values(a),..,(b),(c)]  with freevars */
    2486        3692 :                         if (sq->card > CARD_ATOM && rel_has_freevar(sql, sq) && is_project(sq->op) && !sq->l && sq->nrcols==1) {
    2487             :                                 /* needs check on projection */
    2488           0 :                                 sql_exp *vals = sq->exps->h->data;
    2489           0 :                                 if (!(sq = rel_union_exps(sql, l, exp_get_values(vals), is_tuple)))
    2490             :                                         return NULL;
    2491             :                         } else {
    2492        3692 :                                 if (rel_convert_types(sql, NULL, NULL, l, &ve, 1, type_equal) < 0)
    2493             :                                         return NULL;
    2494             :                                 /* flatten expressions */
    2495        3692 :                                 if (exp_has_rel(ve)) {
    2496        3657 :                                         ve = exp_rel_update_exp(sql, ve);
    2497        3657 :                                         sq = rel_project(sql->sa, sq, append(sa_list(sql->sa), ve));
    2498        3657 :                                         set_processed(sq);
    2499             :                                 }
    2500        3692 :                                 if (freevar)
    2501         114 :                                         exp_set_freevar(sql, ve, sq);
    2502             :                         }
    2503             :                 }
    2504        5520 :                 if (!u) {
    2505             :                         u = sq;
    2506             :                 } else {
    2507          19 :                         u = rel_setop(sql->sa, u, sq, op_union);
    2508          19 :                         rel_setop_set_exps(sql, u, exps, false);
    2509          19 :                         set_distinct(u);
    2510          19 :                         set_processed(u);
    2511             :                 }
    2512        5520 :                 exps = rel_projections(sql, sq, NULL, 1/*keep names */, 1);
    2513             :         }
    2514             :         return u;
    2515             : }
    2516             : 
    2517             : static sql_exp *
    2518        4102 : exp_in_project(mvc *sql, sql_exp **l, list *vals, int anyequal)
    2519             : {
    2520             :         sql_exp *e = NULL;
    2521             : 
    2522       17537 :         for(node *n=vals->h; n; n = n->next) {
    2523       13435 :                 sql_exp *r = n->data, *ne;
    2524             : 
    2525       13435 :                 if (rel_convert_types(sql, NULL, NULL, l, &r, 1, type_equal_no_any) < 0)
    2526           0 :                         return NULL;
    2527       13435 :                 if (anyequal)
    2528       13232 :                         ne = rel_binop_(sql, NULL, *l, r, "sys", "=", card_value);
    2529             :                 else
    2530         203 :                         ne = rel_binop_(sql, NULL, *l, r, "sys", "<>", card_value);
    2531       13435 :                 if (!e) {
    2532             :                         e = ne;
    2533        9333 :                 } else if (anyequal) {
    2534        9221 :                         e = rel_binop_(sql, NULL, e, ne, "sys", "or", card_value);
    2535             :                 } else {
    2536         112 :                         e = rel_binop_(sql, NULL, e, ne, "sys", "and", card_value);
    2537             :                 }
    2538             :         }
    2539             :         return e;
    2540             : }
    2541             : 
    2542             : static sql_exp *
    2543          68 : exp_in_compare(mvc *sql, sql_exp **l, list *vals, int anyequal)
    2544             : {
    2545             :         int vals_only = 1;
    2546             : 
    2547         227 :         for(node *n=vals->h; n; n = n->next) {
    2548         159 :                 sql_exp *r = n->data;
    2549             : 
    2550         159 :                 if (rel_convert_types(sql, NULL, NULL, l, &r, 1, type_equal_no_any) < 0)
    2551           0 :                         return NULL;
    2552         159 :                 n->data = r;
    2553         159 :                 if (!exp_is_atom(r))
    2554             :                         vals_only = 0;
    2555             :         }
    2556          68 :         if (vals_only)
    2557           1 :                 return exp_in(sql->sa, *l, vals, anyequal?cmp_in:cmp_notin);
    2558             : 
    2559          67 :         if (!(*l = exp_in_project(sql, l, vals, anyequal)))
    2560             :                 return NULL;
    2561          67 :         return exp_compare(sql->sa, *l, exp_atom_bool(sql->sa, 1), cmp_equal);
    2562             : }
    2563             : 
    2564             : /* exp visitor */
    2565             : static inline sql_exp *
    2566      866853 : rewrite_anyequal(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    2567             : {
    2568             :         sql_subfunc *sf;
    2569      866853 :         if (e->type != e_func)
    2570             :                 return e;
    2571             : 
    2572      866853 :         sf = e->f;
    2573      866853 :         if (is_ddl(rel->op))
    2574             :                 return e;
    2575      805809 :         if (is_anyequal_func(sf) && !list_empty(e->l)) {
    2576       10230 :                 list *l = e->l;
    2577       10230 :                 mvc *sql = v->sql;
    2578             : 
    2579       10230 :                 if (list_length(l) == 2) { /* input is a set */
    2580             : 
    2581       10230 :                         sql_exp *ile = l->h->data, *le, *re = l->h->next->data;
    2582             :                         sql_rel *lsq = NULL, *rsq = NULL;
    2583             :                         int is_tuple = 0;
    2584             : 
    2585             :                         /* possibly this is already done ? */
    2586       10230 :                         if (exp_has_rel(ile))
    2587          27 :                                 lsq = exp_rel_get_rel(sql->sa, ile); /* get subquery */
    2588             : 
    2589          27 :                         if (lsq)
    2590          27 :                                 le = exp_rel_update_exp(sql, ile);
    2591             :                         else
    2592       10203 :                                 le = ile;
    2593             : 
    2594       10230 :                         if (is_values(le)) /* exp_values */
    2595             :                                 is_tuple = 1;
    2596             : 
    2597             :                         /* re should be a values list */
    2598       10230 :                         if (!lsq && !is_tuple && is_values(re) && !exps_have_rel_exp(re->f)) { /* exp_values */
    2599        4103 :                                 list *vals = re->f;
    2600             : 
    2601        4103 :                                 if (depth == 0 && is_select(rel->op)) {
    2602          68 :                                         v->changes++;
    2603          68 :                                         return exp_in_compare(sql, &le, vals, is_anyequal(sf));
    2604             :                                 } else {
    2605        4035 :                                         le = exp_in_project(sql, &le, vals, is_anyequal(sf));
    2606        4035 :                                         if (le && exp_name(e))
    2607        1590 :                                                 exp_prop_alias(sql->sa, le, e);
    2608        4035 :                                         v->changes++;
    2609        4035 :                                         return le;
    2610             :                                 }
    2611        6127 :                         } else if (!lsq && is_tuple && is_values(re) && !exps_have_rel_exp(re->f)) {
    2612             :                                 return e; /* leave as is, handled later */
    2613             :                         }
    2614             : 
    2615        6127 :                         if (is_atom(re->type) && re->f) { /* exp_values */
    2616             :                                 /* flatten using unions */
    2617        5501 :                                 rsq = rel_union_exps(sql, &le, re->f, is_tuple);
    2618        5501 :                                 if (!rsq)
    2619             :                                         return NULL;
    2620        5501 :                                 re = rsq->exps->t->data;
    2621             : 
    2622        5501 :                                 if (rsq && lsq)
    2623          27 :                                         exp_set_freevar(sql, re, rsq);
    2624        5501 :                                 if (!is_tuple && !is_freevar(re)) {
    2625        3670 :                                         re = exp_label(sql->sa, re, ++sql->label); /* unique name */
    2626        3670 :                                         list_hash_clear(rsq->exps);
    2627        3670 :                                         re = exp_ref(sql, re);
    2628        1831 :                                 } else if (has_label(re)) {
    2629        1831 :                                         re = exp_ref(sql, re);
    2630             :                                 }
    2631        5501 :                                 set_processed(rsq);
    2632             :                         }
    2633             : 
    2634        6127 :                         if (is_project(rel->op) || (is_select(rel->op) && (depth > 0 || (!is_tuple && rsq && rel_has_freevar(sql, rsq) && !is_anyequal(sf))))) {
    2635             :                                 list *exps = NULL;
    2636             :                                 sql_exp *rid, *lid, *a = NULL;
    2637             :                                 sql_rel *sq = lsq;
    2638         239 :                                 sql_subfunc *ea = sql_bind_func(sql, "sys", is_anyequal(sf)?"anyequal":"allnotequal", exp_subtype(re), NULL, F_AGGR);
    2639         198 :                                 int on_right = (lsq != NULL);
    2640             : 
    2641             :                                 /* we introduced extra selects */
    2642         198 :                                 assert(is_project(rel->op) || is_select(rel->op));
    2643         198 :                                 rsq = rel_add_identity2(sql, rsq, &rid);
    2644         198 :                                 rid = exp_ref(sql, rid);
    2645             : 
    2646         198 :                                 if (!lsq)
    2647         175 :                                         lsq = rel->l;
    2648         198 :                                 if (!lsq) { /* single row */
    2649           0 :                                         lid = exp_atom_lng(sql->sa, 1);
    2650             :                                 } else {
    2651         198 :                                         exps = rel_projections(sql, lsq, NULL, 1/*keep names */, 1);
    2652         198 :                                         lsq = rel_add_identity(sql, lsq, &lid);
    2653         198 :                                         if (!sq)
    2654         175 :                                                 rel->l = lsq;
    2655         198 :                                         lid = exp_ref(sql, lid);
    2656             :                                 }
    2657             : 
    2658         198 :                                 if (sq) {
    2659          23 :                                         sql_rel *l = NULL, *rewrite = NULL;
    2660          23 :                                         if (rsq && lsq->card == CARD_ATOM && rsq->card == CARD_ATOM) { /* add project true */
    2661           7 :                                                 lsq = rel_crossproduct(sql->sa, lsq, rsq, op_full);
    2662           7 :                                                 lsq = rel_crossproduct(sql->sa, rel_project_exp(sql->sa, exp_atom_bool(sql->sa, 1)), lsq, op_left);
    2663             :                                                 rsq = 0;
    2664             :                                         }
    2665          39 :                                         (void)rewrite_inner(sql, rel, lsq, rel->card <= CARD_ATOM?op_left:op_join, &rewrite);
    2666          23 :                                         exp_reset_props(rewrite, le, is_left(rewrite->op));
    2667          23 :                                         l = rel->l;
    2668          23 :                                         if (l && on_right && (!is_join(l->op) || (is_project(rel->op) && lsq->card <= CARD_ATOM && rsq->card <= CARD_ATOM)))
    2669             :                                                 on_right = 0;
    2670             :                                 }
    2671         198 :                                 if (rsq) {
    2672         191 :                                         if (on_right) {
    2673          16 :                                                 sql_rel *join = rel->l; /* the introduced join */
    2674          30 :                                                 join->r = rel_crossproduct(sql->sa, join->r, rsq, (depth && is_select(rel->op))?op_right:op_left);
    2675          16 :                                                 set_dependent(join);
    2676          16 :                                                 exp_reset_props(join->r, le, depth > 0);
    2677             :                                         } else {
    2678         175 :                                                 sql_rel *rewrite = NULL;
    2679         175 :                                                 operator_type op = !is_tuple?((depth>0)?op_left:op_join):is_anyequal(sf)?op_semi:op_anti;
    2680         175 :                                                 (void)rewrite_inner(sql, rel, rsq, op, &rewrite);
    2681         175 :                                                 exp_reset_props(rewrite, re, is_left(rewrite->op));
    2682             :                                         }
    2683             :                                 }
    2684             : 
    2685         198 :                                 if (on_right) {
    2686          23 :                                         sql_rel *join = rel->l; /* the introduced join */
    2687          23 :                                         lsq = join->r = rel_groupby(sql, join->r, exp2list(sql->sa, lid));
    2688             :                                 } else
    2689         175 :                                         lsq = rel->l = rel_groupby(sql, rel->l, exp2list(sql->sa, lid));
    2690         198 :                                 if (exps)
    2691         198 :                                         lsq->exps = exps;
    2692             : 
    2693         198 :                                 if (is_tuple) {
    2694           5 :                                         list *t = le->f;
    2695             :                                         /*list *l = sa_list(sql->sa);
    2696             :                                         list *r = sa_list(sql->sa);*/
    2697           5 :                                         int s1 = list_length(t), s2 = list_length(rsq->exps) - 1; /* subtract identity column */
    2698             : 
    2699             :                                         /* find out list of right expression */
    2700           5 :                                         if (s1 != s2)
    2701           0 :                                                 return sql_error(sql, 02, SQLSTATE(42000) "Subquery has too %s columns", (s2 < s1) ? "few" : "many");
    2702             :                                         /*for (node *n = t->h, *m = rsq->exps->h; n && m; n = n->next, m = m->next ) {
    2703             :                                                 sql_exp *le = n->data;
    2704             :                                                 sql_exp *re = m->data;
    2705             : 
    2706             :                                                 append(l, le);
    2707             :                                                 re = exp_ref(sql, re);
    2708             :                                                 append(r, re);
    2709             :                                         }
    2710             :                                         a = exp_aggr1(sql->sa, exp_values(sql->sa, l), ea, 0, 0, CARD_AGGR, has_nil(le));
    2711             :                                         append(a->l, exp_values(sql->sa, r));*/
    2712           5 :                                         return sql_error(sql, 02, SQLSTATE(42000) "Tuple matching at projections not implemented in the backend yet");
    2713             :                                 } else {
    2714         193 :                                         a = exp_aggr1(sql->sa, le, ea, 0, 0, CARD_AGGR, has_nil(le));
    2715         193 :                                         append(a->l, re);
    2716             :                                 }
    2717         193 :                                 append(a->l, rid);
    2718         193 :                                 le = rel_groupby_add_aggr(sql, lsq, a);
    2719         193 :                                 if (exp_name(e))
    2720          31 :                                         exp_prop_alias(sql->sa, le, e);
    2721         193 :                                 set_processed(lsq);
    2722         193 :                                 if (depth == 1 && is_ddl(rel->op)) { /* anyequal is at a ddl statment, it must be inside a relation */
    2723           0 :                                         v->changes++;
    2724           0 :                                         return exp_rel(sql, lsq);
    2725             :                                 }
    2726         193 :                                 v->changes++;
    2727         193 :                                 return le;
    2728             :                         } else {
    2729        5929 :                                 if (lsq) {
    2730           4 :                                         sql_rel *rewrite = NULL;
    2731           7 :                                         (void)rewrite_inner(sql, rel, lsq, rel->card<=CARD_ATOM?op_left:op_join, &rewrite);
    2732           4 :                                         exp_reset_props(rewrite, le, is_left(rewrite->op));
    2733             :                                 }
    2734        5929 :                                 if (rsq) {
    2735        5303 :                                         sql_rel *rewrite = NULL;
    2736        5303 :                                         operator_type op = !is_tuple?op_join:is_anyequal(sf)?op_semi:op_anti;
    2737        5303 :                                         (void)rewrite_inner(sql, rel, rsq, op, &rewrite);
    2738        5303 :                                         exp_reset_props(rewrite, re, is_left(rewrite->op));
    2739             :                                 }
    2740        5929 :                                 if (is_tuple) {
    2741        1823 :                                         list *t = le->f;
    2742        1823 :                                         list *l = sa_list(sql->sa);
    2743        1823 :                                         list *r = sa_list(sql->sa);
    2744        1823 :                                         int s1 = list_length(t), s2 = list_length(rsq->exps);
    2745             : 
    2746             :                                         /* find out list of right expression */
    2747        1823 :                                         if (s1 != s2)
    2748           0 :                                                 return sql_error(sql, 02, SQLSTATE(42000) "Subquery has too %s columns", (s2 < s1) ? "few" : "many");
    2749        5875 :                                         for (node *n = t->h, *m = rsq->exps->h; n && m; n = n->next, m = m->next ) {
    2750        4052 :                                                 sql_exp *le = n->data;
    2751        4052 :                                                 sql_exp *re = m->data;
    2752             : 
    2753        4052 :                                                 append(l, le);
    2754        4052 :                                                 re = exp_ref(sql, re);
    2755        4052 :                                                 append(r, re);
    2756             :                                         }
    2757        1823 :                                         v->changes++;
    2758        1823 :                                         return exp_in_func(sql, exp_values(sql->sa, l), exp_values(sql->sa, r), is_anyequal(sf), 1);
    2759             :                                 } else {
    2760        4106 :                                         if (exp_has_freevar(sql, le))
    2761           0 :                                                 rel_bind_var(sql, rel, le);
    2762        4106 :                                         v->changes++;
    2763        4106 :                                         return exp_in_func(sql, le, re, is_anyequal(sf), 0);
    2764             :                                 }
    2765             :                         }
    2766             :                 }
    2767             :         }
    2768             :         return e;
    2769             : }
    2770             : 
    2771             : /* exp visitor */
    2772             : /* rewrite compare expressions including quantifiers any and all */
    2773             : static inline sql_exp *
    2774      854921 : rewrite_compare(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    2775             : {
    2776             :         sql_subfunc *sf;
    2777      854921 :         if (e->type != e_func || is_ddl(rel->op))
    2778             :                 return e;
    2779             : 
    2780      793881 :         sf = e->f;
    2781      793881 :         if (is_compare_func(sf) && !list_empty(e->l)) {
    2782      159246 :                 list *l = e->l;
    2783             : 
    2784             :                 /* TODO handle range expressions */
    2785      159246 :                 if (list_length(l) == 2) { /* input is a set */
    2786      159246 :                         char *op = sf->func->base.name;
    2787             : 
    2788      159246 :                         sql_exp *ile = l->h->data, *le, *re = l->h->next->data, *rnull = NULL;
    2789             :                         sql_rel *lsq = NULL, *rsq = NULL;
    2790             :                         int is_tuple = 0; /* TODO add this feature, ie select (1,2) = (1,2) etc */
    2791             :                                          /* cleanup tuple handling by introducing an expression for tuples */
    2792      159246 :                         int quantifier = e->flag;
    2793             : 
    2794             :                         /* possibly this is already done ? */
    2795      159246 :                         if (exp_has_rel(ile)) {
    2796          40 :                                 depth += exp_rel_depth(ile);
    2797          40 :                                 lsq = exp_rel_get_rel(v->sql->sa, ile); /* get subquery */
    2798             :                         }
    2799             : 
    2800          40 :                         if (lsq)
    2801          40 :                                 le = exp_rel_update_exp(v->sql, ile);
    2802             :                         else
    2803      159206 :                                 le = ile;
    2804             : 
    2805      159246 :                         if (exp_has_rel(re))
    2806        6212 :                                 rsq = exp_rel_get_rel(v->sql->sa, re); /* get subquery */
    2807        6212 :                         if (rsq)
    2808        6212 :                                 re = exp_rel_update_exp(v->sql, re);
    2809             : 
    2810      159246 :                         if (is_values(le)) /* exp_values */
    2811             :                                 is_tuple = 1;
    2812             : 
    2813      159246 :                         if (!is_tuple && !lsq && !rsq) { /* trivial case, just re-write into a comparison */
    2814      153012 :                                 e->flag = 0; /* remove quantifier */
    2815             : 
    2816      153012 :                                 if (rel_convert_types(v->sql, NULL, NULL, &le, &re, 1, type_equal) < 0)
    2817             :                                         return NULL;
    2818      153012 :                                 if (depth == 0 && is_select(rel->op)) {
    2819           0 :                                         v->changes++;
    2820           0 :                                         return exp_compare(v->sql->sa, le, re, compare_str2type(op));
    2821             :                                 } else {
    2822      153012 :                                         le = exp_compare_func(v->sql, le, re, op, 0);
    2823      153012 :                                         if (exp_name(e))
    2824         462 :                                                 exp_prop_alias(v->sql->sa, le, e);
    2825      153012 :                                         v->changes++;
    2826      153012 :                                         return le;
    2827             :                                 }
    2828             :                         }
    2829        6234 :                         if (!is_tuple && is_values(re) && !exps_have_rel_exp(re->f)) { /* exp_values */
    2830             :                                 list *vals = re->f;
    2831             : 
    2832           0 :                                 assert(0);
    2833             :                                 if (depth == 0 && is_select(rel->op)) {
    2834             :                                         v->changes++;
    2835             :                                         return exp_in_compare(v->sql, &le, vals, is_anyequal(sf));
    2836             :                                 } else {
    2837             :                                         le = exp_in_project(v->sql, &le, vals, is_anyequal(sf));
    2838             :                                         if (le && exp_name(e))
    2839             :                                                 exp_prop_alias(v->sql->sa, le, e);
    2840             :                                         v->changes++;
    2841             :                                         return le;
    2842             :                                 }
    2843             :                         }
    2844             : 
    2845        6234 :                         if (is_values(re)) { /* exp_values */
    2846             :                                 /* flatten using unions */
    2847           0 :                                 rsq = rel_union_exps(v->sql, &le, re->f, is_tuple);
    2848           0 :                                 if (!rsq)
    2849             :                                         return NULL;
    2850           0 :                                 re = rsq->exps->t->data;
    2851             : 
    2852           0 :                                 if (!is_tuple) {
    2853           0 :                                         re = exp_label(v->sql->sa, re, ++v->sql->label); /* unique name */
    2854           0 :                                         list_hash_clear(rsq->exps);
    2855           0 :                                         re = exp_ref(v->sql, re);
    2856             :                                 }
    2857           0 :                                 set_processed(rsq);
    2858             :                         }
    2859             : 
    2860             :                         int is_cnt = 0;
    2861        6234 :                         if (rsq)
    2862        6212 :                                 is_cnt = exp_is_count(re, rsq);
    2863        6234 :                         if (is_project(rel->op) || depth > 0 || quantifier || is_cnt) {
    2864             :                                 sql_rel *sq = lsq;
    2865             : 
    2866        1180 :                                 assert(!is_tuple);
    2867             : 
    2868             :                                 if (!lsq)
    2869             :                                         lsq = rel->l;
    2870        1180 :                                 if (sq) {
    2871          38 :                                         sql_rel *rewrite = NULL;
    2872          38 :                                         operator_type op = (depth||quantifier)?op_left:op_join;
    2873          38 :                                         (void)rewrite_inner(v->sql, rel, sq, op, &rewrite);
    2874          38 :                                         exp_reset_props(rewrite, le, is_left(rewrite->op));
    2875             :                                 }
    2876        1180 :                                 if (quantifier) {
    2877             :                                         sql_subfunc *a;
    2878             : 
    2879         145 :                                         rsq = rel_groupby(v->sql, rsq, NULL);
    2880         145 :                                         a = sql_bind_func(v->sql, "sys", "null", exp_subtype(re), NULL, F_AGGR);
    2881         145 :                                         rnull = exp_aggr1(v->sql->sa, re, a, 0, 1, CARD_AGGR, has_nil(re));
    2882         145 :                                         rnull = rel_groupby_add_aggr(v->sql, rsq, rnull);
    2883             : 
    2884         145 :                                         if (is_notequal_func(sf))
    2885             :                                                 op = "=";
    2886         145 :                                         if (op[0] == '<') {
    2887          29 :                                                 a = sql_bind_func(v->sql, "sys", (quantifier==1)?"max":"min", exp_subtype(re), NULL, F_AGGR);
    2888         126 :                                         } else if (op[0] == '>') {
    2889          80 :                                                 a = sql_bind_func(v->sql, "sys", (quantifier==1)?"min":"max", exp_subtype(re), NULL, F_AGGR);
    2890             :                                         } else /* (op[0] == '=')*/ /* only = ALL */ {
    2891          70 :                                                 a = sql_bind_func(v->sql, "sys", "all", exp_subtype(re), NULL, F_AGGR);
    2892             :                                                 is_cnt = 1;
    2893             :                                         }
    2894         145 :                                         re = exp_aggr1(v->sql->sa, re, a, 0, 1, CARD_AGGR, has_nil(re));
    2895         145 :                                         re = rel_groupby_add_aggr(v->sql, rsq, re);
    2896         145 :                                         set_processed(rsq);
    2897             :                                 }
    2898        1180 :                                 if (rsq) {
    2899        1158 :                                         sql_rel *rewrite = NULL;
    2900        1158 :                                         operator_type op = ((!quantifier && depth > 0)||is_cnt)?op_left:op_join;
    2901        1158 :                                         (void)rewrite_inner(v->sql, rel, rsq, op, &rewrite);
    2902        1158 :                                         exp_reset_props(rewrite, re, is_left(rewrite->op));
    2903             :                                 }
    2904             : 
    2905        1180 :                                 if (rel_convert_types(v->sql, NULL, NULL, &le, &re, 1, type_equal) < 0)
    2906             :                                         return NULL;
    2907        1180 :                                 if (rnull) { /* complex compare operator */
    2908         145 :                                         sql_exp *lnull = rel_unop_(v->sql, rel, le, "sys", "isnull", card_value);
    2909         145 :                                         set_has_no_nil(lnull);
    2910         145 :                                         le = exp_compare_func(v->sql, le, re, op, 0);
    2911         249 :                                         le = rel_nop_(v->sql, rel, le, lnull, rnull, NULL, "sys", (quantifier==1)?"any":"all", card_value);
    2912         145 :                                         if (is_select(rel->op) && depth == 0) {
    2913          44 :                                                 le = exp_compare(v->sql->sa, le, exp_atom_bool(v->sql->sa, is_notequal_func(sf) ? 0 : 1), cmp_equal);
    2914         106 :                                         } else if (is_notequal_func(sf)) {
    2915          14 :                                                 le = rel_unop_(v->sql, rel, le, "sys", "not", card_value);
    2916             :                                         }
    2917        1035 :                                 } else if (is_project(rel->op) || depth) {
    2918        1026 :                                         le = exp_compare_func(v->sql, le, re, op, 0);
    2919             :                                 } else {
    2920           9 :                                         v->changes++;
    2921           9 :                                         return exp_compare(v->sql->sa, le, re, compare_str2type(op));
    2922             :                                 }
    2923        1171 :                                 if (exp_name(e))
    2924          43 :                                         exp_prop_alias(v->sql->sa, le, e);
    2925        1171 :                                 v->changes++;
    2926        1171 :                                 return le;
    2927             :                         } else {
    2928        5054 :                                 if (lsq) {
    2929           2 :                                         sql_rel *rewrite = NULL;
    2930           2 :                                         (void)rewrite_inner(v->sql, rel, lsq, op_join, &rewrite);
    2931           2 :                                         exp_reset_props(rewrite, le, is_left(rewrite->op));
    2932             :                                 }
    2933        5054 :                                 if (rsq) {
    2934        5054 :                                         sql_rel *rewrite = NULL;
    2935        5054 :                                         operator_type op = !is_tuple?op_join:is_anyequal(sf)?op_semi:op_anti;
    2936        5054 :                                         (void)rewrite_inner(v->sql, rel, rsq, op, &rewrite);
    2937        5054 :                                         exp_reset_props(rewrite, re, is_left(rewrite->op));
    2938             :                                 }
    2939        5054 :                                 if (is_tuple) {
    2940           0 :                                         list *t = le->f;
    2941           0 :                                         list *l = sa_list(v->sql->sa);
    2942           0 :                                         list *r = sa_list(v->sql->sa);
    2943           0 :                                         int s1 = list_length(t), s2 = list_length(rsq->exps); /* subtract identity column */
    2944             : 
    2945             :                                         /* find out list of right expression */
    2946           0 :                                         if (s1 != s2)
    2947           0 :                                                 return sql_error(v->sql, 02, SQLSTATE(42000) "Subquery has too %s columns", (s2 < s1) ? "few" : "many");
    2948           0 :                                         for (node *n = t->h, *m = rsq->exps->h; n && m; n = n->next, m = m->next ) {
    2949           0 :                                                 sql_exp *le = n->data;
    2950           0 :                                                 sql_exp *re = m->data;
    2951             : 
    2952           0 :                                                 append(l, le);
    2953           0 :                                                 append(r, re);
    2954             :                                         }
    2955           0 :                                         v->changes++;
    2956           0 :                                         return exp_compare(v->sql->sa, exp_values(v->sql->sa, l), exp_values(v->sql->sa, r), compare_str2type(op));
    2957             :                                 } else {
    2958        5054 :                                         if (exp_has_freevar(v->sql, le))
    2959           0 :                                                 rel_bind_var(v->sql, rel, le);
    2960        5054 :                                         if (rel_convert_types(v->sql, NULL, NULL, &le, &re, 1, type_equal) < 0)
    2961             :                                                 return NULL;
    2962        5054 :                                         v->changes++;
    2963        5054 :                                         return exp_compare(v->sql->sa, le, re, compare_str2type(op));
    2964             :                                 }
    2965             :                         }
    2966             :                 }
    2967             :         }
    2968             :         return e;
    2969             : }
    2970             : 
    2971             : static sql_rel *
    2972     3627913 : rewrite_join2semi(visitor *v, sql_rel *rel)
    2973             : {
    2974     3627913 :         if (mvc_highwater(v->sql))
    2975           0 :                 return sql_error(v->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
    2976             : 
    2977     3627913 :         if (is_select(rel->op) && !list_empty(rel->exps)) {
    2978      337031 :                 sql_rel *j = rel->l;
    2979             :                 int needed = 0, changed = 0;
    2980             : 
    2981      337031 :                 if (!j || (!is_join(j->op) && !is_semi(j->op)) || !list_empty(j->exps))
    2982      324825 :                         return rel;
    2983             :                 /* if needed first push select exps down under the join */
    2984       24666 :                 for (node *n = rel->exps->h; n;) {
    2985       12460 :                         node *next = n->next;
    2986       12460 :                         sql_exp *e = n->data;
    2987       12460 :                         sql_subfunc *sf = e->f;
    2988             : 
    2989       12460 :                         if (is_func(e->type) && is_anyequal_func(sf)) {
    2990        5318 :                                 if (exp_card(e) > CARD_ATOM && rel_has_all_exps(j->l, e->l)) {
    2991          22 :                                         sql_rel *l = j->l;
    2992          22 :                                         if (!is_select(l->op) || rel_is_ref(l)) {
    2993          20 :                                                 set_processed(l);
    2994          20 :                                                 j->l = l = rel_select(v->sql->sa, j->l, NULL);
    2995             :                                         }
    2996          22 :                                         rel_select_add_exp(v->sql->sa, l, e);
    2997          22 :                                         list_remove_node(rel->exps, NULL, n);
    2998             :                                         changed = 1;
    2999          22 :                                         v->changes++;
    3000             :                                 } else {
    3001             :                                         needed = 1;
    3002             :                                 }
    3003             :                         }
    3004             :                         n = next;
    3005             :                 }
    3006       12206 :                 if (changed && !(j->l = rewrite_join2semi(v, j->l)))
    3007             :                         return NULL;
    3008       12206 :                 if (!needed)
    3009        6910 :                         return try_remove_empty_select(v, rel);
    3010        5296 :                 if (!j->exps)
    3011        5296 :                         j->exps = sa_list(v->sql->sa);
    3012       10618 :                 for (node *n = rel->exps->h; n; ) {
    3013        5322 :                         node *next = n->next;
    3014        5322 :                         sql_exp *e = n->data;
    3015        5322 :                         sql_subfunc *sf = e->f;
    3016             : 
    3017             :                         /* If the left relation cannot hold the comparison it cannot be pushed to the semi(anti)-join
    3018             :                            For now I guess only comparisons or anyequal func can appear here */
    3019        5322 :                         assert((is_func(e->type) && is_anyequal_func(sf)) || e->type == e_cmp);
    3020        5322 :                         if ((is_func(e->type) && is_anyequal_func(sf)) || !rel_rebind_exp(v->sql, j->l, e)) {
    3021        5309 :                                 if (e->type == e_cmp) {
    3022          13 :                                         append(j->exps, e);
    3023             :                                 } else {
    3024        5296 :                                         list *args = e->l;
    3025             :                                         sql_exp *l, *r;
    3026             : 
    3027        5296 :                                         assert(list_length(args)==2);
    3028        5296 :                                         l = args->h->data;
    3029        5296 :                                         r = args->h->next->data;
    3030        5296 :                                         j->op = (is_anyequal(sf))?op_semi:op_anti;
    3031             : 
    3032        5296 :                                         if (is_values(l)) {
    3033        1823 :                                                 assert(is_values(r));
    3034             :                                                 list *ll = l->f, *rl = r->f;
    3035        5875 :                                                 for(node *n=ll->h, *m=rl->h; n && m; n=n->next, m=m->next) {
    3036        5635 :                                                         e = exp_compare(v->sql->sa, n->data, m->data, j->op == op_semi?mark_in:mark_notin);
    3037        4052 :                                                         append(j->exps, e);
    3038             :                                                 }
    3039             :                                         } else {
    3040        3473 :                                                 e = exp_compare(v->sql->sa, l, r, j->op == op_semi?mark_in:mark_notin);
    3041        3473 :                                                 append(j->exps, e);
    3042             :                                         }
    3043             :                                 }
    3044        5309 :                                 list_remove_node(rel->exps, NULL, n);
    3045             :                         }
    3046             :                         n = next;
    3047             :                 }
    3048        5296 :                 v->changes++;
    3049        5296 :                 rel = try_remove_empty_select(v, rel);
    3050             :         }
    3051             :         return rel;
    3052             : }
    3053             : 
    3054             : #define is_exists_func(sf) (strcmp(sf->func->base.name, "sql_exists") == 0 || strcmp(sf->func->base.name, "sql_not_exists") == 0)
    3055             : #define is_exists(sf) (strcmp(sf->func->base.name, "sql_exists") == 0)
    3056             : 
    3057             : static sql_exp *
    3058         701 : exp_exist(mvc *sql, sql_exp *le, sql_exp *ne, int exists)
    3059             : {
    3060             :         sql_subfunc *exists_func = NULL;
    3061             : 
    3062         701 :         if (exists)
    3063         687 :                 exists_func = sql_bind_func(sql, "sys", "sql_exists", exp_subtype(le), NULL, F_FUNC);
    3064             :         else
    3065          14 :                 exists_func = sql_bind_func(sql, "sys", "sql_not_exists", exp_subtype(le), NULL, F_FUNC);
    3066             : 
    3067         701 :         if (!exists_func)
    3068           0 :                 return sql_error(sql, 02, SQLSTATE(42000) "exist operator on type %s missing", exp_subtype(le)->type->base.name);
    3069         701 :         if (ne) { /* correlated case */
    3070         701 :                 ne = rel_unop_(sql, NULL, ne, "sys", "isnull", card_value);
    3071         701 :                 set_has_no_nil(ne);
    3072         701 :                 le = rel_nop_(sql, NULL, ne, exp_atom_bool(sql->sa, !exists), exp_atom_bool(sql->sa, exists), NULL, "sys", "ifthenelse", card_value);
    3073         701 :                 return le;
    3074             :         } else {
    3075           0 :                 sql_exp *res = exp_unop(sql->sa, le, exists_func);
    3076           0 :                 set_has_no_nil(res);
    3077           0 :                 return res;
    3078             :         }
    3079             : }
    3080             : 
    3081             : /* exp visitor */
    3082             : static sql_exp *
    3083      856623 : rewrite_exists(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    3084             : {
    3085             :         sql_subfunc *sf;
    3086      856623 :         if (e->type != e_func)
    3087             :                 return e;
    3088             : 
    3089      856623 :         sf = e->f;
    3090      856623 :         if (is_exists_func(sf) && !list_empty(e->l)) {
    3091        1702 :                 list *l = e->l;
    3092             : 
    3093        1702 :                 if (list_length(l) == 1) { /* exp_values */
    3094        1702 :                         sql_exp *ne = NULL, *ie = l->h->data, *le;
    3095             :                         sql_rel *sq = NULL;
    3096             : 
    3097        1702 :                         if (!exp_is_rel(ie)) { /* exists over a constant or a single value */
    3098          16 :                                 le = exp_atom_bool(v->sql->sa, is_exists(sf)?1:0);
    3099          16 :                                 if (depth == 0 && is_select(rel->op))
    3100           0 :                                         le = exp_compare(v->sql->sa, le, exp_atom_bool(v->sql->sa, 1), cmp_equal);
    3101          16 :                                 else if (exp_name(e))
    3102           5 :                                         exp_prop_alias(v->sql->sa, le, e);
    3103          16 :                                 v->changes++;
    3104          16 :                                 return le;
    3105             :                         }
    3106             : 
    3107        1686 :                         sq = exp_rel_get_rel(v->sql->sa, ie); /* get subquery */
    3108             : 
    3109        1686 :                         le = rel_reduce2one_exp(v->sql, sq);
    3110        1686 :                         le = exp_ref(v->sql, le);
    3111        1686 :                         if (is_project(rel->op) && is_freevar(le)) {
    3112             :                                 sql_exp *re, *jc, *null;
    3113             : 
    3114           0 :                                 re = rel_bound_exp(v->sql, sq);
    3115           0 :                                 re = rel_project_add_exp(v->sql, sq, re);
    3116           0 :                                 jc = rel_unop_(v->sql, NULL, re, "sys", "isnull", card_value);
    3117           0 :                                 set_has_no_nil(jc);
    3118           0 :                                 null = exp_null(v->sql->sa, exp_subtype(le));
    3119           0 :                                 le = rel_nop_(v->sql, NULL, jc, null, le, NULL, "sys", "ifthenelse", card_value);
    3120             :                         }
    3121             : 
    3122        1686 :                         if (is_project(rel->op) || depth > 0 || is_outerjoin(rel->op)) {
    3123             :                                 sql_subfunc *ea = NULL;
    3124         765 :                                 sq = rel_groupby(v->sql, sq, NULL);
    3125             : 
    3126         765 :                                 if (exp_is_rel(ie))
    3127         765 :                                         ie->l = sq;
    3128         788 :                                 ea = sql_bind_func(v->sql, "sys", is_exists(sf)?"exist":"not_exist", exp_subtype(le), NULL, F_AGGR);
    3129         765 :                                 le = exp_aggr1(v->sql->sa, le, ea, 0, 0, CARD_AGGR, 0);
    3130         765 :                                 le = rel_groupby_add_aggr(v->sql, sq, le);
    3131         765 :                                 if (rel_has_freevar(v->sql, sq))
    3132             :                                         ne = le;
    3133             : 
    3134         765 :                                 if (exp_has_rel(ie)) {
    3135         765 :                                         visitor iv = { .sql = v->sql };
    3136         765 :                                         if (!rewrite_exp_rel(&iv, rel, ie, depth))
    3137           0 :                                                 return NULL;
    3138             :                                 }
    3139             : 
    3140         765 :                                 if (is_project(rel->op) && rel_has_freevar(v->sql, sq))
    3141         701 :                                         if (!(le = exp_exist(v->sql, le, ne, is_exists(sf))))
    3142             :                                                 return NULL;
    3143         765 :                                 if (exp_name(e))
    3144          14 :                                         exp_prop_alias(v->sql->sa, le, e);
    3145         765 :                                 set_processed(sq);
    3146         765 :                                 if (depth == 1 && is_ddl(rel->op)) { /* exists is at a ddl statment, it must be inside a relation */
    3147           4 :                                         v->changes++;
    3148           4 :                                         return exp_rel(v->sql, sq);
    3149             :                                 }
    3150             :                         } else { /* rewrite into semi/anti join */
    3151        1009 :                                 (void)rewrite_inner(v->sql, rel, sq, is_exists(sf)?op_semi:op_anti, NULL);
    3152         921 :                                 v->changes++;
    3153         921 :                                 return exp_compare(v->sql->sa, exp_atom_bool(v->sql->sa, 1), exp_atom_bool(v->sql->sa, 1), cmp_equal);
    3154             :                         }
    3155         761 :                         v->changes++;
    3156         761 :                         return le;
    3157             :                 }
    3158             :         }
    3159             :         return e;
    3160             : }
    3161             : 
    3162             : /* exp visitor */
    3163             : static sql_exp *
    3164    19602557 : rewrite_ifthenelse(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    3165             : {
    3166             :         (void)depth;
    3167             :         /* for ifthenelse and rank flatten referenced inner expressions */
    3168    19602557 :         if (e->ref) {
    3169          19 :                 sql_rel *r = rel->l = rel_project(v->sql->sa, rel->l, rel_projections(v->sql, rel->l, NULL, 1, 1));
    3170             : 
    3171          19 :                 e->ref = 0;
    3172          19 :                 set_processed(r);
    3173          19 :                 append(r->exps, e);
    3174          19 :                 v->changes++;
    3175          19 :                 return exp_ref(v->sql, e);
    3176             :         }
    3177             : 
    3178             :         sql_subfunc *sf;
    3179    19602538 :         if (e->type != e_func)
    3180             :                 return e;
    3181             : 
    3182      863521 :         sf = e->f;
    3183             :         /* TODO also handle ifthenelse with more than 3 arguments */
    3184      863521 :         if (is_case_func(sf) && !list_empty(e->l) && list_length(e->l) == 3 && rel_has_freevar(v->sql, rel)) {
    3185        3638 :                 list *l = e->l;
    3186             : 
    3187             :                 /* remove unecessary = true expressions under ifthenelse */
    3188       14552 :                 for (node *n = l->h ; n ; n = n->next) {
    3189       10914 :                         sql_exp *e = n->data;
    3190             : 
    3191       10914 :                         if (e->type == e_cmp && e->flag == cmp_equal && exp_is_true(e) && exp_is_true(e->r))
    3192           0 :                                 n->data = e->l;
    3193             :                 }
    3194             : 
    3195        3638 :                 sql_exp *cond = l->h->data;
    3196        3638 :                 sql_exp *then_exp = l->h->next->data;
    3197        3638 :                 sql_exp *else_exp = l->h->next->next->data;
    3198             :                 sql_exp *not_cond, *cond_is_null;
    3199             : 
    3200        3638 :                 if (exp_has_rel(then_exp) || exp_has_rel(else_exp)) {
    3201             :                         bool single = false;
    3202             :                         //return sql_error(v->sql, 10, SQLSTATE(42000) "time to rewrite into union\n");
    3203             :                         // union(
    3204             :                         //      select(
    3205             :                         //              project [then]
    3206             :                         //      )[cond]
    3207             :                         //      select(
    3208             :                         //              project [else]
    3209             :                         //      )[not(cond) or cond is null]
    3210             :                         //) [ cols ]
    3211             :                         sql_rel *lsq = NULL, *rsq = NULL, *usq = NULL;
    3212             : 
    3213        3094 :                         if (exp_has_rel(then_exp)) {
    3214        2426 :                                 lsq = exp_rel_get_rel(v->sql->sa, then_exp);
    3215        2426 :                                 then_exp = exp_rel_update_exp(v->sql, then_exp);
    3216        2426 :                                 if (is_single(lsq))
    3217             :                                         single = true;
    3218        2426 :                                 reset_single(lsq);
    3219             :                         }
    3220        3094 :                         exp_set_freevar(v->sql, then_exp, lsq);
    3221        3094 :                         lsq = rel_project(v->sql->sa, lsq, append(sa_list(v->sql->sa), then_exp));
    3222        3094 :                         exp_set_freevar(v->sql, cond, lsq);
    3223        3094 :                         set_processed(lsq);
    3224        3094 :                         lsq = rel_select(v->sql->sa, lsq, exp_compare(v->sql->sa, cond, exp_atom_bool(v->sql->sa, 1), cmp_equal));
    3225        3094 :                         if (exp_has_rel(else_exp)) {
    3226         668 :                                 rsq = exp_rel_get_rel(v->sql->sa, else_exp);
    3227         668 :                                 else_exp = exp_rel_update_exp(v->sql, else_exp);
    3228         668 :                                 if (is_single(rsq))
    3229             :                                         single = true;
    3230         668 :                                 reset_single(rsq);
    3231             :                         }
    3232        3094 :                         exp_set_freevar(v->sql, else_exp, rsq);
    3233        3094 :                         rsq = rel_project(v->sql->sa, rsq, append(sa_list(v->sql->sa), else_exp));
    3234        3094 :                         cond = exp_copy(v->sql, cond);
    3235        3094 :                         exp_set_freevar(v->sql, cond, rsq);
    3236        3094 :                         not_cond = exp_compare(v->sql->sa, cond, exp_atom_bool(v->sql->sa, 0), cmp_equal);
    3237        3094 :                         cond = exp_copy(v->sql, cond);
    3238        3094 :                         cond_is_null = exp_compare(v->sql->sa, cond, exp_atom(v->sql->sa, atom_general(v->sql->sa, exp_subtype(cond), NULL)), cmp_equal);
    3239        3094 :                         set_has_no_nil(cond_is_null);
    3240        3094 :                         set_semantics(cond_is_null);
    3241        3094 :                         set_processed(rsq);
    3242        3094 :                         rsq = rel_select(v->sql->sa, rsq, exp_or(v->sql->sa, list_append(new_exp_list(v->sql->sa), not_cond), list_append(new_exp_list(v->sql->sa), cond_is_null), 0));
    3243        3094 :                         usq = rel_setop(v->sql->sa, lsq, rsq, op_union);
    3244        3094 :                         rel_setop_set_exps(v->sql, usq, append(sa_list(v->sql->sa), exp_ref(v->sql, e)), false);
    3245        3094 :                         if (single)
    3246        2427 :                                 set_single(usq);
    3247        3094 :                         set_processed(usq);
    3248        3094 :                         e = exp_rel(v->sql, usq);
    3249        3094 :                         v->changes++;
    3250             :                 }
    3251             :         }
    3252             :         return e;
    3253             : }
    3254             : 
    3255             : static list *
    3256      811932 : rewrite_compare_exps(visitor *v, sql_rel *rel, list *exps)
    3257             : {
    3258      811932 :         if (mvc_highwater(v->sql))
    3259           0 :                 return sql_error(v->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
    3260      811932 :         if (list_empty(exps))
    3261             :                 return exps;
    3262             : 
    3263     1780535 :         for(node *n = exps->h; n; n = n->next) {
    3264      968603 :                 sql_exp *e = n->data;
    3265             : 
    3266      968603 :                 if (!is_compare(e->type)) {
    3267             :                         sql_subtype bt;
    3268         131 :                         sql_find_subtype(&bt, "boolean", 0, 0);
    3269         131 :                         if (!(e = exp_check_type(v->sql, &bt, rel, e, type_equal)))
    3270           0 :                                 return NULL;
    3271         131 :                         n->data = e = exp_compare(v->sql->sa, e, exp_atom_bool(v->sql->sa, 1), cmp_equal);
    3272         131 :                         v->changes++;
    3273             :                 }
    3274      968603 :                 if (is_compare(e->type) && e->flag == cmp_or) {
    3275       31388 :                         if (!(e->l = rewrite_compare_exps(v, rel, e->l)))
    3276             :                                 return NULL;
    3277       31388 :                         if (!(e->r = rewrite_compare_exps(v, rel, e->r)))
    3278             :                                 return NULL;
    3279             :                 }
    3280             :         }
    3281             :         return exps;
    3282             : }
    3283             : 
    3284             : /* add an dummy true projection column */
    3285             : static inline sql_rel *
    3286     3627893 : rewrite_compare_exp(visitor *v, sql_rel *rel)
    3287             : {
    3288     3627893 :         if ((is_select(rel->op) || is_join(rel->op) || is_semi(rel->op)) && !list_empty(rel->exps))
    3289      749156 :                 if (!(rel->exps = rewrite_compare_exps(v, rel, rel->exps)))
    3290           0 :                         return NULL;
    3291             :         return rel;
    3292             : }
    3293             : 
    3294             : static inline sql_rel *
    3295     3627894 : rewrite_remove_xp_project(visitor *v, sql_rel *rel)
    3296             : {
    3297     3627894 :         if (rel->op == op_join && list_empty(rel->exps)) {
    3298       82223 :                 sql_rel *r = rel->r;
    3299             : 
    3300       82223 :                 if (is_simple_project(r->op) && r->l && !project_unsafe(r, 1)) {
    3301        9814 :                         sql_rel *rl = r->l;
    3302             : 
    3303        9814 :                         if (is_simple_project(rl->op) && !rl->l && list_length(rl->exps) == 1) {
    3304          18 :                                 sql_exp *t = rl->exps->h->data;
    3305             : 
    3306          18 :                                 if (is_atom(t->type) && !exp_name(t)) { /* atom with out alias cannot be used later */
    3307           8 :                                         rel = rel_project(v->sql->sa, rel->l, rel_projections(v->sql, rel->l, NULL, 1, 1));
    3308           8 :                                         list_merge(rel->exps, r->exps, (fdup)NULL);
    3309           8 :                                         set_processed(rel);
    3310           8 :                                         v->changes++;
    3311             :                                 }
    3312             :                         }
    3313             :                 }
    3314             :         }
    3315     3627894 :         return rel;
    3316             : }
    3317             : 
    3318             : static inline sql_rel *
    3319     6369928 : rewrite_remove_xp(visitor *v, sql_rel *rel)
    3320             : {
    3321     6369928 :         if (rel->op == op_join && list_empty(rel->exps)) {
    3322      188068 :                 sql_rel *r = rel->r;
    3323             : 
    3324      188068 :                 if (is_simple_project(r->op) && !r->l && list_length(r->exps) == 1) {
    3325         139 :                         sql_exp *t = r->exps->h->data;
    3326             : 
    3327         139 :                         if (is_atom(t->type) && !exp_name(t)) { /* atom with out alias cannot be used later */
    3328          71 :                                 v->changes++;
    3329          71 :                                 return rel->l;
    3330             :                         }
    3331             :                 }
    3332             :         }
    3333             :         return rel;
    3334             : }
    3335             : 
    3336             : /* rel visitor */
    3337             : static sql_rel *
    3338     6350164 : rewrite_fix_count(visitor *v, sql_rel *rel)
    3339             : {
    3340     6350164 :         if (rel->op == op_left && !is_single(rel)) {
    3341             :                 int rel_changes = 0;
    3342      299596 :                 sql_rel *r = rel->r;
    3343             : 
    3344      299596 :                 if (!is_rewrite_fix_count_used(r->used)) {
    3345             :                         /* TODO create an exp iterator */
    3346      294704 :                         list *rexps = rel_projections(v->sql, r, NULL, 1, 1), *exps;
    3347             : 
    3348      997400 :                         for(node *n = rexps->h; n; n=n->next) {
    3349      702696 :                                 sql_exp *e = n->data, *ne;
    3350             : 
    3351      702696 :                                 if (exp_is_count(e, r)) {
    3352             :                                         /* rewrite count in subquery */
    3353             :                                         list *args, *targs;
    3354        6809 :                                         sql_subfunc *isnil = sql_bind_func(v->sql, "sys", "isnull", exp_subtype(e), NULL, F_FUNC), *ifthen;
    3355             : 
    3356             :                                         rel_changes = 1;
    3357        6809 :                                         ne = exp_unop(v->sql->sa, e, isnil);
    3358        6809 :                                         set_has_no_nil(ne);
    3359        6809 :                                         targs = sa_list(v->sql->sa);
    3360        6809 :                                         append(targs, sql_bind_localtype("bit"));
    3361        6809 :                                         append(targs, exp_subtype(e));
    3362        6809 :                                         append(targs, exp_subtype(e));
    3363        6809 :                                         ifthen = sql_bind_func_(v->sql, "sys", "ifthenelse", targs, F_FUNC);
    3364        6809 :                                         args = sa_list(v->sql->sa);
    3365        6809 :                                         append(args, ne);
    3366        6809 :                                         append(args, exp_atom(v->sql->sa, atom_zero_value(v->sql->sa, exp_subtype(e))));
    3367        6809 :                                         append(args, e);
    3368        6809 :                                         ne = exp_op(v->sql->sa, args, ifthen);
    3369        6809 :                                         if (exp_name(e))
    3370        6809 :                                                 exp_prop_alias(v->sql->sa, ne, e);
    3371        6809 :                                         n->data = ne;
    3372             :                                 }
    3373             :                         }
    3374      294704 :                         if (rel_changes) { /* add project */
    3375        6809 :                                 exps = list_merge(rel_projections(v->sql, rel->l, NULL, 1, 1), rexps, (fdup)NULL);
    3376        6809 :                                 rel = rel_project(v->sql->sa, rel, exps);
    3377        6809 :                                 set_processed(rel);
    3378        6809 :                                 r->used |= rewrite_fix_count_used;
    3379        6809 :                                 v->changes++;
    3380             :                         }
    3381             :                 }
    3382             :         }
    3383     6350164 :         return rel;
    3384             : }
    3385             : 
    3386             : static inline sql_rel *
    3387     6369928 : rewrite_groupings(visitor *v, sql_rel *rel)
    3388             : {
    3389             :         prop *found;
    3390             : 
    3391     6369928 :         if (is_groupby(rel->op)) {
    3392             :                 /* ROLLUP, CUBE, GROUPING SETS cases */
    3393      135659 :                 if ((found = find_prop(rel->p, PROP_GROUPINGS))) {
    3394         118 :                         list *sets = (list*) found->value;
    3395             :                         sql_rel *unions = NULL;
    3396             : 
    3397         687 :                         for (node *n = sets->h ; n ; n = n->next) {
    3398             :                                 sql_rel *nrel;
    3399         569 :                                 list *l = (list*) n->data, *exps = sa_list(v->sql->sa), *pexps = sa_list(v->sql->sa);
    3400             : 
    3401         569 :                                 l = list_flaten(l);
    3402         569 :                                 nrel = rel_groupby(v->sql, unions ? rel_dup(rel->l) : rel->l, l);
    3403             : 
    3404        3776 :                                 for (node *m = rel->exps->h ; m ; m = m->next) {
    3405        3207 :                                         sql_exp *e = (sql_exp*) m->data, *ne = NULL;
    3406        3207 :                                         sql_subfunc *agr = (sql_subfunc*) e->f;
    3407             : 
    3408        3615 :                                         if (e->type == e_aggr && !agr->func->s && !strcmp(agr->func->base.name, "grouping")) {
    3409             :                                                 /* replace grouping aggregate calls with constants */
    3410         408 :                                                 sql_subtype tpe = ((sql_arg*) agr->func->res->h->data)->type;
    3411         408 :                                                 list *groups = (list*) e->l;
    3412         408 :                                                 atom *a = atom_int(v->sql->sa, &tpe, 0);
    3413             : #ifdef HAVE_HGE
    3414         408 :                                                 hge counter = (hge) list_length(groups) - 1;
    3415             : #else
    3416             :                                                 lng counter = (lng) list_length(groups) - 1;
    3417             : #endif
    3418         408 :                                                 assert(groups && list_length(groups) > 0);
    3419             : 
    3420        1292 :                                                 for (node *nn = groups->h ; nn ; nn = nn->next) {
    3421         884 :                                                         sql_exp *exp = (sql_exp*) nn->data;
    3422         884 :                                                         if (!exps_find_exp(l, exp)) {
    3423         447 :                                                                 switch (ATOMstorage(a->data.vtype)) {
    3424         411 :                                                                         case TYPE_bte:
    3425         411 :                                                                                 a->data.val.btval += (bte) (1 << counter);
    3426         411 :                                                                                 break;
    3427          36 :                                                                         case TYPE_sht:
    3428          36 :                                                                                 a->data.val.shval += (sht) (1 << counter);
    3429          36 :                                                                                 break;
    3430           0 :                                                                         case TYPE_int:
    3431           0 :                                                                                 a->data.val.ival += (int) (1 << counter);
    3432           0 :                                                                                 break;
    3433           0 :                                                                         case TYPE_lng:
    3434           0 :                                                                                 a->data.val.lval += (lng) (1 << counter);
    3435           0 :                                                                                 break;
    3436             : #ifdef HAVE_HGE
    3437           0 :                                                                         case TYPE_hge:
    3438           0 :                                                                                 a->data.val.hval += (hge) (1 << counter);
    3439           0 :                                                                                 break;
    3440             : #endif
    3441             :                                                                         default:
    3442           0 :                                                                                 assert(0);
    3443             :                                                                 }
    3444             :                                                         }
    3445         884 :                                                         counter--;
    3446             :                                                 }
    3447             : 
    3448         408 :                                                 ne = exp_atom(v->sql->sa, a);
    3449         408 :                                                 if (exp_name(e))
    3450         408 :                                                         exp_prop_alias(v->sql->sa, ne, e);
    3451        2799 :                                         } else if (e->type == e_column && !exps_find_exp(l, e) && !has_label(e)) {
    3452             :                                                 /* do not include in the output of the group by, but add to the project as null */
    3453         809 :                                                 ne = exp_atom(v->sql->sa, atom_general(v->sql->sa, exp_subtype(e), NULL));
    3454         809 :                                                 if (exp_name(e))
    3455         809 :                                                         exp_prop_alias(v->sql->sa, ne, e);
    3456             :                                         } else {
    3457        1990 :                                                 sql_exp *ec = exp_copy(v->sql, e);
    3458        1990 :                                                 ne = exp_ref(v->sql, ec);
    3459        1990 :                                                 append(exps, ec);
    3460             :                                         }
    3461        3207 :                                         append(pexps, ne);
    3462             :                                 }
    3463         569 :                                 if (list_empty(exps))
    3464          21 :                                         append(exps, exp_atom_bool(v->sql->sa, 1)); /* protection against empty projections */
    3465         569 :                                 nrel->exps = exps;
    3466         569 :                                 if (!list_empty(rel->r) && !list_empty(nrel->r)) { /* aliases on grouping columns, ugh */
    3467        1394 :                                         for (node *n = ((list*)nrel->r)->h ; n ; n = n->next) {
    3468         923 :                                                 sql_exp *e = n->data;
    3469         923 :                                                 const char *rname = exp_relname(e), *cname = exp_name(e);
    3470         923 :                                                 if (rname && cname) {
    3471         850 :                                                         n->data = exp_copy(v->sql, exps_bind_column2(rel->r, rname, cname, NULL));
    3472          73 :                                                 } else if (cname) {
    3473          73 :                                                         n->data = exp_copy(v->sql, exps_bind_column(rel->r, cname, NULL, NULL, 1));
    3474             :                                                 }
    3475             :                                         }
    3476         471 :                                         list_hash_clear(nrel->r);
    3477             :                                 }
    3478         569 :                                 set_processed(nrel);
    3479         569 :                                 if (list_empty(pexps))
    3480           0 :                                         append(pexps, exp_atom_bool(v->sql->sa, 1)); /* protection against empty projections */
    3481         569 :                                 nrel = rel_project(v->sql->sa, nrel, pexps);
    3482         569 :                                 set_processed(nrel);
    3483             : 
    3484         569 :                                 if (!unions)
    3485             :                                         unions = nrel;
    3486             :                                 else {
    3487         451 :                                         unions = rel_setop(v->sql->sa, unions, nrel, op_union);
    3488         451 :                                         rel_setop_set_exps(v->sql, unions, rel_projections(v->sql, rel, NULL, 1, 1), false);
    3489         451 :                                         set_processed(unions);
    3490             :                                 }
    3491         569 :                                 if (!unions)
    3492             :                                         return unions;
    3493             :                         }
    3494         118 :                         v->changes++;
    3495         118 :                         return unions;
    3496             :                 } else {
    3497             :                         bool found_grouping = false;
    3498      440236 :                         for (node *n = rel->exps->h ; n ; n = n->next) {
    3499      304703 :                                 sql_exp *e = (sql_exp*) n->data;
    3500      304703 :                                 sql_subfunc *agr = (sql_subfunc*) e->f;
    3501             : 
    3502      304703 :                                 if (e->type == e_aggr && !agr->func->s && !strcmp(agr->func->base.name, "grouping")) {
    3503             :                                         found_grouping = true;
    3504             :                                         break;
    3505             :                                 }
    3506             :                         }
    3507      135541 :                         if (found_grouping) {
    3508             :                                 /* replace grouping calls with constants of value 0 */
    3509           8 :                                 sql_rel *nrel = rel_groupby(v->sql, rel->l, rel->r);
    3510           8 :                                 list *exps = sa_list(v->sql->sa), *pexps = sa_list(v->sql->sa);
    3511           8 :                                 sql_subtype *bt = sql_bind_localtype("bte");
    3512             : 
    3513          21 :                                 for (node *n = rel->exps->h ; n ; n = n->next) {
    3514          13 :                                         sql_exp *e = (sql_exp*) n->data, *ne;
    3515          13 :                                         sql_subfunc *agr = (sql_subfunc*) e->f;
    3516             : 
    3517          13 :                                         if (e->type == e_aggr && !agr->func->s && !strcmp(agr->func->base.name, "grouping")) {
    3518           8 :                                                 ne = exp_atom(v->sql->sa, atom_int(v->sql->sa, bt, 0));
    3519           8 :                                                 if (exp_name(e))
    3520           8 :                                                         exp_prop_alias(v->sql->sa, ne, e);
    3521             :                                         } else {
    3522           5 :                                                 ne = exp_ref(v->sql, e);
    3523           5 :                                                 append(exps, e);
    3524             :                                         }
    3525          13 :                                         append(pexps, ne);
    3526             :                                 }
    3527           8 :                                 if (list_empty(exps))
    3528           5 :                                         append(exps, exp_atom_bool(v->sql->sa, 1)); /* protection against empty projections */
    3529           8 :                                 nrel->exps = exps;
    3530           8 :                                 set_processed(nrel);
    3531           8 :                                 v->changes++;
    3532           8 :                                 if (list_empty(pexps))
    3533           0 :                                         append(pexps, exp_atom_bool(v->sql->sa, 1)); /* protection against empty projections */
    3534           8 :                                 nrel = rel_project(v->sql->sa, nrel, pexps);
    3535           8 :                                 set_processed(nrel);
    3536           8 :                                 return nrel;
    3537             :                         }
    3538             :                 }
    3539             :         }
    3540             :         return rel;
    3541             : }
    3542             : 
    3543             : static int
    3544         144 : include_tid(sql_rel *r)
    3545             : {
    3546         144 :         if (is_basetable(r->op))
    3547          97 :                 r->nrcols = list_length(r->exps);
    3548         144 :         return r->nrcols;
    3549             : }
    3550             : 
    3551             : static inline sql_rel *
    3552     3551896 : rewrite_outer2inner_union(visitor *v, sql_rel *rel)
    3553             : {
    3554     3551896 :         if (is_outerjoin(rel->op) && !list_empty(rel->exps) && (((is_left(rel->op) || is_full(rel->op)) && rel_has_freevar(v->sql,rel->l)) ||
    3555      107267 :                 ((is_right(rel->op) || is_full(rel->op)) && rel_has_freevar(v->sql,rel->r)) || exps_have_freevar(v->sql, rel->exps) || exps_have_rel_exp(rel->exps))) {
    3556          72 :                 sql_exp *f = exp_atom_bool(v->sql->sa, 0);
    3557             :                 int nrcols = rel->nrcols;
    3558             : 
    3559          72 :                 nrcols = include_tid(rel->l);
    3560          72 :                 nrcols += include_tid(rel->r);
    3561          72 :                 rel->nrcols = nrcols;
    3562          72 :                 if (is_left(rel->op)) {
    3563          34 :                         sql_rel *prel = rel_project(v->sql->sa, rel, rel_projections(v->sql, rel, NULL, 1, 1));
    3564          68 :                         sql_rel *except = rel_setop(v->sql->sa,
    3565          34 :                                         rel_project(v->sql->sa, rel_dup(rel->l), rel_projections(v->sql, rel->l, NULL, 1, 1)),
    3566          68 :                                         rel_project(v->sql->sa, rel_dup(prel), rel_projections(v->sql, rel->l, NULL, 1, 1)), op_except);
    3567          34 :                         rel_setop_set_exps(v->sql, except, rel_projections(v->sql, rel->l, NULL, 1, 1), false);
    3568          34 :                         set_processed(except);
    3569          34 :                         sql_rel *nrel = rel_crossproduct(v->sql->sa, except, rel_dup(rel->r),  op_left);
    3570          34 :                         rel_join_add_exp(v->sql->sa, nrel, f);
    3571          34 :                         rel->op = op_join;
    3572          68 :                         nrel = rel_setop(v->sql->sa,
    3573             :                                         prel,
    3574          34 :                                         rel_project(v->sql->sa, nrel, rel_projections(v->sql, nrel, NULL, 1, 1)),
    3575             :                                         op_union);
    3576          34 :                         rel_setop_set_exps(v->sql, nrel, rel_projections(v->sql, rel, NULL, 1, 1), false);
    3577          34 :                         set_processed(nrel);
    3578          34 :                         v->changes++;
    3579          34 :                         return nrel;
    3580          38 :                 } else if (is_right(rel->op)) {
    3581          29 :                         sql_rel *prel = rel_project(v->sql->sa, rel, rel_projections(v->sql, rel, NULL, 1, 1));
    3582          58 :                         sql_rel *except = rel_setop(v->sql->sa,
    3583          29 :                                         rel_project(v->sql->sa, rel_dup(rel->r), rel_projections(v->sql, rel->r, NULL, 1, 1)),
    3584          58 :                                         rel_project(v->sql->sa, rel_dup(prel), rel_projections(v->sql, rel->r, NULL, 1, 1)), op_except);
    3585          29 :                         rel_setop_set_exps(v->sql, except, rel_projections(v->sql, rel->r, NULL, 1, 1), false);
    3586          29 :                         set_processed(except);
    3587          29 :                         sql_rel *nrel = rel_crossproduct(v->sql->sa, rel_dup(rel->l), except, op_right);
    3588          29 :                         rel_join_add_exp(v->sql->sa, nrel, f);
    3589          29 :                         rel->op = op_join;
    3590          58 :                         nrel = rel_setop(v->sql->sa,
    3591             :                                         prel,
    3592          29 :                                         rel_project(v->sql->sa, nrel, rel_projections(v->sql, nrel, NULL, 1, 1)),
    3593             :                                         op_union);
    3594          29 :                         rel_setop_set_exps(v->sql, nrel, rel_projections(v->sql, rel, NULL, 1, 1), false);
    3595          29 :                         set_processed(nrel);
    3596          29 :                         v->changes++;
    3597          29 :                         return nrel;
    3598           9 :                 } else if (is_full(rel->op)) {
    3599           9 :                         sql_rel *prel = rel_project(v->sql->sa, rel, rel_projections(v->sql, rel, NULL, 1, 1));
    3600          18 :                         sql_rel *except = rel_setop(v->sql->sa,
    3601           9 :                                         rel_project(v->sql->sa, rel_dup(rel->l), rel_projections(v->sql, rel->l, NULL, 1, 1)),
    3602          18 :                                         rel_project(v->sql->sa, rel_dup(prel), rel_projections(v->sql, rel->l, NULL, 1, 1)), op_except);
    3603           9 :                         rel_setop_set_exps(v->sql, except, rel_projections(v->sql, rel->l, NULL, 1, 1), false);
    3604           9 :                         set_processed(except);
    3605           9 :                         sql_rel *lrel = rel_crossproduct(v->sql->sa, except, rel_dup(rel->r),  op_left);
    3606           9 :                         rel_join_add_exp(v->sql->sa, lrel, f);
    3607             : 
    3608          18 :                         except = rel_setop(v->sql->sa,
    3609           9 :                                         rel_project(v->sql->sa, rel_dup(rel->r), rel_projections(v->sql, rel->r, NULL, 1, 1)),
    3610          18 :                                         rel_project(v->sql->sa, rel_dup(prel), rel_projections(v->sql, rel->r, NULL, 1, 1)), op_except);
    3611           9 :                         rel_setop_set_exps(v->sql, except, rel_projections(v->sql, rel->r, NULL, 1, 1), false);
    3612           9 :                         set_processed(except);
    3613           9 :                         sql_rel *rrel = rel_crossproduct(v->sql->sa, rel_dup(rel->l), except, op_right);
    3614           9 :                         rel_join_add_exp(v->sql->sa, rrel, f);
    3615          27 :                         lrel = rel_setop(v->sql->sa,
    3616           9 :                                         rel_project(v->sql->sa, lrel,  rel_projections(v->sql, lrel, NULL, 1, 1)),
    3617           9 :                                         rel_project(v->sql->sa, rrel, rel_projections(v->sql, rrel, NULL, 1, 1)),
    3618             :                                         op_union);
    3619           9 :                         rel_setop_set_exps(v->sql, lrel, rel_projections(v->sql, rel, NULL, 1, 1), false);
    3620           9 :                         set_processed(lrel);
    3621           9 :                         rel->op = op_join;
    3622          27 :                         lrel = rel_setop(v->sql->sa,
    3623           9 :                                         rel_project(v->sql->sa, prel,  rel_projections(v->sql, rel, NULL, 1, 1)),
    3624           9 :                                         rel_project(v->sql->sa, lrel, rel_projections(v->sql, lrel, NULL, 1, 1)),
    3625             :                                         op_union);
    3626           9 :                         rel_setop_set_exps(v->sql, lrel, rel_projections(v->sql, rel, NULL, 1, 1), false);
    3627           9 :                         set_processed(lrel);
    3628           9 :                         v->changes++;
    3629           9 :                         return lrel;
    3630             :                 }
    3631             :         }
    3632             :         return rel;
    3633             : }
    3634             : 
    3635             : static sql_exp *
    3636    19961788 : rewrite_complex(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    3637             : {
    3638             :         sql_exp *res = NULL;
    3639             : 
    3640    19961788 :         if (e->type != e_func)
    3641             :                 return e;
    3642             : 
    3643      866853 :         res = rewrite_anyequal(v, rel, e, depth);
    3644      866853 :         if (!res || res != e)
    3645             :                 return res;
    3646      856623 :         res = rewrite_exists(v, rel, e, depth);
    3647      856623 :         if (!res || res != e)
    3648             :                 return res;
    3649      854921 :         res = rewrite_compare(v, rel, e, depth);
    3650      854921 :         if (!res || res != e)
    3651      159246 :                 return res;
    3652             :         return e;
    3653             : }
    3654             : 
    3655             : /* rewrite project [ [multi values], [multi values2] , .. [] ] -> union ) */
    3656             : static inline sql_rel *
    3657     3551896 : rewrite_values(visitor *v, sql_rel *rel)
    3658             : {
    3659     3551896 :         int single = is_single(rel);
    3660     3551896 :         if (!is_simple_project(rel->op) || list_empty(rel->exps))
    3661     2274806 :                 return rel;
    3662             : 
    3663     1277090 :         if (rel_is_ref(rel) && !is_rewrite_values_used(rel->used)) { /* need extra project */
    3664        9576 :                 rel->l = rel_project(v->sql->sa, rel->l, rel->exps);
    3665        9576 :                 rel->exps = rel_projections(v->sql, rel->l, NULL, 1, 1);
    3666        9576 :                 ((sql_rel*)rel->l)->r = rel->r; /* propagate order by exps */
    3667        9576 :                 rel->r = NULL;
    3668        9576 :                 rel->used |= rewrite_values_used;
    3669        9576 :                 v->changes++;
    3670        9576 :                 return rel;
    3671             :         }
    3672     1267514 :         sql_exp *e = rel->exps->h->data;
    3673             : 
    3674     1267514 :         if (!is_values(e) || list_length(exp_get_values(e))<=1 || (!exp_has_freevar(v->sql, e) && !exp_has_rel(e)))
    3675     1267494 :                 return rel;
    3676             : 
    3677          20 :         list *exps = sa_list(v->sql->sa);
    3678             :         sql_rel *cur = NULL;
    3679          20 :         list *vals = exp_get_values(e);
    3680          20 :         if (vals) {
    3681          62 :                 for(int i = 0; i<list_length(vals); i++) {
    3682          42 :                         sql_rel *nrel = rel_project(v->sql->sa, NULL, sa_list(v->sql->sa));
    3683          42 :                         set_processed(nrel);
    3684          86 :                         for(node *n = rel->exps->h; n; n = n->next) {
    3685          44 :                                 sql_exp *e = n->data;
    3686          44 :                                 list *vals = exp_get_values(e);
    3687             : 
    3688          44 :                                 if (vals) {
    3689          44 :                                         if (i == 0)
    3690          21 :                                                 append(exps, exp_ref(v->sql, e));
    3691          44 :                                         sql_exp *v = list_fetch(vals, i);
    3692          44 :                                         append(nrel->exps, v);
    3693          44 :                                         rel_set_exps(nrel, nrel->exps);
    3694             :                                 }
    3695             :                         }
    3696          42 :                         if (cur) {
    3697          22 :                                 nrel = rel_setop(v->sql->sa, cur, nrel, op_union);
    3698          22 :                                 rel_setop_set_exps(v->sql, nrel, exps, false);
    3699          22 :                                 set_processed(nrel);
    3700             :                         }
    3701             :                         cur = nrel;
    3702             :                 }
    3703             :                 rel = cur;
    3704          20 :                 if (single)
    3705           2 :                         set_single(rel);
    3706             :         }
    3707          20 :         v->changes++;
    3708          20 :         return rel;
    3709             : }
    3710             : 
    3711             : /* add an dummy true projection column */
    3712             : static sql_rel *
    3713     3551897 : rel_unnest_simplify(visitor *v, sql_rel *rel)
    3714             : {
    3715             :         /* at rel_select.c explicit cross-products generate empty selects, if these are not used, they can be removed at rewrite_simplify */
    3716     3551897 :         if (rel)
    3717     3551897 :                 rel = rewrite_basetable(v->sql, rel);        /* add proper exps lists */
    3718     3551897 :         if (rel)
    3719     3551897 :                 rel = rewrite_empty_project(v, rel); /* remove empty project/groupby */
    3720     3551897 :         if (rel)
    3721     3551897 :                 rel = rewrite_simplify(v, rel);
    3722     3551897 :         if (rel)
    3723     3551897 :                 rel = rewrite_split_select_exps(v, rel); /* has to run before rewrite_complex */
    3724     3551896 :         if (rel)
    3725     3551896 :                 rel = rewrite_outer2inner_union(v, rel); /* has to run before rewrite_or_exp */
    3726     3551896 :         if (rel)
    3727     3551896 :                 rel = rewrite_or_exp(v, rel);
    3728     3551896 :         if (rel)
    3729     3551896 :                 rel = rewrite_aggregates(v, rel);
    3730     3551896 :         if (rel)
    3731     3551896 :                 rel = rewrite_values(v, rel);
    3732     3551896 :         return rel;
    3733             : }
    3734             : 
    3735             : static sql_rel *
    3736     6369928 : rel_unnest_projects(visitor *v, sql_rel *rel)
    3737             : {
    3738             :         /* both rewrite_values and rewrite_fix_count use 'used' property from sql_rel, reset it, so make sure rewrite_reset_used is called first */
    3739     6369928 :         if (rel)
    3740     6369928 :                 rel = rewrite_reset_used(v, rel);
    3741     6369927 :         if (rel)
    3742     6369927 :                 rel = rewrite_remove_xp(v, rel);        /* remove crossproducts with project [ atom ] */
    3743     6369928 :         if (rel)
    3744     6369928 :                 rel = rewrite_groupings(v, rel);        /* transform group combinations into union of group relations */
    3745     6369928 :         return rel;
    3746             : }
    3747             : 
    3748             : static sql_rel *
    3749     3627893 : rel_unnest_comparison_rewriters(visitor *v, sql_rel *rel)
    3750             : {
    3751     3627893 :         if (rel)
    3752     3627893 :                 rel = rewrite_join2semi(v, rel);        /* where possible convert anyequal functions into marks */
    3753     3627893 :         if (rel)
    3754     3627893 :                 rel = rewrite_compare_exp(v, rel);      /* only allow for e_cmp in selects and  handling */
    3755     3627894 :         if (rel)
    3756     3627894 :                 rel = rewrite_remove_xp_project(v, rel);        /* remove crossproducts with project ( project [ atom ] ) [ etc ] */
    3757     3627894 :         if (rel)
    3758     3627894 :                 rel = rewrite_simplify(v, rel);         /* as expressions got merged before, lets try to simplify again */
    3759     3627894 :         return rel;
    3760             : }
    3761             : 
    3762             : static sql_exp *
    3763    16792262 : rel_simplify_exp_and_rank(visitor *v, sql_rel *rel, sql_exp *e, int depth)
    3764             : {
    3765    16792262 :         if (e)
    3766    16792262 :                 e = rewrite_simplify_exp(v, rel, e, depth);
    3767    16792264 :         if (e)
    3768    16792264 :                 e = rewrite_rank(v, rel, e, depth);
    3769    16792265 :         return e;
    3770             : }
    3771             : 
    3772             : sql_rel *
    3773      514641 : rel_unnest(mvc *sql, sql_rel *rel)
    3774             : {
    3775      514641 :         int level = 0;
    3776      514641 :         visitor v = { .sql = sql, .data = &level }; /* make it compatible with rel_optimizer, so set the level to 0 */
    3777             : 
    3778      514641 :         rel = rel_exp_visitor_bottomup(&v, rel, &rel_simplify_exp_and_rank, false);
    3779      514641 :         rel = rel_visitor_bottomup(&v, rel, &rel_unnest_simplify);
    3780      514641 :         rel = rel_visitor_bottomup(&v, rel, &not_anyequal_helper);
    3781             : 
    3782      514641 :         rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_complex, true);
    3783      514641 :         rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_ifthenelse, false);    /* add isnull handling */
    3784      514641 :         rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_exp_rel, true);
    3785             : 
    3786      514641 :         rel = rel_visitor_bottomup(&v, rel, &rel_unnest_comparison_rewriters);
    3787      514641 :         rel = rel_visitor_bottomup(&v, rel, &_rel_unnest);
    3788      514641 :         rel = rel_visitor_bottomup(&v, rel, &rewrite_fix_count);        /* fix count inside a left join (adds a project (if (cnt IS null) then (0) else (cnt)) */
    3789      514641 :         rel = rel_visitor_bottomup(&v, rel, &rel_unnest_projects);
    3790      514641 :         rel = rel_exp_visitor_bottomup(&v, rel, &exp_reset_card_and_freevar_set_physical_type, false);
    3791      514641 :         return rel;
    3792             : }

Generated by: LCOV version 1.14