package gudusoft.gsqlparser;


import gudusoft.gsqlparser.compiler.*;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.nodes.dax.TDaxFunction;
import gudusoft.gsqlparser.nodes.teradata.THashByClause;
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.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.OraclePseudoColumnUtil;
import gudusoft.gsqlparser.util.SQLUtil;
import gudusoft.gsqlparser.util.functionChecker;
import gudusoft.gsqlparser.util.keywordChecker;

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.Stack;
import java.util.TreeMap;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.nio.charset.StandardCharsets;

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

    private String sqlHash;

    public void setSqlHash(String sqlHash) {
        this.sqlHash = sqlHash;
    }

    /**
     * Returns a stable, vendor-aware hash of this statement's SQL text for
     * lineage grouping and statement identity.
     * <p>
     * Purpose:
     * <ul>
     *     <li>Provide a deterministic identifier for a statement that is
     *     insensitive to formatting (whitespace, comments, keyword case).</li>
     *     <li>Act as the first component of a recommended {@code statementKey}
     *     for lineage grouping: {@code statementKey = sqlHash + "#" + queryId}.</li>
     * </ul>
     * How it works:
     * <ul>
     *     <li>Builds a normalized textual representation of the statement by
     *     iterating the token chain between {@code getStartToken()} and
     *     {@code getEndToken()}.</li>
     *     <li>Normalization rules (Profile A by default): remove comments;
     *     collapse spacing deterministically; uppercase keywords; handle
     *     identifiers by the current vendor's case-sensitivity
     *     ({@link gudusoft.gsqlparser.sqlenv.TSQLEnv#columnCollationCaseSensitive}).
     *     For delimited identifiers (quoted identifiers), the quotes are
     *     removed via {@link gudusoft.gsqlparser.TBaseType#removeQuoteChar(String)}
     *     before case normalization. String literals are kept as-is.</li>
     *     <li>The hash input is prefixed by a normalization version and the
     *     current vendor, so future evolution of the normalizer will not break
     *     previously computed values: {@code normVersion + "\n" + vendor + "\n" + normalizedSql}.</li>
     *     <li>Hash function: SHA-256 (lowercase hex).</li>
     * </ul>
     * Usage:
     * <ul>
     *     <li>Call {@code getSqlHash(false)} for cached result or lazy
     *     calculation with Profile A (identity-safe) normalization.</li>
     *     <li>Call {@code getSqlHash(true)} to force recalculation (e.g., after
     *     mutating the underlying token chain).</li>
     *     <li>If you need a grouping-friendly variant (masking certain literal
     *     classes), use {@link #computeSqlHash(SqlNormalizationProfile, String)}
     *     with {@link SqlNormalizationProfile#GROUPING_FRIENDLY}.</li>
     * </ul>
     *
     * @param forceReCalculate if true, recompute the hash even if a cached
     *                         value exists
     * @return lowercase hex SHA-256 hash of the normalized SQL text
     */
    public String getSqlHash(boolean forceReCalculate) {
        if (sqlHash != null && !forceReCalculate) {
            return sqlHash;
        }
        // Default: Profile A (identity-safe)
        this.sqlHash = computeSqlHash(SqlNormalizationProfile.IDENTITY_SAFE, DEFAULT_SQLHASH_NORM_VERSION);
        return this.sqlHash;
    }

    /**
     * Backward compatible overload equivalent to {@code getSqlHash(false)}.
     */
    public String getSqlHash() {
        return getSqlHash(false);
    }

    /**
     * Normalization profiles for SQL hashing.
     * <ul>
     *     <li>IDENTITY_SAFE: Remove comments and normalize spacing/case only.
     *     Literal values are preserved. Operator synonyms are minimally
     *     unified (e.g., {@code !=} to {@code <>}).</li>
     *     <li>GROUPING_FRIENDLY: In addition to IDENTITY_SAFE, date/time
     *     string literals are normalized to {@code '1970-01-01'} to aid
     *     grouping across different runs where only timestamps vary.</li>
     * </ul>
     */
    public enum SqlNormalizationProfile {
        IDENTITY_SAFE,
        GROUPING_FRIENDLY
    }

    private static final String DEFAULT_SQLHASH_NORM_VERSION = "sqlHash.norm.v1";

    /**
     * Compute a SQL hash using the given normalization profile and version.
     * See {@link #getSqlHash(boolean)} for details.
     *
     * @param profile     normalization profile
     * @param normVersion version tag embedded in the hash input
     * @return lowercase hex SHA-256 hash
     */
    public String computeSqlHash(SqlNormalizationProfile profile, String normVersion) {
        String normalized = toNormalizedSql(profile);
        String input = normVersion + "\n" + String.valueOf(this.dbvendor) + "\n" + normalized;
        return sha256Hex(input);
    }

    /**
     * Produce a normalized textual representation of this statement according
     * to the supplied profile. The method is non-mutating: it does not alter
     * token texts or statuses permanently.
     *
     * Rules applied:
     * - Remove comments.
     * - Remove trailing semicolon.
     * - Deterministic spacing around punctuation/operators.
     * - Uppercase keywords, keep string literals as-is.
     * - Identifiers: if the vendor's column collation is case sensitive, keep
     *   identifier case; otherwise uppercase. For quoted identifiers, remove
     *   quoting via {@link TBaseType#removeQuoteChar(String)} prior to case
     *   handling.
     * - Minimal operator unification: {@code "!="} becomes {@code "<>"}.
     * - Profile B adds date/time string masking: {@code '1970-01-01'}.
     *
     * @param profile normalization profile
     * @return normalized SQL string
     */
    public String toNormalizedSql(SqlNormalizationProfile profile) {
        TSourceToken start = getStartToken();
        TSourceToken end = getEndToken();
        if (start == null || end == null) {
            return String.valueOf(this.toString());
        }

        boolean idCaseSensitive = Boolean.TRUE.equals(TSQLEnv.columnCollationCaseSensitive.get(this.dbvendor));

        StringBuilder sb = new StringBuilder(256);
        TokenClass prevClass = null;
        char lastAppended = '\0';

        // We may need to skip a single trailing semicolon that is the end token
        // Detect once to simplify checks in the loop
        boolean endIsSemicolon = (end.toString().equals(";"));

        TSourceToken cur = start;
        while (cur != null) {
            // Skip tokens that should not contribute
            if (isComment(cur)) {
                // skip comments
            } else if (cur == end && endIsSemicolon) {
                // drop trailing semicolon
            } else if (cur.tokenstatus == ETokenStatus.tsdeleted || cur.tokenstatus == ETokenStatus.tsignorebyyacc) {
                // ignore deleted/ignored tokens
            } else {
                String text = normalizeTokenText(cur, idCaseSensitive, profile);
                if (text != null && !text.isEmpty()) {
                    TokenClass clazz = classify(cur, text);
                    if (shouldAddSpaceBefore(prevClass, clazz, lastAppended)) {
                        sb.append(' ');
                        lastAppended = ' ';
                    }
                    sb.append(text);
                    lastAppended = text.charAt(text.length() - 1);
                    prevClass = clazz;
                }
            }

            if (cur == end) {
                break;
            } else {
                cur = cur.getNextTokenInChain();
            }
        }

        // Trim any trailing single space for cleanliness
        int len = sb.length();
        if (len > 0 && sb.charAt(len - 1) == ' ') {
            sb.setLength(len - 1);
        }
        return sb.toString();
    }

    private static boolean isComment(TSourceToken t) {
        return t.tokentype == ETokenType.ttsimplecomment || t.tokentype == ETokenType.ttbracketedcomment;
    }

    private enum TokenClass { WORD, OP, DOT, COMMA, PAREN_LEFT, PAREN_RIGHT, SEMICOLON, OTHER }

    private static TokenClass classify(TSourceToken t, String normalizedText) {
        switch (t.tokentype) {
            case ttidentifier:
            case ttdqstring:
            case ttdbstring:
            case ttbrstring:
            case ttnumber:
            case ttsqstring:
            case ttkeyword:
            case ttnonreservedkeyword:
            case ttbindvar:
            case ttsqlvar:
            case ttsubstitutionvar:
                return TokenClass.WORD;
            case ttperiod:
                return TokenClass.DOT;
            case ttcomma:
                return TokenClass.COMMA;
            case ttleftparenthesis:
                return TokenClass.PAREN_LEFT;
            case ttrightparenthesis:
                return TokenClass.PAREN_RIGHT;
            case ttsemicolon:
            case ttsemicolon2:
            case ttsemicolon3:
                return TokenClass.SEMICOLON;
            case ttequals:
            case ttplussign:
            case ttminussign:
            case ttasterisk:
            case ttslash:
            case ttgreaterthan:
            case ttlessthan:
            case ttsinglecharoperator:
            case ttmulticharoperator:
            case ttconcatenationop:
                return TokenClass.OP;
            default:
                break;
        }
        // Heuristic: treat UNKNOWN single-char punctuation as operator
        if (normalizedText.length() == 1 && !Character.isLetterOrDigit(normalizedText.charAt(0))) {
            return TokenClass.OP;
        }
        return TokenClass.OTHER;
    }

    private static boolean shouldAddSpaceBefore(TokenClass prev, TokenClass curr, char lastAppended) {
        if (prev == null) return false;
        if (lastAppended == '\0') return false;

        // No space rules
        if (curr == TokenClass.DOT || curr == TokenClass.COMMA || curr == TokenClass.PAREN_RIGHT) return false;
        if (prev == TokenClass.DOT || prev == TokenClass.PAREN_LEFT) return false;

        // Space around operators and between words
        if (prev == TokenClass.OP && (curr == TokenClass.WORD || curr == TokenClass.PAREN_LEFT)) return true;
        if ((prev == TokenClass.WORD || prev == TokenClass.PAREN_RIGHT) && (curr == TokenClass.OP || curr == TokenClass.WORD)) return true;

        // After comma ensure a space before next word
        if (prev == TokenClass.COMMA && (curr == TokenClass.WORD || curr == TokenClass.PAREN_LEFT)) return true;

        // Default: no space
        return false;
    }

    private String normalizeTokenText(TSourceToken t, boolean idCaseSensitive, SqlNormalizationProfile profile) {
        String s = t.toString();

        // Minimal operator unification: != => <>
        if (t.tokentype == ETokenType.ttmulticharoperator || t.tokentype == ETokenType.ttsinglecharoperator ||
            t.tokentype == ETokenType.ttgreaterthan || t.tokentype == ETokenType.ttlessthan) {
            if ("!=".equals(s)) {
                return "<>";
            }
            return s;
        }

        switch (t.tokentype) {
            case ttsimplecomment:
            case ttbracketedcomment:
                return null; // removed
            case ttkeyword:
            case ttnonreservedkeyword:
                return s.toUpperCase();
            case ttidentifier: {
                // regular identifier
                return idCaseSensitive ? s : s.toUpperCase();
            }
            case ttdqstring:
            case ttdbstring:
            case ttbrstring: {
                // delimited/quoted identifier -> remove quotes, then apply case rule
                String unquoted = TBaseType.removeQuoteChar(s);
                return idCaseSensitive ? unquoted : unquoted.toUpperCase();
            }
            case ttsqstring: {
                if (profile == SqlNormalizationProfile.GROUPING_FRIENDLY && looksLikeDateOrTimestampLiteral(s)) {
                    return "'1970-01-01'";
                }
                return s; // keep string literal as-is
            }
            default:
                return s;
        }
    }

    private static boolean looksLikeDateOrTimestampLiteral(String s) {
        // Very lightweight check for common SQL string date/timestamp formats, e.g. '2025-09-14' or '2025-09-14 12:34:56'
        // Input includes surrounding quotes per token text.
        if (s == null || s.length() < 2) return false;
        if (!(s.charAt(0) == '\'' && s.charAt(s.length() - 1) == '\'')) return false;
        String inner = s.substring(1, s.length() - 1).trim();
        // yyyy-mm-dd or yyyy-mm-dd hh:mm[:ss[.fff]]
        if (inner.matches("\\d{4}-\\d{2}-\\d{2}")) return true;
        if (inner.matches("\\d{4}-\\d{2}-\\d{2}[ T]\\d{2}:\\d{2}(:\\d{2}(\\.\\d{1,9})?)?")) return true;
        return false;
    }

    private static String sha256Hex(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            byte[] out = md.digest(input.getBytes(StandardCharsets.UTF_8));
            char[] hex = new char[out.length * 2];
            final char[] digits = "0123456789abcdef".toCharArray();
            for (int i = 0, j = 0; i < out.length; i++) {
                int b = out[i] & 0xFF;
                hex[j++] = digits[(b >>> 4) & 0x0F];
                hex[j++] = digits[b & 0x0F];
            }
            return new String(hex);
        } catch (NoSuchAlgorithmException e) {
            // Should never happen on a standard JVM
            throw new RuntimeException("SHA-256 not available", e);
        }
    }

    private String queryId;

    public void setQueryId(String queryId) {
        this.queryId = queryId;
    }

    /**
     * Retrieves the unique and stable identifier for this SQL statement.
     * <p>
     * The queryId provides a reliable way to reference any statement, including subqueries,
     * within a parsed SQL script. It is generated hierarchically based on the statement's
     * position within the Abstract Syntax Tree (AST), ensuring that the ID is reproducible
     * across identical SQL inputs.
     * <p>
     * <b>ID Format:</b>
     * <ul>
     *     <li>A top-level statement has an ID like {@code "stmt_0_select"}, where {@code 0} is the index
     *         of the statement in the script and {@code select} is the statement type.</li>
     *     <li>A nested statement will have a path-like ID that includes its parent's ID. For example,
     *         an {@code INSERT} statement containing a {@code SELECT} subquery might have an ID for the
     *         subquery like {@code "stmt_0_insert#stmt_1_select"}.</li>
     * </ul>
     * This identifier is particularly useful for tasks like data lineage analysis, where tracking
     * the origin and transformation of data through various statements is required.
     *
     * @return The unique query identifier string for this statement, or {@code null} if it has not been set.
     */
    public String getQueryId() {
        return queryId;
    }

    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;
    }

    public TSourceToken semicolonended;
    public 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)||(dbvendor == EDbVendor.dbvsybasease || dbvendor == EDbVendor.dbvsqlanywhere || dbvendor == EDbVendor.dbvsybaseiq)){
            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)
                ||(this.sqlstatementtype == ESqlStatementType.sstcreatetrigger)
                ||(this.sqlstatementtype == ESqlStatementType.sstplsql_createtypebody)
                ;
    }

    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.dbvendor == EDbVendor.dbvoceanbase))&&(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 dbvdameng:
                                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 dbvclickhouse:
                            case dbvmysql:
                            case dbvoceanbase:
                            case dbvsparksql:
                            case dbvdatabricks:
                                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 == TBaseType.rrw_with)
                                        ||(st.tokencode == TBaseType.rrw_create)
                                        ||(st.tokencode == '(')
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                            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 == TBaseType.rrw_with)
                                        ||(st.tokencode == TBaseType.rrw_create)
                                        ||(st.tokencode == TBaseType.rrw_execute)
                                )
                                {
                                    isFoundStopToken = true;
                                }else if (st.tokencode == '('){
                                    // Check if this '(' starts EXPLAIN options like (COSTS FALSE)
                                    // or a subquery like (SELECT ...)
                                    TSourceToken nextSolid = sourcetokenlist.nextsolidtoken(k, 1, false);
                                    if (nextSolid != null
                                            && nextSolid.tokencode != TBaseType.rrw_select
                                            && nextSolid.tokencode != TBaseType.rrw_insert
                                            && nextSolid.tokencode != TBaseType.rrw_update
                                            && nextSolid.tokencode != TBaseType.rrw_delete
                                            && nextSolid.tokencode != TBaseType.rrw_with){
                                        // Options list: skip past closing ')'
                                        int depth = 1;
                                        st.tokencode = TBaseType.sqlpluscmd;
                                        for (k = k + 1; k < sourcetokenlist.size() && depth > 0; k++){
                                            TSourceToken inner = sourcetokenlist.get(k);
                                            if (inner.tokencode == '(') depth++;
                                            else if (inner.tokencode == ')') depth--;
                                            inner.tokencode = TBaseType.sqlpluscmd;
                                        }
                                        k--; // adjust for loop increment
                                    }else{
                                        isFoundStopToken = true;
                                    }
                                }
                                break;
                            case dbvduckdb:
                                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 == TBaseType.rrw_with)
                                        ||(st.tokencode == TBaseType.rrw_create)
                                        ||(st.tokencode == TBaseType.rrw_alter)
                                        ||(st.tokencode == TBaseType.rrw_merge)
                                        ||(st.tokencode == '(')
                                )
                                {
                                    isFoundStopToken = true;
                                }
                                break;
                            case dbvflink:
                                // Flink EXPLAIN can have: EXPLAIN (options) stmt or EXPLAIN options stmt
                                // Don't stop at '(' because it might be part of EXPLAIN (ESTIMATED_COST, ...)
                                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 == TBaseType.rrw_with)
                                        ||(st.tokencode == TBaseType.rrw_create)
                                )
                                {
                                    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) {
            // EXPLAIN FOR CONNECTION has no inner statement to parse - this is valid
            if (this.sqlstatementtype == ESqlStatementType.sstExplain && isExplainForConnection()) {
                ret = 0;
            } else {
                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;
    }

    private boolean isExplainForConnection() {
        if (sourcetokenlist == null) return false;
        for (int i = 0; i < sourcetokenlist.size() - 1; i++) {
            TSourceToken st = sourcetokenlist.get(i);
            if (st.toString().equalsIgnoreCase("for")) {
                TSourceToken next = sourcetokenlist.nextsolidtoken(i, 1, false);
                if (next != null && next.toString().equalsIgnoreCase("connection")) {
                    return true;
                }
            }
        }
        return false;
    }

    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());
            psql.stmtScope.incrementCurrentStmtIndex();
            this.queryId = String.format("%s#stmt_%d_%s", psql.getQueryId(),psql.stmtScope.getCurrentStmtIndex(), this.sqlstatementtype);
            stmtScope = new TStmtScope(psql.stmtScope,this);
            // psql.statements.add(this);
        }else{
            stmtScope = new TStmtScope(this);

            // global scope
            this.getFrameStack().peek().getScope().incrementCurrentStmtIndex();
            this.queryId = String.format("stmt_%d_%s",this.getFrameStack().peek().getScope().getCurrentStmtIndex(), this.sqlstatementtype);
        }

        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.setArrayIndexAlias(pfromTable.getArrayIndexAlias());
        lcTable.setSuperUnpivot(pfromTable.isSuperUnpivot());
        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());
               // Teradata table function HASH BY and LOCAL ORDER BY clauses
               lcTable.setHashByClause(pfromTable.getHashByClause());
               lcTable.setLocalOrderBy(pfromTable.getLocalOrderBy());
               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:{
               // Set the TD_UNPIVOT output table before doParse so that VALUE_COLUMNS and
               // UNPIVOT_COLUMN can be linked to it (they are output columns of TD_UNPIVOT)
               pfromTable.getTdUnpivot().setTdUnpivotOutputTable(lcTable);
               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;

            // strip quote characters so a quoted declaration ("P_DATE" IN NUMBER)
            // still matches an unquoted reference P_DATE (MantisBT 4504)
            if (TBaseType.getTextWithoutQuoted(cr.toString())
                    .compareToIgnoreCase(TBaseType.getTextWithoutQuoted(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 (TBaseType.getTextWithoutQuoted(cr.toString()).compareToIgnoreCase(
                            TBaseType.getTextWithoutQuoted(qualifiedName.toString()) + '.'
                                    + TBaseType.getTextWithoutQuoted(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;
        // ClickHouse expression-CTE alias resolved by an outer scope during
        // the parent-walk: the reference is a scalar symbol — guessing it onto
        // this scope's first table would fabricate lineage (and did, when
        // metadata ruled the inner table out and the outer WITH alias won).
        if ((pColumn.getExpressionCteRef() != null) && (pColumn.getSourceTable() == null)){
            return true;
        }
        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 (lcSql.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 = lcSql.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);
                    pColumn.setOwnStmt(this);
                    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.getAstext(), 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);
                pColumn.setOwnStmt(this);
                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.getAstext(), 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;
        }
    }


    private TTable findInsertedOrDeleteTable(TTable table) {
        if (table == null) return null;
        
        // Check if this table is 'inserted' by examining both name and toString()
        // The inserted table might have different representations
        String tableName = table.getName();
        String tableString = table.toString();
        
        if ("inserted".equalsIgnoreCase(tableName) || 
            "inserted".equalsIgnoreCase(tableString) ||
            "deleted".equalsIgnoreCase(tableName) ||
            "deleted".equalsIgnoreCase(tableString)) {
            return table;
        }
        
        // If this is a join table, recursively check its components
        if (table.getTableType() == ETableSource.join && table.getJoinExpr() != null) {
            TJoinExpr joinExpr = table.getJoinExpr();
            
            // Check left side recursively
            TTable leftResult = findInsertedOrDeleteTable(joinExpr.getLeftTable());
            if (leftResult != null) {
                return leftResult;
            }
            
            // Check right side recursively  
            TTable rightResult = findInsertedOrDeleteTable(joinExpr.getRightTable());
            if (rightResult != null) {
                return rightResult;
            }
        }
        
        return null;
    }
   

    boolean isSQLServerInsertedDelete(TObjectName pColumn){
        if (dbvendor != EDbVendor.dbvmssql) return false;

        // only process sql server inserted delete column, if not then return false
       // if (!((pColumn.toString().toUpperCase().startsWith("INSERTED"))||(pColumn.toString().toUpperCase().startsWith("DELETED")))) return false;
        if (!((pColumn.getStartToken().tokencode == TBaseType.rrw_sqlserver_INSERTED )
                    ||(pColumn.getStartToken().tokencode == TBaseType.rrw_sqlserver_DELETED ))) return false;

        // we need to get the target table in this statement's from clause which can be complex join, so we need to iterate all tables in this statement's from clause
        // to find the target table in the literal as 'inserted' or 'deleted'.

        TTable lcTargetTable = null;
        for(TTable table : this.getRelations()){
            lcTargetTable = findInsertedOrDeleteTable(table);
            if (lcTargetTable != null){
                break;
            }
        }

        if (lcTargetTable == null) return false;


        if (lcTargetTable.getLinkTable() != null){
            lcTargetTable.getLinkTable().getLinkedColumns().addObjectName(pColumn);
            pColumn.setSourceTable(lcTargetTable.getLinkTable());

            pColumn.setResolveStatus(TBaseType.RESOLVED_AND_FOUND); // 避免在 // TAttributeResolver 中再次进行处理，关联到其他 table

        }else{
            lcTargetTable.getLinkedColumns().addObjectName(pColumn);
            pColumn.setSourceTable(lcTargetTable);
            pColumn.setResolveStatus(TBaseType.RESOLVED_AND_FOUND); // 避免在 // TAttributeResolver 中再次进行处理，关联到其他 table
        }
        return true;

    }

    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;
    }

    /**
     * 将列引用解析并绑定到其来源（表、子查询、CTE、表函数、OPENQUERY/UNNEST 等）。
     *
     * 功能概述:
     * 1) 针对 DAX 语法直接走 DAX 分支。
     * 2) 已绑定或标记“延迟到列解析器”的列直接返回。
     * 3) 设定列所在语法位置，并校验列名/保留字（含 MySQL true/false/default、内置函数等）。
     * 4) 处理厂商伪表/特殊前缀（Oracle :new/:old；SQL Server INSERTED/DELETED）。
     * 5) Insert All/VALUES 场景：优先在子查询结果集中/变量或过程参数中匹配。
     * 6) 在当前语句的 FROM 表集合中查找并建立绑定：
     *    - 限定列 table.col：按别名/表名匹配；对子查询/CTE/OPENQUERY 进一步在结果集中定位源列；
     *      命中后写入 linkedColumns，必要时将 TableToken 标记为 subquery_alias。
     *    - 非限定列 col：
     *      a. 先尝试同层 SELECT 列别名（支持 LATERAL 语义且位置在别名之后）；
     *      b. 处理通配符“*”：收集所有来源表；
     *      c. 基础表通过元数据回调 fireOnMetaDatabaseTableColumn 校验；未命中则记录候选；
     *      d. 子查询/CTE/函数/UNNEST/PIVOT 分别按各自规则匹配。
     * 7) 命中后将列加入表的 linkedColumns 并设置 sourceTable/sourceColumn，必要时维持 isContinue 以继续匹配“*”。
     * 8) 若未命中：尝试变量/参数；再按条件（语句类型/位置/是否限定等）向上一层语句递归查找（维护 searchLevel）。
     * 9) 仍未命中：在顶层（searchLevel==0）按“候选唯一/或首表”兜底策略 {@link #linkToFirstTable(TObjectName, int)}。
     *
     * 参数:
     * @param pColumn   需要绑定的列名对象（方法会更新其 location、sourceTable、sourceColumn 等）
     * @param pLocation 列出现的语法位置（如 selectList、where、insertValues 等）
     *
     * 返回值:
     * @return 成功绑定到某个来源返回 true；未能绑定或被识别为变量/保留字等返回 false
     *
     * 厂商兼容:
     * - Oracle: 处理 :new/:old，Insert All 的 values 子句源自子查询的匹配
     * - SQL Server: 处理 INSERTED/DELETED 伪表
     * - MySQL: 对保留字/布尔字面量/内置函数名的特殊判断
     * - DAX: 委托 {@link #linkColumnToTableDax(TObjectName, ESqlClause)}
     *
     * 副作用:
     * - 更新 pColumn 的 location/searchLevel/sourceTable/sourceColumn/validate 状态
     * - 向命中的表写入 linkedColumns 或向别名列写入 targetColumns
     * - 对“*”列填充 sourceTableList；对子查询命中时可能将 TableToken 标为 subquery_alias
     * - 记录候选表数量并填充 pColumn.candidateTables，用于后续兜底
     *
     * 复杂度与顺序:
     * - 优先使用同层信息（别名/元数据/子查询结果），再逐层向外查找；避免无谓的上层搜索
     *
     * 注意:
     * - 本方法完成“旧算法”的快速联接，新的解析/消歧逻辑在解析器（如 TStmtScope/TAttributeResolver）中继续处理
     */
    /**
     * ClickHouse only: the expression-CTE ({@code WITH <expr> AS ident}) whose
     * alias matches the unqualified column reference, or null. The search
     * covers this statement's own WITH list and ascends ONLY through
     * set-operation membership (a compound query's WITH list is shared with
     * its members). It must NOT walk arbitrary lexical parents: ClickHouse
     * resolves closest-scope-first, so an outer alias may only win after this
     * scope's own tables failed — which the existing parent-walk recursion in
     * linkColumnToTable already provides (the recursion re-enters this check
     * at the parent's own level). A reference located inside a CTE's own
     * defining expression never matches that CTE (it is a column named like
     * the alias, not a self-reference).
     */
    private TCTE findExpressionCteInList(TCTEList lcCteList, TObjectName pColumn){
        if (lcCteList == null) return null;
        for(int i=0;i<lcCteList.size();i++){
            TCTE lcCte = lcCteList.getCTE(i);
            if ((lcCte.getExpression() == null) || (lcCte.getTableName() == null)) continue;
            if ((pColumn.getStartToken() != null)
                    && (lcCte.getExpression().getStartToken() != null)
                    && (lcCte.getExpression().getEndToken() != null)
                    && (pColumn.getStartToken().posinlist >= lcCte.getExpression().getStartToken().posinlist)
                    && (pColumn.getStartToken().posinlist <= lcCte.getExpression().getEndToken().posinlist)){
                continue;
            }
            if (SQLUtil.sameName(dbvendor, ESQLDataObjectType.dotColumn,
                    pColumn.toString(), lcCte.getTableName().toString())){
                return lcCte;
            }
        }
        return null;
    }

    private TCTE findExpressionCteForAlias(TObjectName pColumn){
        TCustomSqlStatement stmt = this;
        while (stmt != null){
            TCTE found = findExpressionCteInList(stmt.getCteList(), pColumn);
            if (found != null) return found;
            if (!((stmt instanceof TSelectSqlStatement)
                    && ((TSelectSqlStatement)stmt).isChildOfCombinedQuery())) break;
            stmt = stmt.parentStmt;
        }
        return null;
    }

    /**
     * ClickHouse only: like {@link #findExpressionCteForAlias} but across every
     * enclosing statement. Used ONLY where the local scope has already failed
     * to PROVE the column (e.g. an unverifiable table-function column) — a
     * proven outer alias then outranks the local guess.
     */
    private TCTE findExpressionCteForAliasInAnyScope(TObjectName pColumn){
        for(TCustomSqlStatement stmt = this; stmt != null; stmt = stmt.parentStmt){
            TCTE found = findExpressionCteInList(stmt.getCteList(), pColumn);
            if (found != null) return found;
        }
        return null;
    }

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

        // Skip alias definition columns - they define column names in alias clauses, not column references
        // Example: In "AS x (numbers, animals)", numbers and animals are column_alias type
        if (pColumn.getDbObjectType() == EDbObjectType.column_alias) {
            return true;
        }

        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)
                        &&(pColumn.getStartToken().tokencode != TBaseType.rrw_on)&&(!(pColumn.getStartToken().toString().equalsIgnoreCase("on")))
                   ) {
                        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;
        }

        // ClickHouse expression-CTE alias: WITH <expr> AS ident makes ident a
        // scalar symbol of the consuming query, not a column of its FROM
        // tables (default ClickHouse semantics: the alias shadows a
        // same-named table column). Binding it to a table would fabricate
        // lineage; resolve it to the CTE instead, before any table search.
        if ((dbvendor == EDbVendor.dbvclickhouse) && (!pColumn.isQualified())){
            TCTE lcExprCte = findExpressionCteForAlias(pColumn);
            if (lcExprCte != null){
                pColumn.setExpressionCteRef(lcExprCte);
                pColumn.setResolveStatus(TBaseType.RESOLVED_AND_FOUND);
                return false;
            }
        }

        boolean foundInMetaData = false;

        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.
                // The binding rule itself lives in TResultColumnList.findLateralAliasDefinition()
                // so that this path and resolver2's handleSelectListAliasResolution() can never
                // disagree about what counts as an alias reference (MantisBT 4659).
                if((!lcResult)&& ((!pColumn.isQualified()) && (this instanceof TSelectSqlStatement)&&(getResultColumnList() !=null))){
                    TResultColumn lcField = getResultColumnList().findLateralAliasDefinition(dbvendor, pColumn);
                    if (lcField != null){
                        pColumn.setSourceColumn(lcField);
                        lcField.getTargetColumns().addObjectName(pColumn);
                        pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_COLUMN_ALIAS_IN_OLD_ALGORITHM);
                        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{
                        foundInMetaData = true;
                        isContinue = false;
                    }

                }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){
                                int lcInTableFunction = lcTable.getFuncCall().isColumnInThisTableFunction(this.getSqlEnv(),this.dbvendor,pColumn);
                                lcResult = ( lcInTableFunction != TBaseType.COLUMN_IN_TABEL_FUNCTION_NO);
                                // ClickHouse: an unverifiable table-function column
                                // (NOTSURE) must not outrank an expression-CTE alias
                                // visible in an enclosing scope — the alias is proven,
                                // the guessed column is not. Leaving it unmatched lets
                                // the parent-walk resolve the alias.
                                if (lcResult && (lcInTableFunction == TBaseType.COLUMN_IN_TABEL_FUNCTION_NOTSURE)
                                                && (dbvendor == EDbVendor.dbvclickhouse) && (!pColumn.isQualified())
                                                && (findExpressionCteForAliasInAnyScope(pColumn) != null)){
                                        lcResult = false;
                                }
                        }
                        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());
                    if (lcResult){
                            foundInMetaData = true;
                            isContinue = false;
                    }
                }else if (lcTable.getTableType() == ETableSource.unnest){
                    for(TObjectName objectName:lcTable.getLinkedColumns()){
                        if (SQLUtil.compareIdentifier(this.dbvendor, ESQLDataObjectType.dotColumn, objectName.toString(), 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);
                // A real table binding in this scope outranks an alias mark a
                // wider-scope pre-pass may have left (ClickHouse closest-scope).
                pColumn.setExpressionCteRef(null);
               // pColumn.setValidate_column_status(TBaseType.COLUMN_LINKED_TO_TABLE_IN_OLD_ALGORITHM);
                if (!isContinue) break;
            }
        }

        if ((lcResult) && (foundInMetaData)) return true;

        // 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
     */
    /**
     * True when {@code cr} is an Oracle pseudocolumn rather than a reference to
     * a column of a table, judged only on what the statement itself proves.
     *
     * <p>The name list lives in {@link OraclePseudoColumnUtil}; this method
     * supplies the statement context that decides the non-reserved names.
     * Nothing here consults a catalog, so names that are indistinguishable from
     * a real column without one - {@code ORA_ROWSCN}, {@code OBJECT_ID},
     * {@code OBJECT_VALUE}, {@code XMLDATA}, {@code COLUMN_VALUE} - are
     * deliberately not claimed. {@code OBJECT_ID} in particular is a real
     * column of {@code ALL_OBJECTS}, and claiming it would silently drop that
     * lineage.</p>
     *
     * @param cr the column reference being linked
     * @return true if {@code cr} is provably a pseudocolumn
     */
    protected boolean isOraclePseudoColumnReference(TObjectName cr){
        if (dbvendor != EDbVendor.dbvoracle) return false;
        if (cr == null) return false;
        // getColumnNameOnly() is the raw text of the LAST segment, quotes
        // included, so a quoted "ROWID" simply fails to match the name list -
        // which is the correct outcome, since "ROWID" and ROWID are different
        // names in Oracle. Do NOT add a getQuoteType() guard here: that method
        // reports the quote state of the FIRST segment, so it would reject
        // "seq".NEXTVAL, whose terminal segment is unquoted and really is the
        // sequence pseudocolumn.
        String nameOnly = cr.getColumnNameOnly();
        if (nameOnly == null || nameOnly.length() == 0) return false;

        // Reserved words with no owning table (ROWNUM, LEVEL). Oracle rejects
        // an unquoted column of these names, so no qualifier or clause can make
        // this a real column reference. ROWID is deliberately NOT here: it is
        // scoped to one table, keeps its attribution, and is retyped in
        // TObjectName.setSourceTable() instead.
        if (OraclePseudoColumnUtil.isDetachedReservedPseudoColumn(nameOnly)) return true;

        // CONNECT_BY_ISLEAF / CONNECT_BY_ISCYCLE are not reserved. They are
        // pseudocolumns only inside a hierarchical query, and only unqualified:
        // Oracle has no table to qualify them with, so t.CONNECT_BY_ISLEAF is a
        // reference to a column somebody created called CONNECT_BY_ISLEAF.
        if (OraclePseudoColumnUtil.isConnectByPseudoColumn(nameOnly)
                && cr.getTableToken() == null
                && hasHierarchicalQueryClause()) return true;

        // VERSIONS_* are only pseudocolumns when the table they come from
        // carries a flashback VERSIONS clause.
        if (OraclePseudoColumnUtil.isVersionsPseudoColumn(nameOnly)
                && hasFlashbackVersionsClause(cr.getTableString())) return true;

        return false;
    }

    /**
     * True when {@code cr} is {@code [schema.]sequence.NEXTVAL} or
     * {@code [schema.]sequence.CURRVAL}.
     *
     * <p>Neither name is reserved, so this needs the qualifier to be something
     * other than a table or alias in scope: {@code x.CURRVAL} where {@code x}
     * is a table alias is an ordinary reference to a column named
     * {@code CURRVAL}, and an unqualified {@code CURRVAL} likewise.</p>
     *
     * <p>Kept separate from {@link #isOraclePseudoColumnReference} because the
     * two get different treatment - see the call site.</p>
     *
     * @param cr the column reference being linked
     * @return true if {@code cr} is a sequence pseudocolumn
     */
    protected boolean isOracleSequencePseudoColumnReference(TObjectName cr){
        if (dbvendor != EDbVendor.dbvoracle) return false;
        if (cr == null) return false;
        // See isOraclePseudoColumnReference(): quote state is segment-specific
        // and already carried by getColumnNameOnly(); getQuoteType() reads the
        // wrong segment for a qualified name.
        String nameOnly = cr.getColumnNameOnly();
        if (nameOnly == null || nameOnly.length() == 0) return false;
        return OraclePseudoColumnUtil.isSequencePseudoColumn(nameOnly)
                && cr.getTableToken() != null
                && !isTableOrAliasInScope(cr.getTableString());
    }

    /**
     * True when this statement (or the statement it belongs to) carries an
     * Oracle {@code CONNECT BY}.
     */
    private boolean hasHierarchicalQueryClause(){
        // Deliberately NOT walking up to the parent statement. CONNECT BY
        // pseudocolumns belong to the query that carries the clause; they are
        // not visible to a subquery nested inside it. Walking up would claim
        // CONNECT_BY_ISLEAF in
        //   SELECT (SELECT CONNECT_BY_ISLEAF FROM other) FROM t CONNECT BY ...
        // where it is an ordinary column of "other", and drop that edge.
        if (this instanceof TSelectSqlStatement){
            return ((TSelectSqlStatement)this).getHierarchicalClause() != null;
        }
        return false;
    }

    /**
     * True when any table in scope carries a flashback {@code VERSIONS BETWEEN}
     * clause, which is what makes the {@code VERSIONS_*} pseudocolumns
     * available.
     */
    private boolean hasFlashbackVersionsClause(String qualifier){
        // Same scoping rule as hasHierarchicalQueryClause(): the VERSIONS_*
        // pseudocolumns come from a table in THIS statement's FROM clause, so
        // no parent-chain walk. An inner subquery of a version query does not
        // inherit them.
        if (tables == null) return false;
        // When the reference names a table, only THAT table's clause counts.
        // In "FROM x VERSIONS BETWEEN ... , y", y.VERSIONS_XID is an ordinary
        // column of y - y is not being version-queried.
        if (qualifier != null && qualifier.length() > 0){
            int idx = tables.searchTableByNameOrAlias(qualifier);
            if (idx < 0) return false;
            TFlashback flashback = tables.getTable(idx).getFlashback();
            return flashback != null && flashback.isVersionsQuery();
        }
        for (int i = 0; i < tables.size(); i++){
            TFlashback flashback = tables.getTable(i).getFlashback();
            if (flashback != null && flashback.isVersionsQuery()) return true;
        }
        return false;
    }

    /**
     * True when {@code name} matches a table name or alias visible to this
     * statement. Used to tell {@code sequence.CURRVAL} from
     * {@code tablealias.CURRVAL}.
     */
    private boolean isTableOrAliasInScope(String name){
        if (name == null || name.length() == 0) return false;
        TCustomSqlStatement stmt = this;
        while (stmt != null){
            if (stmt.tables != null && stmt.tables.searchTableByNameOrAlias(name) >= 0) return true;
            stmt = stmt.getParentStmt();
        }
        return false;
    }

    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)||(this.dbvendor == EDbVendor.dbvsybasease || this.dbvendor == EDbVendor.dbvsqlanywhere || this.dbvendor == EDbVendor.dbvsybaseiq)){
            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;
                    case dbvsybasease:
                        // Shares dbvsybase's keyword DATA file (it is the ASE
                        // reserved-word list); independent case block per HC-5.
                        reservedKeyword = keywordChecker.isKeyword(cr.getPartToken().toString(), EDbVendor.dbvsybase, "15.7", true);
                        break;
                    case dbvsqlanywhere:
                        // Same shared keyword DATA file; independent case per HC-5.
                        reservedKeyword = keywordChecker.isKeyword(cr.getPartToken().toString(), EDbVendor.dbvsybase, "15.7", true);
                        break;
                    case dbvsybaseiq:
                        // Same again; independent case per HC-5.
                        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;
            }
            // The comparisons above are against the whole rendered text, so they
            // only ever fire for a bare, unqualified name. Oracle documents 18
            // pseudocolumns and most of them reach here qualified (X.ROWID,
            // SEQ.CURRVAL) or under a spelling the list never had. Left alone
            // they fall through to checkColumnReferenceInTables() below and get
            // bound to a table in the FROM clause, which asserts that e.g.
            // ORA_ROWSCN is a column of that table (Mantis #4675).
            if (isOracleSequencePseudoColumnReference(cr)){
                // [schema.]sequence.NEXTVAL|CURRVAL. Detach it from the FROM
                // clause but leave dbObjectType alone, which keeps CURRVAL on
                // the exact footing NEXTVAL has always had (unknown, no source
                // table). Promoting these to notAColumn would reclassify them
                // from column references to constants in DataFlowAnalyzer and
                // move existing lineage output; the defect reported was CURRVAL
                // being bound to an unrelated table, not its type.
                // Deliberately no setSourceTable(null) here: nothing has linked
                // this reference yet, and that setter has a side effect of
                // forcing dbObjectType to column, which is exactly the type we
                // are trying to leave alone.
                cr.setObjectType(TObjectName.ttobjNotAObject);
                cr.setValidate_column_status(TBaseType.VALIDATED_CAN_NOT_BE_A_COLUMN_NAME);
                return;
            }
            if (isOraclePseudoColumnReference(cr)){
                cr.setObjectType(TObjectName.ttobjNotAObject);
                cr.setSourceTable(null);
                cr.setValidate_column_status(TBaseType.MARKED_NOT_A_COLUMN_IN_COLUMN_RESOLVER);
                cr.setDbObjectTypeDirectly(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 (SQLUtil.compareIdentifier(this.dbvendor, ESQLDataObjectType.dotColumn, cr.toString(), 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){
        // Iterative rewrite of the original recursive descent. Provably identical
        // output: it visits exactly the same set of leaf (non-combined) SELECTs as
        // the recursion, in the same left-before-right order, and applies the
        // identical per-column test (alias match, else expr match, case-insensitive).
        // The only reason for the change is that UNION trees are left-leaning and
        // can be thousands of branches deep, so recursive descent risks
        // StackOverflowError (see repo CLAUDE.md: iterative UNION traversal).
        Deque<TSelectSqlStatement> pending = new ArrayDeque<>();
        pending.push(pSelect);
        while (!pending.isEmpty()) {
            TSelectSqlStatement select = pending.pop();
            if (select == null) {
                continue;
            }
            if (select.isCombinedQuery()) {
                // Push right then left so left is popped/processed first, matching
                // the recursion's "check left before right" order.
                pending.push(select.getRightStmt());
                pending.push(select.getLeftStmt());
                continue;
            }
            if (select.getResultColumnList() != null){ //if it's a db2 value row, then getResultColumnList() will be null
                TResultColumnList resultColumns = select.getResultColumnList();
                for(int j=0;j<resultColumns.size();j++){
                   TResultColumn lcColumn = resultColumns.getResultColumn(j);
                   boolean ret = false;
                   if (lcColumn.getAliasClause() != null){
                       ret = SQLUtil.compareIdentifier(this.dbvendor, ESQLDataObjectType.dotColumn, pColumn, lcColumn.getAliasClause().toString());
                   }
                   if (ret) return true;
                   ret = pColumn.equalsIgnoreCase(lcColumn.getExpr().toString());
                   if (ret) return true;
                }
            }
        }
        return false;
    }

    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;
        }
    }

}