The SQL compiler internally uses a relational algebra tree representation. It can be obtained by pre-pending the query with the PLAN modifier.
sql>PLAN SELECT count(*) FROM sys.tables;
+--------------------------------------------------------------------------------+
| rel |
+================================================================================+
| project ( |
| | group by ( |
| | | union ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table("sys"."_tables") [ "_tables"."id", "_tables"."type" ] COUNT |
| | | | | | ) [ "_tables"."type" != smallint "2" ] |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() NOT NULL as "%10"."%10" ], |
| | | | group by ( |
| | | | | project ( |
| | | | | | table("tmp"."_tables") [ "_tables"."id" ] COUNT |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() NOT NULL as "%10"."%10" ] |
| | | ) [ "%10"."%10" ] |
| | ) [ ] [ "sys"."sum" no nil ("%10"."%10") as "%10"."%10" ] |
| ) [ "%10"."%10" NOT NULL ] |
+--------------------------------------------------------------------------------+
18 tuples
The table below illustrates the components of the relational plan. It refers to the names and functions used within the code base.
BASETABLE
(card MULTI)
-> exps is list of column expressions (also used for aliases)
-> l (sql_table)
-> r rel_base_t { alias table name, disallowed (read access), bitvector of used columns/tid/idxs
-> r rel_optimizer rel_rename_part puts parent merge table in there
TABLE (card MULTI)
-> flags TABLE_PROD_FUNC, TABLE_FROM_RELATION, TRIGGER_WRAPPER
cases
TABLE_PROD_FUNC) TABLE producing function
TABLE_FROM_RELATION) RELATIONAL subquery which is dynamically wrapped
into function call (needed of remote calls)
TRIGGER_WRAPPER) for triggers inserts, deletes and updates (new/old values)
flags
r is list of stmts
-> exps is list of column expressions (also used for aliases)
-> l optional input relation
-> r table function expression
/* Wrapping relational operator, is a big complex top level only
operator, which only does DDL kind of statements
*/
DDL (card 0!, top of the tree always)
-> flags /* OUTPUT, TRANSACTION-types, CREATE/DROP/ALTER* */
-> exps For 'OUTPUT' is list of output options, for ddl_exception is a list of psm_exception
For 'transactions' simple flags
for CREATE etc full sql string.
-> l used in ddl_output as the relation to output and ddl_exception as a ddl_list
-> r used in ddl_list and ddl_exception
SELECT (card MULTI or same ...?)
-> exps selection expressions (ie all e_cmp or e_atom of type bit)
-> l is relation
LEFT|RIGHT|FULL
JOIN (card MULTI)
-> exps join expressions (ie all e_cmp or e_atom of type bit)
-> l is left relation
-> r is right relation
-> flag LEFT (keep left order, only used during code generation)
SEMI/ANTI
(card MULTI)
-> exps join expressions (ie all e_cmp or e_atom of type bit)
-> l is left relation
-> r is right relation
UNION|EXCEPT|INTERSECT
(card ATOM, AGGR or MULTI (same card as lower relation))
-> exps is list of projection expressions
-> l is left relation
-> r is right relation
PROJECT (card ATOM, AGGR or MULTI (same card as lower relation))
-> exps is list of projection expressions
-> l is relation
-> r optional, is list of order by expressions
GROUPBY (card ATOM (no group by exps), AGGR)
-> exps is list of (groupby and) aggregate expressions
-> l is relation
-> r is list of group by expressions
TOPN (card ATOM, AGGR, or MULTI (same card as lower relation))
-> exps (list) lng limit, [ lng offset ] -> if the limit is a NULL value, then it's ignored, ie only the offset will be used
-> l is relation
-> flag (bounds for limit can be including (1) or excluding (0) (ie later just returns the topN, including will return atleast N)
SAMPLE (card ATOM, AGGR, or MULTI (same card as lower relation))
-> exps first is either an lng for rownumbers or double between [0,1] for percentage. An optional second contains an int for the seed value. -> if the first value is a NULL, then it's ignored, ie only the offset will be used
-> l is relation
-> flag (0) no flags
INSERT|DELETE|UPDATE|TRUNCATE (card ATOM, AGGR or MULTI (same card as the to be inserted/deleted/updated relation))
-> l is relation to modify
-> r to be inserted/deleted/updated relation
For update the ->r projection joins in current
values for 'none' updated columns. Not used in truncate.
-> flag (if set don't insert (is done already))
only (UPDATE)
-> exps
Named columns to update
only (TRUNCATE)
-> exps
The first expression is a e_atom integer indicating to restart sequences, and the second is another e_atom integer telling to cascade the truncate if the table has fk dependents
INSERT/UPDATE have a special case
for inserts/updates with indices a double
relation is used as we need to keep access to
the bottom value relation
MERGE (card ATOM, AGGR or MULTI (max card of updates))
-> l left join between the target table and the source
-> r a DDL list with an insert and update/delete, or a single relation update on the target table
-> flag (0) no flags
->exps empty list, ie not used
e_atom (card ATOM)
-> l literal (-> l = atom)
or
-> r parameter ( ->r = sql_var_name -> { ->name variable name, ->sname schema of the variable }, ->type = subtype, ->flag = level)
or
-> f list of atom expressions
or
-> numbered variable ( ->flag = nr, ->type = subtype)
e_convert
-> l sub expression
-> r list of from and to subtypes
e_cmp
-> l left sub expression
-> r right sub expression (f second arg (->f) for range expressions)
-> flag compare type
(
cmp_gt = 0, or [ v > l && v < h ]
cmp_gte = 1, or [ v >= l && v < h ]
cmp_lte = 2, or [ v > l && v <= h ]
cmp_lt = 3, or [ v >= l && v <= h ]
uses flag&CMP_BETWEEN bit for handling nil
open ranges and isNull vs NULL
uses flag&CMP_SYMMETRIC bit for symmetric
cmp_equal = 4,
cmp_notequal = 5,
cmp_filter = 6, filters ->l/r are both lists
cmp_or = 7, or handling ->l/r are both lists
cmp_in = 8, in list handling ->r is a list of values
cmp_notin = 9 not in list handling ->r is a list of values
mark_in = 10, /* mark joins */
mark_notin = 11,
/* The followin cmp_* are only used within stmt (not sql_exp) */
cmp_all = 12, /* special case for crossproducts */
cmp_project = 13, /* special case for projection joins */
cmp_joined = 14, /* special case already joined */
cmp_left = 15, /* special case equi join, keep left order */
cmp_left_project = 16 /* last step of outer join */
)
e_func
e_aggr
-> l list of sub expressions (args for the func and aggr)
+ optional conditional execution argument
-> f func / aggr
for aggr
-> flag DISTINCT and NO_NIL could be set
for window functions
-> r contains a list with two values: the first is a list with the partition by expressions, and the second a list with order by expressions
e_column
-> rname alias for the relation (i.e., alias of ->l, used by higher expressions)
-> name alias for the expression (i.e., alias of ->r, used by higher expressions)
l,r names of referred expression
-> l optional relation name
-> r expression name
-> type
e_psm
-> flag
psm_var ->f sql_table if it is a table
->tpe type of variable if not a table
psm_set ->l name
->l value_exp
psm_if ->l cond_exp
->r then_exp_list
->f else_exp_list
psm_while ->l cond_exp
->r loop_exp_list
psm_return ->l return_exp
psm_rel ->l relation
psm_exception ->l cond_exp
->r error_string