package gudusoft.gsqlparser;


import gudusoft.gsqlparser.compiler.*;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.nodes.dax.TDaxFunction;
import gudusoft.gsqlparser.scriptWriter.TScriptGenerator;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.sqlenv.TSQLFunction;
import gudusoft.gsqlparser.stmt.*;
import gudusoft.gsqlparser.stmt.dax.TDaxStmt;
import gudusoft.gsqlparser.stmt.oracle.*;
import gudusoft.gsqlparser.util.functionChecker;
import gudusoft.gsqlparser.util.keywordChecker;

import java.util.ArrayList;
import java.util.Stack;
import java.util.TreeMap;

/**
 * TCustomSqlStatement is the root class for all SQL statements.
 */
public class TCustomSqlStatement extends TParseTreeNode implements IRelation{

    public void setUsingVariableList(TColumnDefinitionList usingVariableList) {
        this.usingVariableList = usingVariableList;
    }

    private TColumnDefinitionList usingVariableList;

    /*
    * Variables defined in teradata using clause.
    * */
    public TColumnDefinitionList getUsingVariableList() {
        return usingVariableList;
    }

    protected ArrayList<TAttributeNode> relationAttributes = new ArrayList<>();

    @Override
    public ArrayList<TAttributeNode> getAttributes(){
       // if (relationAttributes.size() != 0) return relationAttributes;
        relationAttributes.clear();
        for(TTable table:relations){
            //relationAttributes.addAll(table.getAttributes());
            TAttributeNode.addAllNodesToList(table.getAttributes(),relationAttributes);
        }

        return relationAttributes;
    }

    @Override
    public String getRelationName(){
        return null;
    }

    @Override
    public int size(){
        return relationAttributes.size();
    }

    @Override
    public String toScript(){
        String ret = super.toScript();
        if ((ret == null)||(ret.isEmpty())){
            ret = this.toString();
        }else{
            if ((this.getEndToken() != null) && (this.getEndToken().tokencode == ';')) {
                if (!ret.endsWith(";")){
                    ret = ret + ";";
                }
            }
        }
        return ret;
    }

    protected TFromClause fromClause;

    public void setFromClause(TFromClause fromClause) {
        this.fromClause = fromClause;
    }

    public TFromClause getFromClause() {
        return fromClause;
    }

    /**
     * Relations that used in from clause of select statement.
     * Or tables of other statements such as insert, update, delete and etc
     *
     * Please use this property to get the relations instead of {@link #getTables()} and {@link #getJoins()}after version 2.7.4.0
     *
     * when a join is used in from clause, then the table in getRelations() is type of ETableSource.join, and you can
     * use TTable.getJoinExpr() to get this join.
     *
     * @return
     */
    public ArrayList<TTable> getRelations() {
        return relations;
    }

    private ArrayList<TTable> relations = new ArrayList<>();

    protected TTable fromSourceTable;

    /**
     * This is table in from clause if only one table is listed in the from clause,
     * If more than one table is listed in from clause, please check {@link #getFromSourceJoin()} instead.
     *
     * @return table in from clause
     */
    public TTable getFromSourceTable() {
        return fromSourceTable;
    }

    /**
     * This is a join in from clause, including left and right relation.
     * If only a single table is listed in from clause, please use {@link #getFromSourceTable()} instead
     * @return
     */
    public TJoinExpr getFromSourceJoin() {
        return fromSourceJoin;
    }

    protected TJoinExpr fromSourceJoin;

    private String asCanonicalText = null;

    /**
     *  this method return a canonical form of a SQL statement in plan text.
     *  <br>1. remove all comment inside SQL query.
     *  <br>2. remove redundant parenthesis at the begin/end of a select statement.
     *  <br>3. replace all number in where clause with 999 constant
     *  <br>4. replace all string constant in where clause with 'placeholder_str'
     *  <br>5. all number elements in a list such as (1,2,3,4) will be change to a single element (999)
     *  <br>6. all string elements in a list such as ('a','b','c','d') will be change to a single element ('placeholder_str')
     *
     * @return a canonical form of a SQL statement in plan text.
     */
    public String asCanonical(){
        if (asCanonicalText != null) return asCanonicalText;

        String ret = null;
        TSourceToken lcStartToken = getStartToken();
        if (lcStartToken ==  null) return toString();
        TSourceToken lcEndToken = getEndToken();
        if (lcEndToken ==  null) return toString();

        // remove the ; token at the end of statement
        if (lcEndToken.tokencode == ';') lcEndToken.tokenstatus = ETokenStatus.tsdeleted;


        // remove ( ) at the begin and end of the statement
        TSourceToken lcCurrentToken = lcStartToken;
        while (lcCurrentToken != null){
            if (lcCurrentToken.tokencode != '(') {
                break;
            }else{
                if (lcCurrentToken.getLinkToken() != null){
                    lcCurrentToken.tokenstatus  = ETokenStatus.tsdeleted;
                    lcCurrentToken.getLinkToken().tokenstatus  = ETokenStatus.tsdeleted;
                }
            }

            if (lcCurrentToken.equals(lcEndToken)){
                break;
            }else{
                lcCurrentToken = lcCurrentToken.getNextTokenInChain();
            }
        }

        // change constant to placeholder, all number change to 999 and string constant change to placeholder_str
        constantVisitor cv = new constantVisitor();
        this.acceptChildren(cv);


        boolean chainUnchanged = true, includingComment = false;
        StringBuffer sb = new StringBuffer("");
        TSourceToken lcPrevSt = null;
        boolean ignoreNextReturnToken = false, isChainModified = false;

        lcCurrentToken = lcStartToken;
        while (lcCurrentToken != null){
            if((lcCurrentToken.tokenstatus == ETokenStatus.tsdeleted)
                    ||(!includingComment  && ((lcCurrentToken.tokencode == TBaseType.cmtslashstar) ||(lcCurrentToken.tokencode == TBaseType.cmtdoublehyphen)))
            ){
                // ignore this token, do nothing
                //System.out.println("out: ignore deleted token:"+lcCurrentToken.astext);
            }else{
                //
                sb.append(lcCurrentToken.toString());
                if (lcCurrentToken.isChangedInAsCanonical()){
                    lcCurrentToken.restoreText();
                }
            }

            if (lcCurrentToken.equals(lcEndToken)){
                break;
            }else{
                lcCurrentToken = lcCurrentToken.getNextTokenInChain();
            }

        }
        asCanonicalText = sb.toString();
        return asCanonicalText;
    }

    private TCTE cteIncludeThisStmt = null;

    public void setCteIncludeThisStmt(TCTE cteIncludeThisStmt) {
        this.cteIncludeThisStmt = cteIncludeThisStmt;
    }

    public TCTE getCteIncludeThisStmt() {
        return cteIncludeThisStmt;
    }

    private TreeMap<String,TResultColumn> expandedResultColumns = null;

    public TreeMap<String,TResultColumn> getExpandedResultColumns() {
        if (expandedResultColumns == null){
            expandedResultColumns = new TreeMap<>();
        }
        return expandedResultColumns;
    }

    public TSQLFunction searchFunctionInSQLEnv(String functionName){
        if (getSqlEnv() == null) return null;
        return getSqlEnv().searchFunction(functionName);
    }

    public TSQLEnv getSqlEnv() {
        if (getGlobalScope() == null) return null;
        return getGlobalScope().getSqlEnv();
    }

    public TGlobalScope getGlobalScope() {
        TGlobalScope lcResult = null;
        if (frameStack != null){
            if (frameStack.get(0) != null){
                lcResult = (TGlobalScope)frameStack.get(0).getScope();
            }
        }
        return lcResult;
    }

    private Stack<TFrame> frameStack;

    public void setFrameStack(Stack<TFrame> frameStack) {
        this.frameStack = frameStack;
    }

    public Stack<TFrame> getFrameStack() {
        return frameStack;
    }

    private TPTNodeList<TColumnWithSortOrder> indexColumns = null;

    public TPTNodeList<TColumnWithSortOrder> getIndexColumns() {
        return indexColumns;
    }

    private Stack<TObjectName> variableStack = null;

    public void setVariableStack(Stack<TObjectName> variableStack) {
        this.variableStack = variableStack;
    }

    public Stack<TObjectName> getVariableStack() {
        if (variableStack == null){
            variableStack = new Stack<TObjectName>();
        }

        return variableStack;
    }

    private Stack<TDaxFunction> daxFunctionStack = null;

    public Stack<TDaxFunction> getDaxFunctionStack() {
        if (daxFunctionStack == null){
            daxFunctionStack = new Stack<TDaxFunction>();
        }
        return daxFunctionStack;
    }

    private TObjectName labelName;

    public void setLabelName(TObjectName lName) {
        labelName = lName;
        if (labelName != null){
            //labelName.setObjectType(TObjectName.ttobjLabelName);
            labelName.setDbObjectType(EDbObjectType.label);
        }
    }

    /**
     *
     * @return label name used in plsql statement.
     */
    public TObjectName getLabelName() {

        return labelName;
    }


    private TObjectName endlabelName;

    public void setEndlabelName(TObjectName endlabelName) {
        this.endlabelName = endlabelName;
    }

    public TObjectName getEndlabelName() {

        return endlabelName;
    }

    /**
     * Type of this statement.
     */
    public ESqlStatementType sqlstatementtype;
    /**
     * Source tokens included in this statement. only source tokens available when this is a top level statement, otherwise, there is no source token in this statement.
     * Please check {@link gudusoft.gsqlparser.nodes.TParseTreeNode#getStartToken()}, and {@link gudusoft.gsqlparser.nodes.TParseTreeNode#getEndToken()} of this statement. 
     */
    public TSourceTokenList sourcetokenlist;

    public TSourceTokenList getTokenList() {
        return sourcetokenlist;
    }
    /**
     * Parser used to parse this statement.
     */
    public TCustomParser parser;
    /**
     * PLSQL parser used to parse this statement.
     */
    public TCustomParser plsqlparser;
    /**
     * Tag used by parser internally.
     */
    public int dummytag;

    /**
     * target table in the delete/insert/update/create table statement.
     * @see #joins
     * @see TSelectSqlStatement
     * @see TDeleteSqlStatement
     * @see TUpdateSqlStatement
     * @see TCreateTableSqlStatement
     * @see gudusoft.gsqlparser.stmt.TMergeSqlStatement
     */
    public TTable getTargetTable() {
        return targetTable;
    }

    public void setTargetTable(TTable targetTable) {
        setNewSubNode(this.targetTable,targetTable,getAnchorNode());
        this.targetTable = targetTable;
    }

    private TTable targetTable ;

    /**
     * joins represents table sources in the from clause. All structure information was reserved.
     * <p>SQL 1:
     * <p><blockquote><pre>select f from t1</pre></blockquote>
     * <p>size of joins will be 1, t1 can be fetch via joins.getJoin(0).getTable()
     * <p>
     * <p>SQL 2:
     * <p><blockquote><pre>select f from t1,t2</pre></blockquote>
     * <p>size of joins will be 2,
     * <p>t1 can be fetch via joins.getJoin(0).getTable()
     * <p>t2 can be fetch via joins.getJoin(1).getTable()
     * <p>
     * <p>SQL 3:
     * <p><blockquote><pre>select f from t1 join t2 on t1.f1 = t2.f1</pre></blockquote>
     * <p>size of joins will be 1,
     * <p>t1 information can be fetch via joins.getJoin(0).getTable()
     * <p>In order to access t2, we need to introduce a new  class {@link TJoinItem} which includes all information about t2 and join condition.
     * <p>There is a property named joinItems of {@link TJoin} which is type of {@link TJoinItemList} that includes a list of {@link TJoinItem}.
     * <p>this property can be access via {@link gudusoft.gsqlparser.nodes.TJoin#getJoinItems()}.
     * <p>Now, t2 can be fetch via  joins.getJoin(0).getJoinItems().getJoinItem(0).getTable()
     * <p>
     * <p>SQL 4:
     * <p><blockquote><pre>select f from t1 join t2 on t1.f1 = t2.f1 join t3 on t1.f1 = t3.f1</pre></blockquote>
     * <p>size of joins will be 1,
     * <p>t1 can be fetch via joins.getJoin(0).getTable()
     * <p>t2 can be fetch via joins.getJoin(0).getJoinItems().getJoinItem(0).getTable()
     * <p>t3 can be fetch via joins.getJoin(0).getJoinItems().getJoinItem(1).getTable()
     *
     * @see #tables
     */
    public TJoinList joins;

    /**
     * Provides a quick way to access all tables involved in this SQL statement.
     * <p>It stores all tables in a flat way while {@link #joins} stores all tables in a hierarchical structure.
     * <p>joins only represents tables in from clause of select/delete statement, and tables in update/insert statement.
     * <p>{@link #tables} includes all tables in all types of SQL statements  such as tables involved in a create table or create trigger statements.
     */
    public TTableList tables;

    public TJoinList getJoins() {
        return joins;
    }

    public TTableList getTables() {
        return tables;
    }

    /**
     * Saves all first level sub statements.
     * <p>By iterating statements recursively, you can fetch all included statements in an easy way.
     * <p><blockquote><pre>
     * select f1+(select f2 from t2) from t1
     * where f2 &gt; all (select f3 from t3 where f4 = (select f5 from t4))</pre>
     * </blockquote>
     * <p> Statements included in above SQL was save in a hierarchical way like this:
     * <ul>
     * <li>(select f2 from t2)</li>
     * <li>(select f3 from t3 where f4 = (select f5 from t4))
     *     <ul>
     *      <li>(select f5 from t4)</li>
     *     </ul>
     * </li>
     * </ul>
     * <p>If this statement is a create procedure/function statement, then all declaration statements and statements in
     * procedure body can also be fetched quickly by iterating this property recursively.
     *
     *
     */
    public TStatementList getStatements() {
        if (statements == null){
            statements = new TStatementList();
        }
        return statements;
    }

    private TStatementList statements;

    public void setCteList(TCTEList cteList) {
        setNewSubNode(this.cteList,cteList,getAnchorNode());
        this.cteList = cteList;
    }

    /**
     * Multiple common table expressions {@link TCTE} can be specified following the single WITH keyword.
     *<p> Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.
     *
     * <p>Used in select, delete, update statement.
     * @return  List of common table expression.
     */

    public TCTEList getCteList() {

        return cteList;
    }

    private TCTEList cteList = null;

    public void setResultColumnList(TResultColumnList resultColumnList) {
        setNewSubNode(this.resultColumnList,resultColumnList,getAnchorNode());
        this.resultColumnList = resultColumnList;
    }

