一、背景

在oracle迁移postgres中，为了尽量减少对原有系统的改造，除了迁移数据之外，postgres对oracle的SQL语法兼容也是必不可少的一部分。

本文主要研究postgres对oracle 的外关联兼容性。

oracle连接的语法分为2类：
1、oracle专用语法（传统语法，主要是指sql:89语法，被广泛支持,是oracle专门支持的语法）
2、ANSI SQL:99（标准语法，oracle在8i后引入了sql99的语法，各厂商据此发展自己的sql：T-SQL,PL/SQL）

Oracle为了适应大多数人的使用，既支持了oracle专用语法，也支持了ANSI SQL:99语法，所以oracle 9i实现多表之间的连接既可以用oracle专用语法，也可以用ANSISQL:99语法。两种语法没有效率上的提高。
但对一些古董级别的oracle使用者，沿用oracle专用语法，可以保证自己的SQL兼容oracle 8i 之前的数据库系统。

注：现有的业务系统源码，很多都有这部分古董级别的踪迹。

二、准备测试数据

CREATE TABLE t_A (
id   int,
code   int,
name  VARCHAR(10)
);
 
CREATE TABLE t_B (
id   int,
code   int,
name  VARCHAR(10)
);
 
INSERT INTO t_A(id,code,name) VALUES(1,2,'A');
INSERT INTO t_A(id,code,name) VALUES(2,1,'B');
INSERT INTO t_A(id,code,name) VALUES(3,5,'C');
INSERT INTO t_A(id,code,name) VALUES(4,6,'D');
INSERT INTO t_A(id,code,name) VALUES(5,7,'E');
 
INSERT INTO t_B(id,code,name) VALUES(1,3,'AA');
INSERT INTO t_B(id,code,name) VALUES(1,4,'BB');
INSERT INTO t_B(id,code,name) VALUES(2,1,'CC');
INSERT INTO t_B(id,code,name) VALUES(1,2,'DD');
INSERT INTO t_B(id,code,name) VALUES(7,5,'GG');

三、语法分析过程

oracle专用标准的关联查询，以两个关联键值的左关联SQL为例。

select * 
from t_a a,t_b b 
where a.id=b.id(+) and a.code=b.code(+) 
    and a.id!=8;

转换为ANSI标准的left /right/full join 的写法如下：

select * 
from t_a a 
left join t_b b on a.id=b.id and a.code=b.code
where a.id!=8;

断点分析外关联语法树：

/*连接数据库，并获取当前连接的pid。*/
[appusr@postgre ~]$ psql -h 127.0.0.1 -d postgres
psql (11beta2)
Type "help" for help.

postgres=# select pg_backend_pid();                                                       
 pg_backend_pid 
----------------
          10473
(1 row)

/*切换到gdb*/
[appusr@postgre ~]$ gdb
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-110.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
(gdb) attach 10473
Attaching to process 10473
...
/* 设置断点 */
(gdb) b exec_simple_query 
Breakpoint 1 at 0x8c35db: file postgres.c, line 893.
(gdb) c
Continuing.

/*切换到psql，执行查询*/
postgres=# select * from t_a a 
left join t_b b on a.id=b.id and a.code=b.code
where a.id!=8;

