Source code

001package gudusoft.gsqlparser.nodes;
002
003import gudusoft.gsqlparser.*;
004import gudusoft.gsqlparser.stmt.TDeleteSqlStatement;
005import gudusoft.gsqlparser.stmt.TDescribeStmt;
006import gudusoft.gsqlparser.stmt.TInsertSqlStatement;
007import gudusoft.gsqlparser.stmt.TMergeSqlStatement;
008import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
009import gudusoft.gsqlparser.stmt.TUpdateSqlStatement;
010import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
011import gudusoft.gsqlparser.util.SQLUtil;
012
013import java.util.ArrayList;
014
015/**
016 * A common table expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows.
017 *<p> Multiple common table expressions can be specified following the single WITH keyword.
018 *<p> Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.
019 *<p> Syntax:
020 * <blockquote><pre>
021 * table-name [column-name [,...n]] AS (fullselect)</pre>
022 * </blockquote>
023*/
024public class TCTE extends TTable{
025
026    private boolean recursive = false;
027
028    public void setRecursive(boolean recursive) {
029        this.recursive = recursive;
030    }
031
032    public boolean isRecursive() {
033        return recursive;
034    }
035
036    public void initAttributesFromColumnList(){
037        if (this.getColumnList() != null){
038            int i = 0;
039            if ((this.getSubquery() != null)&&(this.getSubquery().getResultColumnList() != null)){
040                if (this.getSubquery().getResultColumnList().size() != this.getColumnList().size()){
041                    if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
042                        TBaseType.log(String.format("CTE: <%s> columns list size is not the same as select list in subQuery, maybe due to list -> * ", this.getTableName().toString())
043                                ,TLog.ERROR,this.getTableName());
044                    }
045                }
046            }
047            for(TObjectName column: this.getColumnList()){
048                if ((this.getSubquery() != null)&&(this.getSubquery().getResultColumnList(true) != null)){
049                    TResultColumnList resultColumnListFromSubquery = this.getSubquery().getResultColumnList(true);
050                    //  关联 subQuery 中 select list
051                       // relationAttributes.add(new TAttributeNode( this.getTableName().toString()+"." + column.toString() ,this,this.getSubquery().getResultColumnList().getResultColumn(i) ));
052                    // Fix: When subquery has SELECT *, all CTE columns should reference the single star column
053                    TResultColumn resultColumn = null;
054                    if (resultColumnListFromSubquery.size() == 1 &&
055                            resultColumnListFromSubquery.getResultColumn(0).toString().endsWith("*")) {
056                        // All CTE columns map to the single star column
057                        resultColumn = resultColumnListFromSubquery.getResultColumn(0);
058                    } else if (i < resultColumnListFromSubquery.size()) {
059                        // Normal mapping by position
060                        resultColumn = resultColumnListFromSubquery.getResultColumn(i);
061                    }
062                    TAttributeNode.addNodeToList(new TAttributeNode( this.getTableName().toString()+"." + column.toString() ,this,resultColumn ),getAttributes());
063
064                }else{
065                        //relationAttributes.add(new TAttributeNode( this.getTableName().toString()+"." + column.toString() ,this));
066                    TAttributeNode.addNodeToList(new TAttributeNode( this.getTableName().toString()+"." + column.toString() ,this),getAttributes());
067                }
068                i++;
069            }
070            //
071            if ((this.getSubquery() != null)&&(this.getSubquery().isCombinedQuery())&&(this.getSubquery().getLeftStmt().getResultColumnList() != null)){
072                TSelectSqlStatement left = subquery.getLeftStmt();
073
074                String prefix = this.getTableName().toString()+".";
075                addNewAttributeFromSubQuery(left.getResultColumnList(),getAttributes(),prefix,this.getColumnList());
076                while (left.isCombinedQuery()){
077                    left = left.getLeftStmt();
078                    addNewAttributeFromSubQuery(left.getResultColumnList(),getAttributes(),prefix,this.getColumnList());
079                }
080            }
081        }
082
083        if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
084            TBaseType.log(String.format("Prepare attributes for CTE: <%s> via column list",this.getTableName().toString()),TLog.DEBUG,this.getTableName());
085            for(TAttributeNode node:getAttributes()){
086                TBaseType.log(String.format("\tAttribute: <%s>, Select list column: <%s>",node.getName(),node.getSubLevelResultColumn()!=null?node.getSubLevelResultColumn().toString():"N/A" ),TLog.DEBUG);
087            }
088        }
089
090    }
091    public void initAttributesFromSubQuery(){
092        if (this.getSubquery() == null) return;
093        super.initAttributesFromSubquery(this.getSubquery(),this.getTableName().toString()+".");
094
095        if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
096            TBaseType.log(String.format("Prepare attributes (num = %d) for CTE: <%s> via subQuery",getAttributes().size() ,this.getTableName().toString()),TLog.DEBUG,this.getTableName());
097        }
098        int c = 0;
099        for(TAttributeNode node:getAttributes()){
100            if (node.getSqlColumn() != null){
101                if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
102                    TBaseType.log(String.format("\tAttribute: <%s>, SQL column: <%s>",node.getName(), node.getSqlColumn()!=null?node.getSqlColumn().toString():"N/A" ),TLog.DEBUG);
103                }
104            }else{
105                if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
106                    TBaseType.log(String.format("\tAttribute: <%s>, Select list column: <%s>",node.getName(), node.getSubLevelResultColumn()!=null?node.getSubLevelResultColumn().toString():"N/A" ),TLog.DEBUG);
107                }
108            }
109            if (node.getAccompaniedAttributeNodes().size() >0 ){
110                if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
111                    TBaseType.log(String.format("\t\tAccompanied nodes: %d",node.getAccompaniedAttributeNodes().size()),TLog.DEBUG);
112                }
113                for(TAttributeNode n:node.getAccompaniedAttributeNodes()){
114                    if (n.getSqlColumn() != null){
115                        if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
116                            TBaseType.log(String.format("\t\tAttribute: <%s>, SQL column: <%s>",n.getName(), n.getSqlColumn()!=null?n.getSqlColumn().toString():"N/A" ),TLog.DEBUG);
117                        }
118                    }else{
119                        if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
120                            TBaseType.log(String.format("\t\tAttribute: <%s>, Select list column: <%s>",n.getName(), n.getSubLevelResultColumn()!=null?n.getSubLevelResultColumn().toString():"N/A" ),TLog.DEBUG);
121                        }
122                    }
123                }
124            }
125
126            c++;
127            if (c > TLog.OUTPUT_ATTRIBUTES_MAX){
128                if (TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE){
129                    TBaseType.log(String.format("\t...skipped after output %d attributes",c-1),TLog.DEBUG,this.getTableName().getStartToken());
130                }
131
132                break;
133            }
134        }
135    }
136
137    public String getRelationName(){
138        return this.getTableName().toString();
139    }
140
141//    public ArrayList<TAttributeNode> getAttributes(){
142//        return relationAttributes;
143//    }
144
145    private TSelectSqlNode selectNode;
146    private TSelectSqlStatement subquery;
147
148    private  TInsertSqlNode insertSqlNode;
149    private TInsertSqlStatement insertStmt;
150
151    private  TUpdateSqlNode updateSqlNode;
152    private TUpdateSqlStatement updateStmt;
153
154    private TDeleteSqlNode deleteSqlNode;
155    private TDeleteSqlStatement deleteStmt;
156
157    // PostgreSQL 17: MERGE as a CTE body, e.g. WITH m AS (MERGE INTO t ... RETURNING t.a) SELECT ...
158    private TMergeSqlNode mergeSqlNode;
159    private TMergeSqlStatement mergeStmt;
160
161    // Databricks: DESCRIBE HISTORY (and other DESCRIBE forms) as a CTE body,
162    // e.g. WITH h AS (DESCRIBE HISTORY t) SELECT ... (Mantis 4641)
163    private TDescribeSqlNode describeSqlNode;
164    private TDescribeStmt describeStmt;
165
166    private TCustomSqlStatement preparableStmt;
167
168    public  boolean searchColumnInResultSet(TCustomSqlStatement pSql,TTable pTable,TObjectName pColumn,boolean pMustIn){
169      boolean lcResult = false;
170        if (getColumnList() != null){
171            for (int i=0;i<getColumnList().size();i++){
172                lcResult = SQLUtil.sameName(this.dbvendor, ESQLDataObjectType.dotColumn, getColumnList().getObjectName(i).toString(), pColumn.getColumnNameOnly());
173                //pColumn.setSourceColumn(getColumnList().getObjectName(i));
174                if (lcResult) break;
175            }
176            return lcResult;
177        }
178
179        TCustomSqlStatement lcStmt = pSql;
180        while (lcStmt != null){
181            if (lcStmt == getSubquery()){
182                pTable.getLinkedColumns().addObjectName(pColumn);
183                pColumn.setSourceTable(pTable);
184                lcResult = true;
185                break;
186            }
187            lcStmt = lcStmt.getParentStmt();
188        }
189
190        if (lcResult) return true;
191
192        if (getSubquery() == null) return false;
193        lcResult = getSubquery().searchColumnInResultSet(pColumn,pMustIn);
194
195        return lcResult;
196    }
197
198    /**
199     * preparable statement can be:
200     * <p> {@link #getSubquery()}
201     * <p> or{@link #getUpdateStmt()}
202     * <p> or{@link #getInsertStmt()}
203     * <p> or{@link #getDeleteStmt()}
204     * @return
205     */
206    public TCustomSqlStatement getPreparableStmt() {
207        preparableStmt = null;
208        if (subquery!=null){
209            preparableStmt = subquery;
210        }else if (updateStmt != null){
211            preparableStmt = updateStmt;
212        }else if (deleteStmt != null){
213            preparableStmt = deleteStmt;
214        }else if (insertStmt != null){
215            preparableStmt = insertStmt;
216        }else if (mergeStmt != null){
217            preparableStmt = mergeStmt;
218        }else if (describeStmt != null){
219            preparableStmt = describeStmt;
220        }
221
222        return preparableStmt;
223    }
224
225    public TUpdateSqlStatement getUpdateStmt() {
226        return updateStmt;
227    }
228
229    public TInsertSqlStatement getInsertStmt() {
230
231        return insertStmt;
232    }
233
234    public TDeleteSqlStatement getDeleteStmt() {
235
236        return deleteStmt;
237    }
238
239    /**
240     * MERGE statement used as the body of this CTE (PostgreSQL 17:
241     * {@code WITH m AS (MERGE INTO t ... RETURNING ...) SELECT ...}), or null.
242     */
243    public TMergeSqlStatement getMergeStmt() {
244
245        return mergeStmt;
246    }
247
248    /**
249     * DESCRIBE statement used as the body of this CTE (Databricks
250     * DESCRIBE HISTORY inside WITH), or null.
251     */
252    public TDescribeStmt getDescribeStmt() {
253
254        return describeStmt;
255    }
256
257    /**
258     * @return table name of this common table expression.
259     */
260    public TObjectName getTableName() {
261        return tableName;
262    }
263
264    private TObjectName tableName;
265
266    public void setColumnList(TObjectNameList columnList) {
267        this.columnList = columnList;
268    }
269
270    /**
271     * fullselect of this common table expression.
272     * @return
273     */
274    public TSelectSqlStatement getSubquery() {
275        return subquery;
276    }
277
278    /**
279     * @return List of column name of this common table expression.
280
281     */
282    public TObjectNameList getColumnList() {
283        return columnList;
284    }
285
286    private TObjectNameList columnList = null;
287
288    /**
289     * ClickHouse: the columned-CTE grammar ({@code WITH t(c1, c2) AS (subquery)})
290     * parses the parenthesised column list as general expressions so the same
291     * {@code name '('} prefix can also serve function-call expression-CTEs
292     * ({@code WITH plus(1,2) AS x}) without a reduce/reduce conflict. Convert
293     * the expressions back to a column-name list. All-or-nothing: if any
294     * element is not a plain column reference (native ClickHouse rejects such
295     * a list), NO column list is set — compacting the valid entries would
296     * silently shift every later column onto the wrong subquery position.
297     */
298    public void setColumnListFromExpressions(TExpressionList exprs){
299        TObjectNameList cols = null;
300        for(int i=0; i<exprs.size(); i++){
301            TObjectName name = exprs.getExpression(i).getObjectOperand();
302            if (name == null) return;
303            if (cols == null) cols = new TObjectNameList();
304            cols.addObjectName(name);
305        }
306        if (cols != null) setColumnList(cols);
307    }
308
309    public void init(Object arg1,Object arg2)
310    {
311       tableName = (TObjectName)arg1;
312       //tableName.parseTablename();
313       //tableName.setObjectType(TObjectName.ttobjTableCTE);
314       tableName.setDbObjectType(EDbObjectType.cte);
315
316       if (arg2 instanceof TSelectSqlNode){
317       selectNode = (TSelectSqlNode)arg2;
318       }else if (arg2 instanceof TInsertSqlNode){
319           insertSqlNode = (TInsertSqlNode)arg2;
320       }else if (arg2 instanceof TUpdateSqlNode){
321           updateSqlNode = (TUpdateSqlNode)arg2;
322       }else if (arg2 instanceof TDeleteSqlNode){
323           deleteSqlNode = (TDeleteSqlNode)arg2;
324       }else if (arg2 instanceof TMergeSqlNode){
325           mergeSqlNode = (TMergeSqlNode)arg2;
326       }else if (arg2 instanceof TDescribeSqlNode){
327           describeSqlNode = (TDescribeSqlNode)arg2;
328       }else if (arg2 instanceof TExpression){
329           // ClickHouse expression-CTE: WITH <expr> AS <ident>
330           expression = (TExpression)arg2;
331       }
332    }
333
334    /**
335     * ClickHouse, the body of an expression-CTE ({@code WITH <expr> AS <ident>}) —
336     * a literal, lambda, function call or parenthesised expression that the
337     * identifier names. Null for subquery CTEs.
338     *
339     * @return the expression named by this CTE, null for subquery CTEs.
340     */
341    public TExpression getExpression() {
342        return expression;
343    }
344
345    private TExpression expression = null;
346
347    /**
348     * ClickHouse, set when the CTE is declared {@code AS MATERIALIZED (subquery)}.
349     *
350     * @return the MATERIALIZED keyword token, null otherwise.
351     */
352    public TSourceToken getMaterializedToken() {
353        return materializedToken;
354    }
355
356    public void setMaterializedToken(TSourceToken materializedToken) {
357        this.materializedToken = materializedToken;
358    }
359
360    private TSourceToken materializedToken = null;
361
362    public void incParenthesisCount() {
363        if (selectNode != null){
364            selectNode.incParenthesisCount();
365        }
366    }
367
368    public void doParse(TCustomSqlStatement psql, ESqlClause plocation){
369
370        if (selectNode != null){
371            //long t = System.currentTimeMillis();
372            subquery = new TSelectSqlStatement(psql.dbvendor);
373            subquery.rootNode = selectNode;
374            subquery.setQueryOfCTE(true);
375            subquery.setCteIncludeThisStmt(this);
376            subquery.doParseStatement(psql);
377            for(TTable t:subquery.getRelations()){
378                if (t.isCTEName() && t.getCTE() == this){
379                   // System.out.println("found cte name in the same level:"+ t.getCTE().toString());
380                    t.setCTEName(false);
381                    t.setCTE(null);
382                }
383            }
384//            if (columnList == null){
385//                columnList = new TObjectNameList();
386//                for(TResultColumn resultColumn:subquery.getResultColumnList()){
387//                    columnList.addObjectName(new TObjectName());
388//
389//                }
390//            }
391            //System.out.println("Time Escaped: " + (System.currentTimeMillis() - t)+", sql size:"+ this.toString().length() );
392        }else if (insertSqlNode != null){
393            insertStmt = new TInsertSqlStatement(psql.dbvendor);
394            insertStmt.rootNode = insertSqlNode;
395            insertStmt.setCteIncludeThisStmt(this);
396            insertStmt.doParseStatement(psql);
397            unbindSelfReferences(insertStmt);
398        }else if (deleteSqlNode != null){
399            deleteStmt = new TDeleteSqlStatement(psql.dbvendor);
400            deleteStmt.rootNode = deleteSqlNode;
401            deleteStmt.setCteIncludeThisStmt(this);
402            deleteStmt.doParseStatement(psql);
403            unbindSelfReferences(deleteStmt);
404        }else if (updateSqlNode != null){
405            updateStmt = new TUpdateSqlStatement(psql.dbvendor);
406            updateStmt.rootNode = updateSqlNode;
407            updateStmt.setCteIncludeThisStmt(this);
408            updateStmt.doParseStatement(psql);
409            unbindSelfReferences(updateStmt);
410        }else if (mergeSqlNode != null){
411            mergeStmt = new TMergeSqlStatement(psql.dbvendor);
412            mergeStmt.rootNode = mergeSqlNode;
413            mergeStmt.setCteIncludeThisStmt(this);
414            mergeStmt.doParseStatement(psql);
415            unbindSelfReferences(mergeStmt);
416        }else if (describeSqlNode != null){
417            describeStmt = new TDescribeStmt(psql.dbvendor);
418            describeStmt.rootNode = describeSqlNode;
419            describeStmt.doParseStatement(psql);
420        }
421    }
422
423    /**
424     * A data-changing body never reads its own CTE (a data-modifying statement in WITH cannot be recursive): inside
425     * {@code WITH t AS (DELETE FROM t ...)} the name is the table, as for a subquery's own references above.
426     */
427    private void unbindSelfReferences(TCustomSqlStatement body) {
428        final TCTE self = this;
429        final java.util.List<TTable> found = new java.util.ArrayList<TTable>(body.getRelations());
430        for (int i = 0; body.tables != null && i < body.tables.size(); i++) {
431            found.add(body.tables.getTable(i));
432        }
433        // every table of the body's subtree, its subqueries included (Codex 13i #2)
434        body.acceptChildren(new TParseTreeVisitor() {
435            @Override
436            public void preVisit(TTable node) {
437                found.add(node);
438            }
439        });
440        for (TTable t : found) {
441            if (t != null && t.isCTEName() && t.getCTE() == self) {
442                t.setCTEName(false);
443                t.setCTE(null);
444                t.setCteColomnReferences(null);
445            }
446        }
447    }
448
449    public void accept(TParseTreeVisitor v){
450        v.preVisit(this);
451        v.postVisit(this);
452    }
453
454    public void acceptChildren(TParseTreeVisitor v){
455        v.preVisit(this);
456        if (subquery != null){
457            subquery.acceptChildren(v);
458        }
459        if (expression != null){
460            // acceptChildren, not accept: TExpression.accept only visits the
461            // wrapper node, so resolver2's scope builder would never collect
462            // the definition's column references.
463            expression.acceptChildren(v);
464        }
465        v.postVisit(this);
466    }
467
468    public void setSubquery(TSelectSqlStatement subquery) {
469        this.subquery = subquery;
470    }
471
472    public void setInsertStmt(TInsertSqlStatement insertStmt) {
473        this.insertStmt = insertStmt;
474    }
475
476    public void setUpdateStmt(TUpdateSqlStatement updateStmt) {
477        this.updateStmt = updateStmt;
478    }
479
480    public void setDeleteStmt(TDeleteSqlStatement deleteStmt) {
481        this.deleteStmt = deleteStmt;
482    }
483
484    public void setPreparableStmt(TCustomSqlStatement preparableStmt) {
485        this.preparableStmt = preparableStmt;
486    }
487
488    public void setTableName(TObjectName tableName) {
489        this.tableName = tableName;
490    }
491}