    /**
     * In select statement, this method returns Items in select_list.
     * Can be *, expr, and name.*
     * <br><br>
     * In update statement, this method returns assignments in set clause.
     *
     * @return select list of select statement or assignments of update statement.
     */
    public TResultColumnList getResultColumnList() {

        return resultColumnList;
    }

    private TResultColumnList resultColumnList = null;

    private TWhereClause whereClause = null;
    private TTopClause topClause = null;
    private TOutputClause outputClause = null;
    private TReturningClause returningClause = null;

    public void setReturningClause(TReturningClause returningClause) {
        setNewSubNode(this.returningClause,returningClause,getAnchorNode());
        this.returningClause = returningClause;
    }

    /**
     * @return {@link TReturningClause returning clause.}
     */

    public TReturningClause getReturningClause() {

        return returningClause;
    }

    public void setOutputClause(TOutputClause outputClause) {
        setNewSubNode(this.outputClause,outputClause,getAnchorNode());
        this.outputClause = outputClause;
    }

    /**
     * @return output clause.
     */

    public TOutputClause getOutputClause() {

        return outputClause;
    }

    public void setTopClause(TTopClause topClause) {
        setNewSubNode(this.topClause,topClause,getAnchorNode());
        this.topClause = topClause;
    }

    /**
     * @return {@link TTopClause top clause.}
     */
    public TTopClause getTopClause() {
        return topClause;
    }

    public void setWhereClause(TWhereClause newWhereClause){
        setNewSubNode(this.whereClause ,newWhereClause,getAnchorNode());
        this.whereClause = newWhereClause;
    }


    /**
     * @deprecated As of 2.0.9.0, use {@link #setWhereClause(TWhereClause)} instead
     * Or, use {@link TWhereClause#setText(String)}
     *
     * @param condition
     * @return
     */
    public  TWhereClause addWhereClause(String condition){
           return this.whereClause;
    }

    /**
     * restrict the rows selected to those that satisfy one or more conditions.
     * used in select, delete, update statement.
     * @return {@link TWhereClause where clause.}
     */
    public TWhereClause getWhereClause() {
        return whereClause;
    }

    public void setAlreadyAddToParent(boolean alreadyAddToParent) {
        this.alreadyAddToParent = alreadyAddToParent;
    }

    private boolean alreadyAddToParent = false;

    private boolean ableToIncludeCTE(ESqlStatementType sst){
       return ((sst == ESqlStatementType.sstselect)
               ||(sst == ESqlStatementType.sstupdate)
               ||(sst == ESqlStatementType.sstinsert)
               ||(sst == ESqlStatementType.sstdelete)
               );
    }

    private TCTEList cteListInAllLevels = new TCTEList();

    protected  TCTEList searchCTEList(Boolean stopAtFirstFinding){
        cteListInAllLevels.clear();
        if (cteList != null ) {
            cteListInAllLevels.addAll(cteList);
            if (stopAtFirstFinding) return cteListInAllLevels;
        }

        TCustomSqlStatement lcParent = this.parentStmt;
        while (lcParent != null){
            if (!ableToIncludeCTE(lcParent.sqlstatementtype)) break;

            if (lcParent.cteList != null) {
                cteListInAllLevels.addAll(lcParent.cteList);
                if (stopAtFirstFinding)  break;
            }
            lcParent = lcParent.parentStmt;
        }
        return cteListInAllLevels;
    }


//    protected  TCTEList searchCTEList(){
//       TCTEList ret = null;
//       if (cteList != null ) {return cteList;}
//       TCustomSqlStatement lcParent = this.parentStmt;
//       while (lcParent != null){
//           if (!ableToIncludeCTE(lcParent.sqlstatementtype)) break;
//           ret = lcParent.cteList;
//           if (ret != null) break;
//           lcParent = lcParent.parentStmt;
//       }
//        return ret;
//    }

    public TCustomSqlStatement getParentStmt() {
        return parentStmt;
    }

    public TParseTreeNode getParentObjectName(){
        TParseTreeNode result = super.getParentObjectName();
        if (result != null) return result;
        return getParentStmt();
    }

    public void setParentStmt(TCustomSqlStatement parentStmt) {
        if (!alreadyAddToParent){
            this.parentStmt = parentStmt;
            if (this.parentStmt != null){
                parentStmt.getStatements().add(this);
                alreadyAddToParent = true;
            }
        }
    }

    public void setParentStmtToNull() {
        this.parentStmt = null;
    }

    private TCustomSqlStatement ancestorStmt = null;

    public TCustomSqlStatement getAncestorStmt() {
        TCustomSqlStatement lcRet = this;
        while (lcRet.getParentStmt() != null){
            lcRet = lcRet.getParentStmt();
        }
        return lcRet;
    }

    /**
     * parent statement of this statement if any
     */
    private TCustomSqlStatement parentStmt = null;

    /**
     * Original Parse tree node from parser
     */
    public TParseTreeNode rootNode;

    private Stack symbolTable = null;

    /**
     *
     * @deprecated since ver 2.5.3.5, please use {@link TStmtScope} instead
     */
    public Stack getSymbolTable() {
        if (symbolTable == null){
            symbolTable = new Stack();
        }
        return symbolTable;
    }

    TSourceToken semicolonended;
    boolean isctequery;
    private ArrayList <TSyntaxError> syntaxErrors;

    public ArrayList<TSyntaxError> getSyntaxErrors() {
        return syntaxErrors;
    }

    public String getErrormessage(){

        String s="",hint="Syntax error";
        TSyntaxError t;
        for (int i= 0; i< syntaxErrors.size(); i++)
        {
            t = (TSyntaxError) syntaxErrors.get(i);
            if (t.hint.length() > 0) hint = t.hint;
            s= s+hint+"("+t.errorno+") near: "+t.tokentext;
            s=s+"("+t.lineNo;
            s=s+","+t.columnNo +")";
            //s=s+" expected tokentext:"+t.hint;

            // break;//get only one message, remove this one and uncomment next line to get all error messages
            if (i !=  syntaxErrors.size() - 1)
                s = s +TBaseType.linebreak;
        }

        return s;
    }

    public ArrayList<TSyntaxError> getSyntaxHints() {
        return syntaxHints;
    }

    private ArrayList <TSyntaxError> syntaxHints;

    protected boolean isparsed;
    TSourceToken _semicolon;

    /**
     * Number of syntax errors for this statement.
     * @return 0 means no syntax error.
     */
    public int getErrorCount() {
        return syntaxErrors.size();
    }

    
    public TCustomSqlStatement(EDbVendor dbvendor){
        super();
        this.dbvendor = dbvendor;
        sqlstatementtype = ESqlStatementType.sstunknown;
        dummytag = 0;
        sourcetokenlist = new TSourceTokenList();
        syntaxErrors = new ArrayList<TSyntaxError>(4);
        syntaxHints = new ArrayList<TSyntaxError>(4);
        tables = new TTableList();
        joins = new TJoinList();
        indexColumns = new TPTNodeList<TColumnWithSortOrder>();
     }

    /**
     * Log error messages if syntax errors found while parsing this statement.
     * @param se syntax error structure.
     * @return  type of error
     */
    public EActionOnParseError parseerrormessagehandle(TSyntaxError se){
        if (se.errortype == EErrorType.sphint){
            this.getAncestorStmt().syntaxHints.add(se);
        }else
            this.getAncestorStmt().syntaxErrors.add(se);
        return EActionOnParseError.aopcontinue;
    }

    public int parsestatement(TCustomSqlStatement pparentsql,boolean isparsetreeavailable){
        return parsestatement(pparentsql,isparsetreeavailable,false);
    }

    /**
     * Parse this statement.
     * @param pparentsql
     * @param isparsetreeavailable
     * @return parse result, zero means no syntax error found.
     */
    public int parsestatement(TCustomSqlStatement pparentsql,boolean isparsetreeavailable, boolean onlyNeedRawParseTree){
        int ret = 0;
        isparsed = false;
        if (!isparsetreeavailable){
           ret = checksyntax(pparentsql);
        }
        if (ret == 0)
        {
            isparsed = true;
            if (!onlyNeedRawParseTree){
                ret = doParseStatement(pparentsql);
            }
        }else if (dbvendor == EDbVendor.dbvsybase){
            if ((this.rootNode != null)&&
                    ((sqlstatementtype == ESqlStatementType.sstmssqlcreateprocedure)
                     ||(sqlstatementtype == ESqlStatementType.sstmssqlcreatefunction)
                     ||(sqlstatementtype == ESqlStatementType.sstcreatetrigger)
                    )){
                if (!onlyNeedRawParseTree){
                    doParseStatement(pparentsql);
                }

            }
        }
        return ret;
    }

