根据PostgreSQL14.7版本分析
案例准备
数据准备
create table supplier(sno int, sname text, city text);
create table part(pno int, pname text, price numeric(16,2));
create table sells(sno int, pno int);
insert into supplier values(1, 'Smith', 'London');
insert into supplier values(2, 'Jones', 'Paris');
insert into supplier values(3, 'Adama', 'Vienna');
insert into supplier values(4, 'Blake', 'Rome');
insert into part values(1, 'Screw' , 10);
insert into part values(2, 'Nut' , 8);
insert into part values(2, 'Bolt' , 15);
insert into part values(2, 'Cam' , 25);
insert into sells values(1,1);
insert into sells values(1,2);
insert into sells values(2,4);
insert into sells values(3,1);
insert into sells values(3,3);
insert into sells values(4,2);
insert into sells values(4,3);
insert into sells values(4,4);
案例
select s.sname, se.pno from supplier s, sells se where s.sno > 2 and s.sno = se.sno;
原始解析阶段
原始解析阶段SQL语句发送服务器backend经过函数pg_parse_query得到一个分析树列表(RawStmt列表),当只有一个语句时列表中只有一个元素。
涉及结构体
/*
* 解析分析将以RawStmt节点为头的原始解析树转换为以查询节点为头的经过分析语句
*/
typedef struct RawStmt
{
NodeTag type;
Node *stmt; /* 原始解析树 */
int stmt_location; /* 开始位置 */
int stmt_len; /* 字符长度; 0 means "rest of string" */
} RawStmt;
typedef enum SetOperation
{
SETOP_NONE = 0,
SETOP_UNION,
SETOP_INTERSECT,
SETOP_EXCEPT
} SetOperation;
/*
* 一个“简单”选择由一个SelectStmt节点表示;VALUES结构也是如此。
* 一个查询包含集合操作(UNION, INTERSECT, EXCEPT) 由SelectStmt节点树表示,
* 其中叶子节点是组件选择,父节点表示UNION、INTERSECT或EXCEPT操作符。
*
*/
typedef struct SelectStmt
{
NodeTag type;
/*
* These fields are used only in "leaf" SelectStmts.
*/
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
IntoClause *intoClause; /* target for SELECT INTO */
List *targetList; /* the target list (of ResTarget) */
List *fromClause; /* the FROM clause */
Node *whereClause; /* WHERE qualification */
List *groupClause; /* GROUP BY clauses */
bool groupDistinct; /* Is this GROUP BY DISTINCT? */
Node *havingClause; /* HAVING conditional-expression */
List *windowClause; /* WINDOW window_name AS (...), ... */
/*
* In a "leaf" node representing a VALUES list, the above fields are all
* null, and instead this field is set. Note that the elements of the
* sublists are just expressions, without ResTarget decoration. Also note
* that a list element can be DEFAULT (represented as a SetToDefault
* node), regardless of the context of the VALUES list. It's up to parse
* analysis to reject that where not valid.
*/
List *valuesLists; /* untransformed list of expression lists */
/*
* These fields are used in both "leaf" SelectStmts and upper-level
* SelectStmts.
*/
List *sortClause; /* sort clause (a list of SortBy's) */
Node *limitOffset; /* # of result tuples to skip */
Node *limitCount; /* # of result tuples to return */
LimitOption limitOption; /* limit type */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
/*
* These fields are used only in upper-level SelectStmts.
*/
SetOperation op; /* type of set op */
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
/* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
/*
* 结果目标(用于预转换解析树的目标列表)
* 在一个SELECT 目标列表,'name' 是来自'AS ColumnLabel'从句的列标签,如果没有则是
* NULL,'val' 是值表达式自身,'indirection' 字段未使用。
*
* INSERT使用ResTarget作为它的目标列表名,在这里'name'是目标列的名字,
* 'indirection' 储存绑定到目标的任意下表,'val' 没有使用。
*
* 在UPDATE 目标列表中 'name' 是目标列的名字,'indirection' 储存任意下标,
* val 是赋值表达式
*/
typedef struct ResTarget
{
NodeTag type;
char *name; /* column name or NULL */
List *indirection; /* subscripts, field names, and '*', or NIL */
Node *val; /* the value expression to compute or assign */
int location; /* token location, or -1 if unknown */
} ResTarget;
/*
* ColumnRef-指定对列的引用,或者可能是对一整个元组的引用“fields”列表必须非空。
* 它可包含字符串Value节点(表示名字)和A_Star节点(表示出现的一个“*”)。目前,
* A_Star必须只作为最后一个列表元素出现---语法负责执行这一规则!
* 注:任何容器下标操作或从合成列中选择字段的操作都由ColumnRef之上的A_Indirection
* 节点来表示。然而,为简化正常情况,从一个表名字初始选择字段由ColumnRef来表示
* 而不是通过添加A_Indirection。
*/
typedef struct ColumnRef
{
NodeTag type;
List *fields; /* field names (Value strings) or A_Star */
int location; /* token location, or -1 if unknown */
} ColumnRef;
/*
* 范围变量,用于FROM从句
*
* 还用于在实用程序语句中表示表名;alias字段在这种情况下不使用
* ,而inh字段告诉是否递归地将操作应用于子表。在某些情况下,在
* 这里提供TEMP表的指示也是有用的。
*/
typedef struct RangeVar
{
NodeTag type;
char *catalogname; /* the catalog (database) name, or NULL */
char *schemaname; /* the schema name, or NULL */
char *relname; /* the relation/sequence name */
bool inh; /* expand rel by inheritance? recursively act
* on children? */
char relpersistence; /* see RELPERSISTENCE_* in pg_class.h */
Alias *alias; /* table alias & optional column aliases */
int location; /* token location, or -1 if unknown */
} RangeVar;
/*
* 指定范围变量的别名;别名也可能指定表中列的重命名。
*/
typedef struct Alias
{
NodeTag type;
char *aliasname; /* aliased rel name (never qualified) */
List *colnames; /* optional list of column aliases */
} Alias;
/*
* BoolExpr—用于基本布尔运算符“AND”、“OR”、“NOT”的表达式节点
*
* 注意,参数是一个列表。
* 对于NOT始终只有一个元素。
* 对于AND和OR,可以有两个或者更多的参数。
*/
typedef enum BoolExprType
{
AND_EXPR, OR_EXPR, NOT_EXPR
} BoolExprType;
typedef struct BoolExpr
{
Expr xpr;
BoolExprType boolop;
List *args; /* arguments to this expression */
int location; /* token location, or -1 if unknown */
} BoolExpr;
/*
* 中缀、前缀和后缀表达式
*/
typedef enum A_Expr_Kind
{
AEXPR_OP, /* normal operator */
AEXPR_OP_ANY, /* scalar op ANY (array) */
AEXPR_OP_ALL, /* scalar op ALL (array) */
AEXPR_DISTINCT, /* IS DISTINCT FROM - name must be "=" */
AEXPR_NOT_DISTINCT, /* IS NOT DISTINCT FROM - name must be "=" */
AEXPR_NULLIF, /* NULLIF - name must be "=" */
AEXPR_IN, /* [NOT] IN - name must be "=" or "<>" */
AEXPR_LIKE, /* [NOT] LIKE - name must be "~~" or "!~~" */
AEXPR_ILIKE, /* [NOT] ILIKE - name must be "~~*" or "!~~*" */
AEXPR_SIMILAR, /* [NOT] SIMILAR - name must be "~" or "!~" */
AEXPR_BETWEEN, /* name must be "BETWEEN" */
AEXPR_NOT_BETWEEN, /* name must be "NOT BETWEEN" */
AEXPR_BETWEEN_SYM, /* name must be "BETWEEN SYMMETRIC" */
AEXPR_NOT_BETWEEN_SYM /* name must be "NOT BETWEEN SYMMETRIC" */
} A_Expr_Kind;
typedef struct A_Expr
{
NodeTag type;
A_Expr_Kind kind; /* see above */
List *name; /* possibly-qualified name of operator */
Node *lexpr; /* left argument, or NULL if none */
Node *rexpr; /* right argument, or NULL if none */
int location; /* token location, or -1 if unknown */
} A_Expr;
/*
* 字面符常量
*/
typedef struct A_Const
{
NodeTag type;
Value val; /* value (includes type info, see value.h) */
int location; /* token location, or -1 if unknown */
} A_Const;
/*
* 同样的struct值用于5种结点类型:T_Integer、T_Float,
* T_String, T_BitString, T_Null。
*
* 整数值实际上是用机器整数表示的,但浮点数和字符串都表示为字符串。
* 使用T_Float作为结点类型表明字符串的内容看起来像一个有效的数字字面量。
*
* (在Postgres 7.0之前,我们使用double来表示T_Float,这会造成精度损失
* 问题最终注定要转换为数值。由于值节点只在解析过程中使用,而不是用于
* 运行时数据,所以最好使用更通用的表示形式。)
*
* 请注意,看起来是整数的字符串将被词法化为T_Float,如果这个值太大,
* 无法用int表示。
*
* 当然,null完全不需要值部分。
*/
typedef struct Value
{
NodeTag type; /* tag appropriately (eg. T_String) */
union ValUnion
{
int ival; /* machine integer */
char *str; /* string */
} val;
} Value;