Visual Representation after each phase for Query Life Cycle
CREATE TABLE FOO (empName text, empId int) DISTRIBUTED BY (empName);
CREATE TABLE BAR (empName text, empLocation text) DISTRIBUTED RANDOMLY;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';The following trees represent the state of the above sql query during it’s life cycle
(1) Parse Tree
- Incoming SQL query is checked for syntax and is converted to the data structure represented by QUERY node
- Refer to typedef struct Query in parsenodes.h to understand the meaning of different nodes
- What do we know for the query
EXPLAIN SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo'; 2 Tables are being joined: BAR and FOO；Join Condition is: FOO.empName=BAR.empName；Filter Condition is: FOO.empName=‘Rambo’；Projection: All the Columns of FOO and BAR are requested (or a.k.a projected)； SELECT *, where * represents all the columns. - If you look at the below parse tree, it’s the same SQL query but represented by QUERY node.
3 RTE are referred in rtable list, RTE stands for Range Table Entries, and it represents the relations【You could see the name :aliasname foo and :aliasname bar. The other members in RTE node are basically the metadata for the table. Note: The RTE entries could be the relation which are used in the query, or intermediate relations created by the parser】
Join Tree :jointree contains the FROM part of the query (i.e FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName=‘Rambo’ ) where【Join Condition: Found under the node :jointree -> FROMEXPR -> JOINEXPR （larg or left argument is the relation referred by RTE 1, i.e FOO；rarg or right argument is the relation referred by RTE 2, i.e BAR；rtindex is the index into the rtable list which contains RTE；quals containing OPEXPR is the join condition which refers to FOO.empName=BAR.empName { OPEXPR contains args which has 2 VARS；:varno 1 identifies which RTE this column / variable is coming from, in this case RTE at index 1, i.e FOO；:varno 2 identifies which RTE this column / variable is coming from, in this case RTE at index 2, i.e BAR；varattno 1 indicates the first column for FOO, check :colnames (“empname” “empid”) in RTE for Foo；varattno 1 indicates the first column for BAR, check :colnames :colnames (“empname” “emplocation”) in RTE for BAR；So basically FOO INNER JOIN BAR on FOO.empName=BAR.empName }）；Filter Condition under :jointree: quals under FROMEXPR contains OPEXPR which is the filter condition where（OPEXPR contains args which has { 1 VAR corresponding to varno 1, varattno 1 which is FOO.empName；1 CONST (Constant) corresponding to ‘Rambo’；So, basically FOO.empName=‘Rambo’}）】
Projection / Target List contains the columns projected in the query SELECT * 【Refer to the :targetList containing references to varno 3. Target List refers to columns output by this node. It has 4 entries for the columns, varattno 1 to 4. varno 3 refers to the RTE at index 3 i.e :aliasname unnamed_join in rtable list. aliasname unnamed_join contains the columns :colnames (“empname” “empid” “empname” “emplocation”). Parser created this entry to represent the join between FOO and BAR.】
So, in this case, Join Tree, Projection / Target List node explained above are the primary objects which contains references to other supporting members like rtable
The SQL is equivalent to the Parse Tree, just converted into a QUERY Node. In order to view the parse tree, do the following:
set debug_print_parse=on;
set client_min_messages=log;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';调试小技巧：
p nodeToString(parsetree_list))-
p pretty_format_node_dump(nodeToString(parsetree_list))If the content printed is lengthy, it may be trimmed, you set the following parameter for lldbsetting set target.max-string-summary-length 10000Put it in ~/.lldbinit so that you don’t have to set it every time.
parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDynamicFunctions false
:hasFuncsWithExecRestrictions false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname foo
:colnames ("empname" "empid")
}
:rtekind 0
:relid 124170
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname bar
:colnames ("empname" "emplocation")
}
:rtekind 0
:relid 124176
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join
:colnames ("empname" "empid" "empname" "emplocation")
}
:rtekind 2
:jointype 0
:joinaliasvars (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location -1
}
{VAR
:varno 2
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 2
:location -1
}
)
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{JOINEXPR
:jointype 0
:isNatural false
:larg
{RANGETBLREF
:rtindex 1
}
:rarg
{RANGETBLREF
:rtindex 2
}
:usingClause <>
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 36
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location 48
}
)
:location 47
}
:alias <>
:rtindex 3
}
)
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 66
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 78
:constvalue 9 [ 0 0 0 9 82 97 109 98 111 ]
}
)
:location 77
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 1
:location 7
}
:resno 1
:resname empname
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 2
:location 7
}
:resno 2
:resname empid
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 3
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 3
:location 7
}
:resno 3
:resname empname
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 4
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 4
:location 7
}
:resno 4
:resname emplocation
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 2
:resjunk false
}
)
:withCheckOptions <>
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:scatterClause <>
:isTableValueSelect false
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
:parentStmtType false
}Rewritten Parse Tree
There is no difference in Parse Tree and Rewritten Parse Tree in this particular case. In order to view the rewritten parse tree, do the following:
set debug_print_rewritten=on;
set client_min_messages=log;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';rewritten parse tree:
DETAIL: (
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDynamicFunctions false
:hasFuncsWithExecRestrictions false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname foo
:colnames ("empname" "empid")
}
:rtekind 0
:relid 124170
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname bar
:colnames ("empname" "emplocation")
}
:rtekind 0
:relid 124176
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join
:colnames ("empname" "empid" "empname" "emplocation")
}
:rtekind 2
:jointype 0
:joinaliasvars (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location -1
}
{VAR
:varno 2
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 2
:location -1
}
)
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{JOINEXPR
:jointype 0
:isNatural false
:larg
{RANGETBLREF
:rtindex 1
}
:rarg
{RANGETBLREF
:rtindex 2
}
:usingClause <>
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 36
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location 48
}
)
:location 47
}
:alias <>
:rtindex 3
}
)
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 66
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 78
:constvalue 9 [ 0 0 0 9 82 97 109 98 111 ]
}
)
:location 77
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 1
:location 7
}
:resno 1
:resname empname
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 2
:location 7
}
:resno 2
:resname empid
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 3
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 3
:location 7
}
:resno 3
:resname empname
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 4
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 4
:location 7
}
:resno 4
:resname emplocation
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 2
:resjunk false
}
)
:withCheckOptions <>
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:scatterClause <>
:isTableValueSelect false
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
:parentStmtType false
}
)