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 - 2019 MonetDB B.V.
7 */
8
9#include "monetdb_config.h"
10#include "rel_select.h"
11#include "sql_tokens.h"
12#include "sql_semantic.h" /* TODO this dependency should be removed, move
13 the dependent code into sql_mvc */
14#include "sql_privileges.h"
15#include "sql_env.h"
16#include "rel_rel.h"
17#include "rel_exp.h"
18#include "rel_xml.h"
19#include "rel_dump.h"
20#include "rel_prop.h"
21#include "rel_psm.h"
22#include "rel_schema.h"
23#include "rel_unnest.h"
24#include "rel_remote.h"
25#include "rel_sequence.h"
26#ifdef HAVE_HGE
27#include "mal.h" /* for have_hge */
28#endif
29
30#define VALUE_FUNC(f) (f->func->type == F_FUNC || f->func->type == F_FILT)
31#define check_card(card,f) ((card == card_none && !f->res) || (CARD_VALUE(card) && f->res && VALUE_FUNC(f)) || card == card_loader || (card == card_relation && f->func->type == F_UNION))
32
33/* return all expressions, with table name == tname */
34static list *
35rel_table_projections( mvc *sql, sql_rel *rel, char *tname, int level )
36{
37 list *exps;
38
39 if (THRhighwater())
40 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
41
42 if (!rel)
43 return NULL;
44
45 if (!tname)
46 return _rel_projections(sql, rel, NULL, 1, 0, 1);
47
48 switch(rel->op) {
49 case op_join:
50 case op_left:
51 case op_right:
52 case op_full:
53 exps = rel_table_projections( sql, rel->l, tname, level+1);
54 if (exps)
55 return exps;
56 return rel_table_projections( sql, rel->r, tname, level+1);
57 case op_semi:
58 case op_anti:
59 case op_select:
60 return rel_table_projections( sql, rel->l, tname, level+1);
61
62 case op_topn:
63 case op_sample:
64 case op_groupby:
65 case op_union:
66 case op_except:
67 case op_inter:
68 case op_project:
69 if (!is_processed(rel) && level == 0)
70 return rel_table_projections( sql, rel->l, tname, level+1);
71 /* fall through */
72 case op_table:
73 case op_basetable:
74 if (rel->exps) {
75 int rename = 0;
76 node *en;
77
78 /* first check alias */
79 if (!is_base(rel->op) && !level) {
80 list *exps = sa_list(sql->sa);
81
82 for (en = rel->exps->h; en && !rename; en = en->next) {
83 sql_exp *e = en->data;;
84
85 if ((is_basecol(e) && exp_relname(e) && strcmp(exp_relname(e), tname) == 0) ||
86 (is_basecol(e) && !exp_relname(e) && e->l && strcmp(e->l, tname) == 0)) {
87 if (exp_name(e) && exps_bind_column2(exps, tname, exp_name(e)))
88 rename = 1;
89 else
90 append(exps, e);
91 }
92 }
93 }
94
95 exps = new_exp_list(sql->sa);
96 for (en = rel->exps->h; en; en = en->next) {
97 sql_exp *e = en->data;
98 if (is_basecol(e) && exp_relname(e) && strcmp(exp_relname(e), tname) == 0) {
99 if (rename)
100 append(exps, exp_alias_ref(sql, e));
101 else
102 append(exps, exp_alias_or_copy(sql, tname, exp_name(e), rel, e));
103 }
104 if (is_basecol(e) && !exp_relname(e) && e->l && strcmp(e->l, tname) == 0) {
105 if (rename)
106 append(exps, exp_alias_ref(sql, e));
107 else
108 append(exps, exp_alias_or_copy(sql, tname, exp_name(e), rel, e));
109 }
110
111 }
112 if (exps && list_length(exps))
113 return exps;
114 }
115 /* fall through */
116 default:
117 return NULL;
118 }
119}
120
121static sql_rel*
122rel_parent( sql_rel *rel )
123{
124 if (rel->l && (is_project(rel->op) || rel->op == op_topn || rel->op == op_sample)) {
125 sql_rel *l = rel->l;
126 if (is_project(l->op))
127 return l;
128 }
129 return rel;
130}
131
132static sql_exp *
133lastexp(sql_rel *rel)
134{
135 if (!is_processed(rel) || is_topn(rel->op) || is_sample(rel->op))
136 rel = rel_parent(rel);
137 assert(list_length(rel->exps));
138 assert(is_project(rel->op));
139 return rel->exps->t->data;
140}
141
142static sql_exp *
143rel_lastexp(mvc *sql, sql_rel *rel )
144{
145 sql_exp *e;
146
147 if (!is_processed(rel) || is_topn(rel->op))
148 rel = rel_parent(rel);
149 assert(list_length(rel->exps));
150 if (rel->op == op_project) {
151 MT_lock_set(&rel->exps->ht_lock);
152 rel->exps->ht = NULL;
153 MT_lock_unset(&rel->exps->ht_lock);
154 return exp_alias_or_copy(sql, NULL, NULL, rel, rel->exps->t->data);
155 }
156 assert(is_project(rel->op));
157 e = rel->exps->t->data;
158 if (!exp_name(e))
159 exp_label(sql->sa, e, ++sql->label);
160 return exp_ref(sql->sa, e);
161}
162
163static sql_rel *
164rel_orderby(mvc *sql, sql_rel *l)
165{
166 sql_rel *rel = rel_create(sql->sa);
167 if(!rel)
168 return NULL;
169
170 assert(l->op == op_project && !l->r);
171 rel->l = l;
172 rel->r = NULL;
173 rel->op = op_project;
174 rel->exps = rel_projections(sql, l, NULL, 1, 0);
175 rel->card = l->card;
176 rel->nrcols = l->nrcols;
177 return rel;
178}
179
180/* forward refs */
181static sql_rel * rel_setquery(sql_query *query, symbol *sq);
182static sql_rel * rel_joinquery(sql_query *query, sql_rel *rel, symbol *sq);
183static sql_rel * rel_crossquery(sql_query *query, sql_rel *rel, symbol *q);
184static sql_rel * rel_unionjoinquery(sql_query *query, sql_rel *rel, symbol *sq);
185
186static sql_rel *
187rel_table_optname(mvc *sql, sql_rel *sq, symbol *optname)
188{
189 sql_rel *osq = sq;
190 node *ne;
191
192 if (optname && optname->token == SQL_NAME) {
193 dlist *columnrefs = NULL;
194 char *tname = optname->data.lval->h->data.sval;
195 list *l = sa_list(sql->sa);
196
197 columnrefs = optname->data.lval->h->next->data.lval;
198 if (is_topn(sq->op) || (is_project(sq->op) && sq->r) || is_base(sq->op)) {
199 sq = rel_project(sql->sa, sq, rel_projections(sql, sq, NULL, 1, 0));
200 osq = sq;
201 }
202 if (columnrefs && dlist_length(columnrefs) > list_length(sq->exps))
203 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: The number of aliases is longer than the number of columns (%d>%d)", dlist_length(columnrefs), sq->nrcols);
204 if (columnrefs && sq->exps) {
205 dnode *d = columnrefs->h;
206
207 ne = sq->exps->h;
208 MT_lock_set(&sq->exps->ht_lock);
209 sq->exps->ht = NULL;
210 MT_lock_unset(&sq->exps->ht_lock);
211 for (; d && ne; d = d->next, ne = ne->next) {
212 sql_exp *e = ne->data;
213
214 if (exps_bind_column2(l, tname, d->data.sval))
215 return sql_error(sql, ERR_AMBIGUOUS, SQLSTATE(42000) "SELECT: Duplicate column name '%s.%s'", tname, d->data.sval);
216 exp_setname(sql->sa, e, tname, d->data.sval );
217 if (!is_intern(e))
218 set_basecol(e);
219 append(l, e);
220 }
221 }
222 if (!columnrefs && sq->exps) {
223 ne = sq->exps->h;
224 for (; ne; ne = ne->next) {
225 sql_exp *e = ne->data;
226
227 /*
228 if (exp_name(e) && exps_bind_column2(l, tname, exp_name(e)))
229 return sql_error(sql, ERR_AMBIGUOUS, SQLSTATE(42000) "SELECT: Duplicate column name '%s.%s'", tname, exp_name(e));
230 */
231 noninternexp_setname(sql->sa, e, tname, NULL );
232 if (!is_intern(e))
233 set_basecol(e);
234 append(l, e);
235 }
236 }
237 } else {
238 if (!is_project(sq->op) || is_topn(sq->op) || (is_project(sq->op) && sq->r)) {
239 sq = rel_project(sql->sa, sq, rel_projections(sql, sq, NULL, 1, 1));
240 osq = sq;
241 }
242 for (ne = osq->exps->h; ne; ne = ne->next) {
243 sql_exp *e = ne->data;
244
245 if (!is_intern(e))
246 set_basecol(e);
247 }
248 }
249 return osq;
250}
251
252static sql_rel *
253rel_subquery_optname(sql_query *query, sql_rel *rel, symbol *ast)
254{
255 mvc *sql = query->sql;
256 SelectNode *sn = (SelectNode *) ast;
257 exp_kind ek = {type_value, card_relation, TRUE};
258 sql_rel *sq = rel_subquery(query, rel, ast, ek);
259
260 assert(ast->token == SQL_SELECT);
261 if (!sq)
262 return NULL;
263
264 return rel_table_optname(sql, sq, sn->name);
265}
266
267sql_rel *
268rel_with_query(sql_query *query, symbol *q )
269{
270 mvc *sql = query->sql;
271 dnode *d = q->data.lval->h;
272 symbol *next = d->next->data.sym;
273 sql_rel *rel;
274
275 if(!stack_push_frame(sql, "WITH"))
276 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
277 /* first handle all with's (ie inlined views) */
278 for (d = d->data.lval->h; d; d = d->next) {
279 symbol *sym = d->data.sym;
280 dnode *dn = sym->data.lval->h;
281 char *name = qname_table(dn->data.lval);
282 sql_rel *nrel;
283
284 if (frame_find_var(sql, name)) {
285 stack_pop_frame(sql);
286 return sql_error(sql, 01, SQLSTATE(42000) "Variable '%s' already declared", name);
287 }
288 nrel = rel_semantic(query, sym);
289 if (!nrel) {
290 stack_pop_frame(sql);
291 return NULL;
292 }
293 if(!stack_push_rel_view(sql, name, nrel)) {
294 stack_pop_frame(sql);
295 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
296 }
297 if (!is_project(nrel->op)) {
298 if (is_topn(nrel->op) || is_sample(nrel->op)) {
299 nrel = rel_project(sql->sa, nrel, rel_projections(sql, nrel, NULL, 1, 1));
300 } else {
301 stack_pop_frame(sql);
302 return NULL;
303 }
304 }
305 assert(is_project(nrel->op));
306 if (is_project(nrel->op) && nrel->exps) {
307 node *ne = nrel->exps->h;
308
309 for (; ne; ne = ne->next) {
310 sql_exp *e = ne->data;
311
312 noninternexp_setname(sql->sa, e, name, NULL );
313 if (!is_intern(e))
314 set_basecol(e);
315 }
316 }
317 }
318 rel = rel_semantic(query, next);
319 stack_pop_frame(sql);
320 return rel;
321}
322
323static sql_rel *
324query_exp_optname(sql_query *query, sql_rel *r, symbol *q)
325{
326 mvc *sql = query->sql;
327 switch (q->token) {
328 case SQL_WITH:
329 {
330 sql_rel *tq = rel_with_query(query, q);
331
332 if (!tq)
333 return NULL;
334 if (q->data.lval->t->type == type_symbol)
335 return rel_table_optname(sql, tq, q->data.lval->t->data.sym);
336 return tq;
337 }
338 case SQL_UNION:
339 case SQL_EXCEPT:
340 case SQL_INTERSECT:
341 {
342 sql_rel *tq = rel_setquery(query, q);
343
344 if (!tq)
345 return NULL;
346 return rel_table_optname(sql, tq, q->data.lval->t->data.sym);
347 }
348 case SQL_JOIN:
349 {
350 sql_rel *tq = rel_joinquery(query, r, q);
351
352 if (!tq)
353 return NULL;
354 return rel_table_optname(sql, tq, q->data.lval->t->data.sym);
355 }
356 case SQL_CROSS:
357 {
358 sql_rel *tq = rel_crossquery(query, r, q);
359
360 if (!tq)
361 return NULL;
362 return rel_table_optname(sql, tq, q->data.lval->t->data.sym);
363 }
364 case SQL_UNIONJOIN:
365 {
366 sql_rel *tq = rel_unionjoinquery(query, r, q);
367
368 if (!tq)
369 return NULL;
370 return rel_table_optname(sql, tq, q->data.lval->t->data.sym);
371 }
372 default:
373 (void) sql_error(sql, 02, SQLSTATE(42000) "case %d %s\n", q->token, token2string(q->token));
374 }
375 return NULL;
376}
377
378static sql_subfunc *
379bind_func_(mvc *sql, sql_schema *s, char *fname, list *ops, sql_ftype type )
380{
381 sql_subfunc *sf = NULL;
382
383 if (sql->forward && strcmp(fname, sql->forward->base.name) == 0 &&
384 list_cmp(sql->forward->ops, ops, (fcmp)&arg_subtype_cmp) == 0 &&
385 execute_priv(sql, sql->forward))
386 return sql_dup_subfunc(sql->sa, sql->forward, NULL, NULL);
387 sf = sql_bind_func_(sql->sa, s, fname, ops, type);
388 if (sf && execute_priv(sql, sf->func))
389 return sf;
390 return NULL;
391}
392
393static sql_subfunc *
394bind_func(mvc *sql, sql_schema *s, char *fname, sql_subtype *t1, sql_subtype *t2, sql_ftype type )
395{
396 sql_subfunc *sf = NULL;
397
398 if (t1 == NULL)
399 return NULL;
400 if (sql->forward) {
401 if (execute_priv(sql, sql->forward) &&
402 strcmp(fname, sql->forward->base.name) == 0 &&
403 ((!t1 && list_length(sql->forward->ops) == 0) ||
404 (!t2 && list_length(sql->forward->ops) == 1 && subtype_cmp(sql->forward->ops->h->data, t1) == 0) ||
405 (list_length(sql->forward->ops) == 2 &&
406 subtype_cmp(sql->forward->ops->h->data, t1) == 0 &&
407 subtype_cmp(sql->forward->ops->h->next->data, t2) == 0))) {
408 return sql_dup_subfunc(sql->sa, sql->forward, NULL, NULL);
409 }
410 }
411 sf = sql_bind_func(sql->sa, s, fname, t1, t2, type);
412 if (sf && execute_priv(sql, sf->func))
413 return sf;
414 return NULL;
415}
416
417static sql_subfunc *
418bind_member_func(mvc *sql, sql_schema *s, char *fname, sql_subtype *t, int nrargs, sql_subfunc *prev)
419{
420 sql_subfunc *sf = NULL;
421
422 if (sql->forward && strcmp(fname, sql->forward->base.name) == 0 &&
423 list_length(sql->forward->ops) == nrargs && is_subtype(t, &((sql_arg *) sql->forward->ops->h->data)->type) && execute_priv(sql, sql->forward))
424 return sql_dup_subfunc(sql->sa, sql->forward, NULL, t);
425 sf = sql_bind_member(sql->sa, s, fname, t, nrargs, prev);
426 if (sf && execute_priv(sql, sf->func))
427 return sf;
428 return NULL;
429}
430
431static sql_subfunc *
432find_func(mvc *sql, sql_schema *s, char *fname, int len, sql_ftype type, sql_subfunc *prev )
433{
434 sql_subfunc *sf = NULL;
435
436 if (sql->forward && strcmp(fname, sql->forward->base.name) == 0 && list_length(sql->forward->ops) == len && execute_priv(sql, sql->forward))
437 return sql_dup_subfunc(sql->sa, sql->forward, NULL, NULL);
438 sf = sql_find_func(sql->sa, s, fname, len, type, prev);
439 if (sf && execute_priv(sql, sf->func))
440 return sf;
441 return NULL;
442}
443
444static int
445score_func( sql_subfunc *sf, list *tl)
446{
447 int score = 0;
448 node *n, *m;
449
450 /* todo varargs */
451 for (n = sf->func->ops->h, m = tl->h; n && m; n = n->next, m = m->next){
452 sql_arg *a = n->data;
453 sql_subtype *t = m->data;
454
455 if (a->type.type->eclass == EC_ANY)
456 score += 100;
457 else if (is_subtype(t, &a->type))
458 score += t->type->localtype * 20;
459 /* same class over converting to other class */
460 else if (t->type->eclass == a->type.type->eclass &&
461 t->type->localtype <= a->type.type->localtype)
462 score += a->type.type->localtype * 4;
463 /* make sure we rewrite decimals to float/doubles */
464 else if (t->type->eclass == EC_DEC &&
465 a->type.type->eclass == EC_FLT)
466 score += a->type.type->localtype * 2;
467 }
468 return score;
469}
470
471static sql_exp *
472find_table_function_type(mvc *sql, sql_schema *s, char *fname, list *exps, list *tl, sql_ftype type, sql_subfunc **sf)
473{
474 sql_exp *e = NULL;
475 *sf = bind_func_(sql, s, fname, tl, type);
476
477 if (*sf) {
478 e = exp_op(sql->sa, exps, *sf);
479 } else if (list_length(tl)) {
480 int len, match = 0;
481 list *funcs = sql_find_funcs(sql->sa, s, fname, list_length(tl), type);
482 if (!funcs)
483 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
484 len = list_length(funcs);
485 if (len > 1) {
486 int i, score = 0;
487 node *n;
488
489 for (i = 0, n = funcs->h; i<len; i++, n = n->next) {
490 int cscore = score_func(n->data, tl);
491 if (cscore > score) {
492 score = cscore;
493 match = i;
494 }
495 }
496 }
497 if (list_empty(funcs))
498 return NULL;
499
500 *sf = list_fetch(funcs, match);
501 if ((*sf)->func->vararg) {
502 e = exp_op(sql->sa, exps, *sf);
503 } else {
504 node *n, *m;
505 list *nexps = new_exp_list(sql->sa);
506 sql_subtype *atp = NULL;
507 sql_arg *aa = NULL;
508
509 /* find largest any type argument */
510 for (n = exps->h, m = (*sf)->func->ops->h; n && m; n = n->next, m = m->next) {
511 sql_arg *a = m->data;
512 sql_exp *e = n->data;
513
514 if (!aa && a->type.type->eclass == EC_ANY) {
515 atp = &e->tpe;
516 aa = a;
517 }
518 if (aa && a->type.type->eclass == EC_ANY &&
519 e->tpe.type->localtype > atp->type->localtype){
520 atp = &e->tpe;
521 aa = a;
522 }
523 }
524 for (n = exps->h, m = (*sf)->func->ops->h; n && m; n = n->next, m = m->next) {
525 sql_arg *a = m->data;
526 sql_exp *e = n->data;
527 sql_subtype *ntp = &a->type;
528
529 if (a->type.type->eclass == EC_ANY)
530 ntp = sql_create_subtype(sql->sa, atp->type, atp->digits, atp->scale);
531 e = rel_check_type(sql, ntp, NULL, e, type_equal);
532 if (!e) {
533 nexps = NULL;
534 break;
535 }
536 if (e->card > CARD_ATOM) {
537 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(e));
538
539 e = exp_aggr1(sql->sa, e, zero_or_one, 0, 0, CARD_ATOM, has_nil(e));
540 }
541 append(nexps, e);
542 }
543 e = NULL;
544 if (nexps)
545 e = exp_op(sql->sa, nexps, *sf);
546 }
547 }
548 return e;
549}
550
551static sql_exp*
552find_table_function(mvc *sql, sql_schema *s, char *fname, list *exps, list *tl)
553{
554 sql_subfunc* sf = NULL;
555 return find_table_function_type(sql, s, fname, exps, tl, F_UNION, &sf);
556}
557
558static sql_rel *
559rel_named_table_function(sql_query *query, sql_rel *rel, symbol *ast, int lateral)
560{
561 mvc *sql = query->sql;
562 list *exps = NULL, *tl;
563 node *m;
564 exp_kind ek = {type_value, card_relation, TRUE};
565 sql_rel *sq = NULL, *outer = NULL;
566 sql_exp *e = NULL;
567 sql_subfunc *sf = NULL;
568 symbol *sym = ast->data.lval->h->data.sym;
569 dnode *l = sym->data.lval->h;
570 char *tname = NULL;
571 char *fname = qname_fname(l->data.lval);
572 char *sname = qname_schema(l->data.lval);
573 node *en;
574 sql_schema *s = sql->session->schema;
575
576 tl = sa_list(sql->sa);
577 exps = new_exp_list(sql->sa);
578 if (l->next) { /* table call with subquery */
579 if (l->next->type == type_symbol && l->next->data.sym->token == SQL_SELECT) {
580 if (l->next->next != NULL)
581 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' requires a single sub query", fname);
582 sq = rel_subquery(query, NULL, l->next->data.sym, ek);
583 } else if (l->next->type == type_symbol || l->next->type == type_list) {
584 dnode *n;
585 exp_kind iek = {type_value, card_column, TRUE};
586 list *exps = sa_list (sql->sa);
587
588 if (l->next->type == type_symbol)
589 n = l->next;
590 else
591 n = l->next->data.lval->h;
592 for ( ; n; n = n->next) {
593 sql_exp *e = rel_value_exp(query, &outer, n->data.sym, sql_sel, iek);
594
595 if (!e)
596 return NULL;
597 append(exps, e);
598 }
599 sq = rel_project(sql->sa, NULL, exps);
600 if (lateral && outer) {
601 sq = rel_crossproduct(sql->sa, sq, outer, op_join);
602 set_dependent(sq);
603 }
604 }
605 /* reset error */
606 sql->session->status = 0;
607 sql->errstr[0] = '\0';
608 if (!sq || (!lateral && outer))
609 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
610 for (en = sq->exps->h; en; en = en->next) {
611 sql_exp *e = en->data;
612
613 append(exps, e=exp_alias_or_copy(sql, tname, exp_name(e), NULL, e));
614 append(tl, exp_subtype(e));
615 }
616 }
617
618 if (sname)
619 s = mvc_bind_schema(sql, sname);
620 e = find_table_function(sql, s, fname, exps, tl);
621 if (!e)
622 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
623 rel = sq;
624
625 if (ast->data.lval->h->next->data.sym)
626 tname = ast->data.lval->h->next->data.sym->data.lval->h->data.sval;
627 else
628 tname = make_label(sql->sa, ++sql->label);
629
630 /* column or table function */
631 sf = e->f;
632 if (e->type != e_func || sf->func->type != F_UNION) {
633 (void) sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' does not return a table", exp_func_name(e));
634 return NULL;
635 }
636
637 if (sql->emode == m_prepare && rel && rel->exps && sql->params) {
638 int i = 0;
639 /* for prepared statements set possible missing parameter SQL types from */
640 for (m = sf->func->ops->h; m; m = m->next, i++) {
641 sql_arg *func_arg = m->data;
642 sql_exp *proj_parameter = (sql_exp*) list_fetch(rel->exps, i);
643 if(proj_parameter) {
644 sql_arg *prep_arg = (sql_arg*) list_fetch(sql->params, proj_parameter->flag);
645 if(prep_arg && !prep_arg->type.type)
646 prep_arg->type = func_arg->type;
647 }
648 }
649 }
650
651 /* for each column add table.column name */
652 exps = new_exp_list(sql->sa);
653 for (m = sf->func->res->h; m; m = m->next) {
654 sql_arg *a = m->data;
655 sql_exp *e = exp_column(sql->sa, tname, a->name, &a->type, CARD_MULTI, 1, 0);
656
657 set_basecol(e);
658 append(exps, e);
659 }
660 rel = rel_table_func(sql->sa, rel, e, exps, (sq != NULL));
661 if (ast->data.lval->h->next->data.sym && ast->data.lval->h->next->data.sym->data.lval->h->next->data.lval)
662 rel = rel_table_optname(sql, rel, ast->data.lval->h->next->data.sym);
663 return rel;
664}
665
666static sql_exp *
667rel_op_(mvc *sql, sql_schema *s, char *fname, exp_kind ek)
668{
669 sql_subfunc *f = NULL;
670 sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card == card_none)?F_PROC:
671 ((ek.card == card_relation)?F_UNION:F_FUNC));
672
673 f = sql_bind_func(sql->sa, s, fname, NULL, NULL, type);
674 if (f && check_card(ek.card, f)) {
675 return exp_op(sql->sa, NULL, f);
676 } else {
677 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
678 }
679}
680
681static sql_rel *
682rel_values(sql_query *query, symbol *tableref)
683{
684 mvc *sql = query->sql;
685 sql_rel *r = NULL;
686 dlist *rowlist = tableref->data.lval;
687 symbol *optname = rowlist->t->data.sym;
688 dnode *o;
689 node *m;
690 list *exps = sa_list(sql->sa);
691 exp_kind ek = {type_value, card_value, TRUE};
692
693 for (o = rowlist->h; o; o = o->next) {
694 dlist *values = o->data.lval;
695
696 /* When performing sub-queries, the relation name appears under a SQL_NAME symbol at the end of the list */
697 if (o->type == type_symbol && o->data.sym->token == SQL_NAME)
698 break;
699
700 if (!list_empty(exps) && list_length(exps) != dlist_length(values)) {
701 return sql_error(sql, 02, SQLSTATE(42000) "VALUES: number of columns doesn't match between rows");
702 } else {
703 dnode *n;
704
705 if (list_empty(exps)) {
706 for (n = values->h; n; n = n->next) {
707 sql_exp *vals = exp_values(sql->sa, sa_list(sql->sa));
708
709 exp_label(sql->sa, vals, ++sql->label);
710 list_append(exps, vals);
711 }
712 }
713 for (n = values->h, m = exps->h; n && m; n = n->next, m = m->next) {
714 sql_exp *vals = m->data;
715 list *vals_list = vals->f;
716 sql_exp *e = rel_value_exp(query, NULL, n->data.sym, sql_sel, ek);
717 if (!e)
718 return NULL;
719 list_append(vals_list, e);
720 }
721 }
722 }
723 /* loop to check types */
724 for (m = exps->h; m; m = m->next) {
725 node *n;
726 sql_exp *vals = m->data;
727 list *vals_list = vals->f;
728 list *nexps = sa_list(sql->sa);
729 sql_subtype *tpe = exp_subtype(vals_list->h->data);
730
731 if (tpe)
732 vals->tpe = *tpe;
733
734 /* first get super type */
735 for (n = vals_list->h; n; n = n->next) {
736 sql_exp *e = n->data;
737 sql_subtype super, *ttpe;
738
739 /* if the expression is a parameter set its type */
740 if (tpe && e->type == e_atom && !e->l && !e->r && !e->f && !e->tpe.type) {
741 if (set_type_param(sql, tpe, e->flag) == 0)
742 e->tpe = *tpe;
743 else
744 return NULL;
745 }
746 ttpe = exp_subtype(e);
747 if (tpe && ttpe) {
748 supertype(&super, tpe, ttpe);
749 vals->tpe = super;
750 tpe = &vals->tpe;
751 } else {
752 tpe = ttpe;
753 }
754 }
755 if (!tpe)
756 continue;
757 /* if the expression is a parameter set its type */
758 for (n = vals_list->h; n; n = n->next) {
759 sql_exp *e = n->data;
760 if (e->type == e_atom && !e->l && !e->r && !e->f && !e->tpe.type) {
761 if (set_type_param(sql, tpe, e->flag) == 0)
762 e->tpe = *tpe;
763 else
764 return NULL;
765 }
766 }
767 vals->tpe = *tpe;
768 for (n = vals_list->h; n; n = n->next) {
769 sql_exp *e = n->data;
770 e = rel_check_type(sql, &vals->tpe, NULL, e, type_equal);
771 if (!e)
772 return NULL;
773 append(nexps, e);
774 }
775 vals->f = nexps;
776 }
777 r = rel_project(sql->sa, NULL, exps);
778 r->nrcols = list_length(exps);
779 return rel_table_optname(sql, r, optname);
780}
781
782static int
783check_is_lateral(symbol *tableref)
784{
785 if (tableref->token == SQL_NAME || tableref->token == SQL_TABLE) {
786 if (dlist_length(tableref->data.lval) == 3)
787 return tableref->data.lval->h->next->next->data.i_val;
788 return 0;
789 } else if (tableref->token == SQL_VALUES) {
790 return 0;
791 } else if (tableref->token == SQL_SELECT) {
792 SelectNode *sn = (SelectNode *) tableref;
793 return sn->lateral;
794 } else {
795 return 0;
796 }
797}
798
799sql_rel *
800table_ref(sql_query *query, sql_rel *rel, symbol *tableref, int lateral)
801{
802 mvc *sql = query->sql;
803 char *tname = NULL;
804 sql_table *t = NULL;
805 sql_rel *res = NULL;
806
807 if (tableref->token == SQL_NAME) {
808 dlist *name = tableref->data.lval->h->data.lval;
809 sql_rel *temp_table = NULL;
810 char *sname = qname_schema(name);
811 sql_schema *s = NULL;
812
813 tname = qname_table(name);
814
815 if (dlist_length(name) > 2)
816 return sql_error(sql, 02, SQLSTATE(3F000) "SELECT: only a schema and table name expected");
817
818 if (sname && !(s=mvc_bind_schema(sql,sname)))
819 return sql_error(sql, 02, SQLSTATE(3F000) "SELECT: no such schema '%s'", sname);
820 if (!t && !sname) {
821 t = stack_find_table(sql, tname);
822 if (!t)
823 temp_table = stack_find_rel_view(sql, tname);
824 }
825 if (!t && !temp_table) {
826 if (!s)
827 s = cur_schema(sql);
828 t = mvc_bind_table(sql, s, tname);
829 if (!t && !sname) {
830 s = tmp_schema(sql);
831 t = mvc_bind_table(sql, s, tname);
832 }
833 }
834 if (!t && !temp_table) {
835 return sql_error(sql, 02, SQLSTATE(42S02) "SELECT: no such table '%s'", tname);
836 } else if (!temp_table && !table_privs(sql, t, PRIV_SELECT)) {
837 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: access denied for %s to table '%s.%s'", stack_get_string(sql, "current_user"), s->base.name, tname);
838 }
839 if (tableref->data.lval->h->next->data.sym) { /* AS */
840 tname = tableref->data.lval->h->next->data.sym->data.lval->h->data.sval;
841 }
842 if (temp_table && !t) {
843 node *n;
844 int needed = !is_simple_project(temp_table->op);
845
846 for (n = temp_table->exps->h; n && !needed; n = n->next) {
847 sql_exp *e = n->data;
848
849 if (!exp_relname(e) || strcmp(exp_relname(e), tname) != 0)
850 needed = 1;
851 }
852
853 if (needed) {
854 list *exps = rel_projections(sql, temp_table, NULL, 1, 1);
855
856 temp_table = rel_project(sql->sa, temp_table, exps);
857 for (n = exps->h; n; n = n->next) {
858 sql_exp *e = n->data;
859
860 noninternexp_setname(sql->sa, e, tname, NULL);
861 set_basecol(e);
862 }
863 }
864 return temp_table;
865 } else if (isView(t)) {
866 /* instantiate base view */
867 node *n,*m;
868 sql_rel *rel;
869
870 if (sql->emode == m_deps)
871 rel = rel_basetable(sql, t, tname);
872 else
873 rel = rel_parse(sql, t->s, t->query, m_instantiate);
874
875 if (!rel)
876 return NULL;
877
878 /* Rename columns of the rel_parse relation */
879 if (sql->emode != m_deps) {
880 assert(is_project(rel->op));
881 if (!rel)
882 return NULL;
883 set_processed(rel);
884 for (n = t->columns.set->h, m = rel->exps->h; n && m; n = n->next, m = m->next) {
885 sql_column *c = n->data;
886 sql_exp *e = m->data;
887
888 exp_setname(sql->sa, e, tname, c->base.name);
889 set_basecol(e);
890 }
891 }
892 return rel;
893 }
894 if ((isMergeTable(t) || isReplicaTable(t)) && list_empty(t->members.set))
895 return sql_error(sql, 02, SQLSTATE(42000) "MERGE or REPLICA TABLE should have at least one table associated");
896
897 res = rel_basetable(sql, t, tname);
898 if (tableref->data.lval->h->next->data.sym && tableref->data.lval->h->next->data.sym->data.lval->h->next->data.lval) /* AS with column aliases */
899 res = rel_table_optname(sql, res, tableref->data.lval->h->next->data.sym);
900 return res;
901 } else if (tableref->token == SQL_VALUES) {
902 return rel_values(query, tableref);
903 } else if (tableref->token == SQL_TABLE) {
904 return rel_named_table_function(query, rel, tableref, lateral);
905 } else if (tableref->token == SQL_SELECT) {
906 return rel_subquery_optname(query, rel, tableref);
907 } else {
908 return query_exp_optname(query, rel, tableref);
909 }
910}
911
912static sql_exp *
913rel_var_ref(mvc *sql, char *name, int at)
914{
915 if (stack_find_var(sql, name)) {
916 sql_subtype *tpe = stack_find_type(sql, name);
917 int frame = stack_find_frame(sql, name);
918
919 return exp_param(sql->sa, name, tpe, frame);
920 } else if (at) {
921 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: '@""%s' unknown", name);
922 } else {
923 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: identifier '%s' unknown", name);
924 }
925}
926
927static sql_exp *
928exps_get_exp(list *exps, int nth)
929{
930 node *n = NULL;
931 int i = 0;
932
933 if (exps)
934 for (n=exps->h, i=1; n && i<nth; n=n->next, i++)
935 ;
936 if (n && i == nth)
937 return n->data;
938 return NULL;
939}
940
941static sql_rel *
942rel_find_groupby(sql_rel *groupby)
943{
944 if (groupby && !is_processed(groupby) && !is_base(groupby->op)) {
945 while(!is_processed(groupby) && !is_base(groupby->op)) {
946 if (groupby->op == op_groupby || !groupby->l)
947 break;
948 if (groupby->l)
949 groupby = groupby->l;
950 }
951 if (groupby && groupby->op == op_groupby)
952 return groupby;
953 }
954 return NULL;
955}
956
957static int
958is_groupby_col(sql_rel *gb, sql_exp *e)
959{
960 gb = rel_find_groupby(gb);
961
962 if (gb) {
963 if (exp_relname(e)) {
964 if (exp_name(e) && exps_bind_column2(gb->r, exp_relname(e), exp_name(e)))
965 return 1;
966 } else {
967 if (exp_name(e) && exps_bind_column(gb->r, exp_name(e), NULL))
968 return 1;
969 }
970 }
971 return 0;
972}
973
974static sql_exp *
975rel_column_ref(sql_query *query, sql_rel **rel, symbol *column_r, int f)
976{
977 mvc *sql = query->sql;
978 sql_exp *exp = NULL;
979 dlist *l = NULL;
980 sql_rel *inner = rel?*rel:NULL;
981
982 assert((column_r->token == SQL_COLUMN || column_r->token == SQL_IDENT) && column_r->type == type_list);
983 l = column_r->data.lval;
984
985 if (dlist_length(l) == 1 && l->h->type == type_int) {
986 int nr = l->h->data.i_val;
987 atom *a;
988 if ((a = sql_bind_arg(sql, nr)) != NULL) {
989 if (EC_TEMP_FRAC(atom_type(a)->type->eclass)) {
990 /* fix fraction */
991 sql_subtype *st = atom_type(a), t;
992 int digits = st->digits;
993 sql_exp *e;
994
995 sql_find_subtype(&t, st->type->sqlname, digits, 0);
996
997 st->digits = 3;
998 e = exp_atom_ref(sql->sa, nr, st);
999
1000 return exp_convert(sql->sa, e, st, &t);
1001 } else {
1002 return exp_atom_ref(sql->sa, nr, atom_type(a));
1003 }
1004 }
1005 return NULL;
1006 } else if (dlist_length(l) == 1) {
1007 char *name = l->h->data.sval;
1008 sql_arg *a = sql_bind_param(sql, name);
1009 int var = stack_find_var(sql, name);
1010
1011 if (!exp && inner)
1012 exp = rel_bind_column(sql, inner, name, f);
1013 if (!exp && inner && is_sql_having(f) && inner->op == op_select)
1014 inner = inner->l;
1015 if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) && is_groupby(inner->op)) {
1016 exp = rel_bind_column(sql, inner->l, name, f);
1017 }
1018 if (!exp && query && query_has_outer(query)) {
1019 int i;
1020 sql_rel *outer;
1021
1022 for (i=query_has_outer(query)-1; i>= 0 && !exp && (outer = query_fetch_outer(query,i)); i--) {
1023 exp = rel_bind_column(sql, outer, name, f);
1024 if (!exp && (is_sql_having(f) || is_sql_aggr(f)) && is_groupby(outer->op)) {
1025 exp = rel_bind_column(sql, outer->l, name, f);
1026 }
1027 if (exp && is_simple_project(outer->op) && !rel_find_exp(outer, exp)) {
1028 exp = rel_project_add_exp(sql, outer, exp);
1029 }
1030 if (exp)
1031 break;
1032 }
1033 if (exp && outer && outer->card <= CARD_AGGR && exp->card > CARD_AGGR && !is_sql_aggr(f))
1034 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", name);
1035 if (exp) {
1036 if (is_groupby(outer->op) && !is_sql_aggr(f)) {
1037 exp = rel_groupby_add_aggr(sql, outer, exp);
1038 exp->card = CARD_ATOM;
1039 } else if (is_groupby(outer->op) && is_sql_aggr(f) && exps_find_match_exp(outer->exps, exp))
1040 exp = exp_ref(sql->sa, exp);
1041 else
1042 exp->card = CARD_ATOM;
1043 set_freevar(exp, i);
1044 }
1045 }
1046 if (exp) {
1047 if (var || a)
1048 return sql_error(sql, ERR_AMBIGUOUS, SQLSTATE(42000) "SELECT: identifier '%s' ambiguous", name);
1049 } else if (a) {
1050 if (var)
1051 return sql_error(sql, ERR_AMBIGUOUS, SQLSTATE(42000) "SELECT: identifier '%s' ambiguous", name);
1052 exp = exp_param(sql->sa, a->name, &a->type, 0);
1053 }
1054 if (!exp && var) {
1055 sql_rel *r = stack_find_rel_var(sql, name);
1056 if (r) {
1057 *rel = r;
1058 return exp_rel(sql, r);
1059 }
1060 return rel_var_ref(sql, name, 0);
1061 }
1062 if (!exp && !var)
1063 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: identifier '%s' unknown", name);
1064 if (exp && inner && inner->card <= CARD_AGGR && exp->card > CARD_AGGR && (is_sql_sel(f) || is_sql_having(f)) && !is_sql_aggr(f))
1065 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", name);
1066 if (exp && inner && is_groupby(inner->op) && !is_sql_aggr(f) && !is_freevar(exp))
1067 exp = rel_groupby_add_aggr(sql, inner, exp);
1068 } else if (dlist_length(l) == 2) {
1069 char *tname = l->h->data.sval;
1070 char *cname = l->h->next->data.sval;
1071
1072 if (!exp && rel && inner)
1073 exp = rel_bind_column2(sql, inner, tname, cname, f);
1074 if (!exp && inner && is_sql_having(f) && inner->op == op_select)
1075 inner = inner->l;
1076 if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) && is_groupby(inner->op)) {
1077 exp = rel_bind_column2(sql, inner->l, tname, cname, f);
1078 }
1079 if (!exp && query && query_has_outer(query)) {
1080 int i;
1081 sql_rel *outer;
1082
1083 for (i=query_has_outer(query)-1; i>= 0 && !exp && (outer = query_fetch_outer(query,i)); i--) {
1084 exp = rel_bind_column2(sql, outer, tname, cname, f | sql_outer);
1085 if (!exp && (is_sql_having(f) || is_sql_aggr(f)) && is_groupby(outer->op)) {
1086 exp = rel_bind_column2(sql, outer->l, tname, cname, f);
1087 }
1088 if (exp && is_simple_project(outer->op) && !rel_find_exp(outer, exp)) {
1089 exp = rel_project_add_exp(sql, outer, exp);
1090 }
1091 if (exp)
1092 break;
1093 }
1094 if (exp && outer && outer->card <= CARD_AGGR && exp->card > CARD_AGGR && !is_sql_aggr(f))
1095 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query results without an aggregate function", tname, cname);
1096 if (exp) {
1097 if (is_groupby(outer->op) && !is_sql_aggr(f)) {
1098 exp = rel_groupby_add_aggr(sql, outer, exp);
1099 exp->card = CARD_ATOM;
1100 } else if (is_groupby(outer->op) && is_sql_aggr(f) && exps_find_match_exp(outer->exps, exp))
1101 exp = exp_ref(sql->sa, exp);
1102 else
1103 exp->card = CARD_ATOM;
1104 set_freevar(exp, i);
1105 }
1106 }
1107
1108 /* some views are just in the stack,
1109 like before and after updates views */
1110 if (rel && !exp && sql->use_views) {
1111 sql_rel *v = stack_find_rel_view(sql, tname);
1112
1113 if (v) {
1114 if (*rel)
1115 *rel = rel_crossproduct(sql->sa, *rel, v, op_join);
1116 else
1117 *rel = v;
1118 exp = rel_bind_column2(sql, *rel, tname, cname, f);
1119 }
1120 }
1121 if (!exp)
1122 return sql_error(sql, 02, SQLSTATE(42S22) "SELECT: no such column '%s.%s'", tname, cname);
1123 if (exp && inner && inner->card <= CARD_AGGR && exp->card > CARD_AGGR && (is_sql_sel(f) || is_sql_having(f)) && !is_sql_aggr(f))
1124 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query results without an aggregate function", tname, cname);
1125 if (exp && inner && is_groupby(inner->op) && !is_sql_aggr(f) && !is_freevar(exp))
1126 exp = rel_groupby_add_aggr(sql, inner, exp);
1127 } else if (dlist_length(l) >= 3) {
1128 return sql_error(sql, 02, SQLSTATE(42000) "TODO: column names of level >= 3");
1129 }
1130 return exp;
1131}
1132
1133#ifdef HAVE_HGE
1134static hge
1135#else
1136static lng
1137#endif
1138scale2value(int scale)
1139{
1140#ifdef HAVE_HGE
1141 hge val = 1;
1142#else
1143 lng val = 1;
1144#endif
1145
1146 if (scale < 0)
1147 scale = -scale;
1148 for (; scale; scale--) {
1149 val = val * 10;
1150 }
1151 return val;
1152}
1153
1154static sql_exp *
1155exp_fix_scale(mvc *sql, sql_subtype *ct, sql_exp *e, int both, int always)
1156{
1157 sql_subtype *et = exp_subtype(e);
1158
1159 if (ct->type->scale == SCALE_FIX && et->type->scale == SCALE_FIX) {
1160 int scale_diff = ((int) ct->scale - (int) et->scale);
1161
1162 if (scale_diff) {
1163 sql_subtype *it = sql_bind_localtype(et->type->base.name);
1164 sql_subfunc *c = NULL;
1165
1166 if (scale_diff < 0) {
1167 if (!both)
1168 return e;
1169 c = sql_bind_func(sql->sa, sql->session->schema, "scale_down", et, it, F_FUNC);
1170 } else {
1171 c = sql_bind_func(sql->sa, sql->session->schema, "scale_up", et, it, F_FUNC);
1172 }
1173 if (c) {
1174#ifdef HAVE_HGE
1175 hge val = scale2value(scale_diff);
1176#else
1177 lng val = scale2value(scale_diff);
1178#endif
1179 atom *a = atom_int(sql->sa, it, val);
1180 sql_subtype *res = c->res->h->data;
1181
1182 res->scale = (et->scale + scale_diff);
1183 return exp_binop(sql->sa, e, exp_atom(sql->sa, a), c);
1184 }
1185 }
1186 } else if (always && et->scale) { /* scale down */
1187 int scale_diff = -(int) et->scale;
1188 sql_subtype *it = sql_bind_localtype(et->type->base.name);
1189 sql_subfunc *c = sql_bind_func(sql->sa, sql->session->schema, "scale_down", et, it, F_FUNC);
1190
1191 if (c) {
1192#ifdef HAVE_HGE
1193 hge val = scale2value(scale_diff);
1194#else
1195 lng val = scale2value(scale_diff);
1196#endif
1197 atom *a = atom_int(sql->sa, it, val);
1198 sql_subtype *res = c->res->h->data;
1199
1200 res->scale = 0;
1201 return exp_binop(sql->sa, e, exp_atom(sql->sa, a), c);
1202 } else {
1203 printf("scale_down missing (%s)\n", et->type->base.name);
1204 }
1205 }
1206 return e;
1207}
1208
1209static int
1210rel_set_type_param(mvc *sql, sql_subtype *type, sql_rel *rel, sql_exp *rel_exp, int upcast)
1211{
1212 if (!type || !rel_exp || (rel_exp->type != e_atom && rel_exp->type != e_column))
1213 return -1;
1214
1215 /* use largest numeric types */
1216 if (upcast && type->type->eclass == EC_NUM)
1217#ifdef HAVE_HGE
1218 type = sql_bind_localtype(have_hge ? "hge" : "lng");
1219#else
1220 type = sql_bind_localtype("lng");
1221#endif
1222 if (upcast && type->type->eclass == EC_FLT)
1223 type = sql_bind_localtype("dbl");
1224
1225 if ((rel_exp->type == e_atom || rel_exp->type == e_column) && (rel_exp->l || rel_exp->r || rel_exp->f)) {
1226 /* it's not a parameter set possible parameters bellow */
1227 const char *relname = exp_relname(rel_exp), *expname = exp_name(rel_exp);
1228 return rel_set_type_recurse(sql, type, rel, &relname, &expname);
1229 } else if (set_type_param(sql, type, rel_exp->flag) == 0) {
1230 rel_exp->tpe = *type;
1231 return 0;
1232 }
1233 return -1;
1234}
1235
1236/* try to do an in-place conversion
1237 *
1238 * in-place conversion is only possible if the exp is a variable.
1239 * This is only done to be able to map more cached queries onto the same
1240 * interface.
1241 */
1242
1243static void
1244convert_atom(atom *a, sql_subtype *rt)
1245{
1246 if (atom_null(a)) {
1247 if (a->data.vtype != rt->type->localtype) {
1248 const void *p;
1249
1250 a->data.vtype = rt->type->localtype;
1251 p = ATOMnilptr(a->data.vtype);
1252 VALset(&a->data, a->data.vtype, (ptr) p);
1253 }
1254 }
1255 a->tpe = *rt;
1256}
1257
1258static sql_exp *
1259exp_convert_inplace(mvc *sql, sql_subtype *t, sql_exp *exp)
1260{
1261 atom *a;
1262
1263 /* exclude named variables and variable lists */
1264 if (exp->type != e_atom || exp->r /* named */ || exp->f /* list */ || !exp->l /* not direct atom */)
1265 return NULL;
1266
1267 a = exp->l;
1268 if (t->scale && t->type->eclass != EC_FLT)
1269 return NULL;
1270
1271 if (a && atom_cast(sql->sa, a, t)) {
1272 convert_atom(a, t);
1273 exp->tpe = *t;
1274 return exp;
1275 }
1276 return NULL;
1277}
1278
1279static sql_exp *
1280rel_numeric_supertype(mvc *sql, sql_exp *e )
1281{
1282 sql_subtype *tp = exp_subtype(e);
1283
1284 if (tp->type->eclass == EC_DEC) {
1285 sql_subtype *dtp = sql_bind_localtype("dbl");
1286
1287 return rel_check_type(sql, dtp, NULL, e, type_cast);
1288 }
1289 if (tp->type->eclass == EC_NUM) {
1290#ifdef HAVE_HGE
1291 sql_subtype *ltp = sql_bind_localtype(have_hge ? "hge" : "lng");
1292#else
1293 sql_subtype *ltp = sql_bind_localtype("lng");
1294#endif
1295
1296 return rel_check_type(sql, ltp, NULL, e, type_cast);
1297 }
1298 return e;
1299}
1300
1301sql_exp *
1302rel_check_type(mvc *sql, sql_subtype *t, sql_rel *rel, sql_exp *exp, check_type tpe)
1303{
1304 int c, err = 0;
1305 sql_exp* nexp = NULL;
1306 sql_subtype *fromtype = exp_subtype(exp);
1307
1308 if ((!fromtype || !fromtype->type) && rel_set_type_param(sql, t, rel, exp, 0) == 0)
1309 return exp;
1310
1311 /* first try cheap internal (in-place) conversions ! */
1312 if ((nexp = exp_convert_inplace(sql, t, exp)) != NULL)
1313 return nexp;
1314
1315 if (fromtype && subtype_cmp(t, fromtype) != 0) {
1316 if (EC_INTERVAL(fromtype->type->eclass) && (t->type->eclass == EC_NUM || t->type->eclass == EC_POS) && t->digits < fromtype->digits) {
1317 err = 1; /* conversion from interval to num depends on the number of digits */
1318 } else {
1319 c = sql_type_convert(fromtype->type->eclass, t->type->eclass);
1320 if (!c || (c == 2 && tpe == type_set) || (c == 3 && tpe != type_cast)) {
1321 err = 1;
1322 } else {
1323 exp = exp_convert(sql->sa, exp, fromtype, t);
1324 }
1325 }
1326 }
1327 if (err) {
1328 sql_exp *res = sql_error( sql, 03, SQLSTATE(42000) "types %s(%u,%u) and %s(%u,%u) are not equal%s%s%s",
1329 fromtype->type->sqlname,
1330 fromtype->digits,
1331 fromtype->scale,
1332 t->type->sqlname,
1333 t->digits,
1334 t->scale,
1335 (exp->type == e_column ? " for column '" : ""),
1336 (exp->type == e_column ? exp_name(exp) : ""),
1337 (exp->type == e_column ? "'" : "")
1338 );
1339 return res;
1340 }
1341 return exp;
1342}
1343
1344static sql_exp *
1345exp_scale_algebra(mvc *sql, sql_subfunc *f, sql_rel *rel, sql_exp *l, sql_exp *r)
1346{
1347 sql_subtype *lt = exp_subtype(l);
1348 sql_subtype *rt = exp_subtype(r);
1349
1350 if (lt->type->scale == SCALE_FIX && rt->scale &&
1351 strcmp(f->func->imp, "/") == 0) {
1352 sql_subtype *res = f->res->h->data;
1353 int scale, digits, digL, scaleL;
1354 sql_subtype nlt;
1355
1356 /* scale fixing may require a larger type ! */
1357 scaleL = (lt->scale < 3) ? 3 : lt->scale;
1358 scale = scaleL;
1359 scaleL += rt->scale;
1360 digL = lt->digits + (scaleL - lt->scale);
1361 digits = (digL > (int)rt->digits) ? digL : (int)rt->digits;
1362
1363 /* HACK alert: digits should be less than max */
1364#ifdef HAVE_HGE
1365 if (have_hge) {
1366 if (res->type->radix == 10 && digits > 39)
1367 digits = 39;
1368 if (res->type->radix == 2 && digits > 128)
1369 digits = 128;
1370 } else
1371#endif
1372 {
1373 if (res->type->radix == 10 && digits > 19)
1374 digits = 19;
1375 if (res->type->radix == 2 && digits > 64)
1376 digits = 64;
1377 }
1378
1379 sql_find_subtype(&nlt, lt->type->sqlname, digL, scaleL);
1380 l = rel_check_type( sql, &nlt, rel, l, type_equal);
1381
1382 sql_find_subtype(res, lt->type->sqlname, digits, scale);
1383 }
1384 return l;
1385}
1386
1387int
1388rel_convert_types(mvc *sql, sql_rel *ll, sql_rel *rr, sql_exp **L, sql_exp **R, int scale_fixing, check_type tpe)
1389{
1390 sql_exp *ls = *L;
1391 sql_exp *rs = *R;
1392 sql_subtype *lt = exp_subtype(ls);
1393 sql_subtype *rt = exp_subtype(rs);
1394
1395 if (!rt && !lt) {
1396 sql_error(sql, 01, SQLSTATE(42000) "Cannot have a parameter (?) on both sides of an expression");
1397 return -1;
1398 }
1399 if (rt && (!lt || !lt->type))
1400 return rel_set_type_param(sql, rt, ll, ls, 0);
1401 if (lt && (!rt || !rt->type))
1402 return rel_set_type_param(sql, lt, rr, rs, 0);
1403
1404 if (rt && lt) {
1405 sql_subtype *i = lt;
1406 sql_subtype *r = rt;
1407
1408 if (subtype_cmp(lt, rt) != 0 || (tpe == type_equal_no_any && (lt->type->localtype==0 || rt->type->localtype==0))) {
1409 sql_subtype super;
1410
1411 supertype(&super, r, i);
1412 if (scale_fixing) {
1413 /* convert ls to super type */
1414 ls = rel_check_type(sql, &super, ll, ls, tpe);
1415 /* convert rs to super type */
1416 rs = rel_check_type(sql, &super, rr, rs, tpe);
1417 } else {
1418 /* convert ls to super type */
1419 super.scale = lt->scale;
1420 ls = rel_check_type(sql, &super, ll, ls, tpe);
1421 /* convert rs to super type */
1422 super.scale = rt->scale;
1423 rs = rel_check_type(sql, &super, rr, rs, tpe);
1424 }
1425 }
1426 *L = ls;
1427 *R = rs;
1428 if (!ls || !rs) {
1429 return -1;
1430 }
1431 return 0;
1432 }
1433 return -1;
1434}
1435
1436static sql_rel *
1437rel_filter(mvc *sql, sql_rel *rel, list *l, list *r, char *sname, char *filter_op, int anti )
1438{
1439 node *n;
1440 sql_exp *L = l->h->data, *R = r->h->data, *e = NULL;
1441 sql_subfunc *f = NULL;
1442 sql_schema *s = sql->session->schema;
1443 list *tl, *exps;
1444
1445 exps = sa_list(sql->sa);
1446 tl = sa_list(sql->sa);
1447 for (n = l->h; n; n = n->next){
1448 sql_exp *e = n->data;
1449
1450 list_append(exps, e);
1451 list_append(tl, exp_subtype(e));
1452 }
1453 for (n = r->h; n; n = n->next){
1454 sql_exp *e = n->data;
1455
1456 list_append(exps, e);
1457 list_append(tl, exp_subtype(e));
1458 }
1459 if (sname)
1460 s = mvc_bind_schema(sql, sname);
1461 /* find filter function */
1462 f = sql_bind_func_(sql->sa, s, filter_op, tl, F_FILT);
1463
1464 if (!f)
1465 f = find_func(sql, s, filter_op, list_length(exps), F_FILT, NULL);
1466 if (f) {
1467 node *n,*m = f->func->ops->h;
1468 list *nexps = sa_list(sql->sa);
1469
1470 for(n=l->h; m && n; m = m->next, n = n->next) {
1471 sql_arg *a = m->data;
1472 sql_exp *e = n->data;
1473
1474 e = rel_check_type(sql, &a->type, rel, e, type_equal);
1475 if (!e)
1476 return NULL;
1477 list_append(nexps, e);
1478 }
1479 l = nexps;
1480 nexps = sa_list(sql->sa);
1481 for(n=r->h; m && n; m = m->next, n = n->next) {
1482 sql_arg *a = m->data;
1483 sql_exp *e = n->data;
1484
1485 e = rel_check_type(sql, &a->type, rel, e, type_equal);
1486 if (!e)
1487 return NULL;
1488 list_append(nexps, e);
1489 }
1490 r = nexps;
1491 }
1492 if (!f) {
1493 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such FILTER function '%s'", filter_op);
1494 return NULL;
1495 }
1496 e = exp_filter(sql->sa, l, r, f, anti);
1497
1498 /* atom or row => select */
1499 if (exps_card(l) > rel->card) {
1500 sql_exp *ls = l->h->data;
1501 if (exp_name(ls))
1502 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(ls));
1503 else
1504 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
1505 }
1506 if (exps_card(r) > rel->card) {
1507 sql_exp *rs = l->h->data;
1508 if (exp_name(rs))
1509 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(rs));
1510 else
1511 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
1512 }
1513 if (exps_card(r) <= CARD_ATOM && exps_are_atoms(r)) {
1514 if (exps_card(l) == exps_card(r) || rel->processed) /* bin compare op */
1515 return rel_select(sql->sa, rel, e);
1516
1517 if (/*is_semi(rel->op) ||*/ is_outerjoin(rel->op)) {
1518 if ((is_left(rel->op) || is_full(rel->op)) && rel_find_exp(rel->l, l->h->data)) {
1519 rel_join_add_exp(sql->sa, rel, e);
1520 return rel;
1521 } else if ((is_right(rel->op) || is_full(rel->op)) && rel_find_exp(rel->r, l->h->data)) {
1522 rel_join_add_exp(sql->sa, rel, e);
1523 return rel;
1524 }
1525 if (is_left(rel->op) && rel_find_exp(rel->r, l->h->data)) {
1526 rel->r = rel_push_select(sql, rel->r, L, e);
1527 return rel;
1528 } else if (is_right(rel->op) && rel_find_exp(rel->l, l->h->data)) {
1529 rel->l = rel_push_select(sql, rel->l, L, e);
1530 return rel;
1531 }
1532 }
1533 /* push select into the given relation */
1534 return rel_push_select(sql, rel, L, e);
1535 } else { /* join */
1536 sql_rel *r;
1537 if (/*is_semi(rel->op) ||*/ (is_outerjoin(rel->op) && !is_processed((rel)))) {
1538 rel_join_add_exp(sql->sa, rel, e);
1539 return rel;
1540 }
1541 /* push join into the given relation */
1542 if ((r = rel_push_join(sql, rel, L, R, NULL, e)) != NULL)
1543 return r;
1544 rel_join_add_exp(sql->sa, rel, e);
1545 return rel;
1546 }
1547}
1548
1549static sql_rel *
1550rel_filter_exp_(mvc *sql, sql_rel *rel, sql_exp *ls, sql_exp *rs, sql_exp *rs2, char *filter_op, int anti )
1551{
1552 list *l = sa_list(sql->sa);
1553 list *r = sa_list(sql->sa);
1554
1555 list_append(l, ls);
1556 list_append(r, rs);
1557 if (rs2)
1558 list_append(r, rs2);
1559 return rel_filter(sql, rel, l, r, "sys", filter_op, anti);
1560}
1561
1562static sql_rel *
1563rel_compare_exp_(sql_query *query, sql_rel *rel, sql_exp *ls, sql_exp *rs, sql_exp *rs2, int type, int anti, int quantifier)
1564{
1565 mvc *sql = query->sql;
1566 sql_exp *L = ls, *R = rs, *e = NULL;
1567
1568 if (quantifier || exp_is_rel(ls) || exp_is_rel(rs)) {
1569 if (rs2) {
1570 e = exp_compare2(sql->sa, ls, rs, rs2, type);
1571 if (anti)
1572 set_anti(e);
1573 } else {
1574 e = exp_compare_func(sql, ls, rs, rs2, compare_func((comp_type)type, quantifier?0:anti), quantifier);
1575 if (anti && quantifier)
1576 e = rel_unop_(sql, NULL, e, NULL, "not", card_value);
1577 }
1578 return rel_select(sql->sa, rel, e);
1579 } else if (!rs2) {
1580 if (ls->card < rs->card) {
1581 sql_exp *swap = ls;
1582
1583 ls = rs;
1584 rs = swap;
1585
1586 swap = L;
1587 L = R;
1588 R = swap;
1589
1590 type = (int)swap_compare((comp_type)type);
1591 }
1592 if (!exp_subtype(ls) && !exp_subtype(rs))
1593 return sql_error(sql, 01, SQLSTATE(42000) "Cannot have a parameter (?) on both sides of an expression");
1594 if (rel_convert_types(sql, rel, rel, &ls, &rs, 1, type_equal_no_any) < 0)
1595 return NULL;
1596 e = exp_compare(sql->sa, ls, rs, type);
1597 } else {
1598 if ((rs = rel_check_type(sql, exp_subtype(ls), rel, rs, type_equal)) == NULL ||
1599 (rs2 && (rs2 = rel_check_type(sql, exp_subtype(ls), rel, rs2, type_equal)) == NULL))
1600 return NULL;
1601 e = exp_compare2(sql->sa, ls, rs, rs2, type);
1602 }
1603 if (anti)
1604 set_anti(e);
1605
1606 if (!rel)
1607 return rel_select(sql->sa, rel_project_exp(sql->sa, exp_atom_bool(sql->sa, 1)), e);
1608
1609 /* atom or row => select */
1610 if (ls->card > rel->card) {
1611 if (exp_name(ls))
1612 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(ls));
1613 else
1614 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
1615 }
1616 if (rs->card > rel->card || (rs2 && rs2->card > rel->card)) {
1617 if (exp_name(rs))
1618 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(rs));
1619 else
1620 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
1621 }
1622 if (rs->card <= CARD_ATOM && (exp_is_atom(rs) || exp_has_freevar(sql, rs)) &&
1623 (!rs2 || (rs2->card <= CARD_ATOM && (exp_is_atom(rs2) || exp_has_freevar(sql, rs2))))) {
1624 if ((ls->card == rs->card && !rs2) || rel->processed) /* bin compare op */
1625 return rel_select(sql->sa, rel, e);
1626
1627 if (/*is_semi(rel->op) ||*/ is_outerjoin(rel->op)) {
1628 if ((is_left(rel->op) || is_full(rel->op)) && rel_find_exp(rel->l, ls)) {
1629 rel_join_add_exp(sql->sa, rel, e);
1630 return rel;
1631 } else if ((is_right(rel->op) || is_full(rel->op)) && rel_find_exp(rel->r, ls)) {
1632 rel_join_add_exp(sql->sa, rel, e);
1633 return rel;
1634 }
1635 if (is_semi(rel->op)) {
1636 rel_join_add_exp(sql->sa, rel, e);
1637 return rel;
1638 }
1639 if (is_left(rel->op) && rel_find_exp(rel->r, ls)) {
1640 rel->r = rel_push_select(sql, rel->r, L, e);
1641 return rel;
1642 } else if (is_right(rel->op) && rel_find_exp(rel->l, ls)) {
1643 rel->l = rel_push_select(sql, rel->l, L, e);
1644 return rel;
1645 }
1646 }
1647 /* push select into the given relation */
1648 return rel_push_select(sql, rel, L, e);
1649 } else { /* join */
1650 sql_rel *r;
1651 if (/*is_semi(rel->op) ||*/ (is_outerjoin(rel->op) && !is_processed((rel)))) {
1652 rel_join_add_exp(sql->sa, rel, e);
1653 return rel;
1654 }
1655 /* push join into the given relation */
1656 if ((r = rel_push_join(sql, rel, L, R, rs2, e)) != NULL)
1657 return r;
1658 rel_join_add_exp(sql->sa, rel, e);
1659 return rel;
1660 }
1661}
1662
1663static sql_rel *
1664rel_compare_exp(sql_query *query, sql_rel *rel, sql_exp *ls, sql_exp *rs, char *compare_op, sql_exp *esc, int reduce,
1665 int quantifier, int need_not)
1666{
1667 mvc *sql = query->sql;
1668 comp_type type = cmp_equal;
1669
1670 if (!ls || !rs)
1671 return NULL;
1672
1673 if (!quantifier && ((!rel && !query_has_outer(query)) || !reduce)) {
1674 /* TODO to handle filters here */
1675 sql_exp *e;
1676
1677 if (rel_convert_types(sql, rel, rel, &ls, &rs, 1, type_equal) < 0)
1678 return NULL;
1679 e = rel_binop_(sql, rel, ls, rs, NULL, compare_op, card_value);
1680
1681 if (!e)
1682 return NULL;
1683 if (!reduce) {
1684 if (rel->op == op_project) {
1685 append(rel->exps, e);
1686 } else {
1687 list *exps = new_exp_list(sql->sa);
1688
1689 append(exps, e);
1690 return rel_project(sql->sa, rel, exps);
1691 }
1692 } else {
1693 return rel_select(sql->sa, rel, e);
1694 }
1695 }
1696 type = compare_str2type(compare_op);
1697 if (type == cmp_filter)
1698 return rel_filter_exp_(sql, rel, ls, rs, esc, compare_op, 0);
1699 return rel_compare_exp_(query, rel, ls, rs, esc, type, need_not, quantifier);
1700}
1701
1702static sql_rel *
1703rel_compare(sql_query *query, sql_rel *rel, symbol *sc, symbol *lo, symbol *ro, symbol *ro2,
1704 char *compare_op, int f, exp_kind k, int quantifier)
1705{
1706 mvc *sql = query->sql;
1707 sql_exp *rs = NULL, *rs2 = NULL, *ls;
1708 comp_type cmp_type = compare_str2type(compare_op);
1709 exp_kind ek = {type_value, card_column, FALSE};
1710 int need_not = 0;
1711
1712 if ((quantifier == 1 && cmp_type == cmp_equal) ||
1713 (quantifier == 2 && cmp_type == cmp_notequal)) {
1714 dnode *n = sc->data.lval->h;
1715 dlist *dl = dlist_create(sql->sa);
1716 /* map into IN/NOT IN */
1717 sc->token = cmp_type==cmp_equal?SQL_IN:SQL_NOT_IN;
1718 n->next->type = type_list;
1719 n->next->data.lval = dl;
1720 n->next->next->next = NULL; /* remove quantifier */
1721 dl->h = n->next->next;
1722 n->next->next = NULL; /* (remove comparison) moved righthand side */
1723 return rel_logical_exp(query, rel, sc, f);
1724 }
1725 /* <> ANY -> NOT (= ALL) */
1726 if (quantifier == 1 && cmp_type == cmp_notequal) {
1727 need_not = 1;
1728 quantifier = 2;
1729 cmp_type = cmp_equal;
1730 compare_op = "=";
1731 }
1732
1733 if (!ro2 && lo->token == SQL_SELECT) { /* swap subquery to the right hand side */
1734 symbol *tmp = lo;
1735
1736 lo = ro;
1737 ro = tmp;
1738
1739 if (compare_op[0] == '>')
1740 compare_op[0] = '<';
1741 else if (compare_op[0] == '<')
1742 compare_op[0] = '>';
1743 cmp_type = swap_compare(cmp_type);
1744 }
1745
1746 ls = rel_value_exp(query, &rel, lo, f, ek);
1747 if (!ls)
1748 return NULL;
1749 if (ls && rel && exp_has_freevar(sql, ls) && (is_sql_sel(f) || is_sql_having(f))) {
1750 ls = rel_project_add_exp(sql, rel, ls);
1751 }
1752 if (quantifier)
1753 ek.card = card_set;
1754
1755 rs = rel_value_exp(query, &rel, ro, f, ek);
1756 if (!rs)
1757 return NULL;
1758 if (ro2) {
1759 rs2 = rel_value_exp(query, &rel, ro2, f, ek);
1760 if (!rs2)
1761 return NULL;
1762 }
1763 if (!rs)
1764 return NULL;
1765 if (ls->card > rs->card && rs->card == CARD_AGGR && is_sql_having(f))
1766 return sql_error(sql, 05, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query results without an aggregate function", exp_relname(ls), exp_name(ls));
1767 if (rs->card > ls->card && ls->card == CARD_AGGR && is_sql_having(f))
1768 return sql_error(sql, 05, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query results without an aggregate function", exp_relname(rs), exp_name(rs));
1769 return rel_compare_exp(query, rel, ls, rs, compare_op, rs2, k.reduce, quantifier, need_not);
1770}
1771
1772static sql_exp*
1773_rel_nop(mvc *sql, sql_schema *s, char *fname, list *tl, sql_rel *rel, list *exps, sql_subtype *obj_type, int nr_args,
1774 exp_kind ek)
1775{
1776 sql_subfunc *f = NULL;
1777 int table_func = (ek.card == card_relation);
1778 sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card == card_none)?F_PROC:
1779 ((ek.card == card_relation)?F_UNION:F_FUNC));
1780 sql_ftype filt = (type == F_FUNC)?F_FILT:type;
1781
1782 (void)filt;
1783 (void)nr_args;
1784 (void)obj_type;
1785 f = bind_func_(sql, s, fname, tl, type);
1786 if (f) {
1787 return exp_op(sql->sa, exps, f);
1788 } else if (list_length(tl)) {
1789 int len, match = 0;
1790 list *funcs = sql_find_funcs(sql->sa, s, fname, list_length(tl), type);
1791 if (!funcs)
1792 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
1793 len = list_length(funcs);
1794 if (len > 1) {
1795 int i, score = 0;
1796 node *n;
1797
1798 for (i = 0, n = funcs->h; i<len; i++, n = n->next) {
1799 int cscore = score_func(n->data, tl);
1800 if (cscore > score) {
1801 score = cscore;
1802 match = i;
1803 }
1804 }
1805 }
1806 if (list_empty(funcs))
1807 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
1808
1809 f = list_fetch(funcs, match);
1810 if (f->func->vararg) {
1811 return exp_op(sql->sa, exps, f);
1812 } else {
1813 node *n, *m;
1814 list *nexps = new_exp_list(sql->sa);
1815 sql_subtype *atp = NULL;
1816 sql_arg *aa = NULL;
1817
1818 /* find largest any type argument */
1819 for (n = exps->h, m = f->func->ops->h; n && m; n = n->next, m = m->next) {
1820 sql_arg *a = m->data;
1821 sql_exp *e = n->data;
1822
1823 if (!aa && a->type.type->eclass == EC_ANY) {
1824 atp = &e->tpe;
1825 aa = a;
1826 }
1827 if (aa && a->type.type->eclass == EC_ANY &&
1828 e->tpe.type->localtype > atp->type->localtype){
1829 atp = &e->tpe;
1830 aa = a;
1831 }
1832 }
1833 for (n = exps->h, m = f->func->ops->h; n && m; n = n->next, m = m->next) {
1834 sql_arg *a = m->data;
1835 sql_exp *e = n->data;
1836 sql_subtype *ntp = &a->type;
1837
1838 if (a->type.type->eclass == EC_ANY)
1839 ntp = sql_create_subtype(sql->sa, atp->type, atp->digits, atp->scale);
1840 e = rel_check_type(sql, ntp, rel, e, type_equal);
1841 if (!e) {
1842 nexps = NULL;
1843 break;
1844 }
1845 if (table_func && e->card > CARD_ATOM) {
1846 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(e));
1847
1848 e = exp_aggr1(sql->sa, e, zero_or_one, 0, 0, CARD_ATOM, has_nil(e));
1849 }
1850 append(nexps, e);
1851 }
1852 /* dirty hack */
1853 if (f->res && aa)
1854 f->res->h->data = sql_create_subtype(sql->sa, atp->type, atp->digits, atp->scale);
1855 if (nexps)
1856 return exp_op(sql->sa, nexps, f);
1857 }
1858 }
1859 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
1860}
1861
1862static sql_exp *
1863exp_exist(sql_query *query, sql_exp *le, int exists)
1864{
1865 mvc *sql = query->sql;
1866 sql_subfunc *exists_func = NULL;
1867
1868 if (exists)
1869 exists_func = sql_bind_func(sql->sa, sql->session->schema, "sql_exists", exp_subtype(le), NULL, F_FUNC);
1870 else
1871 exists_func = sql_bind_func(sql->sa, sql->session->schema, "sql_not_exists", exp_subtype(le), NULL, F_FUNC);
1872
1873 if (!exists_func)
1874 return sql_error(sql, 02, SQLSTATE(42000) "exist operator on type %s missing", exp_subtype(le)->type->sqlname);
1875 return exp_unop(sql->sa, le, exists_func);
1876}
1877
1878static sql_exp *
1879rel_exists_value_exp(sql_query *query, sql_rel **rel, symbol *sc, int f)
1880{
1881 exp_kind ek = {type_value, card_exists, FALSE};
1882 sql_exp *le, *e;
1883
1884 le = rel_value_exp(query, rel, sc->data.sym, f, ek);
1885 if (!le)
1886 return NULL;
1887 e = exp_exist(query, le, sc->token == SQL_EXISTS);
1888 if (e) {
1889 /* only freevar should have CARD_AGGR */
1890 e->card = CARD_ATOM;
1891 }
1892 return e;
1893}
1894
1895static sql_rel *
1896rel_exists_exp(sql_query *query, sql_rel *rel, symbol *sc, int f)
1897{
1898 exp_kind ek = {type_value, card_exists, TRUE};
1899 mvc *sql = query->sql;
1900 sql_rel *sq = NULL;
1901
1902 if (rel)
1903 query_push_outer(query, rel, f);
1904 sq = rel_subquery(query, NULL, sc->data.sym, ek);
1905 if (rel)
1906 rel = query_pop_outer(query);
1907 assert(!is_sql_sel(f));
1908 if (sq) {
1909 rel = rel_crossproduct(sql->sa, rel, sq, op_join);
1910 if (sc->token == SQL_EXISTS) {
1911 rel->op = op_semi;
1912 } else {
1913 rel->op = op_anti;
1914 }
1915 if (rel_has_freevar(sql, sq))
1916 set_dependent(rel);
1917 return rel;
1918 }
1919 return sq;
1920}
1921
1922static sql_exp *
1923rel_in_value_exp(sql_query *query, sql_rel **rel, symbol *sc, int f)
1924{
1925 exp_kind ek = {type_value, card_column, FALSE};
1926 mvc *sql = query->sql;
1927 sql_exp *le, *e = NULL;
1928 dlist *dl = sc->data.lval;
1929 symbol *lo = dl->h->data.sym;
1930 dnode *n = dl->h->next;
1931 list *vals = NULL;
1932
1933 le = rel_value_exp(query, rel, lo, f, ek);
1934 if (!le)
1935 return NULL;
1936 ek.card = card_set;
1937 if (n->type == type_list) {
1938 vals = sa_list(sql->sa);
1939 n = n->data.lval->h;
1940 for (; n; n = n->next) {
1941 sql_exp *r = NULL;
1942
1943 r = rel_value_exp(query, rel, n->data.sym, f, ek);
1944 if (!r)
1945 return NULL;
1946 append( vals, r);
1947 }
1948 e = exp_in_func(sql, le, exp_values(sql->sa, vals), (sc->token == SQL_IN), 0);
1949 }
1950 if (e)
1951 e->card = le->card;
1952 return e;
1953}
1954
1955static sql_rel *
1956rel_in_exp(sql_query *query, sql_rel *rel, symbol *sc, int f)
1957{
1958 mvc *sql = query->sql;
1959 exp_kind ek = {type_value, card_column, TRUE};
1960 dlist *dl = sc->data.lval;
1961 symbol *lo = NULL;
1962 dnode *n = dl->h->next, *dn = NULL;
1963 sql_exp *le, *re;
1964 list *vals = NULL, *ll = sa_list(sql->sa);
1965 int is_tuple = 0;
1966
1967 /* complex case */
1968 if (dl->h->type == type_list) { /* (a,b..) in (.. ) */
1969 dn = dl->h->data.lval->h;
1970 lo = dn->data.sym;
1971 dn = dn->next;
1972 } else {
1973 lo = dl->h->data.sym;
1974 }
1975 for( ; lo; lo = dn?dn->data.sym:NULL, dn = dn?dn->next:NULL ) {
1976 le = rel_value_exp(query, &rel, lo, f, ek);
1977 if (!le)
1978 return NULL;
1979 ek.card = card_set;
1980 append(ll, le);
1981 }
1982 if (list_length(ll) == 1) {
1983 le = ll->h->data;
1984 } else {
1985 le = exp_values(sql->sa, ll);
1986 ek.card = card_relation;
1987 is_tuple = 1;
1988 }
1989 /* list of values or subqueries */
1990 if (n->type == type_list) {
1991 vals = sa_list(sql->sa);
1992 n = dl->h->next;
1993 n = n->data.lval->h;
1994
1995 for (; n; n = n->next) {
1996 re = rel_value_exp(query, &rel, n->data.sym, f, ek);
1997 if (!re)
1998 return NULL;
1999 if (is_tuple && !exp_is_rel(re))
2000 return NULL;
2001 if (is_tuple)
2002 re = exp_rel_label(sql, re);
2003 append(vals, re);
2004 }
2005 }
2006
2007 assert(!is_sql_sel(f));
2008 sql_exp *e = exp_in_func(sql, le, exp_values(sql->sa, vals), (sc->token == SQL_IN), is_tuple);
2009 rel = rel_select_add_exp(sql->sa, rel, e);
2010 return rel;
2011}
2012
2013
2014sql_exp *
2015rel_logical_value_exp(sql_query *query, sql_rel **rel, symbol *sc, int f)
2016{
2017 mvc *sql = query->sql;
2018 exp_kind ek = {type_value, card_column, FALSE};
2019
2020 if (!sc)
2021 return NULL;
2022
2023 if (THRhighwater())
2024 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
2025
2026 switch (sc->token) {
2027 case SQL_OR:
2028 case SQL_AND:
2029 {
2030 symbol *lo = sc->data.lval->h->data.sym;
2031 symbol *ro = sc->data.lval->h->next->data.sym;
2032
2033 sql_exp *ls = rel_logical_value_exp(query, rel, lo, f);
2034 sql_exp *rs = rel_logical_value_exp(query, rel, ro, f);
2035
2036 if (!ls || !rs)
2037 return NULL;
2038 if (sc->token == SQL_OR)
2039 return rel_binop_(sql, rel ? *rel : NULL, ls, rs, NULL, "or", card_value);
2040 else
2041 return rel_binop_(sql, rel ? *rel : NULL, ls, rs, NULL, "and", card_value);
2042 }
2043 case SQL_FILTER:
2044 /* [ x,..] filter [ y,..] */
2045 /* todo add anti, [ x,..] not filter [ y,...] */
2046 /* no correlation */
2047 {
2048 dnode *ln = sc->data.lval->h->data.lval->h;
2049 dnode *rn = sc->data.lval->h->next->next->data.lval->h;
2050 dlist *filter_op = sc->data.lval->h->next->data.lval;
2051 char *fname = qname_fname(filter_op);
2052 char *sname = qname_schema(filter_op);
2053 list *exps, *tl;
2054 sql_schema *s = sql->session->schema;
2055 sql_subtype *obj_type = NULL;
2056
2057 if (sname)
2058 s = mvc_bind_schema(sql, sname);
2059
2060 exps = sa_list(sql->sa);
2061 tl = sa_list(sql->sa);
2062 for (; ln; ln = ln->next) {
2063 symbol *sym = ln->data.sym;
2064
2065 sql_exp *e = rel_value_exp(query, rel, sym, f, ek);
2066 if (!e)
2067 return NULL;
2068 if (!obj_type)
2069 obj_type = exp_subtype(e);
2070 list_append(exps, e);
2071 append(tl, exp_subtype(e));
2072 }
2073 for (; rn; rn = rn->next) {
2074 symbol *sym = rn->data.sym;
2075
2076 sql_exp *e = rel_value_exp(query, rel, sym, f, ek);
2077 if (!e)
2078 return NULL;
2079 list_append(exps, e);
2080 append(tl, exp_subtype(e));
2081 }
2082 /* find the predicate filter function */
2083 return _rel_nop(sql, s, fname, tl, rel ? *rel : NULL, exps, obj_type, list_length(exps), ek);
2084 }
2085 case SQL_COMPARE:
2086 {
2087 dnode *n = sc->data.lval->h;
2088 symbol *lo = n->data.sym;
2089 symbol *ro = n->next->next->data.sym;
2090 char *compare_op = n->next->data.sval;
2091 int quantifier = 0;
2092
2093 sql_exp *rs = NULL, *ls;
2094 comp_type cmp_type = compare_str2type(compare_op);
2095 int need_not = 0;
2096
2097 /*
2098 * = ANY -> IN, <> ALL -> NOT( = ANY) -> NOT IN
2099 * = ALL -> all(groupby(all, nil)), <> ANY -> NOT ( = ALL )
2100 */
2101 if (n->next->next->next)
2102 quantifier = n->next->next->next->data.i_val + 1;
2103 assert(quantifier == 0 || quantifier == 1 || quantifier == 2);
2104
2105 if ((quantifier == 1 && cmp_type == cmp_equal) ||
2106 (quantifier == 2 && cmp_type == cmp_notequal)) {
2107 dlist *dl = dlist_create(sql->sa);
2108 /* map into IN/NOT IN */
2109 sc->token = cmp_type==cmp_equal?SQL_IN:SQL_NOT_IN;
2110 n->next->type = type_list;
2111 n->next->data.lval = dl;
2112 n->next->next->next = NULL; /* remove quantifier */
2113 dl->h = n->next->next;
2114 n->next->next = NULL; /* (remove comparison) moved righthand side */
2115 return rel_logical_value_exp(query, rel, sc, f);
2116 }
2117 /* <> ANY -> NOT (= ALL) */
2118 if (quantifier == 1 && cmp_type == cmp_notequal) {
2119 need_not = 1;
2120 quantifier = 2;
2121 cmp_type = cmp_equal;
2122 compare_op = "=";
2123 }
2124
2125 ls = rel_value_exp(query, rel, lo, f, ek);
2126 if (!ls)
2127 return NULL;
2128 if (quantifier)
2129 ek.card = card_set;
2130
2131 rs = rel_value_exp(query, rel, ro, f, ek);
2132 if (!rs)
2133 return NULL;
2134 ls = exp_compare_func(sql, ls, rs, NULL, compare_func(compare_str2type(compare_op), quantifier?0:need_not), quantifier);
2135 if (need_not && quantifier)
2136 ls = rel_unop_(sql, NULL, ls, NULL, "not", card_value);
2137 return ls;
2138 }
2139 /* Set Member ship */
2140 case SQL_IN:
2141 case SQL_NOT_IN:
2142 return rel_in_value_exp(query, rel, sc, f);
2143 case SQL_EXISTS:
2144 case SQL_NOT_EXISTS:
2145 return rel_exists_value_exp(query, rel, sc, f);
2146 case SQL_LIKE:
2147 case SQL_NOT_LIKE:
2148 {
2149 symbol *lo = sc->data.lval->h->data.sym;
2150 symbol *ro = sc->data.lval->h->next->data.sym;
2151 int insensitive = sc->data.lval->h->next->next->data.i_val;
2152 int anti = (sc->token == SQL_NOT_LIKE) != (sc->data.lval->h->next->next->next->data.i_val != 0);
2153 sql_subtype *st = sql_bind_localtype("str");
2154 sql_exp *le = rel_value_exp(query, rel, lo, f, ek);
2155 sql_exp *re, *ee = NULL;
2156 char *like = insensitive ? (anti ? "not_ilike" : "ilike") : (anti ? "not_like" : "like");
2157 sql_schema *sys = mvc_bind_schema(sql, "sys");
2158
2159 if (!le)
2160 return NULL;
2161
2162 if (!exp_subtype(le))
2163 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: parameter not allowed on "
2164 "left hand side of LIKE operator");
2165
2166 lo = ro->data.lval->h->data.sym;
2167 /* like uses a single string pattern */
2168 ek.card = card_value;
2169 re = rel_value_exp(query, rel, lo, f, ek);
2170 if (!re)
2171 return NULL;
2172 if ((re = rel_check_type(sql, st, rel ? *rel : NULL, re, type_equal)) == NULL)
2173 return sql_error(sql, 02, SQLSTATE(42000) "LIKE: wrong type, should be string");
2174 /* Do we need to escape ? */
2175 if (dlist_length(ro->data.lval) == 2) {
2176 char *escape = ro->data.lval->h->next->data.sval;
2177 ee = exp_atom(sql->sa, atom_string(sql->sa, st, sa_strdup(sql->sa, escape)));
2178 }
2179 if (ee)
2180 return rel_nop_(sql, rel ? *rel : NULL, le, re, ee, NULL, sys, like, card_value);
2181 return rel_binop_(sql, rel ? *rel : NULL, le, re, sys, like, card_value);
2182 }
2183 case SQL_BETWEEN:
2184 case SQL_NOT_BETWEEN:
2185 {
2186 symbol *lo = sc->data.lval->h->data.sym;
2187 int symmetric = sc->data.lval->h->next->data.i_val;
2188 symbol *ro1 = sc->data.lval->h->next->next->data.sym;
2189 symbol *ro2 = sc->data.lval->h->next->next->next->data.sym;
2190 sql_exp *le = rel_value_exp(query, rel, lo, f, ek);
2191 sql_exp *re1 = rel_value_exp(query, rel, ro1, f, ek);
2192 sql_exp *re2 = rel_value_exp(query, rel, ro2, f, ek);
2193 sql_exp *e1 = NULL, *e2 = NULL;
2194
2195 assert(sc->data.lval->h->next->type == type_int);
2196 if (!le || !re1 || !re2)
2197 return NULL;
2198
2199 if (rel_convert_types(sql, rel ? *rel : NULL, rel ? *rel : NULL, &le, &re1, 1, type_equal) < 0 ||
2200 rel_convert_types(sql, rel ? *rel : NULL, rel ? *rel : NULL, &le, &re2, 1, type_equal) < 0)
2201 return NULL;
2202
2203 if (!re1 || !re2)
2204 return NULL;
2205
2206 if (symmetric) {
2207 sql_exp *tmp = NULL;
2208 sql_subfunc *min = sql_bind_func(sql->sa, sql->session->schema, "sql_min", exp_subtype(re1), exp_subtype(re2), F_FUNC);
2209 sql_subfunc *max = sql_bind_func(sql->sa, sql->session->schema, "sql_max", exp_subtype(re1), exp_subtype(re2), F_FUNC);
2210
2211 if (!min || !max) {
2212 return sql_error(sql, 02, SQLSTATE(42000) "min or max operator on types %s %s missing", exp_subtype(re1)->type->sqlname, exp_subtype(re2)->type->sqlname);
2213 }
2214 tmp = exp_binop(sql->sa, re1, re2, min);
2215 re2 = exp_binop(sql->sa, re1, re2, max);
2216 re1 = tmp;
2217 }
2218
2219 if (sc->token == SQL_NOT_BETWEEN) {
2220 e1 = rel_binop_(sql, rel ? *rel : NULL, le, re1, NULL, "<", card_value);
2221 e2 = rel_binop_(sql, rel ? *rel : NULL, le, re2, NULL, ">", card_value);
2222 } else {
2223 e1 = rel_binop_(sql, rel ? *rel : NULL, le, re1, NULL, ">=", card_value);
2224 e2 = rel_binop_(sql, rel ? *rel : NULL, le, re2, NULL, "<=", card_value);
2225 }
2226 if (!e1 || !e2)
2227 return NULL;
2228 if (sc->token == SQL_NOT_BETWEEN) {
2229 return rel_binop_(sql, rel ? *rel : NULL, e1, e2, NULL, "or", card_value);
2230 } else {
2231 return rel_binop_(sql, rel ? *rel : NULL, e1, e2, NULL, "and", card_value);
2232 }
2233 }
2234 case SQL_IS_NULL:
2235 case SQL_IS_NOT_NULL:
2236 /* is (NOT) NULL */
2237 {
2238 sql_exp *le = rel_value_exp(query, rel, sc->data.sym, f, ek);
2239
2240 if (!le)
2241 return NULL;
2242 le = rel_unop_(sql, rel ? *rel : NULL, le, NULL, "isnull", card_value);
2243 set_has_no_nil(le);
2244 if (sc->token != SQL_IS_NULL)
2245 le = rel_unop_(sql, rel ? *rel : NULL, le, NULL, "not", card_value);
2246 set_has_no_nil(le);
2247 return le;
2248 }
2249 case SQL_NOT: {
2250 sql_exp *le = rel_logical_value_exp(query, rel, sc->data.sym, f);
2251
2252 if (!le)
2253 return le;
2254 return rel_unop_(sql, rel ? *rel : NULL, le, NULL, "not", card_value);
2255 }
2256 case SQL_ATOM: {
2257 /* TRUE or FALSE */
2258 AtomNode *an = (AtomNode *) sc;
2259
2260 if (!an || !an->a) {
2261 assert(0);
2262 return exp_null(sql->sa, sql_bind_localtype("void"));
2263 } else {
2264 return exp_atom(sql->sa, atom_dup(sql->sa, an->a));
2265 }
2266 }
2267 case SQL_IDENT:
2268 case SQL_COLUMN:
2269 return rel_column_ref(query, rel, sc, f);
2270 case SQL_UNION:
2271 case SQL_EXCEPT:
2272 case SQL_INTERSECT: {
2273 if (rel && *rel)
2274 query_push_outer(query, *rel, f);
2275 sql_rel *sq = rel_setquery(query, sc);
2276 if (rel && *rel)
2277 *rel = query_pop_outer(query);
2278 if (sq)
2279 return exp_rel(sql, sq);
2280 return NULL;
2281 }
2282 case SQL_DEFAULT:
2283 return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not allowed outside insert and update statements");
2284 default: {
2285 sql_exp *re, *le = rel_value_exp(query, rel, sc, f, ek);
2286 sql_subtype bt;
2287
2288 if (!le)
2289 return NULL;
2290 re = exp_atom_bool(sql->sa, 1);
2291 sql_find_subtype(&bt, "boolean", 0, 0);
2292 if ((le = rel_check_type(sql, &bt, rel ? *rel : NULL, le, type_equal)) == NULL)
2293 return NULL;
2294 return rel_binop_(sql, rel ? *rel : NULL, le, re, NULL, "=", 0);
2295 }
2296 }
2297}
2298
2299sql_rel *
2300rel_logical_exp(sql_query *query, sql_rel *rel, symbol *sc, int f)
2301{
2302 mvc *sql = query->sql;
2303 exp_kind ek = {type_value, card_column, TRUE};
2304
2305 if (!sc)
2306 return NULL;
2307
2308 if (THRhighwater())
2309 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
2310
2311 switch (sc->token) {
2312 case SQL_OR:
2313 {
2314 list *exps = NULL, *lexps = NULL, *rexps = NULL;
2315
2316 symbol *lo = sc->data.lval->h->data.sym;
2317 symbol *ro = sc->data.lval->h->next->data.sym;
2318
2319 sql_rel *lr, *rr;
2320
2321 if (!rel)
2322 return NULL;
2323
2324 lr = rel;
2325 rr = rel_dup(lr);
2326
2327 if (is_outerjoin(rel->op) && !is_processed(rel)) {
2328 int pushdown = sql->pushdown;
2329
2330 exps = rel->exps;
2331 sql->pushdown = 0;
2332
2333 lr = rel_select_copy(sql->sa, lr, sa_list(sql->sa));
2334 lr = rel_logical_exp(query, lr, lo, f);
2335 if (!lr)
2336 return NULL;
2337 rr = rel_select_copy(sql->sa, rr, sa_list(sql->sa));
2338 rr = rel_logical_exp(query, rr, ro, f);
2339 if (lr && rr && lr->l == rr->l) {
2340 lexps = lr->exps;
2341 lr = lr->l;
2342 rexps = rr->exps;
2343 rr = rr->l;
2344 }
2345 rel = NULL;
2346 sql->pushdown = pushdown;
2347 } else {
2348 lr = rel_logical_exp(query, lr, lo, f);
2349 if (!lr)
2350 return NULL;
2351 rr = rel_logical_exp(query, rr, ro, f);
2352 }
2353
2354 if (!lr || !rr)
2355 return NULL;
2356 return rel_or(sql, rel, lr, rr, exps, lexps, rexps);
2357 }
2358 case SQL_AND:
2359 {
2360 /* split into 2 lists, simle logical expressions and or's */
2361 list *nors = sa_list(sql->sa);
2362 list *ors = sa_list(sql->sa);
2363
2364 symbol *lo = sc->data.lval->h->data.sym;
2365 symbol *ro = sc->data.lval->h->next->data.sym;
2366 node *n;
2367
2368 while (lo->token == SQL_AND) {
2369 symbol *s;
2370
2371 sc = lo;
2372 lo = sc->data.lval->h->data.sym;
2373 s = sc->data.lval->h->next->data.sym;
2374
2375 if (s->token != SQL_OR)
2376 list_prepend(nors, s);
2377 else
2378 list_prepend(ors, s);
2379 }
2380 if (lo->token != SQL_OR)
2381 list_prepend(nors, lo);
2382 else
2383 list_prepend(ors, lo);
2384 if (ro->token != SQL_OR)
2385 append(nors, ro);
2386 else
2387 append(ors, ro);
2388
2389 for(n=nors->h; n; n = n->next) {
2390 symbol *lo = n->data;
2391 rel = rel_logical_exp(query, rel, lo, f);
2392 if (!rel)
2393 return NULL;
2394 }
2395 for(n=ors->h; n; n = n->next) {
2396 symbol *lo = n->data;
2397 rel = rel_logical_exp(query, rel, lo, f);
2398 if (!rel)
2399 return NULL;
2400 }
2401 return rel;
2402 /*
2403 rel = rel_logical_exp(query, rel, lo, f);
2404 if (!rel)
2405 return NULL;
2406 return rel_logical_exp(query, rel, ro, f);
2407 */
2408 }
2409 case SQL_FILTER:
2410 /* [ x,..] filter [ y,..] */
2411 /* todo add anti, [ x,..] NOT filter [ y,...] */
2412 /* no correlation */
2413 {
2414 dnode *ln = sc->data.lval->h->data.lval->h;
2415 dnode *rn = sc->data.lval->h->next->next->data.lval->h;
2416 dlist *filter_op = sc->data.lval->h->next->data.lval;
2417 char *fname = qname_fname(filter_op);
2418 char *sname = qname_schema(filter_op);
2419 list *l, *r;
2420
2421 l = sa_list(sql->sa);
2422 r = sa_list(sql->sa);
2423 for (; ln; ln = ln->next) {
2424 symbol *sym = ln->data.sym;
2425
2426 sql_exp *e = rel_value_exp(query, &rel, sym, f, ek);
2427 if (!e)
2428 return NULL;
2429 list_append(l, e);
2430 }
2431 for (; rn; rn = rn->next) {
2432 symbol *sym = rn->data.sym;
2433
2434 sql_exp *e = rel_value_exp(query, &rel, sym, f, ek);
2435 if (!e)
2436 return NULL;
2437 list_append(r, e);
2438 }
2439 return rel_filter(sql, rel, l, r, sname, fname, 0);
2440 }
2441 case SQL_COMPARE:
2442 {
2443 dnode *n = sc->data.lval->h;
2444 symbol *lo = n->data.sym;
2445 symbol *ro = n->next->next->data.sym;
2446 char *compare_op = n->next->data.sval;
2447 int quantifier = 0;
2448
2449 if (n->next->next->next)
2450 quantifier = n->next->next->next->data.i_val + 1;
2451 assert(quantifier == 0 || quantifier == 1 || quantifier == 2);
2452 return rel_compare(query, rel, sc, lo, ro, NULL, compare_op, f, ek, quantifier);
2453 }
2454 /* Set Member ship */
2455 case SQL_IN:
2456 case SQL_NOT_IN:
2457 return rel_in_exp(query, rel, sc, f);
2458 case SQL_EXISTS:
2459 case SQL_NOT_EXISTS:
2460 return rel_exists_exp(query, rel , sc, f);
2461 case SQL_LIKE:
2462 case SQL_NOT_LIKE:
2463 {
2464 symbol *lo = sc->data.lval->h->data.sym;
2465 symbol *ro = sc->data.lval->h->next->data.sym;
2466 int insensitive = sc->data.lval->h->next->next->data.i_val;
2467 int anti = (sc->token == SQL_NOT_LIKE) != (sc->data.lval->h->next->next->next->data.i_val != 0);
2468 sql_subtype *st = sql_bind_localtype("str");
2469 sql_exp *le = rel_value_exp(query, &rel, lo, f, ek);
2470 sql_exp *re, *ee = NULL;
2471
2472 if (!le)
2473 return NULL;
2474
2475 if (!exp_subtype(le))
2476 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: parameter not allowed on "
2477 "left hand side of LIKE operator");
2478
2479 /* Do we need to escape ? */
2480 if (dlist_length(ro->data.lval) == 2) {
2481 char *escape = ro->data.lval->h->next->data.sval;
2482 ee = exp_atom(sql->sa, atom_string(sql->sa, st, sa_strdup(sql->sa, escape)));
2483 } else {
2484 ee = exp_atom(sql->sa, atom_string(sql->sa, st, sa_strdup(sql->sa, "")));
2485 }
2486 ro = ro->data.lval->h->data.sym;
2487 re = rel_value_exp(query, &rel, ro, f, ek);
2488 if (!re)
2489 return NULL;
2490 if ((re = rel_check_type(sql, st, rel, re, type_equal)) == NULL)
2491 return sql_error(sql, 02, SQLSTATE(42000) "LIKE: wrong type, should be string");
2492 if ((le = rel_check_type(sql, st, rel, le, type_equal)) == NULL)
2493 return sql_error(sql, 02, SQLSTATE(42000) "LIKE: wrong type, should be string");
2494 return rel_filter_exp_(sql, rel, le, re, ee, (insensitive ? "ilike" : "like"), anti);
2495 }
2496 case SQL_BETWEEN:
2497 case SQL_NOT_BETWEEN:
2498 {
2499 symbol *lo = sc->data.lval->h->data.sym;
2500 int symmetric = sc->data.lval->h->next->data.i_val;
2501 symbol *ro1 = sc->data.lval->h->next->next->data.sym;
2502 symbol *ro2 = sc->data.lval->h->next->next->next->data.sym;
2503 sql_exp *le = rel_value_exp(query, &rel, lo, f, ek);
2504 sql_exp *re1 = rel_value_exp(query, &rel, ro1, f, ek);
2505 sql_exp *re2 = rel_value_exp(query, &rel, ro2, f, ek);
2506 int flag = 0;
2507
2508 assert(sc->data.lval->h->next->type == type_int);
2509 if (!le || !re1 || !re2)
2510 return NULL;
2511
2512 if (rel_convert_types(sql, rel, rel, &le, &re1, 1, type_equal) < 0 ||
2513 rel_convert_types(sql, rel, rel, &le, &re2, 1, type_equal) < 0)
2514 return NULL;
2515
2516 if (!re1 || !re2)
2517 return NULL;
2518
2519 /* for between 3 columns we use the between operator */
2520 if (symmetric && re1->card == CARD_ATOM && re2->card == CARD_ATOM) {
2521 sql_exp *tmp = NULL;
2522 sql_subfunc *min = sql_bind_func(sql->sa, sql->session->schema, "sql_min", exp_subtype(re1), exp_subtype(re2), F_FUNC);
2523 sql_subfunc *max = sql_bind_func(sql->sa, sql->session->schema, "sql_max", exp_subtype(re1), exp_subtype(re2), F_FUNC);
2524
2525 if (!min || !max) {
2526 return sql_error(sql, 02, SQLSTATE(42000) "min or max operator on types %s %s missing", exp_subtype(re1)->type->sqlname, exp_subtype(re2)->type->sqlname);
2527 }
2528 tmp = exp_binop(sql->sa, re1, re2, min);
2529 re2 = exp_binop(sql->sa, re1, re2, max);
2530 re1 = tmp;
2531 symmetric = 0;
2532 if (!re1 || !re2)
2533 return NULL;
2534 }
2535
2536 flag = (symmetric)?CMP_SYMMETRIC:0;
2537
2538 if (le->card == CARD_ATOM) {
2539 sql_exp *e1, *e2;
2540 if (sc->token == SQL_NOT_BETWEEN) {
2541 e1 = rel_binop_(sql, rel, le, re1, NULL, "<", card_value);
2542 e2 = rel_binop_(sql, rel, le, re2, NULL, ">", card_value);
2543 } else {
2544 e1 = rel_binop_(sql, rel, le, re1, NULL, ">=", card_value);
2545 e2 = rel_binop_(sql, rel, le, re2, NULL, "<=", card_value);
2546 }
2547 if (!e1 || !e2)
2548 return NULL;
2549 if (sc->token == SQL_NOT_BETWEEN) {
2550 e1 = rel_binop_(sql, rel, e1, e2, NULL, "or", card_value);
2551 } else {
2552 e1 = rel_binop_(sql, rel, e1, e2, NULL, "and", card_value);
2553 }
2554 e2 = exp_atom_bool(sql->sa, 1);
2555 rel = rel_select(sql->sa, rel, exp_compare(sql->sa, e1, e2, cmp_equal));
2556 } else if (sc->token == SQL_NOT_BETWEEN) {
2557 rel = rel_compare_exp_(query, rel, le, re1, re2, 3|CMP_BETWEEN|flag, 1, 0);
2558 } else {
2559 rel = rel_compare_exp_(query, rel, le, re1, re2, 3|CMP_BETWEEN|flag, 0, 0);
2560 }
2561 return rel;
2562 }
2563 case SQL_IS_NULL:
2564 case SQL_IS_NOT_NULL:
2565 /* is (NOT) NULL */
2566 {
2567 sql_exp *re, *le = rel_value_exp(query, &rel, sc->data.sym, f, ek);
2568
2569 if (!le)
2570 return NULL;
2571 le = rel_unop_(sql, rel, le, NULL, "isnull", card_value);
2572 set_has_no_nil(le);
2573 if (sc->token == SQL_IS_NULL)
2574 re = exp_atom_bool(sql->sa, 1);
2575 else
2576 re = exp_atom_bool(sql->sa, 0);
2577 le = exp_compare(sql->sa, le, re, cmp_equal);
2578 return rel_select(sql->sa, rel, le);
2579 }
2580 case SQL_NOT: {
2581 sql_exp *re, *le;
2582 switch (sc->data.sym->token) {
2583 case SQL_IN:
2584 sc->data.sym->token = SQL_NOT_IN;
2585 return rel_logical_exp(query, rel, sc->data.sym, f);
2586 case SQL_NOT_IN:
2587 sc->data.sym->token = SQL_IN;
2588 return rel_logical_exp(query, rel, sc->data.sym, f);
2589 default:
2590 break;
2591 }
2592 le = rel_value_exp(query, &rel, sc->data.sym, f|sql_farg, ek);
2593
2594 if (!le)
2595 return NULL;
2596 le = rel_unop_(sql, rel, le, NULL, "not", card_value);
2597 if (le == NULL)
2598 return NULL;
2599 re = exp_atom_bool(sql->sa, 1);
2600 le = exp_compare(sql->sa, le, re, cmp_equal);
2601 return rel_select(sql->sa, rel, le);
2602 }
2603 case SQL_ATOM: {
2604 /* TRUE or FALSE */
2605 AtomNode *an = (AtomNode *) sc;
2606 sql_exp *e = exp_atom(sql->sa, atom_dup(sql->sa, an->a));
2607 return rel_select(sql->sa, rel, e);
2608 }
2609 case SQL_IDENT:
2610 case SQL_COLUMN: {
2611 sql_rel *or = rel;
2612 sql_exp *e = rel_column_ref(query, &rel, sc, f);
2613
2614 if (e) {
2615 sql_subtype bt;
2616
2617 sql_find_subtype(&bt, "boolean", 0, 0);
2618 e = rel_check_type(sql, &bt, rel, e, type_equal);
2619 }
2620 if (!e || or != rel)
2621 return NULL;
2622 return rel_select(sql->sa, rel, e);
2623 }
2624 case SQL_UNION:
2625 case SQL_EXCEPT:
2626 case SQL_INTERSECT:
2627 assert(!rel);
2628 return rel_setquery(query, sc);
2629 case SQL_DEFAULT:
2630 return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not allowed outside insert and update statements");
2631 default: {
2632 sql_exp *re, *le = rel_value_exp(query, &rel, sc, f, ek);
2633
2634 if (!le)
2635 return NULL;
2636 re = exp_atom_bool(sql->sa, 1);
2637 if (rel_convert_types(sql, rel, NULL, &le, &re, 1, type_equal) < 0)
2638 return NULL;
2639 le = exp_compare(sql->sa, le, re, cmp_equal);
2640 return rel_select(sql->sa, rel, le);
2641 }
2642 }
2643 /* never reached, as all switch cases have a `return` */
2644}
2645
2646static sql_exp *
2647rel_op(mvc *sql, symbol *se, exp_kind ek )
2648{
2649 dnode *l = se->data.lval->h;
2650 char *fname = qname_fname(l->data.lval);
2651 char *sname = qname_schema(l->data.lval);
2652 sql_schema *s = sql->session->schema;
2653
2654 if (sname)
2655 s = mvc_bind_schema(sql, sname);
2656 return rel_op_(sql, s, fname, ek);
2657}
2658
2659sql_exp *
2660rel_unop_(mvc *sql, sql_rel *rel, sql_exp *e, sql_schema *s, char *fname, int card)
2661{
2662 sql_subfunc *f = NULL;
2663 sql_subtype *t = NULL;
2664 sql_ftype type = (card == card_loader)?F_LOADER:((card == card_none)?F_PROC:
2665 ((card == card_relation)?F_UNION:F_FUNC));
2666
2667 if (!s)
2668 s = sql->session->schema;
2669 t = exp_subtype(e);
2670 f = bind_func(sql, s, fname, t, NULL, type);
2671 /* try to find the function without a type, and convert
2672 * the value to the type needed by this function!
2673 */
2674 if (!f && (f = find_func(sql, s, fname, 1, type, NULL)) != NULL && check_card(card, f)) {
2675
2676 if (!f->func->vararg) {
2677 sql_arg *a = f->func->ops->h->data;
2678
2679 e = rel_check_type(sql, &a->type, rel, e, type_equal);
2680 }
2681 if (!e)
2682 f = NULL;
2683 }
2684 if (f && check_card(card, f)) {
2685 if (f->func->fix_scale == INOUT) {
2686 sql_subtype *res = f->res->h->data;
2687 res->digits = t->digits;
2688 res->scale = t->scale;
2689 }
2690 if (card == card_relation && e->card > CARD_ATOM) {
2691 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(e));
2692
2693 e = exp_aggr1(sql->sa, e, zero_or_one, 0, 0, CARD_ATOM, has_nil(e));
2694 }
2695 return exp_unop(sql->sa, e, f);
2696 } else if (e) {
2697 if (t) {
2698 char *type = t->type->sqlname;
2699
2700 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such unary operator '%s(%s)'", fname, type);
2701 } else {
2702 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such unary operator '%s(?)'", fname);
2703 }
2704 }
2705 return NULL;
2706}
2707
2708static sql_exp * _rel_aggr(sql_query *query, sql_rel **rel, int distinct, sql_schema *s, char *aname, dnode *arguments, int f);
2709static sql_exp *rel_aggr(sql_query *query, sql_rel **rel, symbol *se, int f);
2710
2711static sql_exp *
2712rel_unop(sql_query *query, sql_rel **rel, symbol *se, int fs, exp_kind ek)
2713{
2714 mvc *sql = query->sql;
2715 dnode *l = se->data.lval->h;
2716 char *fname = qname_fname(l->data.lval);
2717 char *sname = qname_schema(l->data.lval);
2718 sql_schema *s = sql->session->schema;
2719 exp_kind iek = {type_value, card_column, FALSE};
2720 sql_exp *e = NULL;
2721 sql_subfunc *f = NULL;
2722 sql_subtype *t = NULL;
2723 sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card == card_none)?F_PROC:F_FUNC);
2724
2725 if (sname)
2726 s = mvc_bind_schema(sql, sname);
2727
2728 if (!s)
2729 return NULL;
2730 f = find_func(sql, s, fname, 1, F_AGGR, NULL);
2731 if (f) {
2732 e = rel_aggr(query, rel, se, fs);
2733 if (e)
2734 return e;
2735 /* reset error */
2736 sql->session->status = 0;
2737 sql->errstr[0] = '\0';
2738 }
2739 e = rel_value_exp(query, rel, l->next->data.sym, fs, iek);
2740 if (!e) {
2741 if (!f && *rel && (*rel)->card == CARD_AGGR) {
2742 if (is_sql_having(fs) || is_sql_orderby(fs))
2743 return NULL;
2744 /* reset error */
2745 sql->session->status = 0;
2746 sql->errstr[0] = '\0';
2747 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such aggregate '%s'", fname);
2748 }
2749 return NULL;
2750 }
2751
2752 t = exp_subtype(e);
2753 if (!t) {
2754 f = find_func(sql, s, fname, 1, type, NULL);
2755 if (!f)
2756 f = find_func(sql, s, fname, 1, F_AGGR, NULL);
2757 if (f) {
2758 sql_arg *a = f->func->ops->h->data;
2759
2760 t = &a->type;
2761 if (rel_set_type_param(sql, t, rel ? *rel : NULL, e, 1) < 0)
2762 return NULL;
2763 }
2764 } else {
2765 f = bind_func(sql, s, fname, t, NULL, type);
2766 if (!f)
2767 f = bind_func(sql, s, fname, t, NULL, F_AGGR);
2768 }
2769 if (f && IS_AGGR(f->func))
2770 return _rel_aggr(query, rel, 0, s, fname, l->next, fs);
2771
2772 if (f && type_has_tz(t) && f->func->fix_scale == SCALE_FIX) {
2773 /* set timezone (using msec (.3)) */
2774 sql_subtype *intsec = sql_bind_subtype(sql->sa, "sec_interval", 10 /*hour to second */, 3);
2775 atom *a = atom_int(sql->sa, intsec, sql->timezone);
2776 sql_exp *tz = exp_atom(sql->sa, a);
2777
2778 e = rel_binop_(sql, rel ? *rel : NULL, e, tz, NULL, "sql_add", ek.card);
2779 if (!e)
2780 return NULL;
2781 }
2782 return rel_unop_(sql, rel ? *rel : NULL, e, s, fname, ek.card);
2783}
2784
2785#define is_addition(fname) (strcmp(fname, "sql_add") == 0)
2786#define is_subtraction(fname) (strcmp(fname, "sql_sub") == 0)
2787
2788sql_exp *
2789rel_binop_(mvc *sql, sql_rel *rel, sql_exp *l, sql_exp *r, sql_schema *s, char *fname, int card)
2790{
2791 sql_exp *res = NULL;
2792 sql_subtype *t1, *t2;
2793 sql_subfunc *f = NULL;
2794 sql_ftype type = (card == card_loader)?F_LOADER:((card == card_none)?F_PROC:((card == card_relation)?F_UNION:F_FUNC));
2795 if (card == card_loader)
2796 card = card_none;
2797 t1 = exp_subtype(l);
2798 t2 = exp_subtype(r);
2799
2800 if (!s)
2801 s = sql->session->schema;
2802
2803 /* handle param's early */
2804 if (!t1 || !t2) {
2805 if (t2 && !t1 && rel_set_type_param(sql, t2, rel, l, 1) < 0)
2806 return NULL;
2807 if (t1 && !t2 && rel_set_type_param(sql, t1, rel, r, 1) < 0)
2808 return NULL;
2809 t1 = exp_subtype(l);
2810 t2 = exp_subtype(r);
2811 }
2812
2813 if (!t1 || !t2)
2814 return sql_error(sql, 01, SQLSTATE(42000) "Cannot have a parameter (?) on both sides of an expression");
2815
2816 if ((is_addition(fname) || is_subtraction(fname)) &&
2817 ((t1->type->eclass == EC_NUM && t2->type->eclass == EC_NUM) ||
2818 (t1->type->eclass == EC_BIT && t2->type->eclass == EC_BIT))) {
2819 sql_subtype ntp;
2820
2821 sql_find_numeric(&ntp, t1->type->localtype, t1->digits+1);
2822 l = rel_check_type(sql, &ntp, rel, l, type_equal);
2823 sql_find_numeric(&ntp, t2->type->localtype, t2->digits+1);
2824 r = rel_check_type(sql, &ntp, rel, r, type_equal);
2825 t1 = exp_subtype(l);
2826 t2 = exp_subtype(r);
2827 }
2828
2829 f = bind_func(sql, s, fname, t1, t2, type);
2830 if (!f && is_commutative(fname)) {
2831 f = bind_func(sql, s, fname, t2, t1, type);
2832 if (f) {
2833 sql_subtype *tmp = t1;
2834 t1 = t2;
2835 t2 = tmp;
2836 res = l;
2837 l = r;
2838 r = res;
2839 }
2840 }
2841 if (f && check_card(card,f)) {
2842 if (f->func->fix_scale == SCALE_FIX) {
2843 l = exp_fix_scale(sql, t2, l, 0, 0);
2844 r = exp_fix_scale(sql, t1, r, 0, 0);
2845 } else if (f->func->fix_scale == SCALE_EQ) {
2846 sql_arg *a1 = f->func->ops->h->data;
2847 sql_arg *a2 = f->func->ops->h->next->data;
2848 t1 = &a1->type;
2849 t2 = &a2->type;
2850 l = exp_fix_scale(sql, t1, l, 0, 0);
2851 r = exp_fix_scale(sql, t2, r, 0, 0);
2852 } else if (f->func->fix_scale == SCALE_DIV) {
2853 l = exp_scale_algebra(sql, f, rel, l, r);
2854 } else if (f->func->fix_scale == SCALE_MUL) {
2855 exp_sum_scales(f, l, r);
2856 } else if (f->func->fix_scale == DIGITS_ADD) {
2857 sql_subtype *res = f->res->h->data;
2858 res->digits = (t1->digits && t2->digits)?t1->digits + t2->digits:0;
2859 }
2860 if (card == card_relation && l->card > CARD_ATOM) {
2861 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(l));
2862
2863 l = exp_aggr1(sql->sa, l, zero_or_one, 0, 0, CARD_ATOM, has_nil(l));
2864 }
2865 if (card == card_relation && r->card > CARD_ATOM) {
2866 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(r));
2867
2868 r = exp_aggr1(sql->sa, r, zero_or_one, 0, 0, CARD_ATOM, has_nil(r));
2869 }
2870 /* bind types of l and r */
2871 t1 = exp_subtype(l);
2872 t2 = exp_subtype(r);
2873 if (t1->type->eclass == EC_ANY || t2->type->eclass == EC_ANY) {
2874 sql_exp *ol = l;
2875 sql_exp *or = r;
2876
2877 if (t1->type->eclass == EC_ANY && t2->type->eclass == EC_ANY) {
2878 sql_subtype *s = sql_bind_localtype("str");
2879 l = rel_check_type(sql, s, rel, l, type_equal);
2880 r = rel_check_type(sql, s, rel, r, type_equal);
2881 } else if (t1->type->eclass == EC_ANY) {
2882 l = rel_check_type(sql, t2, rel, l, type_equal);
2883 } else {
2884 r = rel_check_type(sql, t1, rel, r, type_equal);
2885 }
2886 if (l && r)
2887 return exp_binop(sql->sa, l, r, f);
2888
2889 /* reset error */
2890 sql->session->status = 0;
2891 sql->errstr[0] = '\0';
2892 f = NULL;
2893
2894 l = ol;
2895 r = or;
2896 }
2897 if (f)
2898 return exp_binop(sql->sa, l, r, f);
2899 } else {
2900 sql_exp *ol = l;
2901 sql_exp *or = r;
2902
2903 if (!EC_NUMBER(t1->type->eclass)) {
2904 sql_subfunc *prev = NULL;
2905
2906 while((f = bind_member_func(sql, s, fname, t1, 2, prev)) != NULL) {
2907 /* try finding function based on first argument */
2908 node *m = f->func->ops->h;
2909 sql_arg *a = m->data;
2910
2911 prev = f;
2912 if (!check_card(card,f))
2913 continue;
2914
2915 l = rel_check_type(sql, &a->type, rel, l, type_equal);
2916 a = m->next->data;
2917 r = rel_check_type(sql, &a->type, rel, r, type_equal);
2918 if (l && r)
2919 return exp_binop(sql->sa, l, r, f);
2920
2921 /* reset error */
2922 sql->session->status = 0;
2923 sql->errstr[0] = '\0';
2924
2925 l = ol;
2926 r = or;
2927 }
2928 }
2929 /* try finding function based on both arguments */
2930 if (rel_convert_types(sql, rel, rel, &l, &r, 1/*fix scale*/, type_equal) >= 0){
2931 /* try operators */
2932 t1 = exp_subtype(l);
2933 t2 = exp_subtype(r);
2934 f = bind_func(sql, s, fname, t1, t2, type);
2935 if (f && check_card(card,f)) {
2936 if (f->func->fix_scale == SCALE_FIX) {
2937 l = exp_fix_scale(sql, t2, l, 0, 0);
2938 r = exp_fix_scale(sql, t1, r, 0, 0);
2939 } else if (f->func->fix_scale == SCALE_EQ) {
2940 sql_arg *a1 = f->func->ops->h->data;
2941 sql_arg *a2 = f->func->ops->h->next->data;
2942 t1 = &a1->type;
2943 t2 = &a2->type;
2944 l = exp_fix_scale(sql, t1, l, 0, 0);
2945 r = exp_fix_scale(sql, t2, r, 0, 0);
2946 } else if (f->func->fix_scale == SCALE_DIV) {
2947 l = exp_scale_algebra(sql, f, rel, l, r);
2948 } else if (f->func->fix_scale == SCALE_MUL) {
2949 exp_sum_scales(f, l, r);
2950 } else if (f->func->fix_scale == DIGITS_ADD) {
2951 sql_subtype *res = f->res->h->data;
2952 res->digits = (t1->digits && t2->digits)?t1->digits + t2->digits:0;
2953 }
2954 return exp_binop(sql->sa, l, r, f);
2955 }
2956 }
2957 /* reset error */
2958 sql->session->status = 0;
2959 sql->errstr[0] = '\0';
2960
2961 l = ol;
2962 r = or;
2963 t1 = exp_subtype(l);
2964 (void) exp_subtype(r);
2965
2966 if ((f = bind_member_func(sql, s, fname, t1, 2, NULL)) != NULL && check_card(card,f)) {
2967 /* try finding function based on first argument */
2968 node *m = f->func->ops->h;
2969 sql_arg *a = m->data;
2970
2971 l = rel_check_type(sql, &a->type, rel, l, type_equal);
2972 a = m->next->data;
2973 r = rel_check_type(sql, &a->type, rel, r, type_equal);
2974 if (l && r)
2975 return exp_binop(sql->sa, l, r, f);
2976 }
2977 /* reset error */
2978 sql->session->status = 0;
2979 sql->errstr[0] = '\0';
2980
2981 l = ol;
2982 r = or;
2983 /* everything failed, fall back to bind on function name only */
2984 if ((f = find_func(sql, s, fname, 2, type, NULL)) != NULL && check_card(card,f)) {
2985
2986 if (!f->func->vararg) {
2987 node *m = f->func->ops->h;
2988 sql_arg *a = m->data;
2989
2990 l = rel_check_type(sql, &a->type, rel, l, type_equal);
2991 a = m->next->data;
2992 r = rel_check_type(sql, &a->type, rel, r, type_equal);
2993 }
2994 if (l && r)
2995 return exp_binop(sql->sa, l, r, f);
2996 }
2997 /* reset error */
2998 sql->session->status = 0;
2999 sql->errstr[0] = '\0';
3000
3001 l = ol;
3002 r = or;
3003 }
3004 res = sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such binary operator '%s(%s,%s)'", fname,
3005 exp_subtype(l)->type->sqlname,
3006 exp_subtype(r)->type->sqlname);
3007 return res;
3008}
3009
3010static sql_exp *
3011rel_binop(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek)
3012{
3013 mvc *sql = query->sql;
3014 dnode *dl = se->data.lval->h;
3015 sql_exp *l, *r;
3016 char *fname = qname_fname(dl->data.lval);
3017 char *sname = qname_schema(dl->data.lval);
3018 sql_schema *s = sql->session->schema;
3019 exp_kind iek = {type_value, card_column, FALSE};
3020 sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card == card_none)?F_PROC:F_FUNC);
3021
3022 sql_subfunc *sf = NULL;
3023
3024 if (sname)
3025 s = mvc_bind_schema(sql, sname);
3026 if (!s)
3027 return NULL;
3028
3029 l = rel_value_exp(query, rel, dl->next->data.sym, f, iek);
3030 r = rel_value_exp(query, rel, dl->next->next->data.sym, f, iek);
3031
3032 if (!l || !r) {
3033 sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
3034 }
3035 if (!sf && (!l || !r) && *rel && (*rel)->card == CARD_AGGR) {
3036 if (is_sql_having(f) || is_sql_orderby(f))
3037 return NULL;
3038 /* reset error */
3039 sql->session->status = 0;
3040 sql->errstr[0] = '\0';
3041 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such aggregate '%s'", fname);
3042 }
3043 if (!l && !r && sf) { /* possibly we cannot resolve the argument as the function maybe an aggregate */
3044 /* reset error */
3045 sql->session->status = 0;
3046 sql->errstr[0] = '\0';
3047 return rel_aggr(query, rel, se, f);
3048 }
3049 if (type == F_FUNC) {
3050 sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
3051 if (sf) {
3052 if (!l || !r) { /* reset error */
3053 sql->session->status = 0;
3054 sql->errstr[0] = '\0';
3055 }
3056 return _rel_aggr(query, rel, 0, s, fname, dl->next, f);
3057 }
3058 }
3059
3060 if (!l || !r)
3061 return NULL;
3062 return rel_binop_(sql, rel ? *rel : NULL, l, r, s, fname, ek.card);
3063}
3064
3065sql_exp *
3066rel_nop_(mvc *sql, sql_rel *rel, sql_exp *a1, sql_exp *a2, sql_exp *a3, sql_exp *a4, sql_schema *s, char *fname,
3067 int card)
3068{
3069 list *tl = sa_list(sql->sa);
3070 sql_subfunc *f = NULL;
3071 sql_ftype type = (card == card_none)?F_PROC:((card == card_relation)?F_UNION:F_FUNC);
3072
3073 (void) rel;
3074 append(tl, exp_subtype(a1));
3075 append(tl, exp_subtype(a2));
3076 append(tl, exp_subtype(a3));
3077 if (a4)
3078 append(tl, exp_subtype(a4));
3079
3080 if (!s)
3081 s = sql->session->schema;
3082 f = bind_func_(sql, s, fname, tl, type);
3083 if (!f)
3084 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
3085 if (!a4)
3086 return exp_op3(sql->sa, a1,a2,a3,f);
3087 return exp_op4(sql->sa, a1,a2,a3,a4,f);
3088}
3089
3090static sql_exp *
3091rel_nop(sql_query *query, sql_rel **rel, symbol *se, int fs, exp_kind ek)
3092{
3093 mvc *sql = query->sql;
3094 int nr_args = 0;
3095 dnode *l = se->data.lval->h;
3096 dnode *ops = l->next->data.lval->h;
3097 list *exps = new_exp_list(sql->sa);
3098 list *tl = sa_list(sql->sa);
3099 sql_subfunc *f = NULL;
3100 sql_subtype *obj_type = NULL;
3101 char *fname = qname_fname(l->data.lval);
3102 char *sname = qname_schema(l->data.lval);
3103 sql_schema *s = sql->session->schema;
3104 exp_kind iek = {type_value, card_column, FALSE};
3105 int err = 0;
3106
3107 for (; ops; ops = ops->next, nr_args++) {
3108 sql_exp *e = rel_value_exp(query, rel, ops->data.sym, fs, iek);
3109 sql_subtype *tpe;
3110
3111 if (!e)
3112 err = 1;
3113 append(exps, e);
3114 if (e) {
3115 tpe = exp_subtype(e);
3116 if (!nr_args)
3117 obj_type = tpe;
3118 append(tl, tpe);
3119 }
3120 }
3121 if (sname)
3122 s = mvc_bind_schema(sql, sname);
3123
3124 /* first try aggregate */
3125 f = find_func(sql, s, fname, nr_args, F_AGGR, NULL);
3126 if (!f && err && *rel && (*rel)->card == CARD_AGGR) {
3127 if (is_sql_having(fs) || is_sql_orderby(fs))
3128 return NULL;
3129 /* reset error */
3130 sql->session->status = 0;
3131 sql->errstr[0] = '\0';
3132 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such aggregate '%s'", fname);
3133 }
3134 if (f) {
3135 if (err) {
3136 /* reset error */
3137 sql->session->status = 0;
3138 sql->errstr[0] = '\0';
3139 }
3140 return _rel_aggr(query, rel, 0, s, fname, l->next->data.lval->h, fs);
3141 }
3142 if (err)
3143 return NULL;
3144 return _rel_nop(sql, s, fname, tl, rel ? *rel : NULL, exps, obj_type, nr_args, ek);
3145}
3146
3147static sql_exp *
3148_rel_aggr(sql_query *query, sql_rel **rel, int distinct, sql_schema *s, char *aname, dnode *args, int f)
3149{
3150 mvc *sql = query->sql;
3151 exp_kind ek = {type_value, card_column, FALSE};
3152 sql_subaggr *a = NULL;
3153 int no_nil = 0, group = 0, has_freevar = 0;
3154 unsigned int all_freevar = 0;
3155 sql_rel *groupby = *rel, *sel = NULL, *gr, *og = NULL, *res = groupby;
3156 sql_rel *subquery = NULL;
3157 list *exps = NULL;
3158
3159 if (!groupby && !query_has_outer(query)) {
3160 char *uaname = GDKmalloc(strlen(aname) + 1);
3161 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: missing group by",
3162 uaname ? toUpperCopy(uaname, aname) : aname);
3163 if (uaname)
3164 GDKfree(uaname);
3165 return e;
3166 }
3167
3168 if (is_sql_groupby(f)) {
3169 char *uaname = GDKmalloc(strlen(aname) + 1);
3170 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate function '%s' not allowed in GROUP BY clause",
3171 uaname ? toUpperCopy(uaname, aname) : aname, aname);
3172 if (uaname)
3173 GDKfree(uaname);
3174 return e;
3175 } else if (!query_has_outer(query) && is_sql_join(f)) { /* the is_sql_join test must come before is_sql_where, because the join conditions are handled with sql_where */
3176 char *uaname = GDKmalloc(strlen(aname) + 1);
3177 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed in JOIN conditions",
3178 uaname ? toUpperCopy(uaname, aname) : aname);
3179 if (uaname)
3180 GDKfree(uaname);
3181 return e;
3182 } else if (!query_has_outer(query) && is_sql_where(f)) {
3183 char *uaname = GDKmalloc(strlen(aname) + 1);
3184 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed in WHERE clause",
3185 uaname ? toUpperCopy(uaname, aname) : aname);
3186 if (uaname)
3187 GDKfree(uaname);
3188 return e;
3189 } else if (is_sql_aggr(f)) {
3190 char *uaname = GDKmalloc(strlen(aname) + 1);
3191 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions cannot be nested",
3192 uaname ? toUpperCopy(uaname, aname) : aname);
3193 if (uaname)
3194 GDKfree(uaname);
3195 return e;
3196 }
3197
3198 exps = sa_list(sql->sa);
3199 if (args && args->data.sym) {
3200 all_freevar = 1;
3201 for ( ; args; args = args->next ) {
3202 int base = (!groupby || !is_project(groupby->op) || is_base(groupby->op) || is_processed(groupby));
3203 sql_rel *gl = base?groupby:groupby->l, *ogl = gl; /* handle case of subqueries without correlation */
3204 sql_exp *e = rel_value_exp(query, &gl, args->data.sym, f | sql_aggr, ek);
3205
3206 if (gl && gl != ogl) {
3207 if (!base)
3208 groupby->l = subquery = gl;
3209 else
3210 groupby = subquery = gl;
3211 }
3212 if (!e || !exp_subtype(e)) /* we also do not expect parameters here */
3213 return NULL;
3214 has_freevar |= exp_has_freevar(sql, e);
3215 all_freevar &= (is_freevar(e)>0);
3216 list_append(exps, e);
3217 }
3218 }
3219
3220 if (all_freevar) { //* case 2, ie use outer
3221 sql_exp *exp = NULL;
3222 /* find proper relation, base on freevar (stack hight) */
3223 for (node *n = exps->h; n; n = n->next) {
3224 sql_exp *e = n->data;
3225
3226 if (all_freevar<is_freevar(e))
3227 all_freevar = is_freevar(e);
3228 exp = e;
3229 }
3230 int sql_state = query_fetch_outer_state(query,all_freevar-1);
3231 res = groupby = query_fetch_outer(query, all_freevar-1);
3232 if (exp && is_sql_aggr(sql_state) && !is_groupby_col(res, exp)) {
3233 return sql_error(sql, 05, SQLSTATE(42000) "SELECT: aggregate function calls cannot be nested");
3234 }
3235 }
3236
3237 /* find having select */
3238 if (!subquery && groupby && !is_processed(groupby) && is_sql_having(f)) {
3239 og = groupby;
3240 while(!is_processed(groupby) && !is_base(groupby->op)) {
3241 if (is_select(groupby->op) || !groupby->l)
3242 break;
3243 if (groupby->l)
3244 groupby = groupby->l;
3245 }
3246 if (groupby && is_select(groupby->op) && !is_processed(groupby)) {
3247 group = 1;
3248 sel = groupby;
3249 /* At the end we switch back to the old projection relation og.
3250 * During the partitioning and ordering we add the expressions to the intermediate relations. */
3251 }
3252 if (!sel)
3253 groupby = og;
3254 if (sel && sel->l)
3255 groupby = sel->l;
3256 }
3257
3258 /* find groupby */
3259 if (!subquery && groupby && !is_processed(groupby) && !is_base(groupby->op)) {
3260 og = groupby;
3261 groupby = rel_find_groupby(groupby);
3262 if (groupby)
3263 group = 1;
3264 else
3265 groupby = og;
3266 }
3267
3268 if (!groupby && exps_card(exps) > CARD_ATOM) {
3269 char *uaname = GDKmalloc(strlen(aname) + 1);
3270 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: missing group by",
3271 uaname ? toUpperCopy(uaname, aname) : aname);
3272 if (uaname)
3273 GDKfree(uaname);
3274 return e;
3275 }
3276
3277 if (!subquery && groupby && groupby->op != op_groupby) { /* implicit groupby */
3278 res = groupby = rel_groupby(sql, groupby, NULL);
3279 }
3280 if (subquery) {
3281 assert(!all_freevar);
3282 res = groupby;
3283 if (is_sql_sel(f) && is_left(subquery->op) && !is_groupby(groupby->op)) {
3284 res = groupby = rel_groupby(sql, groupby, NULL);
3285 exps_set_freevar(sql, exps, groupby); /* mark free variables */
3286 } else if (!is_groupby(groupby->op)) {
3287 res = groupby = rel_groupby(sql, groupby, NULL);
3288 }
3289 assert(!is_base(groupby->op));
3290 }
3291 if ((!exps || exps_card(exps) > CARD_ATOM) && (!res || !groupby))
3292 return NULL;
3293
3294 if (all_freevar) {
3295 query_update_outer(query, res, all_freevar-1);
3296 } else {
3297 *rel = res;
3298 }
3299
3300 if (args && !args->data.sym) { /* count(*) case */
3301 sql_exp *e;
3302
3303 if (strcmp(aname, "count") != 0) {
3304 char *uaname = GDKmalloc(strlen(aname) + 1);
3305 sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: unable to perform '%s(*)'",
3306 uaname ? toUpperCopy(uaname, aname) : aname, aname);
3307 if (uaname)
3308 GDKfree(uaname);
3309 return e;
3310 }
3311 a = sql_bind_aggr(sql->sa, s, aname, NULL);
3312 e = exp_aggr(sql->sa, NULL, a, distinct, 0, groupby?groupby->card:CARD_ATOM, 0);
3313
3314 if (!groupby)
3315 return e;
3316 e = rel_groupby_add_aggr(sql, groupby, e);
3317 if (!group && !all_freevar)
3318 return e;
3319 if (all_freevar) {
3320 if (is_simple_project(res->op)) {
3321 assert(0);
3322 e = rel_project_add_exp(sql, res, e);
3323 e = exp_ref(sql->sa, e);
3324 }
3325 e->card = CARD_ATOM;
3326 set_freevar(e, all_freevar-1);
3327 return e;
3328 }
3329 return e;
3330 }
3331
3332
3333 /* use cnt as nils shouldn't be counted */
3334 no_nil = 1;
3335
3336 gr = groupby;
3337 if (gr && gr->op == op_project && gr->l)
3338 gr = gr->l;
3339
3340 a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa, exps));
3341 if (!a && list_length(exps) > 1) {
3342 sql_subtype *t1 = exp_subtype(exps->h->data);
3343 a = sql_bind_member_aggr(sql->sa, s, aname, exp_subtype(exps->h->data), list_length(exps));
3344 bool is_group_concat = (!a && strcmp(s->base.name, "sys") == 0 && strcmp(aname, "group_concat") == 0);
3345
3346 if (list_length(exps) != 2 || (!EC_NUMBER(t1->type->eclass) || !a || is_group_concat || subtype_cmp(
3347 &((sql_arg*)a->aggr->ops->h->data)->type,
3348 &((sql_arg*)a->aggr->ops->h->next->data)->type) != 0) ) {
3349 if(!a && is_group_concat) {
3350 sql_subtype *tstr = sql_bind_localtype("str");
3351 list *sargs = sa_list(sql->sa);
3352 if (list_length(exps) >= 1)
3353 append(sargs, tstr);
3354 if (list_length(exps) == 2)
3355 append(sargs, tstr);
3356 a = sql_bind_aggr_(sql->sa, s, aname, sargs);
3357 }
3358 if (a) {
3359 node *n, *op = a->aggr->ops->h;
3360 list *nexps = sa_list(sql->sa);
3361
3362 for (n = exps->h ; a && op && n; op = op->next, n = n->next ) {
3363 sql_arg *arg = op->data;
3364 sql_exp *e = n->data;
3365
3366 e = rel_check_type(sql, &arg->type, *rel, e, type_equal); /* rel is a valid pointer */
3367 if (!e)
3368 a = NULL;
3369 list_append(nexps, e);
3370 }
3371 if (a && list_length(nexps)) /* count(col) has |exps| != |nexps| */
3372 exps = nexps;
3373 }
3374 } else {
3375 sql_exp *l = exps->h->data, *ol = l;
3376 sql_exp *r = exps->h->next->data, *or = r;
3377 sql_subtype *t2 = exp_subtype(r);
3378
3379 if (rel_convert_types(sql, *rel, *rel, &l, &r, 1/*fix scale*/, type_equal) >= 0){
3380 list *tps = sa_list(sql->sa);
3381
3382 t1 = exp_subtype(l);
3383 list_append(tps, t1);
3384 t2 = exp_subtype(r);
3385 list_append(tps, t2);
3386 a = sql_bind_aggr_(sql->sa, s, aname, tps);
3387 }
3388 if (!a) {
3389 sql->session->status = 0;
3390 sql->errstr[0] = '\0';
3391
3392 l = ol;
3393 r = or;
3394 } else {
3395 list *nexps = sa_list(sql->sa);
3396
3397 append(nexps,l);
3398 append(nexps,r);
3399 exps = nexps;
3400 }
3401 }
3402 }
3403 if (!a) { /* find aggr + convert */
3404 /* try larger numeric type */
3405 node *n;
3406 list *nexps = sa_list(sql->sa);
3407
3408 for (n = exps->h ; n; n = n->next ) {
3409 sql_exp *e = n->data;
3410
3411 /* cast up, for now just dec to double */
3412 e = rel_numeric_supertype(sql, e);
3413 if (!e)
3414 break;
3415 list_append(nexps, e);
3416 }
3417 a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa, nexps));
3418 if (a && list_length(nexps)) /* count(col) has |exps| != |nexps| */
3419 exps = nexps;
3420 if (!a) {
3421 a = sql_find_aggr(sql->sa, s, aname);
3422 if (a) {
3423 node *n, *op = a->aggr->ops->h;
3424 list *nexps = sa_list(sql->sa);
3425
3426 for (n = exps->h ; a && op && n; op = op->next, n = n->next ) {
3427 sql_arg *arg = op->data;
3428 sql_exp *e = n->data;
3429
3430 e = rel_check_type(sql, &arg->type, *rel, e, type_equal); /* rel is a valid pointer */
3431 if (!e)
3432 a = NULL;
3433 list_append(nexps, e);
3434 }
3435 if (a && list_length(nexps)) /* count(col) has |exps| != |nexps| */
3436 exps = nexps;
3437 }
3438 }
3439 }
3440 if (a && execute_priv(sql,a->aggr)) {
3441 sql_exp *e = exp_aggr(sql->sa, exps, a, distinct, no_nil, groupby?groupby->card:CARD_ATOM, have_nil(exps));
3442
3443 if (!groupby)
3444 return e;
3445 e = rel_groupby_add_aggr(sql, groupby, e);
3446 if (!group && !all_freevar)
3447 return e;
3448 if (all_freevar) {
3449 exps_reset_freevar(exps);
3450 if (is_simple_project(res->op)) {
3451 assert(0);
3452 e = rel_project_add_exp(sql, res, e);
3453 e = exp_ref(sql->sa, e);
3454 }
3455 e->card = CARD_ATOM;
3456 set_freevar(e, all_freevar-1);
3457 return e;
3458 }
3459 return e;
3460 } else {
3461 sql_exp *e;
3462 char *type = "unknown";
3463 char *uaname = GDKmalloc(strlen(aname) + 1);
3464
3465 if (exps->h) {
3466 sql_exp *e = exps->h->data;
3467 type = exp_subtype(e)->type->sqlname;
3468 }
3469
3470 e = sql_error(sql, 02, SQLSTATE(42000) "%s: no such operator '%s(%s)'",
3471 uaname ? toUpperCopy(uaname, aname) : aname, aname, type);
3472
3473 if (uaname)
3474 GDKfree(uaname);
3475 return e;
3476 }
3477}
3478
3479static sql_exp *
3480rel_aggr(sql_query *query, sql_rel **rel, symbol *se, int f)
3481{
3482 dlist *l = se->data.lval;
3483 dnode *d = l->h->next;
3484 int distinct = 0;
3485 char *aname = qname_fname(l->h->data.lval);
3486 char *sname = qname_schema(l->h->data.lval);
3487 sql_schema *s = query->sql->session->schema;
3488
3489 if (l->h->next->type == type_int) {
3490 distinct = l->h->next->data.i_val;
3491 d = l->h->next->next;
3492 }
3493
3494 if (sname)
3495 s = mvc_bind_schema(query->sql, sname);
3496 return _rel_aggr( query, rel, distinct, s, aname, d, f);
3497}
3498
3499static sql_exp *
3500rel_case(sql_query *query, sql_rel **rel, tokens token, symbol *opt_cond, dlist *when_search_list, symbol *opt_else, int f)
3501{
3502 mvc *sql = query->sql;
3503 sql_subtype *tpe = NULL;
3504 list *conds = new_exp_list(sql->sa);
3505 list *results = new_exp_list(sql->sa);
3506 dnode *dn = when_search_list->h;
3507 sql_subtype *restype = NULL, rtype, bt;
3508 sql_exp *res = NULL, *else_exp = NULL;
3509 node *n, *m;
3510 exp_kind ek = {type_value, card_column, FALSE};
3511
3512 sql_find_subtype(&bt, "boolean", 0, 0);
3513 if (dn) {
3514 sql_exp *cond = NULL, *result = NULL;
3515
3516 /* NULLIF(e1,e2) == CASE WHEN e1=e2 THEN NULL ELSE e1 END */
3517 if (token == SQL_NULLIF) {
3518 sql_exp *e1, *e2;
3519
3520 e1 = rel_value_exp(query, rel, dn->data.sym, f, ek);
3521 e2 = rel_value_exp(query, rel, dn->next->data.sym, f, ek);
3522 if (e1 && e2) {
3523 cond = rel_binop_(sql, rel ? *rel : NULL, e1, e2, NULL, "=", card_value);
3524 result = exp_null(sql->sa, exp_subtype(e1));
3525 else_exp = exp_copy(sql, e1); /* ELSE case */
3526 }
3527 /* COALESCE(e1,e2) == CASE WHEN e1
3528 IS NOT NULL THEN e1 ELSE e2 END */
3529 } else if (token == SQL_COALESCE) {
3530 cond = rel_value_exp(query, rel, dn->data.sym, f, ek);
3531
3532 if (cond) {
3533 sql_exp *le;
3534
3535 result = exp_copy(sql, cond);
3536 le = rel_unop_(sql, rel ? *rel : NULL, cond, NULL, "isnull", card_value);
3537 set_has_no_nil(le);
3538 cond = rel_unop_(sql, rel ? *rel : NULL, le, NULL, "not", card_value);
3539 set_has_no_nil(cond);
3540 }
3541 } else {
3542 dlist *when = dn->data.sym->data.lval;
3543
3544 if (opt_cond) {
3545 sql_exp *l = rel_value_exp(query, rel, opt_cond, f, ek);
3546 sql_exp *r = rel_value_exp(query, rel, when->h->data.sym, f, ek);
3547 if (!l || !r || rel_convert_types(sql, rel ? *rel : NULL, rel ? *rel : NULL, &l, &r, 1, type_equal) < 0)
3548 return NULL;
3549 cond = rel_binop_(sql, rel ? *rel : NULL, l, r, NULL, "=", card_value);
3550 } else {
3551 cond = rel_logical_value_exp(query, rel, when->h->data.sym, f);
3552 }
3553 result = rel_value_exp(query, rel, when->h->next->data.sym, f, ek);
3554 }
3555 if (!cond || !result)
3556 return NULL;
3557 list_prepend(conds, cond);
3558 list_prepend(results, result);
3559
3560 restype = exp_subtype(result);
3561
3562 if (token == SQL_NULLIF)
3563 dn = NULL;
3564 else
3565 dn = dn->next;
3566 }
3567 if (!restype)
3568 return sql_error(sql, 02, SQLSTATE(42000) "result type missing");
3569 /* for COALESCE we skip the last (else part) */
3570 for (; dn && (token != SQL_COALESCE || dn->next); dn = dn->next) {
3571 sql_exp *cond = NULL, *result = NULL;
3572
3573 if (token == SQL_COALESCE) {
3574 cond = rel_value_exp(query, rel, dn->data.sym, f, ek);
3575
3576 if (cond) {
3577 sql_exp *le;
3578
3579 result = exp_copy(sql, cond);
3580 le = rel_unop_(sql, rel ? *rel : NULL, cond, NULL, "isnull", card_value);
3581 set_has_no_nil(le);
3582 cond = rel_unop_(sql, rel ? *rel : NULL, le, NULL, "not", card_value);
3583 set_has_no_nil(cond);
3584 }
3585 } else {
3586 dlist *when = dn->data.sym->data.lval;
3587
3588 if (opt_cond) {
3589 sql_exp *l = rel_value_exp(query, rel, opt_cond, f, ek);
3590 sql_exp *r = rel_value_exp(query, rel, when->h->data.sym, f, ek);
3591 if (!l || !r || rel_convert_types(sql, rel ? *rel : NULL, rel ? *rel : NULL, &l, &r, 1, type_equal) < 0)
3592 return NULL;
3593 cond = rel_binop_(sql, rel ? *rel : NULL, l, r, NULL, "=", card_value);
3594 } else {
3595 cond = rel_logical_value_exp(query, rel, when->h->data.sym, f);
3596 }
3597 result = rel_value_exp(query, rel, when->h->next->data.sym, f, ek);
3598 }
3599 if (!cond || !result)
3600 return NULL;
3601 list_prepend(conds, cond);
3602 list_prepend(results, result);
3603
3604 tpe = exp_subtype(result);
3605 if (!tpe)
3606 return sql_error(sql, 02, SQLSTATE(42000) "result type missing");
3607 supertype(&rtype, restype, tpe);
3608 restype = &rtype;
3609 }
3610 if (opt_else || else_exp) {
3611 sql_exp *result = else_exp;
3612
3613 if (!result && !(result = rel_value_exp(query, rel, opt_else, f, ek)))
3614 return NULL;
3615
3616 tpe = exp_subtype(result);
3617 if (tpe && restype) {
3618 supertype(&rtype, restype, tpe);
3619 tpe = &rtype;
3620 }
3621 restype = tpe;
3622 if (restype->type->localtype == TYPE_void) /* NULL */
3623 restype = sql_bind_localtype("str");
3624
3625 if (!result || !(result = rel_check_type(sql, restype, rel ? *rel : NULL, result, type_equal)))
3626 return NULL;
3627 res = result;
3628
3629 if (!res)
3630 return NULL;
3631 } else {
3632 if (restype->type->localtype == TYPE_void) /* NULL */
3633 restype = sql_bind_localtype("str");
3634 res = exp_null(sql->sa, restype);
3635 }
3636
3637 for (n = conds->h, m = results->h; n && m; n = n->next, m = m->next) {
3638 sql_exp *cond = n->data;
3639 sql_exp *result = m->data;
3640
3641 if (!(result = rel_check_type(sql, restype, rel ? *rel : NULL, result, type_equal)))
3642 return NULL;
3643
3644 if (!(cond = rel_check_type(sql, &bt, rel ? *rel : NULL, cond, type_equal)))
3645 return NULL;
3646
3647 if (!cond || !result || !res)
3648 return NULL;
3649 res = rel_nop_(sql, rel ? *rel : NULL, cond, result, res, NULL, NULL, "ifthenelse", card_value);
3650 if (!res)
3651 return NULL;
3652 /* ugh overwrite res type */
3653 ((sql_subfunc*)res->f)->res->h->data = sql_create_subtype(sql->sa, restype->type, restype->digits, restype->scale);
3654 }
3655 return res;
3656}
3657
3658static sql_exp *
3659rel_case_exp(sql_query *query, sql_rel **rel, symbol *se, int f)
3660{
3661 dlist *l = se->data.lval;
3662
3663 if (se->token == SQL_COALESCE) {
3664 symbol *opt_else = l->t->data.sym;
3665
3666 return rel_case(query, rel, se->token, NULL, l, opt_else, f);
3667 } else if (se->token == SQL_NULLIF) {
3668 return rel_case(query, rel, se->token, NULL, l, NULL, f);
3669 } else if (l->h->type == type_list) {
3670 dlist *when_search_list = l->h->data.lval;
3671 symbol *opt_else = l->h->next->data.sym;
3672
3673 return rel_case(query, rel, SQL_CASE, NULL, when_search_list, opt_else, f);
3674 } else {
3675 symbol *scalar_exp = l->h->data.sym;
3676 dlist *when_value_list = l->h->next->data.lval;
3677 symbol *opt_else = l->h->next->next->data.sym;
3678
3679 return rel_case(query, rel, SQL_CASE, scalar_exp, when_value_list, opt_else, f);
3680 }
3681}
3682
3683static sql_exp *
3684rel_cast(sql_query *query, sql_rel **rel, symbol *se, int f)
3685{
3686 mvc *sql = query->sql;
3687 dlist *dl = se->data.lval;
3688 symbol *s = dl->h->data.sym;
3689 sql_subtype *tpe = &dl->h->next->data.typeval;
3690 exp_kind ek = {type_value, card_column, FALSE};
3691 sql_exp *e = rel_value_exp(query, rel, s, f, ek);
3692
3693 if (!e)
3694 return NULL;
3695 /* strings may need too be truncated */
3696 if (tpe ->type ->localtype == TYPE_str) {
3697 if (tpe->digits > 0) {
3698 sql_subtype *et = exp_subtype(e);
3699 sql_subtype *it = sql_bind_localtype("int");
3700 sql_subfunc *c = sql_bind_func(sql->sa, sql->session->schema, "truncate", et, it, F_FUNC);
3701 if (c)
3702 e = exp_binop(sql->sa, e, exp_atom_int(sql->sa, tpe->digits), c);
3703 }
3704 }
3705 if (e)
3706 e = rel_check_type(sql, tpe, rel ? *rel : NULL, e, type_cast);
3707 if (e)
3708 exp_label(sql->sa, e, ++sql->label);
3709 return e;
3710}
3711
3712static sql_exp *
3713rel_next_value_for( mvc *sql, symbol *se )
3714{
3715 char *seq = qname_table(se->data.lval);
3716 char *sname = qname_schema(se->data.lval);
3717 sql_schema *s = NULL;
3718 sql_subtype t;
3719 sql_subfunc *f;
3720
3721 if (sname && !(s = mvc_bind_schema(sql, sname)))
3722 return sql_error(sql, 02,
3723 SQLSTATE(3F000) "NEXT VALUE FOR: no such schema '%s'", sname);
3724 if (!s)
3725 s = sql->session->schema;
3726
3727 if (!find_sql_sequence(s, seq) && !stack_find_rel_view(sql, seq))
3728 return sql_error(sql, 02, SQLSTATE(42000) "NEXT VALUE FOR: "
3729 "no such sequence '%s'.'%s'", s->base.name, seq);
3730 sql_find_subtype(&t, "varchar", 0, 0);
3731 f = sql_bind_func(sql->sa, s, "next_value_for", &t, &t, F_FUNC);
3732 assert(f);
3733 return exp_binop(sql->sa, exp_atom_str(sql->sa, s->base.name, &t),
3734 exp_atom_str(sql->sa, seq, &t), f);
3735}
3736
3737/* some users like to use aliases already in the groupby */
3738static sql_exp *
3739rel_selection_ref(sql_query *query, sql_rel **rel, symbol *grp, dlist *selection )
3740{
3741 sql_allocator *sa = query->sql->sa;
3742 dnode *n;
3743 dlist *gl = grp->data.lval;
3744 char *name = NULL;
3745 exp_kind ek = {type_value, card_column, FALSE};
3746
3747 if (dlist_length(gl) > 1)
3748 return NULL;
3749 if (!selection)
3750 return NULL;
3751
3752 name = gl->h->data.sval;
3753 for (n = selection->h; n; n = n->next) {
3754 /* we only look for columns */
3755 tokens to = n->data.sym->token;
3756 if (to == SQL_COLUMN || to == SQL_IDENT) {
3757 dlist *l = n->data.sym->data.lval;
3758 /* AS name */
3759 if (l->h->next->data.sval &&
3760 strcmp(l->h->next->data.sval, name) == 0){
3761 sql_exp *ve = rel_value_exp(query, rel, l->h->data.sym, sql_sel, ek);
3762 if (ve) {
3763 dlist *l = dlist_create(sa);
3764 symbol *sym;
3765 exp_setname(sa, ve, NULL, name);
3766 /* now we should rewrite the selection
3767 such that it uses the new group
3768 by column
3769 */
3770 dlist_append_string(sa, l,
3771 sa_strdup(sa, name));
3772 sym = symbol_create_list(sa, to, l);
3773 l = dlist_create(sa);
3774 dlist_append_symbol(sa, l, sym);
3775 /* no alias */
3776 dlist_append_symbol(sa, l, NULL);
3777 n->data.sym = symbol_create_list(sa, to, l);
3778
3779 }
3780 return ve;
3781 }
3782 }
3783 }
3784 return NULL;
3785}
3786
3787static list *
3788rel_group_by(sql_query *query, sql_rel **rel, symbol *groupby, dlist *selection, int f )
3789{
3790 mvc *sql = query->sql;
3791 dnode *o = groupby->data.lval->h;
3792 list *exps = new_exp_list(sql->sa);
3793
3794 for (; o; o = o->next) {
3795 symbol *grp = o->data.sym;
3796 int is_last = 1;
3797 exp_kind ek = {type_value, card_value, TRUE};
3798 sql_exp *e = rel_value_exp2(query, rel, grp, f, ek, &is_last);
3799
3800 if (!e) {
3801 int status = sql->session->status;
3802 char buf[ERRSIZE];
3803
3804 /* reset error */
3805 sql->session->status = 0;
3806 strcpy(buf, sql->errstr);
3807 sql->errstr[0] = '\0';
3808
3809 e = rel_selection_ref(query, rel, grp, selection);
3810 if (!e) {
3811 if (sql->errstr[0] == 0) {
3812 sql->session->status = status;
3813 strcpy(sql->errstr, buf);
3814 }
3815 return NULL;
3816 }
3817 }
3818 if(e->type != e_column) { //store group by expressions in the stack
3819 if(!stack_push_groupby_expression(sql, grp, e))
3820 return NULL;
3821 }
3822 if (e->card > CARD_AGGR)
3823 e->card = CARD_AGGR;
3824 append(exps, e);
3825 }
3826 return exps;
3827}
3828
3829/* find selection expressions matching the order by column expression */
3830
3831/* first limit to simple columns only */
3832static sql_exp *
3833rel_order_by_simple_column_exp(mvc *sql, sql_rel *r, symbol *column_r)
3834{
3835 sql_exp *e = NULL;
3836 dlist *l = column_r->data.lval;
3837
3838 if (!r || !is_project(r->op) || column_r->type == type_int)
3839 return NULL;
3840 assert(column_r->token == SQL_COLUMN && column_r->type == type_list);
3841
3842 r = r->l;
3843 if (!r)
3844 return e;
3845 if (dlist_length(l) == 1) {
3846 char *name = l->h->data.sval;
3847 e = rel_bind_column(sql, r, name, sql_sel | sql_orderby);
3848 }
3849 if (dlist_length(l) == 2) {
3850 char *tname = l->h->data.sval;
3851 char *name = l->h->next->data.sval;
3852
3853 e = rel_bind_column2(sql, r, tname, name, sql_sel | sql_orderby);
3854 }
3855 if (e)
3856 return e;
3857 return sql_error(sql, 02, SQLSTATE(42000) "ORDER BY: absolute column names not supported");
3858}
3859
3860/* second complex columns only */
3861static sql_exp *
3862rel_order_by_column_exp(sql_query *query, sql_rel **R, symbol *column_r)
3863{
3864 mvc *sql = query->sql;
3865 sql_rel *r = *R, *p = NULL;
3866 sql_exp *e = NULL;
3867 exp_kind ek = {type_value, card_column, FALSE};
3868
3869 if (!r)
3870 return e;
3871
3872 if (r && is_simple_project(r->op) && is_processed(r)) {
3873 p = r;
3874 r = r->l;
3875 }
3876
3877 e = rel_value_exp(query, &r, column_r, sql_sel | sql_orderby, ek);
3878
3879 if (r && !p)
3880 *R = r;
3881 else if (r)
3882 p->l = r;
3883 if (e && p) {
3884 e = rel_project_add_exp(sql, p, e);
3885 return e;
3886 }
3887 if (e && r && is_project(r->op)) {
3888 sql_exp * found = exps_find_exp(r->exps, e);
3889
3890 if (!found) {
3891 append(r->exps, e);
3892 } else {
3893 e = found;
3894 }
3895 if (!exp_name(e))
3896 exp_label(sql->sa, e, ++sql->label);
3897 e = exp_ref(sql->sa, e);
3898 }
3899 return e;
3900}
3901
3902static dlist *
3903simple_selection(symbol *sq)
3904{
3905 if (sq->token == SQL_SELECT) {
3906 SelectNode *sn;
3907
3908 sn = (SelectNode *) sq;
3909
3910 if (!sn->from && !sn->where && !sn->distinct && !sn->window && dlist_length(sn->selection) == 1)
3911 return sn->selection;
3912 }
3913 return NULL;
3914}
3915
3916static list *
3917rel_order_by(sql_query *query, sql_rel **R, symbol *orderby, int f )
3918{
3919 mvc *sql = query->sql;
3920 sql_rel *rel = *R;
3921 sql_rel *or = rel; // the order by relation
3922 list *exps = new_exp_list(sql->sa);
3923 dnode *o = orderby->data.lval->h;
3924 dlist *selection = NULL;
3925
3926 if (is_sql_orderby(f)) {
3927 assert(is_project(rel->op));
3928 rel = rel->l;
3929 }
3930
3931 for (; o; o = o->next) {
3932 symbol *order = o->data.sym;
3933
3934 if (order->token == SQL_COLUMN || order->token == SQL_IDENT) {
3935 symbol *col = order->data.lval->h->data.sym;
3936 int direction = order->data.lval->h->next->data.i_val;
3937 sql_exp *e = NULL;
3938
3939 assert(order->data.lval->h->next->type == type_int);
3940 if ((selection = simple_selection(col)) != NULL) {
3941 dnode *o = selection->h;
3942 order = o->data.sym;
3943 col = order->data.lval->h->data.sym;
3944 /* remove optional name from selection */
3945 order->data.lval->h->next = NULL;
3946 }
3947 if (col->token == SQL_COLUMN || col->token == SQL_IDENT || col->token == SQL_ATOM) {
3948 int is_last = 0;
3949 exp_kind ek = {type_value, card_column, FALSE};
3950
3951 e = rel_value_exp2(query, &rel, col, f, ek, &is_last);
3952
3953 if (e && e->card <= CARD_ATOM) {
3954 sql_subtype *tpe = &e->tpe;
3955 /* integer atom on the stack */
3956 if (e->type == e_atom &&
3957 tpe->type->eclass == EC_NUM) {
3958 atom *a = e->l?e->l:sql->args[e->flag];
3959 int nr = (int)atom_get_int(a);
3960
3961 e = exps_get_exp(rel->exps, nr);
3962 if (!e)
3963 return NULL;
3964 e = exp_ref(sql->sa, e);
3965 /* do not cache this query */
3966 if (e)
3967 scanner_reset_key(&sql->scanner);
3968 } else if (e->type == e_atom) {
3969 return sql_error(sql, 02, SQLSTATE(42000) "order not of type SQL_COLUMN");
3970 }
3971 } else if (e && exp_card(e) > rel->card) {
3972 if (e && exp_name(e)) {
3973 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(e));
3974 } else {
3975 return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
3976 }
3977 }
3978 if (e && rel && is_project(rel->op)) {
3979 sql_exp * found = exps_find_exp(rel->exps, e);
3980
3981 if (!found) {
3982 append(rel->exps, e);
3983 } else {
3984 e = found;
3985 }
3986 e = exp_ref(sql->sa, e);
3987 }
3988 }
3989
3990 if (!e && sql->session->status != -ERR_AMBIGUOUS && (col->token == SQL_COLUMN || col->token == SQL_IDENT)) {
3991 /* reset error */
3992 sql->session->status = 0;
3993 sql->errstr[0] = '\0';
3994
3995 e = rel_order_by_simple_column_exp(sql, rel, col);
3996 if (e && e->card > rel->card)
3997 e = NULL;
3998 if (e)
3999 e = rel_project_add_exp(sql, rel, e);
4000 }
4001 if (rel && !e && sql->session->status != -ERR_AMBIGUOUS) {
4002 /* reset error */
4003 sql->session->status = 0;
4004 sql->errstr[0] = '\0';
4005
4006 if (!e)
4007 e = rel_order_by_column_exp(query, &rel, col);
4008 if (e && e->card > rel->card && e->card != CARD_ATOM)
4009 e = NULL;
4010 }
4011 if (!e)
4012 return NULL;
4013 set_direction(e, direction);
4014 append(exps, e);
4015 } else {
4016 return sql_error(sql, 02, SQLSTATE(42000) "order not of type SQL_COLUMN");
4017 }
4018 }
4019 if (is_sql_orderby(f) && or != rel)
4020 or->l = rel;
4021 if (is_sql_window(f))
4022 *R = rel;
4023 return exps;
4024}
4025
4026static int
4027generate_window_bound(tokens sql_token, bool first_half)
4028{
4029 switch(sql_token) {
4030 case SQL_PRECEDING:
4031 return first_half ? BOUND_FIRST_HALF_PRECEDING : BOUND_SECOND_HALF_PRECEDING;
4032 case SQL_FOLLOWING:
4033 return first_half ? BOUND_FIRST_HALF_FOLLOWING : BOUND_SECOND_HALF_FOLLOWING;
4034 case SQL_CURRENT_ROW:
4035 return first_half ? CURRENT_ROW_PRECEDING : CURRENT_ROW_FOLLOWING;
4036 default:
4037 assert(0);
4038 }
4039 return 0;
4040}
4041
4042/* window functions */
4043static sql_exp*
4044generate_window_bound_call(mvc *sql, sql_exp **estart, sql_exp **eend, sql_schema *s, sql_exp *pe, sql_exp *e,
4045 sql_exp *start, sql_exp *fend, int frame_type, int excl, tokens t1, tokens t2)
4046{
4047 list *rargs1 = sa_list(sql->sa), *rargs2 = sa_list(sql->sa), *targs1 = sa_list(sql->sa), *targs2 = sa_list(sql->sa);
4048 sql_subfunc *dc1, *dc2;
4049 sql_subtype *it = sql_bind_localtype("int");
4050
4051 if(pe) {
4052 append(targs1, exp_subtype(pe));
4053 append(targs2, exp_subtype(pe));
4054 append(rargs1, exp_copy(sql, pe));
4055 append(rargs2, exp_copy(sql, pe));
4056 }
4057 append(rargs1, exp_copy(sql, e));
4058 append(rargs2, exp_copy(sql, e));
4059 append(targs1, exp_subtype(e));
4060 append(targs2, exp_subtype(e));
4061 append(targs1, it);
4062 append(targs2, it);
4063 append(targs1, it);
4064 append(targs2, it);
4065 append(targs1, it);
4066 append(targs2, it);
4067 append(targs1, exp_subtype(start));
4068 append(targs2, exp_subtype(fend));
4069
4070 dc1 = sql_bind_func_(sql->sa, s, "window_bound", targs1, F_ANALYTIC);
4071 dc2 = sql_bind_func_(sql->sa, s, "window_bound", targs2, F_ANALYTIC);
4072 if (!dc1 || !dc2)
4073 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: function 'window_bound' not found");
4074 append(rargs1, exp_atom_int(sql->sa, frame_type));
4075 append(rargs2, exp_atom_int(sql->sa, frame_type));
4076 append(rargs1, exp_atom_int(sql->sa, generate_window_bound(t1, true)));
4077 append(rargs2, exp_atom_int(sql->sa, generate_window_bound(t2, false)));
4078 append(rargs1, exp_atom_int(sql->sa, excl));
4079 append(rargs2, exp_atom_int(sql->sa, excl));
4080 append(rargs1, start);
4081 append(rargs2, fend);
4082
4083 *estart = exp_op(sql->sa, rargs1, dc1);
4084 *eend = exp_op(sql->sa, rargs2, dc2);
4085 return e; //return something to say there were no errors
4086}
4087
4088static sql_exp*
4089calculate_window_bound(sql_query *query, sql_rel *p, tokens token, symbol *bound, sql_exp *ie, int frame_type, int f)
4090{
4091 mvc *sql = query->sql;
4092 sql_subtype *bt, *it = sql_bind_localtype("int"), *lon = sql_bind_localtype("lng"), *iet;
4093 sql_class bclass = EC_ANY;
4094 sql_exp *res = NULL;
4095
4096 if((bound->token == SQL_PRECEDING || bound->token == SQL_FOLLOWING || bound->token == SQL_CURRENT_ROW) && bound->type == type_int) {
4097 atom *a = NULL;
4098 bt = (frame_type == FRAME_ROWS || frame_type == FRAME_GROUPS) ? lon : exp_subtype(ie);
4099 bclass = bt->type->eclass;
4100
4101 if((bound->data.i_val == UNBOUNDED_PRECEDING_BOUND || bound->data.i_val == UNBOUNDED_FOLLOWING_BOUND)) {
4102 if(EC_NUMBER(bclass))
4103 a = atom_general(sql->sa, bt, NULL);
4104 else
4105 a = atom_general(sql->sa, it, NULL);
4106 } else if(bound->data.i_val == CURRENT_ROW_BOUND) {
4107 if(EC_NUMBER(bclass))
4108 a = atom_zero_value(sql->sa, bt);
4109 else
4110 a = atom_zero_value(sql->sa, it);
4111 } else {
4112 assert(0);
4113 }
4114 res = exp_atom(sql->sa, a);
4115 } else { //arbitrary expression case
4116 int is_last = 0;
4117 exp_kind ek = {type_value, card_column, FALSE};
4118 const char* bound_desc = (token == SQL_PRECEDING) ? "PRECEDING" : "FOLLOWING";
4119 iet = exp_subtype(ie);
4120
4121 assert(token == SQL_PRECEDING || token == SQL_FOLLOWING);
4122 if (bound->token == SQL_NULL ||
4123 (bound->token == SQL_IDENT &&
4124 bound->data.lval->h->type == type_int &&
4125 sql->args[bound->data.lval->h->data.i_val]->isnull)) {
4126 return sql_error(sql, 02, SQLSTATE(42000) "%s offset must not be NULL", bound_desc);
4127 }
4128 res = rel_value_exp2(query, &p, bound, f, ek, &is_last);
4129 if(!res)
4130 return NULL;
4131 bt = exp_subtype(res);
4132 if(bt)
4133 bclass = bt->type->eclass;
4134 if(!bt || !(bclass == EC_NUM || EC_INTERVAL(bclass) || bclass == EC_DEC || bclass == EC_FLT))
4135 return sql_error(sql, 02, SQLSTATE(42000) "%s offset must be of a countable SQL type", bound_desc);
4136 if((frame_type == FRAME_ROWS || frame_type == FRAME_GROUPS) && bclass != EC_NUM) {
4137 char *err = subtype2string(bt);
4138 if(!err)
4139 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
4140 (void) sql_error(sql, 02, SQLSTATE(42000) "Values on %s boundary on %s frame can't be %s type", bound_desc,
4141 (frame_type == FRAME_ROWS) ? "rows":"groups", err);
4142 _DELETE(err);
4143 return NULL;
4144 }
4145 if(frame_type == FRAME_RANGE) {
4146 if(bclass == EC_FLT && iet->type->eclass != EC_FLT)
4147 return sql_error(sql, 02, SQLSTATE(42000) "Values in input aren't floating-point while on %s boundary are", bound_desc);
4148 if(bclass != EC_FLT && iet->type->eclass == EC_FLT)
4149 return sql_error(sql, 02, SQLSTATE(42000) "Values on %s boundary aren't floating-point while on input are", bound_desc);
4150 if(bclass == EC_DEC && iet->type->eclass != EC_DEC)
4151 return sql_error(sql, 02, SQLSTATE(42000) "Values in input aren't decimals while on %s boundary are", bound_desc);
4152 if(bclass != EC_DEC && iet->type->eclass == EC_DEC)
4153 return sql_error(sql, 02, SQLSTATE(42000) "Values on %s boundary aren't decimals while on input are", bound_desc);
4154 if(bclass != EC_SEC && iet->type->eclass == EC_TIME) {
4155 char *err = subtype2string(iet);
4156 if(!err)
4157 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
4158 (void) sql_error(sql, 02, SQLSTATE(42000) "For %s input the %s boundary must be an interval type up to the day", err, bound_desc);
4159 _DELETE(err);
4160 return NULL;
4161 }
4162 if(EC_INTERVAL(bclass) && !EC_TEMP(iet->type->eclass)) {
4163 char *err = subtype2string(iet);
4164 if(!err)
4165 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
4166 (void) sql_error(sql, 02, SQLSTATE(42000) "For %s input the %s boundary must be an interval type", err, bound_desc);
4167 _DELETE(err);
4168 return NULL;
4169 }
4170 }
4171 }
4172 return res;
4173}
4174
4175static dlist*
4176get_window_clauses(mvc *sql, char* ident, symbol **partition_by_clause, symbol **order_by_clause, symbol **frame_clause)
4177{
4178 dlist *window_specification = NULL;
4179 char *window_ident;
4180 int pos;
4181
4182 if (THRhighwater())
4183 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
4184
4185 if((window_specification = stack_get_window_def(sql, ident, &pos)) == NULL)
4186 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: window '%s' not found", ident);
4187
4188 //avoid infinite lookups
4189 if(stack_check_var_visited(sql, pos))
4190 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: cyclic references to window '%s' found", ident);
4191 stack_set_var_visited(sql, pos);
4192
4193 if(window_specification->h->next->data.sym) {
4194 if(*partition_by_clause)
4195 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: redefinition of PARTITION BY clause from window '%s'", ident);
4196 *partition_by_clause = window_specification->h->next->data.sym;
4197 }
4198 if(window_specification->h->next->next->data.sym) {
4199 if(*order_by_clause)
4200 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: redefinition of ORDER BY clause from window '%s'", ident);
4201 *order_by_clause = window_specification->h->next->next->data.sym;
4202 }
4203 if(window_specification->h->next->next->next->data.sym) {
4204 if(*frame_clause)
4205 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: redefinition of frame clause from window '%s'", ident);
4206 *frame_clause = window_specification->h->next->next->next->data.sym;
4207 }
4208
4209 window_ident = window_specification->h->data.sval;
4210 if(window_ident && !get_window_clauses(sql, window_ident, partition_by_clause, order_by_clause, frame_clause))
4211 return NULL; //the error was already set
4212
4213 return window_specification; //return something to say there were no errors
4214}
4215
4216/*
4217 * select x, y, rank_op() over (partition by x order by y) as, ...
4218 aggr_op(z) over (partition by y order by x) as, ...
4219 * from table [x,y,z,w,v]
4220 *
4221 * project and order by over x,y / y,x
4222 * a = project( table ) [ x, y, z, w, v ], [ x, y]
4223 * b = project( table ) [ x, y, z, w, v ], [ y, x]
4224 *
4225 * project with order dependent operators, ie combined prev/current value
4226 * aa = project (a) [ x, y, r = rank_op(diff(x) (marks a new partition), rediff(diff(x), y) (marks diff value with in partition)), z, w, v ]
4227 * project(aa) [ aa.x, aa.y, aa.r ] -- only keep current output list
4228 * bb = project (b) [ x, y, a = aggr_op(z, diff(y), rediff(diff(y), x)), z, w, v ]
4229 * project(bb) [ bb.x, bb.y, bb.a ] -- only keep current output list
4230 */
4231static sql_exp *
4232rel_rankop(sql_query *query, sql_rel **rel, symbol *se, int f)
4233{
4234 mvc *sql = query->sql;
4235 node *n;
4236 dlist *l = se->data.lval, *window_specification = NULL;
4237 symbol *window_function = l->h->data.sym, *partition_by_clause = NULL, *order_by_clause = NULL, *frame_clause = NULL;
4238 char *aname = NULL, *sname = NULL, *window_ident = NULL;
4239 sql_subfunc *wf = NULL;
4240 sql_exp *in = NULL, *pe = NULL, *oe = NULL, *call = NULL, *start = NULL, *eend = NULL, *fstart = NULL, *fend = NULL;
4241 sql_rel *p;
4242 list *gbe = NULL, *obe = NULL, *args = NULL, *types = NULL, *fargs = NULL;
4243 sql_schema *s = sql->session->schema;
4244 dnode *dn = window_function->data.lval->h;
4245 int distinct = 0, is_last, frame_type, pos, nf = f;
4246 bool is_nth_value, supports_frames;
4247
4248 stack_clear_frame_visited_flag(sql); //clear visited flags before iterating
4249
4250 if (l->h->next->type == type_list) {
4251 window_specification = l->h->next->data.lval;
4252 } else if (l->h->next->type == type_string) {
4253 const char* window_alias = l->h->next->data.sval;
4254 if((window_specification = stack_get_window_def(sql, window_alias, &pos)) == NULL)
4255 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: window '%s' not found", window_alias);
4256 stack_set_var_visited(sql, pos);
4257 } else {
4258 assert(0);
4259 }
4260
4261 window_ident = window_specification->h->data.sval;
4262 partition_by_clause = window_specification->h->next->data.sym;
4263 order_by_clause = window_specification->h->next->next->data.sym;
4264 frame_clause = window_specification->h->next->next->next->data.sym;
4265
4266 if (window_ident && !get_window_clauses(sql, window_ident, &partition_by_clause, &order_by_clause, &frame_clause))
4267 return NULL;
4268
4269 frame_type = order_by_clause ? FRAME_RANGE : FRAME_ROWS;
4270 aname = qname_fname(dn->data.lval);
4271 sname = qname_schema(dn->data.lval);
4272
4273 if (sname)
4274 s = mvc_bind_schema(sql, sname);
4275
4276 is_nth_value = (strcmp(s->base.name, "sys") == 0 && strcmp(aname, "nth_value") == 0);
4277 supports_frames = (window_function->token != SQL_RANK) || is_nth_value ||
4278 (strcmp(s->base.name, "sys") == 0 && ((strcmp(aname, "first_value") == 0) || strcmp(aname, "last_value") == 0));
4279
4280 if (is_sql_join(f) || is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f)) {
4281 char *uaname = GDKmalloc(strlen(aname) + 1);
4282 const char *clause = is_sql_join(f)?"JOIN conditions":is_sql_where(f)?"WHERE clause":is_sql_groupby(f)?"GROUP BY clause":"HAVING clause";
4283 (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window function '%s' not allowed in %s",
4284 uaname ? toUpperCopy(uaname, aname) : aname, aname, clause);
4285 if (uaname)
4286 GDKfree(uaname);
4287 return NULL;
4288 } else if (is_sql_window(f)) {
4289 char *uaname = GDKmalloc(strlen(aname) + 1);
4290 (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window functions cannot be nested",
4291 uaname ? toUpperCopy(uaname, aname) : aname);
4292 if (uaname)
4293 GDKfree(uaname);
4294 return NULL;
4295 }
4296
4297 /* window operations are only allowed in the projection */
4298 if (!is_sql_sel(f))
4299 return sql_error(sql, 02, SQLSTATE(42000) "OVER: only possible within the selection");
4300
4301 p = *rel;
4302 /* Partition By */
4303 if (partition_by_clause) {
4304 gbe = rel_group_by(query, &p, partition_by_clause, NULL /* cannot use (selection) column references, as this result is a selection column */, nf | sql_window);
4305 if (!gbe)
4306 return NULL;
4307 for (n = gbe->h ; n ; n = n->next) {
4308 sql_exp *en = n->data;
4309
4310 set_direction(en, 1);
4311 }
4312 }
4313
4314 /* Order By */
4315 if (order_by_clause) {
4316 obe = rel_order_by(query, &p, order_by_clause, nf | sql_window);
4317 if (!obe)
4318 return NULL;
4319 for (n = obe->h ; n ; n = n->next) {
4320 sql_exp *oexp = n->data, *nexp = oexp;
4321
4322 if (is_ascending(oexp))
4323 set_direction(nexp, 1);
4324 if (nulls_last(oexp))
4325 set_direction(nexp, 2);
4326 }
4327 }
4328
4329 fargs = sa_list(sql->sa);
4330 if (window_function->token == SQL_RANK) { //rank function call
4331 dlist* dnn = window_function->data.lval->h->next->data.lval;
4332 bool is_ntile = (strcmp(s->base.name, "sys") == 0 && strcmp(aname, "ntile") == 0),
4333 is_lag = (strcmp(s->base.name, "sys") == 0 && strcmp(aname, "lag") == 0),
4334 is_lead = (strcmp(s->base.name, "sys") == 0 && strcmp(aname, "lead") == 0);
4335 int nfargs = 0;
4336
4337 if (!dnn || is_ntile) { //pass an input column for analytic functions that don't require it
4338 in = rel_first_column(sql, p);
4339 if (is_atom(in->type)) {
4340 in = exp_copy(sql, in);
4341 } else {
4342 if (!exp_name(in))
4343 exp_label(sql->sa, in, ++sql->label);
4344 in = exp_ref(sql->sa, in);
4345 }
4346 if(!in)
4347 return NULL;
4348 append(fargs, in);
4349 nfargs++;
4350 }
4351 if (dnn) {
4352 for(dnode *nn = dnn->h ; nn ; nn = nn->next) {
4353 is_last = 0;
4354 exp_kind ek = {type_value, card_column, FALSE};
4355 in = rel_value_exp2(query, &p, nn->data.sym, f | sql_window, ek, &is_last);
4356 if(!in)
4357 return NULL;
4358 if(is_ntile && nfargs == 1) { //ntile first argument null handling case
4359 sql_subtype *empty = sql_bind_localtype("void");
4360 if(subtype_cmp(&(in->tpe), empty) == 0) {
4361 sql_subtype *to = sql_bind_localtype("bte");
4362 in = exp_convert(sql->sa, in, empty, to);
4363 }
4364 } else if(is_nth_value && nfargs == 1) { //nth_value second argument null handling case
4365 sql_subtype *empty = sql_bind_localtype("void");
4366 if(subtype_cmp(&(in->tpe), empty) == 0) {
4367 sql_exp *ep = rel_first_column(sql, p);
4368 in = exp_convert(sql->sa, in, empty, &(ep->tpe));
4369 }
4370 } else if((is_lag || is_lead) && nfargs == 2) { //lag and lead 3rd arg must have same type as 1st arg
4371 sql_exp *first = (sql_exp*) fargs->h->data;
4372 if(!(in = rel_check_type(sql, &first->tpe, p, in, type_equal)))
4373 return NULL;
4374 }
4375 append(fargs, in);
4376 nfargs++;
4377 }
4378 }
4379 } else { //aggregation function call
4380 dnode *n = dn->next;
4381
4382 if (n) {
4383 if (!n->next->data.sym) { /* count(*) */
4384 in = rel_first_column(sql, p);
4385 if (!exp_name(in))
4386 exp_label(sql->sa, in, ++sql->label);
4387 in = exp_ref(sql->sa, in);
4388 append(fargs, in);
4389 append(fargs, exp_atom_bool(sql->sa, 0)); //don't ignore nills
4390 } else {
4391 is_last = 0;
4392 exp_kind ek = {type_value, card_column, FALSE};
4393
4394 distinct = n->data.i_val;
4395 /*
4396 * all aggregations implemented in a window have 1 and only 1 argument only, so for now no further
4397 * symbol compilation is required
4398 */
4399 in = rel_value_exp2(query, &p, n->next->data.sym, f | sql_window, ek, &is_last);
4400 if(!in)
4401 return NULL;
4402
4403 append(fargs, in);
4404 if(strcmp(s->base.name, "sys") == 0 && strcmp(aname, "count") == 0) {
4405 sql_subtype *empty = sql_bind_localtype("void"), *bte = sql_bind_localtype("bte");
4406 sql_exp* eo = fargs->h->data;
4407 //corner case, if the argument is null convert it into something countable such as bte
4408 if(subtype_cmp(&(eo->tpe), empty) == 0)
4409 fargs->h->data = exp_convert(sql->sa, eo, empty, bte);
4410 append(fargs, exp_atom_bool(sql->sa, 1)); //ignore nills
4411 }
4412 }
4413 }
4414 }
4415
4416 if (distinct)
4417 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: DISTINCT clause is not implemented for window functions");
4418
4419 /* diff for partitions */
4420 if (gbe) {
4421 sql_subtype *bt = sql_bind_localtype("bit");
4422
4423 for( n = gbe->h; n; n = n->next) {
4424 sql_subfunc *df;
4425 sql_exp *e = n->data;
4426
4427 e = exp_copy(sql, e);
4428 args = sa_list(sql->sa);
4429 if (pe) {
4430 df = bind_func(sql, s, "diff", bt, exp_subtype(e), F_ANALYTIC);
4431 append(args, pe);
4432 } else {
4433 df = bind_func(sql, s, "diff", exp_subtype(e), NULL, F_ANALYTIC);
4434 }
4435 if (!df)
4436 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: function '%s' not found", "diff" );
4437 append(args, e);
4438 pe = exp_op(sql->sa, args, df);
4439 }
4440 } else {
4441 pe = exp_atom_bool(sql->sa, 0);
4442 }
4443 /* diff for orderby */
4444 if (obe) {
4445 sql_subtype *bt = sql_bind_localtype("bit");
4446
4447 for( n = obe->h; n; n = n->next) {
4448 sql_subfunc *df;
4449 sql_exp *e = n->data;
4450
4451 e = exp_copy(sql, e);
4452 args = sa_list(sql->sa);
4453 if (oe) {
4454 df = bind_func(sql, s, "diff", bt, exp_subtype(e), F_ANALYTIC);
4455 append(args, oe);
4456 } else {
4457 df = bind_func(sql, s, "diff", exp_subtype(e), NULL, F_ANALYTIC);
4458 }
4459 if (!df)
4460 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: function '%s' not found", "diff" );
4461 append(args, e);
4462 oe = exp_op(sql->sa, args, df);
4463 }
4464 } else {
4465 oe = exp_atom_bool(sql->sa, 0);
4466 }
4467
4468 /* Frame */
4469 if (frame_clause) {
4470 dnode *d = frame_clause->data.lval->h;
4471 symbol *wstart = d->data.sym, *wend = d->next->data.sym, *rstart = wstart->data.lval->h->data.sym,
4472 *rend = wend->data.lval->h->data.sym;
4473 int excl = d->next->next->next->data.i_val;
4474 frame_type = d->next->next->data.i_val;
4475 sql_exp *ie = obe ? exp_copy(sql, obe->t->data) : in;
4476
4477 if(!supports_frames)
4478 return sql_error(sql, 02, SQLSTATE(42000) "OVER: frame extend only possible with aggregation and first_value, last_value and nth_value functions");
4479 if(!obe && frame_type == FRAME_GROUPS)
4480 return sql_error(sql, 02, SQLSTATE(42000) "GROUPS frame requires an order by expression");
4481 if(wstart->token == SQL_FOLLOWING && wend->token == SQL_PRECEDING)
4482 return sql_error(sql, 02, SQLSTATE(42000) "FOLLOWING offset must come after PRECEDING offset");
4483 if(wstart->token == SQL_CURRENT_ROW && wend->token == SQL_PRECEDING)
4484 return sql_error(sql, 02, SQLSTATE(42000) "CURRENT ROW offset must come after PRECEDING offset");
4485 if(wstart->token == SQL_FOLLOWING && wend->token == SQL_CURRENT_ROW)
4486 return sql_error(sql, 02, SQLSTATE(42000) "FOLLOWING offset must come after CURRENT ROW offset");
4487 if(wstart->token != SQL_CURRENT_ROW && wend->token != SQL_CURRENT_ROW && wstart->token == wend->token &&
4488 (frame_type != FRAME_ROWS && frame_type != FRAME_ALL))
4489 return sql_error(sql, 02, SQLSTATE(42000) "Non-centered windows are only supported in row frames");
4490 if(!obe && frame_type == FRAME_RANGE) {
4491 bool ok_preceding = false, ok_following = false;
4492 if((wstart->token == SQL_PRECEDING || wstart->token == SQL_CURRENT_ROW) &&
4493 (rstart->token == SQL_PRECEDING || rstart->token == SQL_CURRENT_ROW) && rstart->type == type_int &&
4494 (rstart->data.i_val == UNBOUNDED_PRECEDING_BOUND || rstart->data.i_val == CURRENT_ROW_BOUND))
4495 ok_preceding = true;
4496 if((wend->token == SQL_FOLLOWING || wend->token == SQL_CURRENT_ROW) &&
4497 (rend->token == SQL_FOLLOWING || rend->token == SQL_CURRENT_ROW) && rend->type == type_int &&
4498 (rend->data.i_val == UNBOUNDED_FOLLOWING_BOUND || rend->data.i_val == CURRENT_ROW_BOUND))
4499 ok_following = true;
4500 if(!ok_preceding || !ok_following)
4501 return sql_error(sql, 02, SQLSTATE(42000) "RANGE frame with PRECEDING/FOLLOWING offset requires an order by expression");
4502 frame_type = FRAME_ALL; //special case, iterate the entire partition
4503 }
4504
4505 if((fstart = calculate_window_bound(query, p, wstart->token, rstart, ie, frame_type, f | sql_window)) == NULL)
4506 return NULL;
4507 if((fend = calculate_window_bound(query, p, wend->token, rend, ie, frame_type, f | sql_window)) == NULL)
4508 return NULL;
4509 if(generate_window_bound_call(sql, &start, &eend, s, gbe ? pe : NULL, ie, fstart, fend, frame_type, excl,
4510 wstart->token, wend->token) == NULL)
4511 return NULL;
4512 } else if (supports_frames) { //for analytic functions with no frame clause, we use the standard default values
4513 sql_exp *ie = obe ? exp_copy(sql, obe->t->data) : in;
4514 sql_subtype *it = sql_bind_localtype("int"), *lon = sql_bind_localtype("lng"), *bt;
4515 unsigned char sclass;
4516
4517 bt = (frame_type == FRAME_ROWS || frame_type == FRAME_GROUPS) ? lon : exp_subtype(ie);
4518 sclass = bt->type->eclass;
4519 if(sclass == EC_POS || sclass == EC_NUM || sclass == EC_DEC || EC_INTERVAL(sclass)) {
4520 fstart = exp_null(sql->sa, bt);
4521 if(order_by_clause)
4522 fend = exp_atom(sql->sa, atom_zero_value(sql->sa, bt));
4523 else
4524 fend = exp_null(sql->sa, bt);
4525 } else {
4526 fstart = exp_null(sql->sa, it);
4527 if(order_by_clause)
4528 fend = exp_atom(sql->sa, atom_zero_value(sql->sa, it));
4529 else
4530 fend = exp_null(sql->sa, it);
4531 }
4532 if(!obe)
4533 frame_type = FRAME_ALL;
4534
4535 if(generate_window_bound_call(sql, &start, &eend, s, gbe ? pe : NULL, ie, fstart, fend, frame_type, EXCLUDE_NONE,
4536 SQL_PRECEDING, SQL_FOLLOWING) == NULL)
4537 return NULL;
4538 }
4539
4540 if (!pe || !oe)
4541 return NULL;
4542
4543 if (!supports_frames) {
4544 append(fargs, pe);
4545 append(fargs, oe);
4546 }
4547
4548 types = exp_types(sql->sa, fargs);
4549 wf = bind_func_(sql, s, aname, types, F_ANALYTIC);
4550 if (!wf) {
4551 wf = sql_find_func_by_name(sql->sa, NULL, aname, list_length(types), F_ANALYTIC);
4552 if (wf) {
4553 node *op = wf->func->ops->h;
4554 list *nexps = sa_list(sql->sa);
4555
4556 for (n = fargs->h ; wf && op && n; op = op->next, n = n->next ) {
4557 sql_arg *arg = op->data;
4558 sql_exp *e = n->data;
4559
4560 e = rel_check_type(sql, &arg->type, NULL, e, type_equal);
4561 if (!e) {
4562 wf = NULL;
4563 break;
4564 }
4565 list_append(nexps, e);
4566 }
4567 if (wf && list_length(nexps))
4568 fargs = nexps;
4569 else
4570 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: function '%s' not found", aname );
4571 } else {
4572 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: function '%s' not found", aname );
4573 }
4574 }
4575 args = sa_list(sql->sa);
4576 for(node *nn = fargs->h ; nn ; nn = nn->next)
4577 append(args, (sql_exp*) nn->data);
4578 if (supports_frames) {
4579 append(args, start);
4580 append(args, eend);
4581 }
4582 call = exp_rank_op(sql->sa, args, gbe, obe, wf);
4583 *rel = p;
4584 return call;
4585}
4586
4587sql_exp *
4588rel_value_exp2(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek, int *is_last)
4589{
4590 mvc *sql = query->sql;
4591 (void)is_last;
4592 if (!se)
4593 return NULL;
4594
4595 if (THRhighwater())
4596 return sql_error(sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
4597
4598 if (rel && *rel && (*rel)->card <= CARD_AGGR) { //group by expression case, handle it before
4599 sql_exp *exp = stack_get_groupby_expression(sql, se);
4600 if (sql->errstr[0] != '\0')
4601 return NULL;
4602 if (exp) {
4603 sql_exp *res;
4604
4605 if (!exp_name(exp))
4606 exp_label(sql->sa, exp, ++sql->label);
4607 res = exp_ref(sql->sa, exp);
4608 res->card = (*rel)->card;
4609 if(se->token == SQL_AGGR) {
4610 dlist *l = se->data.lval;
4611 int distinct = l->h->next->data.i_val;
4612 if (distinct)
4613 set_distinct(res);
4614 }
4615 if (!query_has_outer(query) && is_groupby((*rel)->op))
4616 res = rel_groupby_add_aggr(sql, *rel, res);
4617 return res;
4618 }
4619 }
4620
4621 switch (se->token) {
4622 case SQL_OP:
4623 return rel_op(sql, se, ek);
4624 case SQL_UNOP:
4625 return rel_unop(query, rel, se, f, ek);
4626 case SQL_BINOP:
4627 return rel_binop(query, rel, se, f, ek);
4628 case SQL_NOP:
4629 return rel_nop(query, rel, se, f, ek);
4630 case SQL_AGGR:
4631 return rel_aggr(query, rel, se, f);
4632 case SQL_RANK:
4633 return rel_rankop(query, rel, se, f);
4634 case SQL_IDENT:
4635 case SQL_COLUMN:
4636 return rel_column_ref(query, rel, se, f );
4637 case SQL_NAME:
4638 return rel_var_ref(sql, se->data.sval, 1);
4639 case SQL_WITH:
4640 case SQL_SELECT: {
4641 sql_rel *r;
4642
4643 if (se->token == SQL_WITH) {
4644 r = rel_with_query(query, se);
4645 } else {
4646 if (rel && *rel)
4647 query_push_outer(query, *rel, f);
4648 r = rel_subquery(query, NULL, se, ek);
4649 if (rel && *rel)
4650 *rel = query_pop_outer(query);
4651 if (!r)
4652 return NULL;
4653 }
4654 if (ek.card <= card_set && is_project(r->op) && list_length(r->exps) > 1)
4655 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: subquery must return only one column");
4656 if (list_length(r->exps) == 1) { /* for now don't rename multi attribute results */
4657 sql_exp *e = lastexp(r);
4658 if (!has_label(e))
4659 exp_label(sql->sa, e, ++sql->label);
4660 if (ek.card < card_set && r->card > CARD_ATOM) {
4661 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(e));
4662
4663 e = exp_ref(sql->sa, e);
4664 e = exp_aggr1(sql->sa, e, zero_or_one, 0, 0, CARD_ATOM, has_nil(e));
4665 r = rel_groupby(sql, r, NULL);
4666 (void)rel_groupby_add_aggr(sql, r, e);
4667 }
4668 }
4669 return exp_rel(sql, r);
4670 }
4671 case SQL_TABLE: {
4672 /* turn a subquery into a tabular result */
4673 *rel = rel_selects(query, se->data.sym);
4674 if (*rel)
4675 return lastexp(*rel);
4676 return NULL;
4677 }
4678 case SQL_PARAMETER:{
4679 if (sql->emode != m_prepare)
4680 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: parameters ('?') not allowed in normal queries, use PREPARE");
4681 assert(se->type == type_int);
4682 return exp_atom_ref(sql->sa, se->data.i_val, NULL);
4683 }
4684 case SQL_NULL:
4685 return exp_null(sql->sa, sql_bind_localtype("void"));
4686 case SQL_ATOM:{
4687 AtomNode *an = (AtomNode *) se;
4688
4689 if (!an || !an->a) {
4690 return exp_null(sql->sa, sql_bind_localtype("void"));
4691 } else {
4692 return exp_atom(sql->sa, atom_dup(sql->sa, an->a));
4693 }
4694 }
4695 case SQL_NEXT:
4696 return rel_next_value_for(sql, se);
4697 case SQL_CAST:
4698 return rel_cast(query, rel, se, f);
4699 case SQL_CASE:
4700 case SQL_COALESCE:
4701 case SQL_NULLIF:
4702 return rel_case_exp(query, rel, se, f);
4703 case SQL_DEFAULT:
4704 return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not allowed outside insert and update statements");
4705 case SQL_XMLELEMENT:
4706 case SQL_XMLFOREST:
4707 case SQL_XMLCOMMENT:
4708 case SQL_XMLATTRIBUTE:
4709 case SQL_XMLCONCAT:
4710 case SQL_XMLDOCUMENT:
4711 case SQL_XMLPI:
4712 case SQL_XMLTEXT:
4713 return rel_xml(query, rel, se, f, ek);
4714 default:
4715 return rel_logical_value_exp(query, rel, se, f);
4716 }
4717}
4718
4719sql_exp *
4720rel_value_exp(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek)
4721{
4722 int is_last = 0;
4723 sql_exp *e;
4724 if (!se)
4725 return NULL;
4726
4727 if (THRhighwater())
4728 return sql_error(query->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space");
4729
4730 e = rel_value_exp2(query, rel, se, f, ek, &is_last);
4731 if (e && (se->token == SQL_SELECT || se->token == SQL_TABLE) && !is_last && !exp_is_rel(e)) {
4732 assert(*rel);
4733 return rel_lastexp(query->sql, *rel);
4734 }
4735 return e;
4736}
4737
4738static sql_exp *
4739column_exp(sql_query *query, sql_rel **rel, symbol *column_e, int f)
4740{
4741 dlist *l = column_e->data.lval;
4742 exp_kind ek = {type_value, card_column, FALSE};
4743 sql_exp *ve;
4744
4745 if (f == sql_sel && rel && *rel && (*rel)->card < CARD_AGGR)
4746 ek.card = card_value;
4747 ve = rel_value_exp(query, rel, l->h->data.sym, f, ek);
4748 if (!ve)
4749 return NULL;
4750 /* AS name */
4751 if (ve && l->h->next->data.sval)
4752 exp_setname(query->sql->sa, ve, NULL, l->h->next->data.sval);
4753 return ve;
4754}
4755
4756static list *
4757rel_table_exp(sql_query *query, sql_rel **rel, symbol *column_e )
4758{
4759 mvc *sql = query->sql;
4760 if (column_e->token == SQL_TABLE && column_e->data.lval->h->type == type_symbol) {
4761 sql_rel *r;
4762
4763 if (!is_project((*rel)->op))
4764 return NULL;
4765 r = rel_named_table_function( query, (*rel)->l, column_e, 0);
4766
4767 if (!r)
4768 return NULL;
4769 *rel = r;
4770 return sa_list(sql->sa);
4771 } else if (column_e->token == SQL_TABLE) {
4772 char *tname = column_e->data.lval->h->data.sval;
4773 list *exps;
4774
4775 if ((exps = rel_table_projections(sql, *rel, tname, 0)) != NULL && !list_empty(exps))
4776 return exps;
4777 if (!tname)
4778 return sql_error(sql, 02,
4779 SQLSTATE(42000) "Table expression without table name");
4780 return sql_error(sql, 02,
4781 SQLSTATE(42000) "Column expression Table '%s' unknown", tname);
4782 }
4783 return NULL;
4784}
4785
4786sql_exp *
4787rel_column_exp(sql_query *query, sql_rel **rel, symbol *column_e, int f)
4788{
4789 if (column_e->token == SQL_COLUMN || column_e->token == SQL_IDENT) {
4790 return column_exp(query, rel, column_e, f);
4791 }
4792 return NULL;
4793}
4794
4795static sql_rel*
4796rel_where_groupby_nodes(sql_query *query, sql_rel *rel, SelectNode *sn)
4797{
4798 mvc *sql = query->sql;
4799
4800 if (sn->where) {
4801 rel = rel_logical_exp(query, rel, sn->where, sql_where);
4802 if (!rel) {
4803 if (sql->errstr[0] == 0)
4804 return sql_error(sql, 02, SQLSTATE(42000) "Subquery result missing");
4805 return NULL;
4806 }
4807 }
4808
4809 if (rel && sn->groupby) {
4810 list *gbe = rel_group_by(query, &rel, sn->groupby, sn->selection, sql_sel | sql_groupby);
4811 if (!gbe)
4812 return NULL;
4813 rel = rel_groupby(sql, rel, gbe);
4814 }
4815
4816 if (rel && sn->having) {
4817 /* having implies group by, ie if not supplied do a group by */
4818 if (rel->op != op_groupby)
4819 rel = rel_groupby(sql, rel, NULL);
4820 }
4821
4822 return rel;
4823}
4824
4825static sql_rel*
4826rel_having_limits_nodes(sql_query *query, sql_rel *rel, SelectNode *sn, exp_kind ek)
4827{
4828 mvc *sql = query->sql;
4829
4830 if (sn->having) {
4831 sql_rel *inner = NULL;
4832 int single_value = 1;
4833
4834 if (is_project(rel->op) && rel->l) {
4835 inner = rel->l;
4836 single_value = 0;
4837 }
4838
4839 if (inner && inner->op == op_groupby)
4840 set_processed(inner);
4841 inner = rel_logical_exp(query, inner, sn->having, sql_having);
4842
4843 if (!inner)
4844 return NULL;
4845 if (inner->exps && exps_card(inner->exps) > CARD_AGGR)
4846 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: cannot compare sets with values, probably an aggregate function missing");
4847 if (!single_value)
4848 rel->l = inner;
4849 }
4850
4851 if (rel && sn->distinct)
4852 rel = rel_distinct(rel);
4853
4854 if (rel && sn->orderby) {
4855 list *obe = NULL;
4856 sql_rel *sel = NULL, *l = rel->l;
4857
4858 /* project( select ) */
4859 if (sn->having && is_select(l->op)) {
4860 sel = l;
4861 rel->l = l->l;
4862 }
4863 rel = rel_orderby(sql, rel);
4864 set_processed(rel);
4865 obe = rel_order_by(query, &rel, sn->orderby, sql_orderby);
4866 if (!obe)
4867 return NULL;
4868 rel->r = obe;
4869 if (sel) {
4870 sql_rel *o = rel, *p = o->l;
4871 p->l = sel;
4872 }
4873 }
4874 if (!rel)
4875 return NULL;
4876
4877 if (sn->limit || sn->offset) {
4878 sql_subtype *lng = sql_bind_localtype("lng");
4879 list *exps = new_exp_list(sql->sa);
4880
4881 if (sn->limit) {
4882 sql_exp *l = rel_value_exp(query, NULL, sn->limit, 0, ek);
4883
4884 if (!l || !(l=rel_check_type(sql, lng, NULL, l, type_equal)))
4885 return NULL;
4886 if ((ek.card != card_relation && sn->limit) &&
4887 (ek.card == card_value && sn->limit)) {
4888 sql_subaggr *zero_or_one = sql_bind_aggr(sql->sa, sql->session->schema, "zero_or_one", exp_subtype(l));
4889 l = exp_aggr1(sql->sa, l, zero_or_one, 0, 0, CARD_ATOM, has_nil(l));
4890 }
4891 append(exps, l);
4892 } else
4893 append(exps, NULL);
4894 if (sn->offset) {
4895 sql_exp *o = rel_value_exp( query, NULL, sn->offset, 0, ek);
4896 if (!o || !(o=rel_check_type(sql, lng, NULL, o, type_equal)))
4897 return NULL;
4898 append(exps, o);
4899 }
4900 rel = rel_topn(sql->sa, rel, exps);
4901 }
4902
4903 if (sn->sample) {
4904 list *exps = new_exp_list(sql->sa);
4905 dlist* sample_parameters = sn->sample->data.lval;
4906 sql_exp *sample_size = rel_value_exp(query, NULL, sample_parameters->h->data.sym, 0, ek);
4907 if (!sample_size)
4908 return NULL;
4909 append(exps, sample_size);
4910
4911 if (sample_parameters->cnt == 2) {
4912 sql_exp *seed_value = rel_value_exp(query, NULL, sample_parameters->h->next->data.sym, 0, ek);
4913 if (!seed_value)
4914 return NULL;
4915 append(exps, seed_value);
4916 }
4917
4918 rel = rel_sample(sql->sa, rel, exps);
4919 }
4920
4921 if (rel)
4922 set_processed(rel);
4923 return rel;
4924}
4925
4926static sql_rel *
4927join_on_column_name(sql_query *query, sql_rel *rel, sql_rel *t1, sql_rel *t2, int op, int l_nil, int r_nil)
4928{
4929 mvc *sql = query->sql;
4930 int nr = ++sql->label, found = 0, full = (op != op_join);
4931 char name[16], *nme;
4932 list *exps = rel_projections(sql, t1, NULL, 1, 0);
4933 list *r_exps = rel_projections(sql, t2, NULL, 1, 0);
4934 list *outexps = new_exp_list(sql->sa);
4935 node *n;
4936
4937 nme = number2name(name, sizeof(name), nr);
4938 if (!exps)
4939 return NULL;
4940 for (n = exps->h; n; n = n->next) {
4941 sql_exp *le = n->data;
4942 const char *nm = exp_name(le);
4943 sql_exp *re = exps_bind_column(r_exps, nm, NULL);
4944
4945 if (re) {
4946 found = 1;
4947 rel = rel_compare_exp(query, rel, le, re, "=", NULL, TRUE, 0, 0);
4948 if (full) {
4949 sql_exp *cond = rel_unop_(sql, rel, le, NULL, "isnull", card_value);
4950 set_has_no_nil(cond);
4951 le = rel_nop_(sql, rel, cond, re, le, NULL, NULL, "ifthenelse", card_value);
4952 }
4953 exp_setname(sql->sa, le, nme, sa_strdup(sql->sa, nm));
4954 append(outexps, le);
4955 list_remove_data(r_exps, re);
4956 } else {
4957 if (l_nil)
4958 set_has_nil(le);
4959 append(outexps, le);
4960 }
4961 }
4962 if (!found) {
4963 sql_error(sql, 02, SQLSTATE(42000) "JOIN: no columns of tables '%s' and '%s' match", rel_name(t1)?rel_name(t1):"", rel_name(t2)?rel_name(t2):"");
4964 rel_destroy(rel);
4965 return NULL;
4966 }
4967 for (n = r_exps->h; n; n = n->next) {
4968 sql_exp *re = n->data;
4969 if (r_nil)
4970 set_has_nil(re);
4971 append(outexps, re);
4972 }
4973 rel = rel_project(sql->sa, rel, outexps);
4974 return rel;
4975}
4976
4977static int
4978exp_is_not_intern(sql_exp *e)
4979{
4980 return is_intern(e)?-1:0;
4981}
4982
4983static void
4984rel_remove_internal_exp(sql_rel *rel)
4985{
4986 if (rel->exps) {
4987 list *n_exps = list_select(rel->exps, rel, (fcmp)&exp_is_not_intern, (fdup)NULL);
4988
4989 rel->exps = n_exps;
4990 }
4991}
4992
4993static sql_rel *
4994rel_select_exp(sql_query *query, sql_rel *rel, SelectNode *sn, exp_kind ek)
4995{
4996 mvc *sql = query->sql;
4997 sql_rel *inner = NULL;
4998 list *pexps = NULL;
4999
5000 assert(sn->s.token == SQL_SELECT);
5001 if (!sn->selection)
5002 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: the selection or from part is missing");
5003
5004 if (!rel)
5005 rel = rel_project(sql->sa, NULL, append(new_exp_list(sql->sa), exp_atom_bool(sql->sa, 1)));
5006 rel = rel_where_groupby_nodes(query, rel, sn);
5007 if (sql->session->status) /* rel might be NULL as input, so we have to check for the session status for errors */
5008 return NULL;
5009
5010 inner = rel;
5011 pexps = sa_list(sql->sa);
5012 for (dnode *n = sn->selection->h; n; n = n->next) {
5013 /* Here we could get real column expressions
5014 * (including single atoms) but also table results.
5015 * Therefor we try both rel_column_exp
5016 * and rel_table_exp.
5017 */
5018 list *te = NULL;
5019 sql_exp *ce = rel_column_exp(query, &inner, n->data.sym, sql_sel);
5020
5021 if (ce && exp_subtype(ce)) {
5022 pexps = append(pexps, ce);
5023 rel = inner;
5024 continue;
5025 } else if (!ce) {
5026 te = rel_table_exp(query, &rel, n->data.sym);
5027 } else
5028 ce = NULL;
5029 if (!ce && !te) {
5030 if (sql->errstr[0])
5031 return NULL;
5032 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: subquery result missing");
5033 }
5034 /* here we should merge the column expressions we
5035 * obtained so far with the table expression, ie
5036 * t1.* or a subquery.
5037 */
5038 pexps = list_merge(pexps, te, (fdup)NULL);
5039 }
5040 if (rel && is_groupby(rel->op) && !sn->groupby) {
5041 for (node *n=pexps->h; n; n = n->next) {
5042 sql_exp *ce = n->data;
5043 if (rel->card < ce->card) {
5044 if (exp_name(ce)) {
5045 return sql_error(sql, 05, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", exp_name(ce));
5046 } else {
5047 return sql_error(sql, 05, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results without an aggregate function");
5048 }
5049 }
5050 }
5051 }
5052 rel = rel_project(sql->sa, rel, pexps);
5053
5054 rel = rel_having_limits_nodes(query, rel, sn, ek);
5055 return rel;
5056}
5057
5058static sql_rel*
5059rel_unique_names(mvc *sql, sql_rel *rel)
5060{
5061 node *n;
5062 list *l;
5063
5064 if (!is_project(rel->op))
5065 return rel;
5066 l = sa_list(sql->sa);
5067 for (n = rel->exps->h; n; n = n->next) {
5068 sql_exp *e = n->data;
5069
5070 if (exp_relname(e)) {
5071 if (exp_name(e) && exps_bind_column2(l, exp_relname(e), exp_name(e)))
5072 exp_label(sql->sa, e, ++sql->label);
5073 } else {
5074 if (exp_name(e) && exps_bind_column(l, exp_name(e), NULL))
5075 exp_label(sql->sa, e, ++sql->label);
5076 }
5077 append(l,e);
5078 }
5079 rel->exps = l;
5080 return rel;
5081}
5082
5083static sql_rel *
5084rel_query(sql_query *query, sql_rel *rel, symbol *sq, int toplevel, exp_kind ek)
5085{
5086 mvc *sql = query->sql;
5087 sql_rel *res = NULL;
5088 SelectNode *sn = NULL;
5089
5090 if (sq->token != SQL_SELECT)
5091 return table_ref(query, rel, sq, 0);
5092
5093 /* select ... into is currently not handled here ! */
5094 sn = (SelectNode *) sq;
5095 if (sn->into)
5096 return NULL;
5097
5098 if (ek.card != card_relation && sn->orderby)
5099 return sql_error(sql, 01, SQLSTATE(42000) "SELECT: ORDER BY only allowed on outermost SELECT");
5100
5101 if (sn->window) {
5102 dlist *wl = sn->window->data.lval;
5103 for (dnode *n = wl->h; n ; n = n->next) {
5104 dlist *wd = n->data.sym->data.lval;
5105 const char *name = wd->h->data.sval;
5106 dlist *wdef = wd->h->next->data.lval;
5107 if(stack_get_window_def(sql, name, NULL)) {
5108 return sql_error(sql, 01, SQLSTATE(42000) "SELECT: Redefinition of window '%s'", name);
5109 } else if(!stack_push_window_def(sql, name, wdef)) {
5110 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
5111 }
5112 }
5113 }
5114
5115 if (sn->from) { /* keep variable list with tables and names */
5116 dlist *fl = sn->from->data.lval;
5117 dnode *n = NULL;
5118 sql_rel *fnd = NULL;
5119
5120 for (n = fl->h; n ; n = n->next) {
5121 int lateral = check_is_lateral(n->data.sym);
5122
5123 /* just used current expression */
5124 fnd = table_ref(query, NULL, n->data.sym, lateral);
5125 if (!fnd && res && lateral && sql->session->status != -ERR_AMBIGUOUS) {
5126 /* reset error */
5127 sql->session->status = 0;
5128 sql->errstr[0] = 0;
5129
5130 query_push_outer(query, res, sql_from);
5131 fnd = table_ref(query, NULL, n->data.sym, lateral);
5132 res = query_pop_outer(query);
5133 }
5134 if (!fnd)
5135 break;
5136 if (res) {
5137 res = rel_crossproduct(sql->sa, res, fnd, op_join);
5138 if (lateral)
5139 set_dependent(res);
5140 } else {
5141 res = fnd;
5142 }
5143 }
5144 if (!fnd) {
5145 if (res)
5146 rel_destroy(res);
5147 return NULL;
5148 }
5149 } else if (toplevel || !res) /* only on top level query */
5150 return rel_select_exp(query, rel, sn, ek);
5151
5152 if (res)
5153 rel = rel_select_exp(query, res, sn, ek);
5154 if (!rel && res)
5155 rel_destroy(res);
5156 return rel;
5157}
5158
5159static sql_rel *
5160rel_setquery_(sql_query *query, sql_rel *l, sql_rel *r, dlist *cols, int op )
5161{
5162 mvc *sql = query->sql;
5163 sql_rel *rel;
5164
5165 if (!cols) {
5166 list *ls, *rs;
5167
5168 l = rel_unique_names(sql, l);
5169 r = rel_unique_names(sql, r);
5170 ls = rel_projections(sql, l, NULL, 0, 1);
5171 rs = rel_projections(sql, r, NULL, 0, 1);
5172 rel = rel_setop_check_types(sql, l, r, ls, rs, (operator_type)op);
5173 } else {
5174 rel = rel_setop(sql->sa, l, r, (operator_type)op);
5175 }
5176 if (rel) {
5177 rel->exps = rel_projections(sql, rel, NULL, 0, 1);
5178 set_processed(rel);
5179 }
5180 return rel;
5181}
5182
5183static sql_rel *
5184rel_setquery(sql_query *query, symbol *q)
5185{
5186 mvc *sql = query->sql;
5187 sql_rel *res = NULL;
5188 dnode *n = q->data.lval->h;
5189 symbol *tab_ref1 = n->data.sym;
5190 int distinct = n->next->data.i_val;
5191 dlist *corresponding = n->next->next->data.lval;
5192 symbol *tab_ref2 = n->next->next->next->data.sym;
5193 sql_rel *t1, *t2;
5194
5195 assert(n->next->type == type_int);
5196 t1 = table_ref(query, NULL, tab_ref1, 0);
5197 if (!t1)
5198 return NULL;
5199 t2 = table_ref(query, NULL, tab_ref2, 0);
5200 if (!t2)
5201 return NULL;
5202
5203 rel_remove_internal_exp(t1);
5204 rel_remove_internal_exp(t2);
5205 if (list_length(t1->exps) != list_length(t2->exps)) {
5206 int t1nrcols = list_length(t1->exps);
5207 int t2nrcols = list_length(t2->exps);
5208 char *op = "UNION";
5209 if (q->token == SQL_EXCEPT)
5210 op = "EXCEPT";
5211 else if (q->token == SQL_INTERSECT)
5212 op = "INTERSECT";
5213 rel_destroy(t1);
5214 rel_destroy(t2);
5215 return sql_error(sql, 02, SQLSTATE(42000) "%s: column counts (%d and %d) do not match", op, t1nrcols, t2nrcols);
5216 }
5217 if ( q->token == SQL_UNION) {
5218 /* For EXCEPT/INTERSECT the group by is always done within the implementation */
5219 if (t1 && distinct)
5220 t1 = rel_distinct(t1);
5221 if (t2 && distinct)
5222 t2 = rel_distinct(t2);
5223 res = rel_setquery_(query, t1, t2, corresponding, op_union );
5224 }
5225 if ( q->token == SQL_EXCEPT)
5226 res = rel_setquery_(query, t1, t2, corresponding, op_except );
5227 if ( q->token == SQL_INTERSECT)
5228 res = rel_setquery_(query, t1, t2, corresponding, op_inter );
5229 if (res && distinct)
5230 res = rel_distinct(res);
5231 return res;
5232}
5233
5234static sql_rel *
5235rel_joinquery_(sql_query *query, sql_rel *rel, symbol *tab1, int natural, jt jointype, symbol *tab2, symbol *js)
5236{
5237 mvc *sql = query->sql;
5238 operator_type op = op_join;
5239 sql_rel *t1 = NULL, *t2 = NULL, *inner;
5240 int l_nil = 0, r_nil = 0, lateral = 0;
5241
5242 assert(!rel);
5243 switch(jointype) {
5244 case jt_inner: op = op_join;
5245 break;
5246 case jt_left: op = op_left;
5247 r_nil = 1;
5248 break;
5249 case jt_right: op = op_right;
5250 l_nil = 1;
5251 break;
5252 case jt_full: op = op_full;
5253 l_nil = 1;
5254 r_nil = 1;
5255 break;
5256 case jt_union:
5257 /* fool compiler */
5258 return NULL;
5259 }
5260
5261 lateral = check_is_lateral(tab2);
5262 t1 = table_ref(query, NULL, tab1, 0);
5263 if (rel && !t1 && sql->session->status != -ERR_AMBIGUOUS) {
5264 /* reset error */
5265 sql->session->status = 0;
5266 sql->errstr[0] = 0;
5267 t1 = table_ref(query, NULL, tab1, 0);
5268 }
5269 if (t1) {
5270 t2 = table_ref(query, NULL, tab2, 0);
5271 if (lateral && !t2 && sql->session->status != -ERR_AMBIGUOUS) {
5272 /* reset error */
5273 sql->session->status = 0;
5274 sql->errstr[0] = 0;
5275
5276 query_push_outer(query, t1, sql_from);
5277 t2 = table_ref(query, NULL, tab2, 0);
5278 t1 = query_pop_outer(query);
5279 }
5280 }
5281 if (rel)
5282 rel_destroy(rel);
5283 if (!t1 || !t2)
5284 return NULL;
5285
5286 if (!lateral && rel_name(t1) && rel_name(t2) && strcmp(rel_name(t1), rel_name(t2)) == 0) {
5287 sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' on both sides of the JOIN expression;", rel_name(t1));
5288 rel_destroy(t1);
5289 rel_destroy(t2);
5290 return NULL;
5291 }
5292
5293 inner = rel = rel_crossproduct(sql->sa, t1, t2, op_join);
5294 inner->op = op;
5295 if (lateral)
5296 set_dependent(inner);
5297
5298 if (js && natural) {
5299 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: cannot have a NATURAL JOIN with a join specification (ON or USING);");
5300 }
5301 if (!js && !natural) {
5302 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: must have NATURAL JOIN or a JOIN with a join specification (ON or USING);");
5303 }
5304
5305 if (js && js->token != SQL_USING) { /* On sql_logical_exp */
5306 rel = rel_logical_exp(query, rel, js, sql_where | sql_join);
5307 } else if (js) { /* using */
5308 char rname[16], *rnme;
5309 dnode *n = js->data.lval->h;
5310 list *outexps = new_exp_list(sql->sa), *exps;
5311 node *m;
5312
5313 rnme = number2name(rname, sizeof(rname), ++sql->label);
5314 for (; n; n = n->next) {
5315 char *nm = n->data.sval;
5316 sql_exp *cond;
5317 sql_exp *ls = rel_bind_column(sql, t1, nm, sql_where);
5318 sql_exp *rs = rel_bind_column(sql, t2, nm, sql_where);
5319
5320 if (!ls || !rs) {
5321 sql_error(sql, 02, SQLSTATE(42000) "JOIN: tables '%s' and '%s' do not have a matching column '%s'\n", rel_name(t1)?rel_name(t1):"", rel_name(t2)?rel_name(t2):"", nm);
5322 rel_destroy(rel);
5323 return NULL;
5324 }
5325 rel = rel_compare_exp(query, rel, ls, rs, "=", NULL, TRUE, 0, 0);
5326 if (op != op_join) {
5327 cond = rel_unop_(sql, rel, ls, NULL, "isnull", card_value);
5328 set_has_no_nil(cond);
5329 if (rel_convert_types(sql, t1, t2, &ls, &rs, 1, type_equal) < 0)
5330 return NULL;
5331 ls = rel_nop_(sql, rel, cond, rs, ls, NULL, NULL, "ifthenelse", card_value);
5332 }
5333 exp_setname(sql->sa, ls, rnme, nm);
5334 append(outexps, ls);
5335 if (!rel)
5336 return NULL;
5337 }
5338 exps = rel_projections(sql, t1, NULL, 1, 1);
5339 for (m = exps->h; m; m = m->next) {
5340 const char *nm = exp_name(m->data);
5341 int fnd = 0;
5342
5343 for (n = js->data.lval->h; n; n = n->next) {
5344 if (strcmp(nm, n->data.sval) == 0) {
5345 fnd = 1;
5346 break;
5347 }
5348 }
5349 if (!fnd) {
5350 sql_exp *ls = m->data;
5351 if (l_nil)
5352 set_has_nil(ls);
5353 append(outexps, ls);
5354 }
5355 }
5356 exps = rel_projections(sql, t2, NULL, 1, 1);
5357 for (m = exps->h; m; m = m->next) {
5358 const char *nm = exp_name(m->data);
5359 int fnd = 0;
5360
5361 for (n = js->data.lval->h; n; n = n->next) {
5362 if (strcmp(nm, n->data.sval) == 0) {
5363 fnd = 1;
5364 break;
5365 }
5366 }
5367 if (!fnd) {
5368 sql_exp *rs = m->data;
5369 if (r_nil)
5370 set_has_nil(rs);
5371 append(outexps, rs);
5372 }
5373 }
5374 rel = rel_project(sql->sa, rel, outexps);
5375 } else { /* ! js -> natural join */
5376 rel = join_on_column_name(query, rel, t1, t2, op, l_nil, r_nil);
5377 }
5378 if (!rel)
5379 return NULL;
5380 if (inner && is_outerjoin(inner->op))
5381 set_processed(inner);
5382 set_processed(rel);
5383 return rel;
5384}
5385
5386static sql_rel *
5387rel_joinquery(sql_query *query, sql_rel *rel, symbol *q)
5388{
5389
5390 dnode *n = q->data.lval->h;
5391 symbol *tab_ref1 = n->data.sym;
5392 int natural = n->next->data.i_val;
5393 jt jointype = (jt) n->next->next->data.i_val;
5394 symbol *tab_ref2 = n->next->next->next->data.sym;
5395 symbol *joinspec = n->next->next->next->next->data.sym;
5396
5397 assert(n->next->type == type_int);
5398 assert(n->next->next->type == type_int);
5399 return rel_joinquery_(query, rel, tab_ref1, natural, jointype, tab_ref2, joinspec);
5400}
5401
5402static sql_rel *
5403rel_crossquery(sql_query *query, sql_rel *rel, symbol *q)
5404{
5405 dnode *n = q->data.lval->h;
5406 symbol *tab1 = n->data.sym;
5407 symbol *tab2 = n->next->data.sym;
5408 sql_rel *t1 = table_ref(query, rel, tab1, 0);
5409 sql_rel *t2 = NULL;
5410
5411 if (t1)
5412 t2 = table_ref(query, rel, tab2, 0);
5413 if (!t1 || !t2)
5414 return NULL;
5415
5416 rel = rel_crossproduct(query->sql->sa, t1, t2, op_join);
5417 return rel;
5418}
5419
5420static sql_rel *
5421rel_unionjoinquery(sql_query *query, sql_rel *rel, symbol *q)
5422{
5423 mvc *sql = query->sql;
5424 dnode *n = q->data.lval->h;
5425 sql_rel *lv = table_ref(query, rel, n->data.sym, 0);
5426 sql_rel *rv = NULL;
5427 int all = n->next->data.i_val;
5428 list *lexps, *rexps;
5429 node *m;
5430 int found = 0;
5431
5432 if (lv)
5433 rv = table_ref(query, rel, n->next->next->data.sym, 0);
5434 assert(n->next->type == type_int);
5435 if (!lv || !rv)
5436 return NULL;
5437
5438 lexps = rel_projections(sql, lv, NULL, 1, 1);
5439 /* find the matching columns (all should match?)
5440 * union these
5441 * if !all do a distinct operation at the end
5442 */
5443 /* join all result columns ie join(lh,rh) on column_name */
5444 rexps = new_exp_list(sql->sa);
5445 for (m = lexps->h; m; m = m->next) {
5446 sql_exp *le = m->data;
5447 sql_exp *rc = rel_bind_column(sql, rv, exp_name(le), sql_where);
5448
5449 if (!rc && all)
5450 break;
5451 if (rc) {
5452 found = 1;
5453 append(rexps, rc);
5454 }
5455 }
5456 if (!found) {
5457 rel_destroy(rel);
5458 return NULL;
5459 }
5460 lv = rel_project(sql->sa, lv, lexps);
5461 rv = rel_project(sql->sa, rv, rexps);
5462 rel = rel_setop(sql->sa, lv, rv, op_union);
5463 rel->exps = rel_projections(sql, rel, NULL, 0, 1);
5464 set_processed(rel);
5465 if (!all)
5466 rel = rel_distinct(rel);
5467 return rel;
5468}
5469
5470sql_rel *
5471rel_subquery(sql_query *query, sql_rel *rel, symbol *sq, exp_kind ek)
5472{
5473 int toplevel = 0;
5474
5475 if (!rel || (rel->op == op_project &&
5476 (!rel->exps || list_length(rel->exps) == 0)))
5477 toplevel = 1;
5478
5479 return rel_query(query, rel, sq, toplevel, ek);
5480}
5481
5482sql_rel *
5483rel_selects(sql_query *query, symbol *s)
5484{
5485 mvc *sql = query->sql;
5486 sql_rel *ret = NULL;
5487
5488 switch (s->token) {
5489 case SQL_WITH:
5490 ret = rel_with_query(query, s);
5491 sql->type = Q_TABLE;
5492 break;
5493 case SQL_VALUES:
5494 ret = rel_values(query, s);
5495 sql->type = Q_TABLE;
5496 break;
5497 case SQL_SELECT: {
5498 exp_kind ek = {type_value, card_relation, TRUE};
5499 SelectNode *sn = (SelectNode *) s;
5500
5501 if (!stack_push_frame(sql, "SELECT"))
5502 return sql_error(sql, 02, SQLSTATE(HY001) MAL_MALLOC_FAIL);
5503
5504 if (sn->into) {
5505 sql->type = Q_SCHEMA;
5506 ret = rel_select_with_into(query, s);
5507 } else {
5508 ret = rel_subquery(query, NULL, s, ek);
5509 sql->type = Q_TABLE;
5510 }
5511 stack_pop_frame(sql);
5512 } break;
5513 case SQL_JOIN:
5514 ret = rel_joinquery(query, NULL, s);
5515 sql->type = Q_TABLE;
5516 break;
5517 case SQL_CROSS:
5518 ret = rel_crossquery(query, NULL, s);
5519 sql->type = Q_TABLE;
5520 break;
5521 case SQL_UNION:
5522 case SQL_EXCEPT:
5523 case SQL_INTERSECT:
5524 ret = rel_setquery(query, s);
5525 sql->type = Q_TABLE;
5526 break;
5527 default:
5528 return NULL;
5529 }
5530 if (!ret && sql->errstr[0] == 0)
5531 (void) sql_error(sql, 02, SQLSTATE(42000) "relational query without result");
5532 return ret;
5533}
5534
5535sql_rel *
5536schema_selects(sql_query *query, sql_schema *schema, symbol *s)
5537{
5538 sql_rel *res;
5539 sql_schema *os = query->sql->session->schema;
5540
5541 query->sql->session->schema = schema;
5542 res = rel_selects(query, s);
5543 query->sql->session->schema = os;
5544 return res;
5545}
5546
5547sql_rel *
5548rel_loader_function(sql_query *query, symbol* fcall, list *fexps, sql_subfunc **loader_function)
5549{
5550 mvc *sql = query->sql;
5551 list *exps = NULL, *tl;
5552 exp_kind ek = { type_value, card_relation, TRUE };
5553 sql_rel *sq = NULL;
5554 sql_exp *e = NULL;
5555 symbol *sym = fcall;
5556 dnode *l = sym->data.lval->h;
5557 char *sname = qname_schema(l->data.lval);
5558 char *fname = qname_fname(l->data.lval);
5559 char *tname = NULL;
5560 node *en;
5561 sql_schema *s = sql->session->schema;
5562 sql_subfunc* sf;
5563
5564 tl = sa_list(sql->sa);
5565 exps = new_exp_list(sql->sa);
5566 if (l->next) { /* table call with subquery */
5567 if (l->next->type == type_symbol && l->next->data.sym->token == SQL_SELECT) {
5568 if (l->next->next != NULL)
5569 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' requires a single sub query", fname);
5570 sq = rel_subquery(query, NULL, l->next->data.sym, ek);
5571 } else if (l->next->type == type_symbol || l->next->type == type_list) {
5572 dnode *n;
5573 exp_kind iek = {type_value, card_column, TRUE};
5574 list *exps = sa_list (sql->sa);
5575
5576 if (l->next->type == type_symbol)
5577 n = l->next;
5578 else
5579 n = l->next->data.lval->h;
5580 for ( ; n; n = n->next) {
5581 sql_exp *e = rel_value_exp(query, NULL, n->data.sym, sql_sel, iek);
5582
5583 if (!e)
5584 return NULL;
5585 append(exps, e);
5586 }
5587 sq = rel_project(sql->sa, NULL, exps);
5588 }
5589
5590 /* reset error */
5591 sql->session->status = 0;
5592 sql->errstr[0] = '\0';
5593 if (!sq)
5594 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
5595 for (en = sq->exps->h; en; en = en->next) {
5596 sql_exp *e = en->data;
5597
5598 append(exps, e = exp_alias_or_copy(sql, tname, exp_name(e), NULL, e));
5599 append(tl, exp_subtype(e));
5600 }
5601 }
5602 if (sname)
5603 s = mvc_bind_schema(sql, sname);
5604
5605 e = find_table_function_type(sql, s, fname, exps, tl, F_LOADER, &sf);
5606 if (!e || !sf)
5607 return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such operator '%s'", fname);
5608
5609 if (loader_function)
5610 *loader_function = sf;
5611
5612 return rel_table_func(sql->sa, sq, e, fexps, (sq != NULL));
5613}
5614