    public boolean OracleStatementCanBeSeparatedByBeginEndPair(){
        return  (

                (this.sqlstatementtype == ESqlStatementType.sstplsql_createprocedure)
                //|| (this.sqlstatementtype == ESqlStatementType.sst_block_with_label)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createfunction)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createpackage)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtype_placeholder)
                ||(this.sqlstatementtype == ESqlStatementType.sstoraclecreatepackagebody)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtrigger)
//                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtypebody)
//                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_tabletypedef)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_varraytypedef)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createprocedure)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_execimmestmt)
                ||(this.sqlstatementtype == ESqlStatementType.sstoraclecreatelibrary)
                );
    }

    public boolean VerticaStatementCanBeSeparatedByBeginEndPair(){
        return  (

                        (this.sqlstatementtype == ESqlStatementType.sstcreatefunction)
        );
    }

    public boolean isnzplsql(){
        return  (
                (this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
        );
    }

    public boolean ispgplsql(){
        return (this instanceof TCommonBlock)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreatefunction)
                ||(this.sqlstatementtype == ESqlStatementType.sstDoExecuteBlock)
                ;
    }

    public boolean isGaussDBStoredProcedure(){
        return (this instanceof TCommonBlock)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreatefunction)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createpackage)
                ||(this.sqlstatementtype == ESqlStatementType.sstoraclecreatepackagebody)
                ||(this.sqlstatementtype == ESqlStatementType.sstDoExecuteBlock)
                ;
    }

    public boolean isdatabricksplsql(){
        return (this instanceof TCommonBlock)
                ;
    }

    public boolean isgreeplumplsql(){
        return (this instanceof TCommonBlock)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreatefunction)
                ||(this.sqlstatementtype == ESqlStatementType.sstDoExecuteBlock)
                ;
    }

    public boolean isathenaplsql(){
        return (this instanceof TCommonBlock)
                ;
    }

    public boolean isprestoplsql(){
        return (this instanceof TCommonBlock)
                ;
    }

    public boolean issnowflakeplsql(){
        return ((this instanceof TCommonBlock)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
        );
    }

    public boolean isBigQueryplsql(){
        return ((this instanceof TCommonBlock)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreateprocedure)
        );
    }

    public boolean isverticaplsql(){
        return  (
                        (this.sqlstatementtype == ESqlStatementType.sstcreatefunction)
        );
    }

    public boolean isoracleplsql(){
        return  (
                (this.sqlstatementtype == ESqlStatementType.sst_plsql_block)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createprocedure)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createfunction)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createpackage)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtype_placeholder)
                        ||(this.sqlstatementtype == ESqlStatementType.sstoraclecreatepackagebody)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtrigger)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtypebody)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_tabletypedef)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_varraytypedef)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createprocedure)
                        ||(this.sqlstatementtype == ESqlStatementType.sstplsql_execimmestmt)
                        ||(this.sqlstatementtype == ESqlStatementType.sstoraclecreatelibrary)
                );
    }

    int checksyntax(TCustomSqlStatement psql){
        return dochecksyntax(psql);
    }

    protected int dochecksyntax(TCustomSqlStatement psql){
        int ret = -1;
        clear();
        if (sourcetokenlist.size() == 0) return ret;

//        TCustomParser lcparser;
//        lcparser = new TLzParserOracleSql(sourcetokenlist);
//        lcparser.sql = this;
//        ret = lcparser.yyparse();


        if ((this.dbvendor == EDbVendor.dbvoracle)&&(this.isoracleplsql()&&(plsqlparser!=null))
//                || ((this.dbvendor == EDbVendor.dbvgaussdb) // gaussdb 中用 oracle plsql 写的存储过程，用 oracle plsql parser 来解析
//                        &&(
//                            ((this instanceof TCreateFunctionStmt)&&(((TCreateFunctionStmt)this).isGaussDBSpInOracle()))
//                            ||((this instanceof TCreateProcedureStmt)&&(((TCreateProcedureStmt)this).isGaussDBSpInOracle()))
//                            ||(this instanceof TPlsqlCreatePackage)
//                          )
//                )
        ){
            plsqlparser.sql = this;
//            if (this.dbvendor == EDbVendor.dbvgaussdb){
//                // 原来用 gaussDB lexer tokenize 的 token 需要用 Oracle lexer 重新 tokenize 一边
//                String sqlText="";
//                for(int k = 0;k<sourcetokenlist.size();k++){
//                    sqlText = sqlText + sourcetokenlist.get(k).toString();
//                }
//                // TODO, need to use singleton pattern to get a single instance of Oracle parser.
//                TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
//                // keep coordinate of the origin query
//                long originalLineNo = sourcetokenlist.get(0).lineNo;
//                long originalColumnNo = sourcetokenlist.get(0).columnNo;
//                sqlParser.sqltext = TBaseType.stringBlock((int) originalLineNo - 1,(int) originalColumnNo - 1)+ sqlText;;
//
//                int r = sqlParser.getrawsqlstatements();
//                sourcetokenlist.clear();
//                for(int k=0;k<sqlParser.sourcetokenlist.size();k++){
//                    sourcetokenlist.add(sqlParser.sourcetokenlist.get(k));
//                }
//            }

            plsqlparser.sourcetokenlist = sourcetokenlist;

            if ((this instanceof TCommonStoredProcedureSqlStatement)
                &&((TCommonStoredProcedureSqlStatement)this).isWrapped()){
                // don't parse wrapped oracle plsql
                ret = 0;
                this.rootNode = this;
            }else {
                ret = plsqlparser.yyparse();
                this.rootNode = plsqlparser.rootNode;
            }
        }
        else{
            if ((this.sqlstatementtype == ESqlStatementType.sstExplain)&&(dbvendor != EDbVendor.dbvhana)){
                    // EXPLAIN PLAN ... FOR statement; only parse token after FOR keyword
                    boolean isFoundStopToken = false;

                    for(int k=0;k<sourcetokenlist.size();k++){
                        TSourceToken st = sourcetokenlist.get(k);
                        switch (dbvendor){
                            case dbvoracle:
                                if (st.tokencode == TBaseType.rrw_for) {
                                    st.tokencode = TBaseType.sqlpluscmd;
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvredshift:
                                if (st.tokencode == TBaseType.rrw_explain){
                                    st.tokencode = TBaseType.sqlpluscmd;
                                    TSourceToken nextst = st.nextSolidToken();
                                    if (nextst.tokencode == TBaseType.rrw_redshift_verbose){
                                        nextst.tokencode = TBaseType.sqlpluscmd;
                                        //System.out.println("Found verbose after explain");
                                    }
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvvertica:
                                if ((st.tokencode == TBaseType.rrw_select)
                                        ||(st.tokencode == TBaseType.rrw_insert)
                                        ||(st.tokencode == TBaseType.rrw_update)
                                        ||(st.tokencode == TBaseType.rrw_merge)
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvmysql:
                            case dbvsparksql:
                            case dbvdatabricks:
                            case dbvpostgresql:
                                if ((st.tokencode == TBaseType.rrw_select)
                                        ||(st.tokencode == TBaseType.rrw_insert)
                                        ||(st.tokencode == TBaseType.rrw_update)
                                        ||(st.tokencode == TBaseType.rrw_delete)
                                        ||(st.tokencode == TBaseType.rrw_replace)
                                        ||(st.tokencode == '(')
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvcouchbase:
                                if (st.tokencode == TBaseType.rrw_explain){
                                    st.tokencode = TBaseType.sqlpluscmd;
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvpresto:
                            case dbvathena:
                            case dbvnetezza:
                                if ((st.tokencode == TBaseType.rrw_select)
                                        ||(st.tokencode == TBaseType.rrw_insert)
                                        ||(st.tokencode == TBaseType.rrw_update)
                                        ||(st.tokencode == TBaseType.rrw_delete)
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvteradata:
                                if ((st.tokencode == TBaseType.rrw_select)
                                        ||(st.tokencode == TBaseType.rrw_insert)
                                        ||(st.tokencode == TBaseType.rrw_update)
                                        ||(st.tokencode == TBaseType.rrw_delete)
                                        ||(st.tokencode == TBaseType.rrw_teradata_collect)
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                        }//switch

                        if (isFoundStopToken) break;
                        st.tokencode = TBaseType.sqlpluscmd;
                    }
            }else if (this.sqlstatementtype == ESqlStatementType.sstProfile){
                for(int k=0;k<sourcetokenlist.size();k++) {
                    TSourceToken st = sourcetokenlist.get(k);
                    if (dbvendor == EDbVendor.dbvvertica){
                        if ((st.tokencode == TBaseType.rrw_select)
                                ||(st.tokencode == TBaseType.rrw_insert)
                                ||(st.tokencode == TBaseType.rrw_update)
                                ||(st.tokencode == TBaseType.rrw_merge)
                                )
                        {
                            break;
                        }
                    }
                    st.tokencode = TBaseType.sqlpluscmd;
                }
            }else if (this.sqlstatementtype == ESqlStatementType.sstprepare){
                if ((dbvendor == EDbVendor.dbvcouchbase)||(dbvendor == EDbVendor.dbvpresto)||(dbvendor == EDbVendor.dbvathena)){
                    int keywordCount = 0;
                    for(int k=0;k<sourcetokenlist.size();k++) {
                        TSourceToken st = sourcetokenlist.get(k);
                            if (st.tokencode == TBaseType.rrw_prepare)
                            {
                                keywordCount++;
                            }else if ((st.tokentype == ETokenType.ttkeyword)
                                    &&(st.tokencode != TBaseType.rrw_from)
                                    &&(st.tokencode != TBaseType.rrw_as)){
                                keywordCount++;
                                if (keywordCount > 1) break;
                            }
                        st.tokencode = TBaseType.sqlpluscmd;
                    }
                }
            }

            if (parser == null){
                // statement such as select/insert and etc  inside plsql
                if (psql != null){
                    parser = psql.getTopStatement().parser;
                    this.setParentStmt(psql);
                }
                // parser =  new TParserOracleSql(null);
                //parser.lexer = new TLexerOracle();
                //parser.lexer.delimiterchar = '/';
            }
            parser.sql = this;
            parser.sourcetokenlist = sourcetokenlist;
            ret = parser.yyparse();
            this.rootNode = parser.rootNode;
        }

        if (ret == 0){
            ret = syntaxErrors.size();
        }
       // if (rootNode == null) {
       if (rootNode == null) {
            ret = TBaseType.MSG_ERROR_NO_ROOT_NODE;
            // todo , uncomment next line when all sql statements in .y file was processed
             parseerrormessagehandle( new TSyntaxError("no root node",0,0,"no_root_node",EErrorType.sperror,TBaseType.MSG_ERROR_NO_ROOT_NODE,this,-1));
        }
        return ret;
    }

    public void clearError(){
        syntaxErrors.clear();
        syntaxHints.clear();
    }

    void clear(){
        syntaxErrors.clear();
        syntaxHints.clear();
// todo all subclass should add super()       
    }

    public void setStmtScope(TStmtScope stmtScope) {
        this.stmtScope = stmtScope;
    }

    public TStmtScope getStmtScope() {
        return stmtScope;
    }

    /**
     * Original SQL fragment of this statement.
     * @return   Original statement text.
     */

    /*
    public String toString(){
       StringBuffer sb = new StringBuffer("");
       for(int i=0; i<sourcetokenlist.size();i++){
          sb.append(sourcetokenlist.get(i).toString());  
        }
       return sb.toString();
    }
    */
    protected TStmtScope stmtScope = null;
    void buildsql(){}
    public int doParseStatement(TCustomSqlStatement psql){
        if (psql != null){
            this.setParentStmt(psql);
            this.setFrameStack(psql.getFrameStack());
            stmtScope = new TStmtScope(psql.stmtScope,this);
            // psql.statements.add(this);
        }else{
            stmtScope = new TStmtScope(this);
        }

        if ((this.getStartToken() == null)&&(rootNode != null)){
            this.setStartToken(rootNode.getStartToken());
        }
        if ((this.getEndToken() == null)&&(rootNode != null)){
            this.setEndToken(rootNode.getEndToken());
        }

        if(this.getGsqlparser() == null){
            if (rootNode != null){
                this.setGsqlparser(rootNode.getGsqlparser());
            }
        }
        return 0;
    }

    void addtokentolist(TSourceToken st){
       st.stmt = this;
       sourcetokenlist.add(st);
    }

    public TTable analyzeTablename(TObjectName tableName){
        TTable lcTable = new TTable();
        lcTable.setTableType(ETableSource.objectname);
        lcTable.setStartToken(tableName.getStartToken());
        lcTable.setEndToken(tableName.getEndToken());
        lcTable.setGsqlparser(this.getGsqlparser());
        lcTable.setTableName(tableName);

        tables.addTable(lcTable);
        return lcTable;
    }

    protected boolean isTableACTE(TTable pTable){
        boolean lcResult = false;
        TCTEList cteList1 = getCteList();
        if (cteList1 == null){
            TCustomSqlStatement lcStmt = getParentStmt();
            while (lcStmt != null){
                if (lcStmt.getCteList() != null){
                    cteList1 = lcStmt.getCteList();
                    break;
                }else {
                    lcStmt = lcStmt.getParentStmt();
                }
            }
        }
        if (cteList1 == null) return  false;
       // TCTE lcCTE = cteList1.cteNames.get(TBaseType.getTextWithoutQuoted(pTable.toString()).toUpperCase());
        if (pTable.toString() == null) return false;

        int searchPos = pTable.getStartToken().posinlist;
        if (this.getCteIncludeThisStmt() != null){
            searchPos = this.getCteIncludeThisStmt().getStartToken().posinlist;
        }
        TCTE lcCTE = cteList1.searchCTEByName(TBaseType.getTextWithoutQuoted(pTable.toString()).toUpperCase(),searchPos);
        if ( lcCTE != null ){
            if (pTable.setCTE(lcCTE)){
                pTable.setCTEName(true);
                lcResult = true;
            }
        }
//        for (int i=0;i<cteList1.size();i++){
//            lcCTE = cteList1.getCTE(i);
//            if (TBaseType.getTextWithoutQuoted(lcCTE.getTableName().toString()).equalsIgnoreCase(TBaseType.getTextWithoutQuoted(pTable.toString()))){
//                pTable.setCTEName(true);
//                pTable.setCTE(lcCTE);
//                lcResult = true;
//                break;
//            }
//        }

        return lcResult;

    }

    public TTable findTable(ETableEffectType[] tableEffectTypes){
        TTable lcResult = null;
        for(int i=0;i<tables.size();i++){
            for(int j=0;j<tableEffectTypes.length;j++){
                if (tables.getTable(i).getEffectType() == tableEffectTypes[j]){
                    lcResult = tables.getTable(i);
                    return  lcResult;
                }
            }
        }
        return  lcResult;
    }
    public void addToTables(TTable pTable){
        tables.addTable(pTable);
        if (isTableACTE(pTable)) return;

        if (pTable.getTableName() == null) return;
        if (pTable.getTableName().getTableToken() == null) return;
        if ((pTable.getTableName().getTableString().toString().equalsIgnoreCase("inserted"))||(pTable.getTableName().getTableString().toString().equalsIgnoreCase("deleted"))){
           if ((getAncestorStmt().sqlstatementtype == ESqlStatementType.sstcreatetrigger)
               ||(getAncestorStmt().sqlstatementtype == ESqlStatementType.sstmssqlaltertrigger)){
               //pTable.setLinkTable(true);
               ETableEffectType[] effectTypes = new ETableEffectType[]{
                       ETableEffectType.tetTriggerOn,ETableEffectType.tetTriggerInsert,ETableEffectType.tetTriggerDelete,ETableEffectType.tetTriggerUpdate,ETableEffectType.tetTriggerInsteadOf
               };
               pTable.setLinkTable(getAncestorStmt().findTable(effectTypes));
           }
        }

    }

    public TJoin analyzeTableOrJoin(TFromTable pfromTable){
        TFromTable lcFromTable = pfromTable;
        TJoin lcJoin;
        TTable lcTable;

        if (lcFromTable.getFromtableType() != ETableSource.join){
            lcJoin = new TJoin();
            lcTable = analyzeFromTable(lcFromTable,true);
            lcTable.setEffectType(ETableEffectType.tetSelect);
            lcJoin.setTable(lcTable);
            lcJoin.setStartToken(lcJoin.getTable().getStartToken());
            lcJoin.setEndToken(lcJoin.getTable().getEndToken());
            lcJoin.setGsqlparser(getGsqlparser());
            this.fromSourceTable = lcTable;
            this.getRelations().add(lcTable);
        }else{
            this.fromSourceJoin = lcFromTable.getJoinExpr();

            this.fromSourceTable = new TTable();
            this.fromSourceTable.setTableType(ETableSource.join);
            this.fromSourceTable.setAliasClause(lcFromTable.getJoinExpr().getAliasClause());
            this.fromSourceTable.setStartToken(lcFromTable.getStartToken());
            this.fromSourceTable.setEndToken(lcFromTable.getEndToken());
            this.fromSourceTable.setGsqlparser(lcFromTable.getGsqlparser());
            this.fromSourceTable.setJoinExpr(this.fromSourceJoin);
            this.getRelations().add(this.fromSourceTable);

            lcJoin = analyzeJoin(lcFromTable.getJoinExpr(),null,true);
            lcJoin.doParse(this, ESqlClause.join);

            if (lcFromTable.getLateralViewList() != null){
                for(TLateralView lateralView:lcFromTable.getLateralViewList()){
                    TTable t = lateralView.createATable(this);
                    addToTables(t);
                    this.relations.add(t);
                }
            }
        }

        return lcJoin;
    }

    public TTable analyzeFromTable(TFromTable pfromTable, Boolean addToTableList){
        return analyzeFromTable(pfromTable,addToTableList,ESqlClause.unknown);
    }

    public TTable analyzeFromTable(TFromTable pfromTable, Boolean addToTableList, ESqlClause pLocation){
        TTable lcTable = new TTable();
        lcTable.setTableType(pfromTable.getFromtableType());
        lcTable.setAliasClause(pfromTable.getAliasClause());
        lcTable.setStartToken(pfromTable.getStartToken());
        lcTable.setEndToken(pfromTable.getEndToken());
        lcTable.setGsqlparser(pfromTable.getGsqlparser());
        lcTable.setTableHintList(pfromTable.getTableHintList());
        lcTable.setTableSample(pfromTable.getTableSample());
        lcTable.setLateralViewList(pfromTable.getLateralViewList());
        lcTable.setTableProperties(pfromTable.getTableProperties());
        lcTable.setPivotedTable(pfromTable.getPivotedTable());
        lcTable.setParenthesisCount(pfromTable.getParenthesisCount());
        lcTable.setParenthesisAfterAliasCount(pfromTable.getParenthesisAfterAliasCount());
        lcTable.setTableKeyword(pfromTable.isTableKeyword());
        lcTable.setOnlyKeyword(pfromTable.isOnlyKeyword());
        lcTable.setFlashback(pfromTable.getFlashback());
        lcTable.setPxGranule(pfromTable.getPxGranule());
        lcTable.setTimeTravelClause(pfromTable.getTimeTravelClause());
        //lcTable.setPartitionClause(pfromTable.getPartitionClause());

        if(getFrameStack().firstElement() != null){
            TFrame stackFrame = getFrameStack().firstElement();
            TGlobalScope globalScope = (TGlobalScope)stackFrame.getScope();
            lcTable.setSqlEnv(globalScope.getSqlEnv());
        }

       switch(lcTable.getTableType()){
           case objectname:{
              // tables.addTableByTableRefernce(pfromTable.getTableObjectName());
               boolean insertedInTrigger = false;
               if (getTopStatement().sqlstatementtype == ESqlStatementType.sstcreatetrigger){
                  insertedInTrigger = (pfromTable.getTableObjectName().toString().compareToIgnoreCase("inserted")==0);
               }

               if (insertedInTrigger){
                   // change table name from inserted to onTable name in create trigger 
                 lcTable.setTableName(((TCreateTriggerStmt)getTopStatement()).getOnTable().getTableName());
                 //lcTable.setLinkTable(true);
                 lcTable.setLinkTable(((TCreateTriggerStmt)getTopStatement()).getOnTable());
               }else{
                    lcTable.setTableName(pfromTable.getTableObjectName());
                   lcTable.getTableName().setSqlEnv(getSqlEnv());

//                   if (getSqlEnv().getDefaultCatalogName() != null){
//                       if (lcTable.getTableName().getDatabaseToken() == null){
//                           lcTable.getTableName().setDatabaseToken(new TSourceToken(getSqlEnv().getDefaultCatalogName()),true);
//                       }
//                   }

//                   if ((lcTable.getTableName().getSchemaToken() == null)&&(TSQLEnv.supportSchema(this.dbvendor))){
//                       // let find schema name for this table in env
//                       TSQLTable t = getSqlEnv().searchTable(".."+lcTable.getFullName());
//                       if (t != null){
//                           TSQLSchema s = t.getSchema();
//                           if (s != null){
//                               lcTable.getTableName().setSchemaToken(new TSourceToken(s.getName()),true);
//                           }
//                       }
//                   }

               }
               // let's check is it cte name or ordinary table name
               TCTEList lcCteList = searchCTEList(false);
               TCTE lcCte = null;
               if (lcCteList != null){
                 for(int i=0;i<lcCteList.size();i++){
                    lcCte = lcCteList.getCTE(i);
                    if (lcCte.getTableName().toString().compareToIgnoreCase(TBaseType.getTextWithoutQuoted(lcTable.getTableName().toString()))==0){
                        // this is cte name
                        if (lcTable.setCTE(lcCte)){
                            lcTable.setCTEName(true);
                            lcTable.setCteColomnReferences(lcCte.getColumnList());
                            break;
                        }
                    }
                 }
               }

               break;
           }
           case tableExpr:{
               ESqlClause location =  ESqlClause.tableExpr; //ESqlClause.resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setTableExpr(pfromTable.getTableExpr());
               lcTable.getTableExpr().doParse(this,location);
               // teradata: SELECT table1.* FROM table(strtok_split_to_table(1, 'dm-calcite-raven/td/bq', '-') RETURNS (outkey integer, tokennum integer, token varchar(20)) ) as table1;
               // RETURNS (outkey integer, tokennum integer, token varchar(20))
               lcTable.setColumnDefinitions(pfromTable.getColumnDefinitions());
               break;
           }
           case subquery:{
//               if (pfromTable.getSubquerynode().isHiveFromQuery()){
//                   THiveFromQuery fromQuery  = new THiveFromQuery(dbvendor);
//                   lcTable.setHiveFromQuery(fromQuery);
//                   fromQuery.rootNode = pfromTable.getSubquerynode();
//                   fromQuery.setStartToken(pfromTable.getSubquerynode());
//                   fromQuery.setEndToken(pfromTable.getSubquerynode());
//                   fromQuery.setLabelName(this.labelName);
//                   fromQuery.doParseStatement(this);
//               }else{
//                   lcTable.subquery = new TSelectSqlStatement(dbvendor);
//                   lcTable.subquery.rootNode = pfromTable.getSubquerynode();
//                   lcTable.subquery.setLocation(ESqlClause.elTable);
//                   //lcTable.subquery.resultColumnList = ((TSelectSqlNode)lcTable.subquery.rootNode).getResultColumnList();
//                   lcTable.subquery.doParseStatement(this);
//               }

               lcTable.subquery = new TSelectSqlStatement(dbvendor);
               lcTable.subquery.rootNode = pfromTable.getSubquerynode();
               if (pLocation == ESqlClause.unknown){
                   lcTable.subquery.setLocation(ESqlClause.elTable);
               }else{
                   lcTable.subquery.setLocation(pLocation);
               }
               //lcTable.subquery.resultColumnList = ((TSelectSqlNode)lcTable.subquery.rootNode).getResultColumnList();
               lcTable.subquery.doParseStatement(this);

               break;
           }
           case function:{
               ESqlClause location = ESqlClause.tableFunction;// resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setFuncCall(pfromTable.getFuncCall());
               lcTable.getFuncCall().doParse(this,location);
               break;
           }
           case containsTable:{
               ESqlClause location = ESqlClause.containsTable;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setContainsTable(pfromTable.getContainsTable());
               lcTable.getContainsTable().doParse(this,location);
               break;
           }

           case openrowset:{
               ESqlClause location = ESqlClause.openrowset;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setOpenRowSet(pfromTable.getOpenRowSet());
               lcTable.getOpenRowSet().doParse(this,location);
               break;
           }

           case openxml:{
               ESqlClause location = ESqlClause.openxml;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setOpenXML(pfromTable.getOpenXML());
               lcTable.getOpenXML().doParse(this,location);
               break;
           }

           case opendatasource:{
               ESqlClause location = ESqlClause.opendatasource;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setOpenDatasource(pfromTable.getOpenDatasource());
               lcTable.getOpenDatasource().doParse(this,location);
               break;
           }

           case openquery:{
               ESqlClause location = ESqlClause.openquery;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setOpenquery(pfromTable.getOpenQuery());
               lcTable.getOpenquery().doParse(this,location);
               lcTable.setSubquery(lcTable.getOpenquery().getSubquery());
               break;
           }

           case datachangeTable:{
               ESqlClause location = ESqlClause.datachangeTable;//resultColumn;
               if (sqlstatementtype == ESqlStatementType.sstinsert ){
                   // change location here
               }
               lcTable.setDatachangeTable(pfromTable.getDatachangeTable());
               lcTable.getDatachangeTable().doParse(this,location);
               break;
           }
           case rowList:{
               ESqlClause location = ESqlClause.rowList;//resultColumn;
               lcTable.setValueClause(pfromTable.getValueClause());
               lcTable.getValueClause().doParse(this,location);
               break;
           }
           case pivoted_table:{
               ESqlClause location = ESqlClause.pivoted_table;//resultColumn;
               lcTable.getPivotedTable().doParse(this,location);
               addToTableList = false;
               targetTable = lcTable;
               break;
           }
           case xmltable:{
               ESqlClause location = ESqlClause.xmltable;//resultColumn;
               lcTable.setXmlTable(pfromTable.getXmlTable());
               lcTable.getXmlTable().doParse(this,location);
               break;
           }

           case informixOuter:{
               ESqlClause location = ESqlClause.outerTable;//resultColumn;
               lcTable.setOuterClause(pfromTable.getOuterClause());
               lcTable.getOuterClause().doParse(this,location);
               break;
           }

           case table_ref_list:{
               lcTable.setFromTableList(pfromTable.getFromTableList());
               break;
           }
//           case hiveFromQuery:{
//               THiveFromQuery fromQuery = new THiveFromQuery(EDbVendor.dbvhive);
//               fromQuery.rootNode = pfromTable.getFromQuerySqlNode();
//               fromQuery.doParseStatement(this);
//               lcTable.setHiveFromQuery(fromQuery);
//               break;
//           }
           case output_merge:{
               TMergeSqlStatement outputMerge = new TMergeSqlStatement(EDbVendor.dbvmssql);
               outputMerge.rootNode = pfromTable.getMergeSqlNode();
               outputMerge.doParseStatement(this);
               lcTable.setOutputMerge(outputMerge);
               break;
           }
           case td_unpivot:{
               pfromTable.getTdUnpivot().doParse(this,ESqlClause.tdUnPivot);
               lcTable.setTdUnpivot(pfromTable.getTdUnpivot());
               break;
           }
           case unnest:{
               pfromTable.getUnnestClause().doParse(this,ESqlClause.elTable);
               lcTable.setUnnestClause(pfromTable.getUnnestClause());
               if (lcTable.getAliasClause() != null){
                   if (lcTable.getAliasClause().getColumns() != null){
                       for(TObjectName pColumn:lcTable.getAliasClause().getColumns()){
                           lcTable.getLinkedColumns().addObjectName(pColumn);
                           pColumn.setSourceTable(lcTable);
                       }
                   }else if (lcTable.getAliasClause().getAliasName() != null){
//                       SELECT *
//                               FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS element
//                       WITH OFFSET AS offset

                       // add element as column of unnest table.
                       TObjectName newColumn = TObjectName.createObjectName(this.dbvendor,EDbObjectType.column,lcTable.getAliasClause().getAliasName().getStartToken());
                       lcTable.getLinkedColumns().addObjectName(newColumn);
                       newColumn.setSourceTable(lcTable);
                   }
               }

               if (lcTable.getUnnestClause().getWithOffset() != null){
                   if (lcTable.getUnnestClause().getWithOffsetAlais() != null){
                       // with offset as offsetAlias
                       TAliasClause aliasClause = lcTable.getUnnestClause().getWithOffsetAlais();
                       if (aliasClause.getAliasName() != null){
                           TObjectName newColumn = TObjectName.createObjectName(this.dbvendor,EDbObjectType.column,aliasClause.getAliasName().getStartToken());
                           lcTable.getLinkedColumns().addObjectName(newColumn);
                           newColumn.setSourceTable(lcTable);
                       }
                   }else{
                       // with offset
                       TObjectName newColumn = TObjectName.createObjectName(this.dbvendor,EDbObjectType.column,new TSourceToken("offset"));
                       lcTable.getLinkedColumns().addObjectName(newColumn);
                       newColumn.setSourceTable(lcTable);
                   }
               }

               // link columns in the select list to unnest()
               // select emp_id,name,state,city,zipcode from `absolute-runner-302907.gudu_sqlflow.ADDRESS_NESTED`, UNNEST(address)
               if (lcTable.getUnnestClause().getDerivedColumnList() != null){
                   TObjectNameList derivedColumns = lcTable.getUnnestClause().getDerivedColumnList();
                   for(int i=0;i<derivedColumns.size();i++){
                       //System.out.println(derivedColumns.getObjectName(i).toString());
                       lcTable.getLinkedColumns().addObjectName(derivedColumns.getObjectName(i));
                   }
               }

               break;
           }
           case jsonTable:{
               ESqlClause location = ESqlClause.jsonTable;//resultColumn;
               lcTable.setJsonTable(pfromTable.getJsonTable());
               lcTable.getJsonTable().doParse(this,location);
               break;
           }
           case externalTable:
               lcTable.setTableName(pfromTable.getTableObjectName());
               lcTable.setColumnDefinitions(pfromTable.getColumnDefinitions());
               lcTable.getColumnDefinitions().doParse(this,pLocation);
               lcTable.setTableType(ETableSource.externalTable); // tableType is reset in setTableName() method, so we reset it here
               break;
           case caseJoin:
               lcTable.setCaseJoin(pfromTable.getCaseJoin());
               lcTable.getCaseJoin().doParse(this,pLocation);
               break;
           case stageReference:
               lcTable.setStageReference(pfromTable.getStageReference());
               lcTable.getStageReference().doParse(this,pLocation);

               lcTable.setTableName(lcTable.getStageReference().getStageName());
               lcTable.setTableType(ETableSource.stageReference);
               lcTable.getTableName().setSqlEnv(getSqlEnv());

               break;

      }//switch

//        if (pfromTable.getPivotClause() != null){
//            lcTable.setPivotClause(pfromTable.getPivotClause());
//            lcTable.getPivotClause().doParse(this,ESqlClause.resultColumn);
//        }

        lcTable.setPartitionExtensionClause(pfromTable.getPartitionExtensionClause());

        //tables.addTable(lcTable);
        if (addToTableList) {
            addToTables(lcTable);
        }

        if (lcTable.getTableHintList() != null){
            for(int i=0;i<lcTable.getTableHintList().size();i++){
                TTableHint hint = lcTable.getTableHintList().getElement(i);
                hint.setOwnerTable(lcTable);
                hint.doParse(this,ESqlClause.tableHint);
            }
        }

        if (lcTable.getLateralViewList() != null){
            for(TLateralView lateralView:lcTable.getLateralViewList()){
                TTable t = lateralView.createATable(this);
                addToTables(t);
                this.relations.add(t);
            }
        }

        if (lcTable.getAliasClause() != null){
            if (lcTable.getAliasClause().toString().equalsIgnoreCase("and")){
                // end keyword can't be alias name
                TSourceToken st1 = lcTable.getAliasClause().getStartToken();
                TSyntaxError err = new TSyntaxError(st1.toString()
                        ,st1.lineNo,st1.columnNo
                        ,String.format("AND keyword can't be table alias")
                        ,EErrorType.sperror
                        ,TBaseType.MSG_ERROR_AND_KEYWORD_CANT_USED_AS_TABLE_ALIAS
                        ,this,st1.posinlist);
                this.parseerrormessagehandle( err);

            }
        }

        return lcTable;
    }

   public TJoin analyzeJoin(TJoinExpr pJoinExpr,TJoin pJoin,Boolean isSub){
        TJoin retval = pJoin;
        TJoinItem lcJoinItem = null ;

        if (pJoinExpr == null) {return retval;}

        if (pJoinExpr.getJointype() == EJoinType.nested)
        {
            if (isSub)
            {
                if (retval == null) {  // top level, left side is a join
                  retval = new TJoin();
                  retval.setStartToken(pJoinExpr.getStartToken());
                  retval.setEndToken(pJoinExpr.getEndToken());
                }

                pJoinExpr.setJointype(pJoinExpr.original_jontype);
                retval.setJoin(analyzeJoin(pJoinExpr,null,true));
                //retval =analyzeJoin(pJoinExpr,null,true);
                retval.setKind(TBaseType.join_source_join);
                retval.getJoin().setAliasClause(pJoinExpr.getAliasClause());
                retval.getJoin().setWithParen(true);
                retval.getJoin().setNestedParen(pJoinExpr.getNestedParen());
            }
           else
            {
                if (retval == null)
                {
                    retval = new TJoin();
                    retval.setStartToken(pJoinExpr.getStartToken());
                    retval.setEndToken(pJoinExpr.getEndToken());
                    retval.setGsqlparser(this.getGsqlparser());
                }
                else
                {
                }
                pJoinExpr.setJointype(pJoinExpr.original_jontype);
                retval = analyzeJoin(pJoinExpr,retval,false);
                //retval.setJoin(analyzeJoin(pJoinExpr,retval,false));
                //retval = analyzeJoin(pJoinExpr,retval,false);
                //retval.setKind(TBaseType.join_source_join);
                //retval.setKind(TBaseType.join_source_table);
                //retval.setAliasClause(pJoinExpr.getAliasClause());
                retval.setAliasClause(pJoinExpr.getAliasClause());
                retval.setWithParen(true);
                retval.setNestedParen(pJoinExpr.getNestedParen());
            }
            return retval;
        }

        if (pJoinExpr.getLeftOperand().getFromtableType() != ETableSource.join){
            if (retval == null) {
              retval = new TJoin();
              retval.setStartToken(pJoinExpr.getStartToken());
              retval.setEndToken(pJoinExpr.getEndToken());
              retval.setGsqlparser(this.getGsqlparser());

              //  retval.setStartToken(pJoinExpr.getLeftOperand().getStartToken());
              //  retval.setEndToken(pJoinExpr.getLeftOperand().getEndToken());
            }
            TTable lcTable = analyzeFromTable(pJoinExpr.getLeftOperand(),true,ESqlClause.join);
            lcTable.setEffectType(ETableEffectType.tetSelect);
            retval.setTable(lcTable);
            //retval.joinTable.OwnerJoin = result;
            retval.setKind(TBaseType.join_source_table);
            pJoinExpr.setLeftTable(lcTable);
        }else{
            TJoinExpr lcJoinItemJoinExpr = pJoinExpr.getLeftOperand().getJoinExpr();
            //if (lcJoinItemJoinExpr.getJointype() == TBaseType.join_nested){
            //    lcJoinItemJoinExpr.setJointype(lcJoinItemJoinExpr.original_jontype);
            //}

            if (retval != null) {
              retval = analyzeJoin(lcJoinItemJoinExpr,retval,true);
            } else {
              retval = analyzeJoin(lcJoinItemJoinExpr,retval,isSub);
            }
            retval.setStartToken(lcJoinItemJoinExpr.getStartToken());
            retval.setEndToken(lcJoinItemJoinExpr.getEndToken());


            TTable lcTable = new TTable();
            lcTable.setTableType(pJoinExpr.getLeftOperand().getFromtableType());
            lcTable.setAliasClause(lcJoinItemJoinExpr.getAliasClause());
            lcTable.setStartToken(lcJoinItemJoinExpr.getStartToken());
            lcTable.setEndToken(lcJoinItemJoinExpr.getEndToken());
            pJoinExpr.setLeftTable(lcTable);
            lcTable.setJoinExpr(lcJoinItemJoinExpr);
        }

        if (pJoinExpr.getRightOperand().getFromtableType() != ETableSource.join){
            if (retval != null)
            {
                lcJoinItem = new TJoinItem();
                TTable lcTable = analyzeFromTable(pJoinExpr.getRightOperand(),true,ESqlClause.join);
                lcTable.setEffectType(ETableEffectType.tetSelect);
                lcJoinItem.setTable(lcTable);
                lcJoinItem.setStartToken(lcJoinItem.getTable().getStartToken());
                lcJoinItem.setEndToken(lcJoinItem.getTable().getEndToken());
               // lcJoinItem.JoinItemTable.OwnerJoinItem := lcJoinItem;
                lcJoinItem.setKind(TBaseType.join_source_table);
                retval.getJoinItems().addJoinItem(lcJoinItem);
                pJoinExpr.setRightTable(lcTable);
            }
        }else{
            if (retval != null)
            {
                lcJoinItem = new TJoinItem();
                lcJoinItem.setKind(TBaseType.join_source_join);
                TJoinExpr lcJoinItemJoinExpr = pJoinExpr.getRightOperand().getJoinExpr();
                //if (lcJoinItemJoinExpr.getJointype() == TBaseType.join_nested){
                //    lcJoinItemJoinExpr.setJointype(lcJoinItemJoinExpr.original_jontype);
                //}
                lcJoinItem.setJoin(analyzeJoin(pJoinExpr.getRightOperand().getJoinExpr(),null,false));
                lcJoinItem.getJoin().setAliasClause(lcJoinItemJoinExpr.getAliasClause());
                lcJoinItem.setStartToken(lcJoinItem.getJoin().getStartToken());
                lcJoinItem.setEndToken(lcJoinItem.getJoin().getEndToken());
                retval.getJoinItems().addJoinItem(lcJoinItem);

                TTable lcTable = new TTable();
                lcTable.setTableType(pJoinExpr.getRightOperand().getFromtableType());
                lcTable.setAliasClause(lcJoinItemJoinExpr.getAliasClause());
                lcTable.setStartToken(lcJoinItemJoinExpr.getStartToken());
                lcTable.setEndToken(lcJoinItemJoinExpr.getEndToken());
                pJoinExpr.setRightTable(lcTable);
                lcTable.setJoinExpr(lcJoinItemJoinExpr);
            }
        }

        if (lcJoinItem == null) return retval;

        lcJoinItem.setJoinType(pJoinExpr.getJointype());
        lcJoinItem.setUsingColumns(pJoinExpr.usingColumns);
        if ((lcJoinItem.getUsingColumns() != null) && (tables.size()>1)){
            TObjectName crf ;
            for (int i=0;i<lcJoinItem.getUsingColumns().size();i++){
                crf = lcJoinItem.getUsingColumns().getObjectName(i);
                // link this column to last 2 tables
                tables.getTable(tables.size()-1).getObjectNameReferences().addObjectName(crf);
                tables.getTable(tables.size()-2).getObjectNameReferences().addObjectName(crf);

                tables.getTable(tables.size()-1).getLinkedColumns().addObjectName(crf);
                crf.setSourceTable(tables.getTable(tables.size()-1));
                tables.getTable(tables.size()-2).getLinkedColumns().addObjectName(crf);
                crf.setSourceTable(tables.getTable(tables.size()-2));

            }
            lcJoinItem.setEndToken(lcJoinItem.getUsingColumns().getEndToken());
        }
        lcJoinItem.setOnCondition(pJoinExpr.onCondition);
        if (lcJoinItem.getOnCondition() != null)
        {
            lcJoinItem.getOnCondition().doParse(this,ESqlClause.joinCondition);
            lcJoinItem.setEndToken(lcJoinItem.getOnCondition().getEndToken());
        }


        return retval;
    }

    public boolean locateVariableOrParameter(TObjectName cr){
        return locateVariableOrParameter(cr,false);
    }
    public boolean locateVariableOrParameter(TObjectName cr, boolean checkVariableDeclaredInProcedure){
      boolean ret =  false;
      if (cr.getDbObjectType() == EDbObjectType.variable) return true;
      if (cr.toString().equalsIgnoreCase("*")) return  false;
      //search variable in framestack

      TVariable symbolVariable = null;

      if (cr.getTableToken() != null){
          // record_variable.column
          symbolVariable =  TSymbolTableManager.searchSymbolVariable(this.getFrameStack(),cr.getTableToken().toString());
          if (symbolVariable != null){
              cr.getTableToken().setDbObjectType(EDbObjectType.variable);
              //TTable sourceTable = new TTable(new TObjectName(EDbObjectType.table,symbolVariable.getVariableName().getStartToken()));
              TTable sourceTable = new TTable(TObjectName.createObjectName (this.dbvendor, EDbObjectType.variable,symbolVariable.getVariableName().getStartToken()));
              sourceTable.getLinkedColumns().addObjectName(cr);
              cr.setSourceTable(sourceTable);
             // symbolVariable.getVariableName().getReferencedObjects().addObjectName(cr);
             // System.out.println("find variable:"+cr.toString());
              cr.setResolveStatus(TBaseType.RESOLVED_AND_FOUND); // set resolve status to resolved,避免在 TAttributeResolver 中关联到其他 table
              return true;
          }

      }else{
          // variable
          symbolVariable =  TSymbolTableManager.searchSymbolVariable(this.getFrameStack(),cr.toString());
          if (symbolVariable != null){
              cr.setDbObjectType(EDbObjectType.variable);
              symbolVariable.getVariableName().getReferencedObjects().addObjectName(cr);
              return true;
          }
      }

        // check parameters in plsql only, may add support for sql server later.
      if(! ((dbvendor == EDbVendor.dbvoracle)||(dbvendor == EDbVendor.dbvmysql))) return false;
      if (cr.getObjectType() == TObjectName.ttobjVariable) return true;

        Stack symbolTable = this.getTopStatement().getSymbolTable();
        TSymbolTableItem item = null;
        TObjectName objName = null;
        TObjectName qualifiedName = null; // function/procedure name or label name of plsql block
        for (int i = symbolTable.size()-1;i>=0;i--){
            item = (TSymbolTableItem)symbolTable.get(i);
            if (item.getData() instanceof TParameterDeclaration){
                objName = ((TParameterDeclaration)item.getData()).getParameterName();
            }else if (item.getData() instanceof TVarDeclStmt){
                objName = ((TVarDeclStmt)item.getData()).getElementName();
            }else  if (item.getData() instanceof TObjectName){
                objName = (TObjectName)item.getData();
            }

            if (objName == null) continue;

            if (cr.toString().compareToIgnoreCase(objName.toString()) == 0){
                ret = true;
                if (checkVariableDeclaredInProcedure) break; // return true if variable declared in procedure
                for(int j=0;i<tables.size();i++){
                    TTable lcTable = tables.getTable(j);
                    if (lcTable.isBaseTable()){
                        if (fireOnMetaDatabaseTableColumn(
                                             lcTable.getPrefixServer()
                                            ,lcTable.getPrefixDatabase()
                                            ,lcTable.getPrefixSchema()
                                            ,lcTable.getName()
                                            ,cr.getColumnNameOnly())){
                            ret = false;
                            break;
                        }
                    }
                }

                if (ret)  break;
            }else if (cr.toString().indexOf(".")>0){
                // qualified object reference, compare it with procedure/function/block label prefixed
                if (item.getStmt() instanceof TPlsqlCreateFunction){
                   qualifiedName = ((TPlsqlCreateFunction)item.getStmt()).getFunctionName();
                }else if (item.getStmt() instanceof TPlsqlCreateProcedure){
                   qualifiedName = ((TPlsqlCreateProcedure)item.getStmt()).getProcedureName();
                }else if (item.getStmt() instanceof TCommonBlock){
                   qualifiedName = ((TCommonBlock)item.getStmt()).getLabelName();
                }

                if (qualifiedName != null){
                    if (cr.toString().compareToIgnoreCase(qualifiedName.toString()+'.'+objName.toString()) == 0){
                        ret = true;
                    }
                }

                if (ret ) break;
            }

        }
          if (ret){
              //add this parameter or variable reference to original parameter/variable
                  objName.getReferencedObjects().addObjectName(cr);
                  cr.setObjectType(TObjectName.ttobjVariable);
          }
          return ret;
    }

    TCTE findCTEByName(String cteName){
        TCTEList lcCteList = searchCTEList(false);
        TCTE lcCte = null;
        if (lcCteList != null){
          for(int i=0;i<lcCteList.size();i++){
             if (lcCteList.getCTE(i).getTableName().toString().compareToIgnoreCase(cteName)==0){
                 lcCte = lcCteList.getCTE(i);
                 break;
             }
          }
        }
     return lcCte;
    }

    /**
     * @deprecated since 2.3.8.2, use {@link TTable#getExpandedStarColumns()} instead.
     *
     * @param lcTable
     * @return
     */
    public ArrayList<String> getColumnsInTable(TTable lcTable){
        if (lcTable.isCTEName()){
            ArrayList<String> columns = new ArrayList<>();
            if (lcTable.getCteColomnReferences()!=null){
                for(TObjectName n:lcTable.getCteColomnReferences()){
                    columns.add(n.toString());
                }
            }else if (lcTable.getCTE().getSubquery() != null && lcTable.getCTE().getSubquery().getResultColumnList() != null){
                for(TResultColumn resultColumn:lcTable.getCTE().getSubquery().getResultColumnList()){
                    columns.add(resultColumn.getDisplayName());
                }
            }
            return columns;
        }else{
            return getColumnsInTable(
                    lcTable.getPrefixServer()
                    ,lcTable.getPrefixDatabase()
                    ,lcTable.getPrefixSchema()
                    ,lcTable.getName()
            );
        }
    }


    /**
     * @deprecated since 2.3.8.2, use {@link TTable#getExpandedStarColumns()} instead.
     *
     * @param pServer
     * @param pDatabase
     * @param pSchema
     * @param pTable
     * @return
     */
    public ArrayList<String> getColumnsInTable(String pServer,String pDatabase,String pSchema,String pTable){
        TFrame stackFrame = getFrameStack().firstElement();
        TGlobalScope globalScope = (TGlobalScope)stackFrame.getScope();

        if (globalScope.getSqlEnv() != null){
            return globalScope.getSqlEnv().getColumnsInTable(pDatabase+"."+pSchema+"."+pTable,false);
        }else{
            return  null;
        }
    }


    public boolean fireOnMetaDatabaseTableColumn(String pServer,String pDatabase,String pSchema,String pTable,String pColumn){
//        boolean lcResult = false;
//        if (this.getGsqlparser().getMetaDatabase() != null){
//            lcResult = this.getGsqlparser().getMetaDatabase().checkColumn(pServer,pDatabase,pSchema,pTable,pColumn);
//        }

        TFrame stackFrame = getFrameStack().firstElement();
        TGlobalScope globalScope = (TGlobalScope)stackFrame.getScope();

        if (globalScope.getSqlEnv() != null){
           // System.out.println(globalScope.getSqlEnv().toString());

            return globalScope.getSqlEnv().columnInTable(pDatabase+"."+pSchema+"."+pTable,pColumn);
        }else{
            return  false;
        }

//        return lcResult;
    }

    public TTable getFirstPhysicalTable(){
        TTable ret = null;
        if (tables.size() == 0) return null;
        for(int i=0;i<tables.size();i++){
            if (tables.getTable(i).isBaseTable()) {
                ret = tables.getTable(i);
                break;
            }
        }
        return ret;
    }
    private TObjectNameList orphanColumns = null;

    public TObjectNameList getOrphanColumns() {
        if (orphanColumns == null) orphanColumns = new TObjectNameList();
        return orphanColumns;
    }

    protected boolean linkToFirstTable(TObjectName pColumn,int pCandidateTableCnt){
        boolean lcResult = false;
        if ((dbvendor == EDbVendor.dbvteradata)&&(pColumn.isQualified())&&(pColumn.getTableToken().getDbObjectType() != EDbObjectType.subquery_alias)){
            // update table1 set col = 'value' where table1.id = table2.id2
            boolean isFoundLinkedTable = false;
            TCustomSqlStatement lcSql = this;
            while (lcSql != null){
                int i = 0;
                i = lcSql.tables.searchTableByNameOrAlias(pColumn.getTableToken().toString());
                isFoundLinkedTable = ( i != -1);
                if (isFoundLinkedTable) {
                    if (tables.getTable(i).getEffectType() == ETableEffectType.tetImplicitLateralDerivedTable ){
                        // 如果不查重table，会导致 employee.first_name 中的 employee 被第二次加到 tables 中
//                        DELETE FROM foodmart.trimmed_employee ACT
//                        WHERE ACT.employee_id = employee.employee_id
//                        AND  employee.first_name = 'Walter'
//                        AND  trimmed_salary.employee_id = -1

                        TTable newTable = tables.getTable(i);
                        newTable.getLinkedColumns().addObjectName(pColumn);
                        pColumn.setSourceTable(newTable);
                        pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_TABLE_IN_OLD_ALGORITHM);
                    }
                    break;
                }
                lcSql = lcSql.getParentStmt();
            }
            if (!isFoundLinkedTable){
                TTable newTable = null;
                if (pColumn.getDatabaseToken() == null){

                    //newTable = new TTable(new TObjectName(EDbObjectType.table,pColumn.getTableToken()));
                    newTable = new TTable(TObjectName.createObjectName (this.dbvendor, EDbObjectType.table,pColumn.getTableToken()));
                    newTable.setStartToken(pColumn.getTableToken());
                    newTable.setEndToken(pColumn.getTableToken());
                }else{

                    //newTable = new TTable(new TObjectName(EDbObjectType.table,pColumn.getSchemaToken(), pColumn.getTableToken()));
                    newTable = new TTable(TObjectName.createObjectName (this.dbvendor,EDbObjectType.table,pColumn.getDatabaseToken(), pColumn.getTableToken()));
                    newTable.setStartToken(pColumn.getSchemaToken());
                    newTable.setEndToken(pColumn.getTableToken());
                }

                newTable.setTableType(ETableSource.objectname);
                newTable.setEffectType(ETableEffectType.tetImplicitLateralDerivedTable);
                newTable.getLinkedColumns().addObjectName(pColumn);
                pColumn.setSourceTable(newTable);
                pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_TABLE_IN_OLD_ALGORITHM);
                this.addToTables(newTable);

                // 2024 年
                // 不能加入到 relations 中，否则会导致 下面 SQL 中 star column 同时链接到 SPCOMM.L_FIXED_RATE_PLAN_REF, ipshare_ofccplv.cprof_d_period_dates_ref
                // 从而导致 本来不该有的歧义产生

                // UPDATE b_rate_plan
                //FROM
                //(
                //SELECT * FROM SPCOMM.L_FIXED_RATE_PLAN_REF
                //WHERE rate_plan_ref_eff_dt<= ipshare_ofccplv.cprof_d_period_dates_ref.PERIOD
                //) AS ref
                //SET accs_fee = REF.accs_fee,
                //SVC_TYPE = REF.prod_grp_lvl_1,
                //rate_plan_lvl3 = REF.rate_plan_lvl_3,
                //prod_grp_lvl3 = REF.prod_grp_lvl_2
                //WHERE b_rate_plan.svc_type IS NULL

                // 2025/2/25, v3.0.4.8
                // 需要加入到 relations 中，新的 gudusoft.gsqlparser.resolver package 中的算法会处理这种情况
                // teradata 的隐式横向派生表不能加入到关系解析器中
                // this.getRelations().add(newTable);
            }
            return true;
        }
        if (pColumn.getCandidateTables().size() == 1){
            TTable table = pColumn.getCandidateTables().getTable(0);
            table.getLinkedColumns().addObjectName(pColumn);
            pColumn.setSourceTable(table);
            lcResult = true;
        }
        else if ((tables.size() == 1) || (pCandidateTableCnt == 1)){
            TTable table = tables.getTable(0);

            if(table.getTableType() == ETableSource.function){
                //lcResult = linkToFunctionTable(table, pColumn);
                int iRet = table.getFuncCall().isColumnInThisTableFunction(this.getSqlEnv(),this.dbvendor,pColumn);
                if ( iRet == TBaseType.COLUMN_IN_TABEL_FUNCTION_YES){
                    lcResult = true;
                    table.getLinkedColumns().addObjectName(pColumn);
                    pColumn.setSourceTable(table);
                    lcResult = true;
                }else if ( iRet == TBaseType.COLUMN_IN_TABEL_FUNCTION_NO){
                    lcResult = false;
                }else{
                    table.getLinkedColumns().addObjectName(pColumn);
                    pColumn.setSourceTable(table);
                    lcResult = true;
                }
            }else if(table.getTableType() == ETableSource.subquery){
                if (! table.getSubquery().searchColumnInResultSet(pColumn,(tables.size()==1))){
                    getOrphanColumns().addObjectName(pColumn);
                    pColumn.setOrphanColumn(true);
                    TSourceToken st = pColumn.getStartToken();
                    if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
                        TBaseType.log(String.format("Add orphan column <%s> to statement in old algorithm in subquery %s",pColumn.toString(),table.getAliasName()),TLog.WARNING,table);
                    }
                    this.parseerrormessagehandle(new TSyntaxError(st.astext, st.lineNo, st.columnNo
                            ,"find orphan column", EErrorType.sphint
                            , TBaseType.MSG_HINT_FIND_ORPHAN_COLUMN,this,st.posinlist,pColumn));
                }
            }
            else{
                table.getLinkedColumns().addObjectName(pColumn);
                pColumn.setSourceTable(table);
                lcResult = true;
                if ((dbvendor == EDbVendor.dbvbigquery)&&(pCandidateTableCnt == 0) && (pColumn.isQualified())){
                    // bigquery struct column used in query
//                    create view test as (SELECT rollNo,
//                            info.name as n1,
//                    info2.name as n2,
//                            info.age from my_first_dataset.student_records);

                    pColumn.columnToProperty();
                }
            }
        }else if (tables.size() > 1){
            // if there is only a table without table alias, then, link to this table
            boolean foundOnlyOneTable = false;
            TTable tableWithoutAlias = null;
            for(TTable table:tables){
                if (table.isCTEName()) continue; // CTE 即便没有 指定alias，也不作为考虑对象
                if (table.getAliasClause() == null){
                    tableWithoutAlias = table;
                    if (foundOnlyOneTable){
                        foundOnlyOneTable = false;
                        break;
                    }else{
                        foundOnlyOneTable = true;
                    }
                }
            }

            if (foundOnlyOneTable){
                tableWithoutAlias.getLinkedColumns().addObjectName(pColumn);
                pColumn.setSourceTable(tableWithoutAlias);
                lcResult = true;
            }else{
                getOrphanColumns().addObjectName(pColumn);
                pColumn.setOrphanColumn(true);
                TSourceToken st = pColumn.getStartToken();
                if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
                    TBaseType.log(String.format("Add orphan column <%s> to statement in old algorithm ",pColumn.toString()),TLog.WARNING,this);
                }

                this.parseerrormessagehandle(new TSyntaxError(st.astext, st.lineNo, st.columnNo
                        ,"find orphan column", EErrorType.sphint
                        , TBaseType.MSG_HINT_FIND_ORPHAN_COLUMN,this,st.posinlist,pColumn));
            }

        }
        return  lcResult;
    }

    private boolean linkToFunctionTable(TTable table, TObjectName pColumn) {
        if(table.getTableName().toString().toUpperCase().equals("STRING_SPLIT")){
            if(pColumn.getColumnNameOnly().toUpperCase().equals("VALUE")){
                table.getLinkedColumns().addObjectName(pColumn);
                pColumn.setSourceTable(table);
                return true;
            }
            else return false;
        }
        else {
            table.getLinkedColumns().addObjectName(pColumn);
            pColumn.setSourceTable(table);
            return true;
        }
    }

    boolean isSQLServerInsertedDelete(TObjectName pColumn){
        if (((pColumn.toString().toUpperCase().startsWith("INSERTED"))
                ||(pColumn.toString().toUpperCase().startsWith("DELETED")))
                //&&(plocation == ESqlClause.output)
                &&(targetTable != null)){
            targetTable.getObjectNameReferences().addObjectName(pColumn);
            pColumn.setSourceTable(targetTable);
            return true;
        }else
        {
            return false;
        }
    }

    boolean isOracleNewOldTable(TObjectName pColumn){
        boolean ret = false;
        if (dbvendor != EDbVendor.dbvoracle) return false;
        if (!(pColumn.isQualified())) return false;
        if ((pColumn.getTableString().equalsIgnoreCase(":new"))
            ||(pColumn.getTableString().equalsIgnoreCase(":old"))){
            if (getAncestorStmt().tables != null){
                if (getAncestorStmt().tables.size() > 0){
                    getAncestorStmt().tables.getTable(0).getLinkedColumns().addObjectName(pColumn);
                    pColumn.setSourceTable(getAncestorStmt().tables.getTable(0));
                    ret = true;
                }
            }
        }

        return ret;
    }

    public boolean searchDaxVariableInStack(TObjectName pName){
        boolean ret = false;
        if (getVariableStack().size() == 0) return false;
        if (pName.getDbObjectType() == EDbObjectType.column) return false;
        for(int i=0;i<variableStack.size();i++){
            if (pName.toString().equalsIgnoreCase(((TObjectName) variableStack.get(i)).toString())){
                ret = true;
                break;
            }
        }
        return ret;
    }

    boolean linkColumnToTableDax(TObjectName pColumn, ESqlClause pLocation){
        boolean lcResult = true ;
        TDaxFunction daxFunction = null;
        if (searchDaxVariableInStack(pColumn)) return false;
        if (getDaxFunctionStack().size() > 0){
            daxFunction = daxFunctionStack.peek();
        }

        if (pColumn.getTableToken() != null){
            //TTable sourceTable = new TTable(new TObjectName(EDbObjectType.table,pColumn.getTableToken()));
            TTable sourceTable = new TTable(TObjectName.createObjectName (this.dbvendor,EDbObjectType.table,pColumn.getTableToken()));
            sourceTable.getLinkedColumns().addObjectName(pColumn);
            addToTables(sourceTable);
            if ((daxFunction != null) && (daxFunction.getDefaultTable() == null)){
                daxFunction.setDefaultTable(sourceTable);
            }
        }else{
            if ((daxFunction != null) &&(daxFunction.getDefaultTable() != null)){
                daxFunction.getDefaultTable().getLinkedColumns().addObjectName(pColumn);
            }else{
                ((TDaxStmt)this).getDefaultTable().getLinkedColumns().addObjectName(pColumn);
            }
        }
        return  lcResult;
    }

    public boolean linkColumnToTable(TObjectName pColumn, ESqlClause pLocation){
        boolean lcResult = false,isContinue = false;
        int candidateTableCnt = 0;
        if (dbvendor == EDbVendor.dbvdax){
            return linkColumnToTableDax(pColumn,pLocation);
        }

        if (pColumn.getSourceTable() != null) {
            lcResult = true;
            return lcResult;
        }

        if (pColumn.getResolveStatus() == TBaseType.RESOLVE_DELAY_TO_COLUMN_RESOLVER) return true;

        pColumn.setLocation(pLocation);

        if (! pColumn.isValidColumnName(dbvendor)) {
            if (pColumn.isReservedKeyword()){
                if (
                        ((pColumn.getStartToken().tokencode != TBaseType.rrw_mysql_true)&&(!(pColumn.getStartToken().toString().equalsIgnoreCase("true"))))
                        &&((pColumn.getStartToken().tokencode != TBaseType.rrw_mysql_false)&&(!(pColumn.getStartToken().toString().equalsIgnoreCase("false"))))
                        &&(pColumn.getStartToken().tokencode != TBaseType.rrw_mysql_default)
                   ) {
                        boolean mysqlBuiltFunction = false;
                        if (dbvendor == EDbVendor.dbvmysql){
                            mysqlBuiltFunction = functionChecker.isBuiltInFunction(pColumn.toString(),EDbVendor.dbvmysql,"6.0");
                        }
                        if (!mysqlBuiltFunction){
                            TSourceToken st1 = pColumn.getStartToken();
                            TSyntaxError err = new TSyntaxError(st1.toString()
                                    , st1.lineNo, st1.columnNo
                                    , String.format("Reserved keyword can't be column name")
                                    , EErrorType.sperror
                                    , TBaseType.MSG_ERROR_RESERVED_KEYWORD_CANT_USED_AS_COLUMN_NAME
                            ,this,st1.posinlist);
                            this.parseerrormessagehandle(err);
                        }
                }
            }
            return false;
        }

        if (isOracleNewOldTable(pColumn)) return true;
        if (isSQLServerInsertedDelete(pColumn)) return true;

        // oracle insert all statement,
        // WHEN id <= 3 THEN INTO dest_tab1 VALUES(id, description1)
        // column in values clause must be in the subquery of insert all statement
        if (pColumn.getLocation() == ESqlClause.insertValues){
            if (this instanceof TInsertSqlStatement){
                TInsertSqlStatement insertSqlStatement = (TInsertSqlStatement)this;
                if (insertSqlStatement.isInsertAll()){
                   // if (pColumn.getStartToken().tokencode == TBaseType.rrw_snowflake_default) return true;
                    lcResult = insertSqlStatement.getSubQuery().searchColumnInResultSet(pColumn, true);
                }
            }

            if (lcResult) return true;

            // value in values clause maybe parameter of the procedure/function parameter
            lcResult = locateVariableOrParameter(pColumn,true);
            if (lcResult) return true;
        }

        for(int i=0;i<tables.size();i++){
            TTable lcTable = tables.getTable(i);
            if (lcTable.getEffectType() == ETableEffectType.tetSelectInto) continue;
            if (lcTable.getEffectType() == ETableEffectType.tetImplicitLateralDerivedTable) continue;

            if (pColumn.isQualified()){
                lcResult = pColumn.resolveWithThisTable(lcTable);
                if ((lcResult) && (lcTable.getTableType() == ETableSource.subquery)){
                    pColumn.getTableToken().setDbObjectType(EDbObjectType.subquery_alias);

                    int lcPos = lcTable.searchColumnInAlias(pColumn);
                    lcResult = lcPos>=0;
                    if (lcResult){
                        // 在 alias 中找到 source column， 还需要对应到 subquery select 中的 select list
                        // sql 见 https://e.gitee.com/gudusoft/projects/151613/tasks/list?issue=I8JR0W#note_23051633
                        if ((lcTable.getSubquery() != null)&&(lcTable.getSubquery().getResultColumnList() != null)){
                            pColumn.setSourceColumn(lcTable.getSubquery().getResultColumnList().getResultColumn(lcPos));
                        }
                    }else{
                        lcResult =  lcTable.getSubquery().searchColumnInResultSet(pColumn,true);
                    }
               //     lcTable.getSubquery().searchColumnInResultSet(pColumn,true);

                }else if ((lcResult) && (lcTable.isCTEName())){
                    lcTable.getCTE().searchColumnInResultSet(this,lcTable,pColumn,true);
                }else if ((lcResult) && (lcTable.getTableType() == ETableSource.openquery)&&(lcTable.getSubquery() != null)){
                    pColumn.getTableToken().setDbObjectType(EDbObjectType.subquery_alias);
                    lcTable.getSubquery().searchColumnInResultSet(pColumn,true);
//                }else if ((lcResult) && (lcTable.getTableType() == ETableSource.unnest)&&(lcTable.getUnnestClause() != null)){
//                    pColumn.getTableToken().setDbObjectType(EDbObjectType.subquery_alias);
//                    lcTable.getSubquery().searchColumnInResultSet(pColumn,true);
                }
                if (lcResult&&pColumn.toString().endsWith("*")){
                    pColumn.getSourceTableList().add(lcTable);
//                    ArrayList<String> lcColumns = getColumnsInTable(lcTable);
//                    if (lcColumns != null){
//                        pColumn.getColumnsLinkedToStarColumn().addAll(lcColumns);
//                    }
                }
            }else {
              // column not qualified

                // check if this is the column alias in current select list.
                if((!lcResult)&& ((!pColumn.isQualified()) && (this instanceof TSelectSqlStatement)&&(getResultColumnList() !=null))
                        && (TBaseType.isSupportLateralColumn(dbvendor))
                ){
                    for(int j=0;j<getResultColumnList().size();j++){
                        TResultColumn lcField = getResultColumnList().getResultColumn(j);
                        lcResult = lcField.isMatchedUsingAlias(pColumn);
                        if (
                                 (!TSQLEnv.isAliasReferenceForbidden[this.dbvendor.ordinal()])&&
                                ((lcResult)&&(pColumn.getStartToken().posinlist > lcField.getAliasClause().getStartToken().posinlist))){
                            pColumn.setSourceColumn(lcField);
                            lcField.getTargetColumns().addObjectName(pColumn);
                            pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_COLUMN_ALIAS_IN_OLD_ALGORITHM);
                            break;
                        }else{
                            lcResult = false;
                        }
                    }

                    if (lcResult) return true;
                }

                if (pColumn.getColumnNameOnly().equalsIgnoreCase("*")){
                    lcResult = true;
                    isContinue = true; // in order to match next table in the from clause
                    pColumn.getSourceTableList().add(lcTable);
//                    ArrayList<String> lcColumns = getColumnsInTable(lcTable);
//                    if (lcColumns != null){
//                        pColumn.getColumnsLinkedToStarColumn().addAll(lcColumns);
//                    }
                }else if (lcTable.isBaseTable()){
                    lcResult = fireOnMetaDatabaseTableColumn(
                                         lcTable.getPrefixServer()
                                        ,lcTable.getPrefixDatabase()
                                        ,lcTable.getPrefixSchema()
                                        ,lcTable.getName()
                                        ,pColumn.getColumnNameOnly());
                    if (! lcResult) {
                        candidateTableCnt++;
                        pColumn.getCandidateTables().addTable(lcTable);
                    }

                }else if ((lcTable.getTableType() == ETableSource.subquery)
                            ||((lcTable.getTableType() == ETableSource.openquery)&&(lcTable.getSubquery() != null))){

                    lcResult = lcTable.searchColumnInAlias(pColumn)>=0;
                    if (!lcResult){
                        lcResult = lcTable.getSubquery().searchColumnInResultSet(pColumn,(tables.size() == 1)
                                &&(pColumn.getCandidateTables().size() == 0));
                        if (! lcResult) {
                            candidateTableCnt++;
                            pColumn.getCandidateTables().addTable(lcTable);
                        }
                    }


//                    if (lcTable.isIncludeColumnAlias()){
//                       // System.out.println("subquery with alias:"+lcTable.getAliasClause().toString()+", skip search:"+pColumn.toString());
//
//                    }else{
//                        lcResult = lcTable.getSubquery().searchColumnInResultSet(pColumn,(tables.size() == 1)&&(pColumn.getCandidateTables().size() == 0));
//                        if (! lcResult) candidateTableCnt++;
//                    }
                }else  if (lcTable.isCTEName()){
                    lcResult = lcTable.getCTE().searchColumnInResultSet(this,lcTable,pColumn,tables.size() == 1);
                    if (! lcResult) {
                        candidateTableCnt++;
                        pColumn.getCandidateTables().addTable(lcTable);
                    }
                }else if (lcTable.getTableType() == ETableSource.function){
                    //  search in this table function
                        if(tables.size() == 1){
                                lcResult = ( lcTable.getFuncCall().isColumnInThisTableFunction(this.getSqlEnv(),this.dbvendor,pColumn)
                                != TBaseType.COLUMN_IN_TABEL_FUNCTION_NO);
                        }
                        else{
                                lcResult = ( lcTable.getFuncCall().isColumnInThisTableFunction(this.getSqlEnv(),this.dbvendor,pColumn)
                                                                        == TBaseType.COLUMN_IN_TABEL_FUNCTION_YES);
                        }
                }else if (lcTable.getTableType() == ETableSource.tableExpr 
                                && lcTable.getTableExpr().getExpressionType() == EExpressionType.function_t
                                && lcTable.getTableExpr().getFunctionCall() != null){
                    //  search in this table function
                    lcResult = ( lcTable.getTableExpr().getFunctionCall().isColumnInThisTableFunction(this.getSqlEnv(),this.dbvendor,pColumn)
                                                                        == TBaseType.COLUMN_IN_TABEL_FUNCTION_YES);
                }else if (lcTable.getTableType() == ETableSource.pivoted_table){
                    lcResult = fireOnMetaDatabaseTableColumn(
                            lcTable.getPrefixServer()
                            ,lcTable.getPrefixDatabase()
                            ,lcTable.getPrefixSchema()
                            ,lcTable.getName()
                            ,pColumn.getColumnNameOnly());
                }else if (lcTable.getTableType() == ETableSource.unnest){
                    for(TObjectName objectName:lcTable.getLinkedColumns()){
                        if (objectName.toString().equalsIgnoreCase(pColumn.toString())){
                            lcResult = true;
                            break;
                        }
                    }

                    if (!lcResult){
                        if (lcTable.getAliasClause() == null){
                            // this unnest() clause generate column with default name: "value"
                            if (pColumn.toString().equalsIgnoreCase("value")){
                                lcResult = true;
                            }
                        }else{
                        }
                    }
                }//unnest
            }

            if (lcResult) {
                lcTable.getLinkedColumns().addObjectName(pColumn);
                pColumn.setSourceTable(lcTable);
               // pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_TABLE_IN_OLD_ALGORITHM);
                if (!isContinue) break;
            }
        }

        // check variable after metadata checking
        if (locateVariableOrParameter(pColumn)) return false;

        // check if this is the column alias in current select list.
//        if((!lcResult)&& ((!pColumn.isPrefixed()) && (this instanceof TSelectSqlStatement)&&(getResultColumnList() !=null))){
//            for(int j=0;j<getResultColumnList().size();j++){
//                TResultColumn lcField = getResultColumnList().getResultColumn(j);
//                lcResult = lcField.isMatchedUsingAlias(pColumn);
//                if ((lcResult)&&(pColumn.getStartToken().posinlist > lcField.getAliasClause().getStartToken().posinlist)){
//                    pColumn.setSourceColumn(lcField);
//                    lcField.getTargetColumns().addObjectName(pColumn);
//                    break;
//                }else{
//                    lcResult = false;
//                }
//            }
//        }

        if (lcResult) return true;

        boolean isSearchUpLevel = (this.parentStmt != null);

        if ((isSearchUpLevel) && (sqlstatementtype == ESqlStatementType.sstselect)){
            isSearchUpLevel = (pColumn.isQualified()
                                || (
//                                        (((TSelectSqlStatement)(this)).getLocation() != ESqlClause.elTable) &&
                                         (! ((TSelectSqlStatement)(this)).isQueryOfCTE())
                                    )
                               )
                            && (parentStmt.sqlstatementtype != ESqlStatementType.sstinsert)
                            && (!((pColumn.getLocation() == ESqlClause.selectList)&&(((TSelectSqlStatement)(this)).getLocation() == ESqlClause.join)))
                            && ((((TSelectSqlStatement)(this)).getLocation() != ESqlClause.pivot_in))
                            && (!((parentStmt.sqlstatementtype == ESqlStatementType.sstcreatetable)))
                            && (!((parentStmt.sqlstatementtype == ESqlStatementType.sstcreateview)))
//                            && (!((pColumn.getLocation() == ESqlClause.selectList)&&(parentStmt.sqlstatementtype == ESqlStatementType.sstcreatetable)))
//                            && (!((pColumn.getLocation() == ESqlClause.selectList)&&(parentStmt.sqlstatementtype == ESqlStatementType.sstcreateview)))
                            && (! ((pColumn.getLocation() == ESqlClause.selectList)
                                    &&(candidateTableCnt == 1) && (this instanceof TSelectSqlStatement)
                                    && (((TSelectSqlStatement)(this)).getLocation() == ESqlClause.elTable)
                                    ) ) // ref:mantis: #2628
                           // && ( ((TSelectSqlStatement)(this.parentStmt)).getSetOperatorType() == ESetOperatorType.none)
            ;

            if (isSearchUpLevel){
                isSearchUpLevel = !((!pColumn.isQualified())&&(((TSelectSqlStatement) this).getLocation() == ESqlClause.where));
            }
        }

        if (isSearchUpLevel&&(pColumn.isContinueToSearch())){ // only search one level up, c:\prg\gsp_sqlfiles\TestCases\java\oracle\dbobject\berger_sqltest_04.sql
            boolean increaseLevel = true;
            if (parentStmt instanceof TSelectSqlStatement){
                if( ((TSelectSqlStatement)parentStmt).getSetOperatorType() != ESetOperatorType.none){
                   increaseLevel = false;
                }
            }
            if (increaseLevel){
                pColumn.searchLevel++;
            }

            lcResult = parentStmt.linkColumnToTable(pColumn,pLocation);

            if (increaseLevel){
                pColumn.searchLevel--;
            }
        }

        if ((! lcResult) && (pColumn.searchLevel == 0)) {
            if (this.sqlstatementtype == ESqlStatementType.sstselect){
                if( ((TSelectSqlStatement)this).getSetOperatorType() == ESetOperatorType.none){
                    //                    USING _spVV0 (INTEGER)
                    //                            INSERT INTO table3
                    //                    SELECT :_spVV0,x. *,m.col3
                    //                    from ((           select table1.col1, (table1.col1 + table5.col2) c from table1
                    //                            union all select col3,col4 from table2) x
                    //                    cross join (select id from table2) m )

                    // table5 in the above sql only link to the nearest level sql, but not to up-level which is union all

                    linkToFirstTable(pColumn,candidateTableCnt);
                }
            }else{
                linkToFirstTable(pColumn,candidateTableCnt);
            }
        }

        return lcResult;
    }


    /**
     *
     * @deprecated As of v1.6.0.1, use  {@link #linkColumnToTable} instead
     */
    public void linkColumnReferenceToTable(TObjectName cr, ESqlClause plocation){
        // this is the column name, link it to table
        if (cr == null) return;
        cr.setLocation(plocation);
        if (cr.getObjectType() == TObjectName.ttobjVariable) return;
        if (cr.getObjectType() == TObjectName.ttobjColumnAlias) return;
        if (this.dbvendor == EDbVendor.dbvsybase){
            TSourceToken pt = cr.getPartToken();
            if ( pt != null){
                if (pt.tokentype == ETokenType.ttdqstring){
                //"0123", quoted string start with a number can't a column
                    if ((pt.toString().charAt(1) >= '0')
                        &&(pt.toString().charAt(1) <= '9')){
                        return;
                    }else if (pt.toString().length() == 2){
                        //"", empty
                        return;
                    }else if (pt.toString().substring(1,pt.toString().length()-1).trim().length() == 0){
                        //"  "
                        return;
                    }
                }
            }
        }


        if (cr.getPartToken() != null){
            if (cr.getPartToken().tokentype == ETokenType.ttkeyword){
                boolean reservedKeyword = false;
                switch (dbvendor){
                    case dbvmssql:
                        //reservedKeyword = ! this.getGsqlparser().getFlexer().canBeColumnName(cr.getPartToken().tokencode);
                        reservedKeyword = ! TLexerMssql.canBeColumnName(cr.getPartToken().tokencode);
                        break;
                    case dbvsybase:
                        reservedKeyword = keywordChecker.isKeyword(cr.getPartToken().toString(), EDbVendor.dbvsybase, "15.7", true);
                        break;
                    default:
                        break;
                }
                if (reservedKeyword) return;
            }
        }

        // let's check is this columnreference is variable or parameter of plsql function/procedure
      //  if (locateVariableOrParameter(cr)) return;

//        if ((cr.getPartToken() != null)&&((dbvendor == EDbVendor.dbvmssql)||(dbvendor == EDbVendor.dbvsybase))){
//            if ((cr.getPartToken().tokentype == ETokenType.ttkeyword)&&(!(this.getGsqlparser().getFlexer().canBeColumnName(cr.getPartToken().tokencode)))){
//                // keyword can't be column name:
//                //select * From dbo.table Where DATEDIFF(day, create_date, expiry_date) < 14
//               return;
//            }
//        }

        if ((cr.toString().startsWith("@")))
//            if ((cr.toString().endsWith("*"))||(cr.toString().startsWith("@")))
        {
            cr.setObjectType(TObjectName.ttobjNotAObject);
            return;
        }

        if (dbvendor == EDbVendor.dbvoracle){
            if ( //(cr.toString().compareToIgnoreCase ("rowid") == 0)||
                    (cr.toString().compareToIgnoreCase ("sysdate") == 0)
            || (cr.toString().compareToIgnoreCase ("nextval") == 0)
            || (cr.toString().compareToIgnoreCase ("rownum") == 0)
            || (cr.toString().compareToIgnoreCase ("level") == 0)
                    ){
                cr.setObjectType(TObjectName.ttobjNotAObject);
                if (cr.getDbObjectType() == EDbObjectType.unknown){
                    cr.setDbObjectType(EDbObjectType.notAColumn);
                }
                return;
            }
        }

        if (((cr.toString().toUpperCase().startsWith("INSERTED"))||(cr.toString().toUpperCase().startsWith("DELETED")))&&(plocation == ESqlClause.output)&&(targetTable != null)){
            targetTable.getObjectNameReferences().addObjectName(cr);
            return;
        }

        if ( ((cr.toString().toUpperCase().startsWith(":NEW"))
              ||(cr.toString().toUpperCase().startsWith(":OLD")))
             &&(this.getTopStatement() instanceof TPlsqlCreateTrigger)
             &&(dbvendor == EDbVendor.dbvoracle)){
             this.getTopStatement().tables.getTable(0).getObjectNameReferences().addObjectName(cr);
            return;
        }



        int ret = this.tables.checkColumnReferenceInTables(cr);
         if (ret >= 0) {
             TTable lcTable = this.tables.getTable(ret);
             if (lcTable.isBaseTable()){
                lcTable.getObjectNameReferences().addObjectName(cr);
             }else if (lcTable.isCTEName()){
                //WITH temp
                //     AS (SELECT *
                //         FROM   sysibm.systables),
                //     temp1
                //     AS (SELECT *
                //         FROM   sysibm.syscolumns)
                //SELECT *
                //FROM   temp A
                //       INNER JOIN temp1 B
                //               ON A.creator = B.tbcreator
                //                  AND A.name = B.tbname
                 TCTE lccte = findCTEByName(lcTable.toString());
                 if (lccte != null){
                     TObjectName objectName = new TObjectName();
                     objectName.init(cr.getPartToken());
                     if (lccte.getSubquery() != null){
                         lccte.getSubquery().linkColumnReferenceToTable(objectName,plocation);
                     }
                 }
             }else if (lcTable.getTableType() == ETableSource.subquery){
                // link s2t1a1 to  subselect2table1 via s2
                //select
                //       s2.s2t1a1
                //from
                //    (
                //       select s2t1.*
                //          from subselect2table1 s2t1
                //    ) s2
               TSelectSqlStatement subquery = lcTable.getSubquery();

                 if(((subquery.getValueClause() == null))&&(!subquery.isCombinedQuery())&&(subquery.getResultColumnList() != null)&&(subquery.getResultColumnList().size() == 1)){
                     TResultColumn lcColumn = subquery.getResultColumnList().getResultColumn(0);
                     if (lcColumn.toString().endsWith("*")){
                        boolean isfound = false;

                        for(int i=0;i<subquery.tables.size();i++){
                            if (subquery.tables.getTable(i).getTableType() == ETableSource.subquery) continue;
                            String columnStr = null;
                            if (cr.getPartToken() != null){
                                //cr.getObjectType() is not ttObjColumn, so we can't use
                                // getColumnToken, this is a bug, need to check it later.
                                columnStr = cr.getPartToken().toString();
                            }
                            if (this.fireOnMetaDatabaseTableColumn(
                                    subquery.tables.getTable(i).getTableName().getServerString(),
                                    subquery.tables.getTable(i).getTableName().getDatabaseString(),
                                    subquery.tables.getTable(i).getTableName().getSchemaString(),
                                    subquery.tables.getTable(i).getName(),columnStr)){
                                subquery.tables.getTable(i).getObjectNameReferences().addObjectName(cr);
                                isfound = true;
                                break;
                            }
                        }



                         if (!isfound)
                         {
                             if(subquery.tables.size() > 1){
                                 cr.setTableDetermined(false);
                             }
                           for(int i=0;i<subquery.tables.size();i++){
                             subquery.tables.getTable(i).getObjectNameReferences().addObjectName(cr);
                           }
                         }

                     } // "*"
                 }
             }
         }else if (ret == -2){
           // no qualifier before column, check is this column of a cte, if not,set it to non-cte table
           boolean isfound = false;
             for (int i=0;i<this.tables.size();i++){
                 if ((this.tables.getTable(i).isCTEName()) &&(this.tables.getTable(i).getCteColomnReferences() != null)){
                     if (this.tables.getTable(i).getCteColomnReferences().searchColumnReference(cr) >= 0){
                        this.tables.getTable(i).getObjectNameReferences().addObjectName(cr);
                         isfound = true;
                         break;
                     }
                 }
             }

           // no qualifier before column, but we still need to check uplevel table like this:
            //SELECT
            //       col1 ,
            //
            //           (    SELECT col2
            //                FROM tab1
            //                WHERE col2 = col1      )
            //   FROM tab2
           // we need to link col1 to tab2 in up level, but not to tab1
            if ((!isfound) &&(
                            (cr.getLocation() != ESqlClause.resultColumn)
                          &&(cr.getLocation() != ESqlClause.insertColumn)
                                    &&(cr.getLocation() != ESqlClause.mergeInsert)
                                    &&(cr.getLocation() != ESqlClause.selectList)
            ) ){  // code #111
                TCustomSqlStatement lcParent = null;
                lcParent = this.getParentStmt();
               while ( lcParent != null) {
                   TTable lcTable;
                 //ret = lcParent.tables.checkColumnReferenceInTables(cr);
                   if (lcParent.sqlstatementtype != ESqlStatementType.sstselect) {
                       break;
                   }
                   for (int i=0;i<lcParent.tables.size();i++){
                       lcTable = lcParent.tables.getTable(i);
                       if (lcTable.getTableType() == ETableSource.objectname) {
                           for(int k = 0; k< lcTable.getObjectNameReferences().size();k++){
                               if (lcTable.getObjectNameReferences().getObjectName(k).isTableDetermined()){
                                   if (cr.toString().equalsIgnoreCase(lcTable.getObjectNameReferences().getObjectName(k).toString())){
                                      isfound = true;
                                       break;
                                   }
                               }
                           }
                       if (isfound) break;
                       }
                   }

                 if (isfound){
                     break;
                 }else{
                     lcParent = lcParent.getParentStmt();
                 }
               } // while

             } // end of code #111

            if (!isfound){
                isfound = checkNonQualifiedColumnReferenceInSubQueryOfUplevelStmt(cr
                        , ((plocation == ESqlClause.resultColumn)
                            ||(plocation == ESqlClause.insertColumn)
                                ||(plocation == ESqlClause.mergeInsert)
                                ||(plocation == ESqlClause.selectList)
                        )
                );
            }

             if ((!isfound)&&(this.tables.size() > 0)){
                 int candidate = 0, firstCandidate = -1;
                 // add this column reference to first non-cte( or cte with column list is null) and non-subquery table
                 for (int i=0;i<this.tables.size();i++){
                     // no qualified column can't belong to a table with alias, that column must be qualified if it's belong to a table with alias
                     //if (this.tables.getTable(i).aliasClause != null) continue;
                     if (
                             (
                                     (!this.tables.getTable(i).isCTEName())
                                ||((this.tables.getTable(i).isCTEName())&&(this.tables.getTable(i).getCteColomnReferences() == null))
                             )&&((this.tables.getTable(i).getTableType() != ETableSource.subquery))
                     )
                     {
                         candidate++;
                         if (firstCandidate == -1) firstCandidate = i;
                         if (this.fireOnMetaDatabaseTableColumn(
                                    this.tables.getTable(i).getTableName().getServerString(),
                                    this.tables.getTable(i).getTableName().getDatabaseString(),
                                    this.tables.getTable(i).getTableName().getSchemaString(),
                                    this.tables.getTable(i).getName(),cr.toString())){
                                this.tables.getTable(i).getObjectNameReferences().addObjectName(cr);
                                isfound = true;
                                break;
                            }
                         else{
                             this.tables.getTable(i).getObjectNameReferences().addObjectName(cr);
                             if (this.tables.size() > 1){
                                 cr.setTableDetermined(false);
                             }
                             isfound = true;
                             break;
                         }
                     }
                  }
                 if ((!isfound) && (candidate == 1)){
                     this.tables.getTable(firstCandidate).getObjectNameReferences().addObjectName(cr);
                 }
             }
         }else if (ret == -1){
             TCustomSqlStatement lcParent = null;
             lcParent = this.getParentStmt();
            while ( lcParent != null) {
              ret = lcParent.tables.checkColumnReferenceInTables(cr);
              if (ret >= 0){
               lcParent.tables.getTable(ret).getObjectNameReferences().addObjectName(cr);
               break;
              }else{
                  lcParent = lcParent.getParentStmt();
              }
            } // while
         } //-1

    }

    /**
     * Found out is a non qualified column is a column in uplevel subquery table like this:
     * take ma_parkey for example: ma_parkey is not a physical column
     * 
        SELECT c_mandant
             , CASE WHEN EXISTS (SELECT 1
                                   FROM CDS_H_GRUPPE  GRP1
                                  WHERE GRP1.c_mandant = c_mandant
                                    AND GRP1.parkey1       = ma_parkey)
                      THEN 1
                  ELSE NULL
               END MA_ME
          FROM (SELECT c_mandant
                     , CASE WHEN funktionscode = 'U'
                              THEN parkey1
                          ELSE parkey2
                       END MA_PARKEY
                  FROM
                       CDS_H_GRUPPE
               )
     */
    public boolean checkNonQualifiedColumnReferenceInSubQueryOfUplevelStmt(TObjectName crf,boolean sameLevelOnly){
        boolean ret = false;

        TCustomSqlStatement lcParent = null;
        lcParent = this;//getParentStmt();
       while ( lcParent != null) {
           TTable lcTable;
             for (int i=0;i<lcParent.tables.size();i++){
                 lcTable = lcParent.tables.getTable(i);

                 if ((lcTable.getTableType() != ETableSource.subquery)) {continue;}

                 ret = isColumnNameInSelectList(crf.toString(),lcTable.subquery);
                if (ret) {break;}

             }
          if (ret) {break;}
           else{
              if (sameLevelOnly){
                  lcParent = null;
              }else{
                lcParent = lcParent.getParentStmt();
              }
          }
       } // while

        return ret;
    }

    private boolean isColumnNameInSelectList(String pColumn, TSelectSqlStatement pSelect){
        boolean ret = false;
        TResultColumn lcColumn;
        if (pSelect.isCombinedQuery()){
            ret = isColumnNameInSelectList(pColumn,pSelect.getLeftStmt());
            if (!ret){
                ret = isColumnNameInSelectList(pColumn,pSelect.getRightStmt());
            }
        }else{
            if (pSelect.getResultColumnList() != null){ //if it's a db2 value row, then pSelect.getResultColumnList() will be null 
                for(int j=0;j<pSelect.getResultColumnList().size();j++){
                   lcColumn = pSelect.getResultColumnList().getResultColumn(j);
                   if (lcColumn.getAliasClause() != null){
                       ret = pColumn.equalsIgnoreCase(lcColumn.getAliasClause().toString());
                   }
                   if (ret)  break;
                   ret = pColumn.equalsIgnoreCase(lcColumn.getExpr().toString());
                   if (ret)  break;
                }
            }
        }
        return ret;
    }

    public TCustomSqlStatement getTopStatement(){
        TCustomSqlStatement ret = this;
        while (ret.getParentStmt() != null){
            ret = ret.getParentStmt();
        }
        return ret;
    }


//    public String toScript(){
//        if (!isChanged()){
//            return this.toString();
//        }
//        return super.toScript();
//    }

}

class constantVisitor extends TParseTreeVisitor {
    private boolean inWhere = false,inExprList = false;
    public void preVisit(TWhereClause node){
        inWhere = true;
    }

    public void postVisit(TWhereClause node){
        inWhere = false;
    }

    public void preVisit(TExpression node){
        if (inWhere){
            switch (node.getExpressionType()){
                case list_t:
                    inExprList = true;
                    boolean isNumber = true;
                    if (node.getExprList().size() > 0){
                        // check the type of the constant in the expr list
                        TExpression expr = node.getExprList().getExpression(0);
                        if (expr.getExpressionType() == EExpressionType.simple_constant_t){
                            if (expr.getConstantOperand().getLiteralType() == ELiteralType.etString){
                                isNumber = false;
                            }
                        }
                    }

                    TSourceToken lcStartToken = node.getStartToken();
                    TSourceToken lcEndToken = node.getEndToken();
                    int tokenPos = 0;
                    if ((lcEndToken != null) && (lcStartToken != null)){
                        TSourceToken lcCurrentToken = lcStartToken;
                        while (lcCurrentToken != null){

                            if (lcCurrentToken.equals(lcEndToken)){
                                break;
                            }else{

                                if (tokenPos == 1){
                                    if (isNumber){
                                        lcCurrentToken.setTextWithBackup("999");
                                    }else{
                                        lcCurrentToken.setTextWithBackup("'placeholder_str'");
                                    }
                                }else if (tokenPos > 1){
                                    lcCurrentToken.tokenstatus = ETokenStatus.tsdeleted;
                                }

                                lcCurrentToken = lcCurrentToken.getNextTokenInChain();
                                tokenPos++;
                            }
                        }
                    }

                    break;
            }
        } // where
    }

    public void postVisit(TExpression node){
        if (inWhere){
            switch (node.getExpressionType()){
                case list_t:
                    inExprList = false;
                    break;
            }
        }
    }

    public void preVisit(TConstant node){
        if (inWhere&&(!inExprList)){
            switch (node.getLiteralType()){
                case etNumber:
                case etFloat:
                    node.getStartToken().setTextWithBackup("999");
                    break;
                case etString:
                    node.getStartToken().setTextWithBackup("'placeholder_str'");
                    break;
            }
        }
    }

    public void preVisit(TFunctionCall node){
        if (TBaseType.as_canonical_f_decrypt_replace_password){
            int i = TBaseType.searchCryptFunction(node.getFunctionName().toString());

            if (i>0){ // find this function
                if (node.getArgs().size() >= i){
                    TExpression secondArg = node.getArgs().getExpression(i-1);
                    if (secondArg.getExpressionType() == EExpressionType.simple_constant_t){
                        TConstant constant = secondArg.getConstantOperand();
                        constant.getValueToken().setTextWithBackup("'***'");
                        //System.out.println(node.toString()+":"+constant.toString());
                    }else if (secondArg.getExpressionType() == EExpressionType.simple_object_name_t){
                        TObjectName objectName = secondArg.getObjectOperand();
                        objectName.getStartToken().setTextWithBackup("'***'");
                        //System.out.println(node.toString()+":"+constant.toString());
                    }
                }
            }

        }
    }

    void processConstant(TConstant node){
        switch (node.getLiteralType()){
            case etNumber:
            case etFloat:
                node.getStartToken().setTextWithBackup("999");
                break;
            case etString:
                node.getStartToken().setTextWithBackup("'placeholder_str'");
                break;
        }
    }

}