[This is preliminary documentation and subject to change.]
The following table summarizes all DML command nodes. For each node, the table describes the information stored within the node, its required and optional input types, and the types of the output produced by the node. This table, along with the description of the semantics of each node in previous section, represents a first step to defining the correct way to build OLE DB command trees.
The supported output types are and minor types are listed in the form
output-type:minor-type
where output-type and minor-type are as follows. The minor type is not listed if it is not required by the operator. For more information, see IDBInfo::GetOperatorInfo.
| Output Type | output-type | minor-type | Description |
|---|---|---|---|
| Table1 | T | O U OU H |
Ordered Unique Ordered unique Hierarchical |
| Row | R | -- | -- |
| Scalar | S | AF BMK COL CON DEF EXP NUL PAR SF |
Aggregate function Bookmark Column Constant Default Expression NULL Parameter Scalar function |
| Boolean | B | -- | -- |
| List anchor | LA_CD LA_COL LA_CMD LA_FRM LA_IDX LA_PRJ LA_PRP LA_ROW LA_SC LA_SET LA_SRT |
-- | Column definition list anchor Column list anchor Command list anchor From list anchor Index list anchor Project list anchor Property list anchor Row list anchor Scalar list anchor Set list anchor Sort list anchor |
| List element | LE_CD LE_COL LE_CMD LE_FRM LE_IDX LE_PRJ LE_PRP LE_ROW LE_SC LE_SET LE_SRT |
-- | Column definition list element Column list element Command list element From list element Index list element Project list element Property list element Row list element Scalar list element Set list element Sort list element |
| Catalog name | CN | -- | -- |
| Schema name | SN | -- | -- |
| Outall name | ON | -- | -- |
| DDL operator | DDL | -- | -- |
| Update operator | U | U D I |
Update operator Delete operator Insert operator |
| Any legal output type | Any | -- | -- |
| 1 UT, OT, and OUT imply T, but not the other way around. UT, OT, and OUT are specializations of T. UT and OT do not imply each other. OUT implies UT and OT. | |||
| Node | DBVALUEKIND | R | O | Input Types | Output Types |
|---|---|---|---|---|---|
| absolute_sampling | see relative_sampling | ||||
| add_columns | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T 2nd: LA_PRJ |
T |
| aggregate | DBVALUEKIND_EMPTY | 3 | 0 | 1st: T 2,3rd: LA_PRJ |
T |
| alias | PwszValue | 1 | 1 | 1st: T 2nd: LA_COL |
T |
| allbits | see anybits | ||||
| allbits_all | see anybits | ||||
| allbits_any | see anybits | ||||
| and, or, xor, equivalent | Node weight is stored as DBVALUEKIND_I4. | 2 | N | 1st: B 2nd: B |
B |
| any_sample | see min | ||||
| anybits, allbits, anybits_any, allbits_any, anybits_all, allbits_all | Node weight is store as DBVALUEKIND_I4. | 2 | 0 | 1st: S:COL 2nd: S |
B |
| anybits_all | see anybits | ||||
| anybits_any | see anybits | ||||
| avg | see min | ||||
| bag_anti_difference | see set_intersection | ||||
| bag _intersection | see set_intersection | ||||
| bag_left_difference | see set_intersection | ||||
| bag_right_difference | see set_intersection | ||||
| bag_union | see set_intersection | ||||
| between, between_unordered | DBVALUEKIND_EMPTY | 3 | 0 | 1st: S or R 2nd: S or R 3rd: S, or R |
B |
| between_unordered | see between | ||||
| bit_length | see overlaps | ||||
| bookmark_name | Optional pwszValue | 0 | 1 | 1st: table_name | S:BMK |
| case_condition | see overlaps | ||||
| case_value | see overlaps | ||||
| Cast | see overlaps | ||||
| catalog_name | Catalog name as: DBID, pwszValue, pMoniker | 0 | 0 | CN | |
| char_length | see overlaps | ||||
| Coalesce | see overlaps | ||||
| column_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth: LE_COL | LA_COL |
| column_list_element | PwszValue | 0 | 0 | LE_COL | |
| column_name | DBID, pwszValue, pMoniker, ulValue | 0 | 0 | S:COL | |
| command_list_anchor | DBVALUEKIND_EMPTY | 0 | 1 | 1st-nth: LE_CMD | LA_CMD |
| command_list_element | DBVALUEKIND_EMPTY | 1 | 0 | Any DML or DDL command node | LE_CMD |
| content, content_freetext | DBCONTENT | 1 | 0 | 1st: S:COL | B |
| content_freetext | see content | ||||
| content_proximity | DBCONTENTPROXIMITY | 2 | n | 1st: content or content_freetext 2nd: content or content_freetext nth: Boolean expression formed from and, or, not, content_proximity, and content. |
B |
| content_vector | DBCONTENTVECTOR | 2 | n | 1st: content or content_freetext 2nd: content or content_freetext nth: Boolean expression formed from and, or, not, content_proximity, content, and content_freetext?? |
B |
| convert | see overlaps | ||||
| count | dwSetQuantifier field of DBSETFUNC | 0 | 1 | 1st: S:COL | S:AF |
| cross_join | 2 | 0 | 1st: T 2nd: T |
T | |
| cross_tab | DBVALUEKIND_EMPTY | 4 | 1 | 1st: T:H 2nd: LE_COL 3rd: LA_COL 4th: LA_COL 5th: LA_PRJ |
T:H |
| current_date | see overlaps | ||||
| current_time | see overlaps | ||||
| current_timestamp | see overlaps | ||||
| DEFAULT, NULL | DBVALUEKIND_EMPTY | 0 | 0 | S:DEF, S:NUL | |
| defined_by_GUID | DBBYGUID | * | * | * defined by implementation | * |
| delete | DBVALUEKIND_EMPTY | 1 | 1 | 1st: table_name 2nd: T |
U:D |
| disjoint | see subset | ||||
| distinct | DBVALUEKIND_EMPTY | 1 | 0 | 1st: T | T:U |
| distinct_order_preserving | DBVALUEKIND_EMPTY | 1 | 0 | 1st: T or T:O | T:U or T:OU |
| div | see plus | ||||
| division | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T 2nd: T |
T |
| equal, not_equal, less, less_equal, greater, greater_equal | Node weight is stored as DBVALUEKIND_I4. | 2 | 0 | B | |
| equal_all, not_equal_all, less_all, less_equal_all, greater_all, greater_equal_all, equal_any, not_equal_any, less_any, less_equal_any, greater_any, greater_equal_any | Node weight is stored as DBVALUEKIND_I4. | 2 | 0 | 1st: S or R 2nd: T |
B |
| equal_any | see equal_all | ||||
| equivalent | see and | ||||
| exists, unique | DBVALUEKIND_EMPTY | 1 | 0 | 1st: T | B |
| extract | see overlaps | ||||
| first | see min | ||||
| from_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth: LE_FRM | LA_FRM |
| from_list_element | DBVALUEKIND_EMPTY | 1 | 0 | 1st: T [including table_name or alias nodes] | LE_FRM |
| full_outer_join | see inner_join | ||||
| greater | see equal | ||||
| greater_all | see equal_all | ||||
| greater_any | see equal_all | ||||
| greater_equal | see equal | ||||
| greater_equal_all | see equal_all | ||||
| greater_equal_any | see equal_all | ||||
| implies | DBVALUEKIND_EMPTY | 2 | 0 | 1st: B 2nd: B |
B |
| in | DBVALUEKIND_EMPTY | 2 | 0 | 1st: R 2nd: T |
B |
| inner_join, left_semi_join, right_semi_join, left_anti_semi_join, right_anti_semi_join, left_outer_join, right_outer_join, full_outer_join | DBVALUEKIND_EMPTY | 3 | 0 | 1st: T 2nd: T 3rd: B [condition join] |
T |
| insert | DBVALUEKIND_EMPTY | 3 | 0 | 1st: R or T 2nd: T 3rd: LA_COL |
U:I |
| is_FALSE | see is_INVALID | ||||
| is_INVALID, is_TRUE, is_FALSE | DBVALUEKIND_EMPTY | 1 | 0 | 1st: S | B |
| is_NOT_NULL | see is_NULL | ||||
| is_NULL, is_NOT_NULL | DBVALUEKIND_EMPTY | 0 | 0 | B | |
| is_TRUE | see is_INVALID | ||||
| last | see min | ||||
| left_anti_semi_join | see inner_join | ||||
| left_outer_join | see inner_join | ||||
| left_semi_join | see inner_join | ||||
| less | see equal | ||||
| less_all | see equal_all | ||||
| less_any | see equal_all | ||||
| less_equal | see equal | ||||
| less_equal_all | see equal_all | ||||
| less_equal_any | see equal_all | ||||
| like | DBLIKE | 2 | 0 | 1st: S 2nd: S |
B |
| like_all | see like_any | ||||
| like_any, like_all | DBVALUEKIND_EMPTY | 2 | 0 | 1st: S 2nd: T |
B |
| lower | see overlaps | ||||
| match, match_unique, match_partial, match_partial_unique, match_full, match_full_unique | DBVALUEKIND_EMPTY | 2 | 0 | 1st: S or R 2nd: S or R |
B |
| match_full | see match | ||||
| match_full_unique | see match | ||||
| match_partial | see match | ||||
| match_partial_unique | see match | ||||
| match_unique | see match | ||||
| max | see min | ||||
| min, max, sum, avg, any_sample, stddev, stddev_pop, var, var_pop, first, last | dwSetQuantifier field of DBSETFUNC | 1 | 0 | 1st: S:COL | S:AF |
| minus | see plus | ||||
| module | see plus | ||||
| natural_full_outer_join | see natural_join | ||||
| natural_join, natural_left_outer_join, natural_right_outer_join, natural_full_outer_join | DBVALUEKIND_EMPTY | 2 | 1 | 1st: T 2nd: T 3rd: LA_COL [using list] |
T |
| natural_left_outer_join | see natural_join | ||||
| natural_right_outer_join | see natural_join | ||||
| navigate | DBVALUEKIND_EMPTY | 6 | 0 | 1st: T:H or T 2nd: T:H or T 3rd: B 4th: LE_COL 5th: LE_COL 6th: LE_COL |
T:H |
| nested_column_name | Column name as: DBID, pwszValue, pMoniker | 2 | 0 | 1st: nested_table_name 2nd: S:COL |
T:H |
| nested_table_name | Table name as: DBID, pwszValue, pMoniker | 0 | 1 | 1st: nested_table_name or table_name | T:H |
| nested_apply | DBVALUEKIND_EMPTY | 2 | 1 | 1st: T:H ? 3rd: LE_COL |
T:H or T |
| nesting | DBVALUEKIND_EMPTY | 5 | 0 | 1st: T:H or T 2nd: LE_COL 3rd: LE_COL |
T:H |
| not | Node weight is stored as DBVALUEKIND_I4. | 1 | 0 | 1st: B | B |
| not_equal | see equal | ||||
| not_equal_all | see equal_all | ||||
| not_equal_any | see equal_all | ||||
| NULL | see DEFAULT | ||||
| nullif | see overlaps | ||||
| octet_length | see overlaps | ||||
| or | see and | ||||
| order_preserve_select | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T or T:O 2nd: B |
T or T:O |
| outall_name | DBVALUEKIND_EMPTY | 0 | 0 | ON | |
| over | see plus | ||||
| overlaps, case_condition, case_value, nullif, cast, coalesce, position, extract, char_length, octet_length, bit_length, substring, upper, lower, trim, translate, convert, string_concat, current_date, current_time, current_timestamp | TBD | TBD | TBD | TBD | B or S:SF |
| pass_through | DBVALUEKIND_EMPTY | 1 | 0 | 1st: any tree input | Any |
| plus, minus, times, over, div, module, power | DBVALUEKIND_EMPTY | 2 | 0 | 1st: S 2nd: S |
S:EXP |
| position | see overlaps | ||||
| power | see plus | ||||
| prior_command_tree | DBVALUEKIND_EMPTY | 0 | 0 | S or T | |
| project | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T 2nd: LA_PRJ |
T |
| project_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth: LE_PRJ | LA_PRJ |
| project_list_element | PwszValue | 1 | 0 | 1st: S, qualifier_name, or ON | LE_PRJ |
| project_order_preserving | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T or T:O 2nd: LA_PRJ |
T or T:O |
| proper_subset | see subset | ||||
| proper_superset | see subset | ||||
| qualified_column_name | DBVALUEKIND_EMPTY | 2 | 0 | 1st: table_name 2nd: S:COL |
S:COL |
| qualifier_name | Table name as: DBID, pwszValue, pMoniker | 0 | 0 | qualifier_name | |
| rank, rank_ties_equally, rank_ties_equally_and_skip | DBVALUEKIND_EMPTY | 1 | 0 | 1st: T:O | T:O |
| rank_ties_equally | see rank | ||||
| rank_ties_equally_and_skip | see rank | ||||
| recursive_union, recursive_union_join |
DBVALUEKIND_EMPTY | 2 | 0 | 1st: T 2nd: B |
T |
| recursive_union_join | see recursive_union | ||||
| relative_sampling, absolute_sampling |
integer in ulValue | 1 | 0 | 1st: T |
T |
| remote_table | DBVALUEKIND_EMPTY | 2 | 0 | REQ 1: table_name
OPT 1: property_list_anchor, |
T |
| right_anti_semi_join | see inner_join | ||||
| right_outer_join | see inner_join | ||||
| right_semi_join | see inner_join | ||||
| row | DBVALUEKIND_EMPTY | 1 | 0 | 1st: LA_SC | R |
| row_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth: LE_ROW | LA_ROW |
| row_list_element | DBVALUEKIND_EMPTY | 1 | 0 | 1st: R | LE_ROW |
| scalar_constant | any value | 0 | 0 | S:CON | |
| scalar_function | DBID, pwszValue, Imoniker |
0 | N | 1st-nth: S | S:SF |
| scalar_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth: S:CON | LA_SC |
| scalar_list_element | DBVALUEKIND_EMPTY | 1 | 0 | 1st: S:CON | LE_SC |
| scalar_parameter | DBPARAMETER | 0 | 0 | S:PAR | |
| schema_name | Schema name as: DBID, pwszValue, pMoniker | 0 | 1 | 1st: CN | SN |
| select | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T 2nd: B |
T |
| set_anti_difference | see set_intersection | ||||
| set_intersection, set_union, set_left_difference, set_right_difference, set_anti_difference, bag_intersection, bag_union, bag_left_difference, bag_right_difference, bag_anti_difference | fValue = TRUE represents "SQL's union corresponding" | 2 | 1 | 1st: T 2nd: T 3rd: LA_COL |
T |
| set_left_difference | see set_intersection | ||||
| set_right_difference | see set_intersection | ||||
| set_union | see set_intersection | ||||
| sort | DBVALUEKIND_EMPTY | 2 | 0 | 1st: T. 2nd: LA_SRT |
T:O |
| sort_list_anchor | DBVALUEKIND_EMPTY | 0 | N | 1st-nth:LE_SRT | LA_SRT |
| sortalistaelement | DBSORTINFO | 1 | 0 | 1st: S | LEaSRT |
| soundsalike | DBVALUEKINDaEMPTY | 2 | 0 | 1st:S 2nd: S |
B |
| SQLaselect | fValue=TRUE denotes DISTINCT | 6 | 0 | 1st: LAaPRJ 2nd: LAaFRM 3rd: B 4th: LAaPRJ 5th: B 6th: LAaSRT NOTE: Lists may be empty |
T, T:O, T:U, T:OU |
| stddev | see min | ||||
| stddevapop | see min | ||||
| stringaconcat | see overlaps | ||||
| subset, properasubset, superset, properasuperset, disjoint | DBVALUEKINDaEMPTY | 2 | 0 | 1st: T 2nd: T |
B |
| substring | see overlaps | ||||
| sum | see min | ||||
| superset | see subset | ||||
| table | DBVALUEKINDaEMPTY | 1 | 0 | 1st: LAaROW | T |
| tableaname | Table name as: DBID, pwszValue, pMoniker | 0 | 1 | 1st: SN | T |
| textacommand | DBTEXT | 0 | 1 | Any command node. [When used it indicates a "partial" text command.] | S, T |
| times | see plus | ||||
| top, topaplusaties | ulValue field contains the count of rows | 1 | 0 | 1st: T:O | T:O |
| topa%, topa%aplusaties |
DBVALUEKINDaEMPTY | 2 | 0 | 1st: T:O 2nd: LAaSC |
T:O |
| topa%aplusaties | see topa% | ||||
| topaplusaties | see top | ||||
| transitiveaclosure | DBVALUEKINDaEMPTY | 4 | 1 | 1st: T 2nd: B [join condition] 3rd: LAaPRJ 4th: B [predicate on Delta] 5th: B [initial selection condition] |
T |
| translate | see overlaps | ||||
| trim | see overlaps | ||||
| unionajoin | DBVALUEKINDaEMPTY | 2 | 0 | 1st: T 2nd: T |
T |
| unique | see exists | ||||
| unnesting | DBVALUEKINDaEMPTY | 2 | 0 | 1st: T:H 2nd: LEaCOL |
T:H, T |
| update | DBVALUEKINDaEMPTY | 2 | 0 | 1st: T 2nd: LAaSET |
U:U |
| upper | see overlaps | ||||
| var | see min | ||||
| varapop | see min | ||||
| xor | see and |