PLAN Statement

The SQL compiler internally uses a relational algebra tree representation. It can be obtained by pre-pending the query with the PLAN modifier.

Example

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

Relational plan components

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

Relational Plan Expressions

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