/*切换到gdb，分析语法树*/
Breakpoint 1, exec_simple_query (query_string=0x2574968 "select * from t_a a \nleft join t_b b on a.id=b.id and a.code=b.code\nwhere a.id!=8;") at postgres.c:893
893             CommandDest dest = whereToSendOutput;
(gdb) n
897             bool            save_log_statement_stats = log_statement_stats;
(gdb) 
898             bool            was_logged = false;
(gdb) 
905             debug_query_string = query_string;
(gdb) 
907             pgstat_report_activity(STATE_RUNNING, query_string);
(gdb) 
909             TRACE_POSTGRESQL_QUERY_START(query_string);
(gdb) 
915             if (save_log_statement_stats)
(gdb) 
925             start_xact_command();
(gdb) 
933             drop_unnamed_stmt();
(gdb) 
938             oldcontext = MemoryContextSwitchTo(MessageContext);
(gdb) 
944             parsetree_list = pg_parse_query(query_string); /* 一个字符串内可能含有多个SQL，所以语法分析的结果是列表结构 */
(gdb) 
947             if (check_log_statement(parsetree_list))
(gdb) 
949                     ereport(LOG,
(gdb) 
953                     was_logged = true;
(gdb) 
959             MemoryContextSwitchTo(oldcontext);
(gdb) 
969             use_implicit_block = (list_length(parsetree_list) > 1);
(gdb) 
974             foreach(parsetree_item, parsetree_list)
(gdb) 
976                     RawStmt    *parsetree = lfirst_node(RawStmt, parsetree_item); /* 一个SQL的语法树 */
(gdb) 
977                     bool            snapshot_set = false;
(gdb) p (*parsetree)
$1 = {type = T_RawStmt, stmt = 0x25761b0, stmt_location = 0, stmt_len = 81}
(gdb) p *(parsetree->stmt)
$2 = {type = T_SelectStmt}
(gdb) p *((SelectStmt*)parsetree->stmt)
$3 = {type = T_SelectStmt, distinctClause = 0x0, intoClause = 0x0, targetList = 0x25754f8, fromClause = 0x2575f10, whereClause = 0x25760d0, groupClause = 0x0, havingClause = 0x0, windowClause = 0x0, 
  valuesLists = 0x0, sortClause = 0x0, limitOffset = 0x0, limitCount = 0x0, lockingClause = 0x0, withClause = 0x0, op = SETOP_NONE, all = false, larg = 0x0, rarg = 0x0}

/* 分析targetList结构 */
(gdb) p *((SelectStmt*)parsetree->stmt)->targetList
$4 = {type = T_List, length = 1, head = 0x25754d0, tail = 0x25754d0}
(gdb) p *((SelectStmt*)parsetree->stmt)->targetList->head
$5 = {data = {ptr_value = 0x2575478, int_value = 39277688, oid_value = 39277688}, next = 0x0}
(gdb) p *((Node*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)
$6 = {type = T_ResTarget}
(gdb) p *((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)
$7 = {type = T_ResTarget, name = 0x0, indirection = 0x0, val = 0x25753c0, location = 7}
(gdb) p *((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val
$8 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)
$9 = {type = T_ColumnRef, fields = 0x2575440, location = 7}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields
$10 = {type = T_List, length = 1, head = 0x2575418, tail = 0x2575418}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head
$11 = {data = {ptr_value = 0x25753f8, int_value = 39277560, oid_value = 39277560}, next = 0x0}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
$12 = {type = T_A_Star}
(gdb) p *((A_Star*)((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
$13 = {type = T_A_Star}  /* 标识 '*' */

/* 分析fromClause结构 */
(gdb) p *((SelectStmt*)parsetree->stmt)->fromClause
$14 = {type = T_List, length = 1, head = 0x2575ee8, tail = 0x2575ee8}
(gdb) p *((Node*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)
$15 = {type = T_JoinExpr}
(gdb) p *((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)
$16 = {type = T_JoinExpr, jointype = JOIN_LEFT, isNatural = false, larg = 0x2575570, rarg = 0x2575640, usingClause = 0x0, quals = 0x2575e58, alias = 0x0, rtindex = 0}
(gdb) p *((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->larg
$17 = {type = T_RangeVar}
(gdb) p *((RangeVar*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->larg)
$18 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0, relname = 0x2575530 "t_a", inh = true, relpersistence = 112 'p', alias = 0x25755c8, location = 14}
(gdb) p *((RangeVar*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->larg)->alias
$19 = {type = T_Alias, aliasname = 0x2575550 "a", colnames = 0x0}
(gdb) p *((RangeVar*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->rarg)
$20 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0, relname = 0x2575600 "t_b", inh = true, relpersistence = 112 'p', alias = 0x2575698, location = 31}
(gdb) p *((RangeVar*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->rarg)->alias
$21 = {type = T_Alias, aliasname = 0x2575620 "b", colnames = 0x0}
 
/*分析join on条件*/
(gdb) p *((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals
$22 = {type = T_BoolExpr}
(gdb) p *((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)
$23 = {xpr = {type = T_BoolExpr}, boolop = AND_EXPR, args = 0x2575df8, location = 50}
(gdb) p *((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->agrs
There is no member named agrs.
(gdb) p *((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args
$24 = {type = T_List, length = 2, head = 0x2575e30, tail = 0x2575dd0} /*注意，长度为2，列表中有两个节点*/
(gdb) p *((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value
Attempt to dereference a generic pointer.

/* 分析join on第一个条件，args的第一个元素:a.id=b.id */
(gdb) p *((Node*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)
$25 = {type = T_A_Expr}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)
$26 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2575a18, lexpr = 0x2575798, rexpr = 0x25758e8, location = 44}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->name
$27 = {type = T_List, length = 1, head = 0x25759f0, tail = 0x25759f0}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->name->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->name->head->data->ptr_value)
$28 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->name->head->data->ptr_value)
$29 = {type = T_String, val = {ival = 12092910, str = 0xb885ee "="}}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr
$30 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)
$31 = {type = T_ColumnRef, fields = 0x2575760, location = 40}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)->fields
$32 = {type = T_List, length = 2, head = 0x25757f8, tail = 0x2575738}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$33 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$34 = {type = T_String, val = {ival = 39278288, str = 0x25756d0 "a"}}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$35 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$36 = {type = T_String, val = {ival = 39278320, str = 0x25756f0 "id"}}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr
$37 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)
$38 = {type = T_ColumnRef, fields = 0x25758b0, location = 45}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)->fields
$39 = {type = T_List, length = 2, head = 0x2575948, tail = 0x2575888}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$40 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$41 = {type = T_String, val = {ival = 39278624, str = 0x2575820 "b"}}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)->fields->head->next->data->ptr_value)
$42 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->data->ptr_value)->rexpr)->fields->head->next->data->ptr_value)
$43 = {type = T_String, val = {ival = 39278656, str = 0x2575840 "id"}}

/* 分析join on第二个条件，args的第二个元素:a.code=b.code */
(gdb) p *((Node*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)
$44 = {type = T_A_Expr}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)
$45 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2575d98, lexpr = 0x2575b18, rexpr = 0x2575c68, location = 60}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->name
$46 = {type = T_List, length = 1, head = 0x2575d70, tail = 0x2575d70}
(gdb) p *((Node*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->name->head->data->ptr_value)
$47 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->name->head->data->ptr_value)
$48 = {type = T_String, val = {ival = 12092910, str = 0xb885ee "="}}
(gdb) p *((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr
$49 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr)
$50 = {type = T_ColumnRef, fields = 0x2575ae0, location = 54}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr)->fields
$51 = {type = T_List, length = 2, head = 0x2575b78, tail = 0x2575ab8}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$52 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$53 = {type = T_String, val = {ival = 39279184, str = 0x2575a50 "a"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$54 = {type = T_String, val = {ival = 39279216, str = 0x2575a70 "code"}}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->rexpr)
$55 = {type = T_ColumnRef, fields = 0x2575c30, location = 61}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->rexpr)->fields
$56 = {type = T_List, length = 2, head = 0x2575cc8, tail = 0x2575c08}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$57 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$58 = {type = T_String, val = {ival = 39279520, str = 0x2575ba0 "b"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((JoinExpr*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->quals)->args->head->next->data->ptr_value)->rexpr)->fields->head->next->data->ptr_value)
$59 = {type = T_String, val = {ival = 39279552, str = 0x2575bc0 "code"}}

/* 分析where条件部分:a.id=8*/
(gdb) p *((SelectStmt*)parsetree->stmt)->whereClause
$60 = {type = T_A_Expr}
(gdb) p *((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)
$61 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2576178, lexpr = 0x2576010, rexpr = 0x2576098, location = 78}
(gdb) p *((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->name
$62 = {type = T_List, length = 1, head = 0x2576150, tail = 0x2576150}
(gdb) p *((Node*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->name->head->data->ptr_value)
$63 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->name->head->data->ptr_value)
$64 = {type = T_String, val = {ival = 12092918, str = 0xb885f6 "<>"}}
(gdb) p *((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr
$65 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr)
$66 = {type = T_ColumnRef, fields = 0x2575fd8, location = 74}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr)->fields
$67 = {type = T_List, length = 2, head = 0x2576070, tail = 0x2575fb0}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr)->fields->head->data->ptr_value)
$68 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr)->fields->head->data->ptr_value)
$69 = {type = T_String, val = {ival = 39280456, str = 0x2575f48 "a"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->lexpr)->fields->head->next->data->ptr_value)
$70 = {type = T_String, val = {ival = 39280488, str = 0x2575f68 "id"}}
(gdb) p *((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->rexpr
$71 = {type = T_A_Const}
(gdb) p *((A_Const*)((A_Expr*)((SelectStmt*)parsetree->stmt)->whereClause)->rexpr)
$72 = {type = T_A_Const, val = {type = T_Integer, val = {ival = 8, str = 0x8 <Address 0x8 out of bounds>}}, location = 80}
(gdb) 

parsetree的结构：

外关联语法树

因为现在还没有改造，无法识别oracle专用语法

select * 
from t_a a,t_b b 
where a.id=b.id(+) and a.code=b.code(+) 
    and a.id!=8;

先分析对应的内关联SQL查询（删除"(+)"字符串之后的）：

select * 
from t_a a,t_b b 
where a.id=b.id and a.code=b.code
    and a.id!=8;

/*切换到psql，执行查询*/
postgres=# select * 
from t_a a,t_b b 
where a.id=b.id and a.code=b.code
    and a.id!=8;

/*切换到gdb，分析语法树*/
Breakpoint 1, exec_simple_query (query_string=0x2574968 "select * \nfrom t_a a,t_b b \nwhere a.id=b.id and a.code=b.code and a.id!=8;") at postgres.c:893
893             CommandDest dest = whereToSendOutput;
(gdb) bt
#0  exec_simple_query (query_string=0x2574968 "select * \nfrom t_a a,t_b b \nwhere a.id=b.id and a.code=b.code and a.id!=8;") at postgres.c:893
#1  0x00000000008c7d23 in PostgresMain (argc=1, argv=0x25a0890, dbname=0x25a06f0 "postgres", username=0x25a06d0 "appusr") at postgres.c:4153
#2  0x000000000082405c in BackendRun (port=0x25966a0) at postmaster.c:4361
#3  0x00000000008237c0 in BackendStartup (port=0x25966a0) at postmaster.c:4033
#4  0x000000000081fb58 in ServerLoop () at postmaster.c:1706
#5  0x000000000081f3f0 in PostmasterMain (argc=3, argv=0x256f320) at postmaster.c:1379
#6  0x00000000007469d4 in main (argc=3, argv=0x256f320) at main.c:228
(gdb) n
897             bool            save_log_statement_stats = log_statement_stats;
(gdb) 
898             bool            was_logged = false;
(gdb) 
905             debug_query_string = query_string;
(gdb) 
907             pgstat_report_activity(STATE_RUNNING, query_string);
(gdb) 
909             TRACE_POSTGRESQL_QUERY_START(query_string);
(gdb) 
915             if (save_log_statement_stats)
(gdb) 
925             start_xact_command();
(gdb) 
933             drop_unnamed_stmt();
(gdb) 
938             oldcontext = MemoryContextSwitchTo(MessageContext);
(gdb) 
944             parsetree_list = pg_parse_query(query_string);
(gdb) 
947             if (check_log_statement(parsetree_list))
(gdb) 
949                     ereport(LOG,
(gdb) 
953                     was_logged = true;
(gdb) 
959             MemoryContextSwitchTo(oldcontext);
(gdb) 
969             use_implicit_block = (list_length(parsetree_list) > 1);
(gdb) 
974             foreach(parsetree_item, parsetree_list)
(gdb) 
976                     RawStmt    *parsetree = lfirst_node(RawStmt, parsetree_item);
(gdb) 
977                     bool            snapshot_set = false;
(gdb) p *parsetree
$73 = {type = T_RawStmt, stmt = 0x25761a8, stmt_location = 0, stmt_len = 73}
(gdb) p *parsetree->stmt
$74 = {type = T_SelectStmt}
(gdb) p *((SelectStmt*)parsetree->stmt)
$75 = {type = T_SelectStmt, distinctClause = 0x0, intoClause = 0x0, targetList = 0x25754f8, fromClause = 0x2575628, whereClause = 0x2575ee0, groupClause = 0x0, havingClause = 0x0, windowClause = 0x0, 
  valuesLists = 0x0, sortClause = 0x0, limitOffset = 0x0, limitCount = 0x0, lockingClause = 0x0, withClause = 0x0, op = SETOP_NONE, all = false, larg = 0x0, rarg = 0x0}

/* 分析targetList结构 */
(gdb) p *((SelectStmt*)parsetree->stmt)->targetList
$76 = {type = T_List, length = 1, head = 0x25754d0, tail = 0x25754d0}
(gdb) p *((Node*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)
$77 = {type = T_ResTarget}
(gdb) p *((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)
$78 = {type = T_ResTarget, name = 0x0, indirection = 0x0, val = 0x25753c0, location = 7}
(gdb) p *((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val
$79 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)
$80 = {type = T_ColumnRef, fields = 0x2575440, location = 7}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields
$81 = {type = T_List, length = 1, head = 0x2575418, tail = 0x2575418}
(gdb) p *((Node*)((ColumnRef*)((ResTarget*)((SelectStmt*)parsetree->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
$82 = {type = T_A_Star}

/* 分析fromClause结构 */
(gdb) p *((SelectStmt*)parsetree->stmt)->fromClause
$83 = {type = T_List, length = 2, head = 0x2575600, tail = 0x2575730} /* 两个节点*/
(gdb) p *((Node*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)
$84 = {type = T_RangeVar}
(gdb) p *((RangeVar*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)
$85 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0, relname = 0x2575530 "t_a", inh = true, relpersistence = 112 'p', alias = 0x25755c8, location = 15}
(gdb) p *((RangeVar*)((SelectStmt*)parsetree->stmt)->fromClause->head->data->ptr_value)->alias
$86 = {type = T_Alias, aliasname = 0x2575550 "a", colnames = 0x0}
(gdb) p *((Node*)((SelectStmt*)parsetree->stmt)->fromClause->head->next->data->ptr_value)
$87 = {type = T_RangeVar}
(gdb) p *((RangeVar*)((SelectStmt*)parsetree->stmt)->fromClause->head->next->data->ptr_value)
$88 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0, relname = 0x2575660 "t_b", inh = true, relpersistence = 112 'p', alias = 0x25756f8, location = 21}
(gdb) p *((RangeVar*)((SelectStmt*)parsetree->stmt)->fromClause->head->next->data->ptr_value)->alias
$89 = {type = T_Alias, aliasname = 0x2575680 "b", colnames = 0x0}

/* 分析whereClause结构 */
(gdb) p *((SelectStmt*)parsetree->stmt)->whereClause
$90 = {type = T_BoolExpr}
(gdb) p *((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)
$91 = {xpr = {type = T_BoolExpr}, boolop = AND_EXPR, args = 0x2575e80, location = 44}
(gdb) p *((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args
$92 = {type = T_List, length = 3, head = 0x2575eb8, tail = 0x2576180} /* 三个节点*/

/* 分析whereClause结构，第一个条件：a.id=b.id */
(gdb) p *((Node*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)
$93 = {type = T_A_Expr}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)
$95 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2575aa0, lexpr = 0x2575820, rexpr = 0x2575970, location = 38}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->name
$96 = {type = T_List, length = 1, head = 0x2575a78, tail = 0x2575a78}
(gdb) p *((Node*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->name->head->data->ptr_value)
$97 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->name->head->data->ptr_value)
$98 = {type = T_String, val = {ival = 12092910, str = 0xb885ee "="}}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr
$99 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)
$100 = {type = T_ColumnRef, fields = 0x25757e8, location = 34}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)->fields
$101 = {type = T_List, length = 2, head = 0x2575880, tail = 0x25757c0}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$102 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$103 = {type = T_String, val = {ival = 39278424, str = 0x2575758 "a"}}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$104 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$105 = {type = T_String, val = {ival = 39278456, str = 0x2575778 "id"}}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr
$106 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr)
$107 = {type = T_ColumnRef, fields = 0x2575938, location = 39}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr)->fields
$108 = {type = T_List, length = 2, head = 0x25759d0, tail = 0x2575910}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$109 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr)->fields->head->data->ptr_value)
$110 = {type = T_String, val = {ival = 39278760, str = 0x25758a8 "b"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->data->ptr_value)->rexpr)->fields->head->next->data->ptr_value)
$111 = {type = T_String, val = {ival = 39278792, str = 0x25758c8 "id"}}

/* 分析whereClause结构，第二个条件：a.code=b.code */
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)
$112 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2575e20, lexpr = 0x2575ba0, rexpr = 0x2575cf0, location = 54}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->name
$113 = {type = T_List, length = 1, head = 0x2575df8, tail = 0x2575df8}
(gdb) p *((Node*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->name->head->data->ptr_value)
$114 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->name->head->data->ptr_value)
$115 = {type = T_String, val = {ival = 12092910, str = 0xb885ee "="}}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr
$116 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr)
$117 = {type = T_ColumnRef, fields = 0x2575b68, location = 48}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr)->fields
$118 = {type = T_List, length = 2, head = 0x2575c00, tail = 0x2575b40}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$119 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$120 = {type = T_String, val = {ival = 39279320, str = 0x2575ad8 "a"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$121 = {type = T_String, val = {ival = 39279352, str = 0x2575af8 "code"}}

/* 分析whereClause结构，第三个条件：a.id=8 */
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)
$122 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x2576148, lexpr = 0x2575fe0, rexpr = 0x2576068, location = 70}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->name
$123 = {type = T_List, length = 1, head = 0x2576120, tail = 0x2576120}
(gdb) p *((Node*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->name->head->data->ptr_value)
$124 = {type = T_String}
(gdb) p *((Value*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->name->head->data->ptr_value)
$125 = {type = T_String, val = {ival = 12092918, str = 0xb885f6 "<>"}}
(gdb) p *((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr
$126 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr)
$127 = {type = T_ColumnRef, fields = 0x2575fa8, location = 66}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr)->fields
$128 = {type = T_List, length = 2, head = 0x2576040, tail = 0x2575f80}
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$129 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr)->fields->head->data->ptr_value)
$130 = {type = T_String, val = {ival = 39280408, str = 0x2575f18 "a"}}
(gdb) p *((Value*)((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->lexpr)->fields->head->next->data->ptr_value)
$131 = {type = T_String, val = {ival = 39280440, str = 0x2575f38 "id"}}
(gdb) p *((ColumnRef*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->rexpr)
$132 = {type = T_A_Const, fields = 0xd8, location = 8}
(gdb) p *((A_Const*)((A_Expr*)((BoolExpr*)((SelectStmt*)parsetree->stmt)->whereClause)->args->head->next->next->data->ptr_value)->rexpr)
$133 = {type = T_A_Const, val = {type = T_Integer, val = {ival = 8, str = 0x8 <Address 0x8 out of bounds>}}, location = 72}
(gdb) 

对应的语法树：

内关联语法树

四、兼容性改造

为了兼容oracle专有的外关联语法，类似以下语法

select * 
from t_a a,t_b b 
where a.id=b.id(+) and a.code=b.code(+) 
    and a.id!=8;

通过改造语法分析器，上面的SQL对应的语法树为：

改造后的语法树

然后在通过算法，将上面的语法树修改为外关联查询语法树

外关联语法树2

本语法树就是标准的外关联语法树，SQL查询的后续 语义分析 及 生成执行计划均不需要修改，即可实现oracle的专有语法兼容。

总结

通过分析原有的外关联语法树，及对应的内关联语法树，通过改造语法分析器和语法树，达到兼容oracle的目的