{"record":{"id":"592ce051b3bf9259","repo":"n8n-io/n8n","slug":"nested-ctes-aren-t-supported-cte-cte-alias","errorCode":null,"errorMessage":"Nested CTEs aren't supported (CTE: ${cte.alias})","messagePattern":"Nested CTEs aren't supported \\(CTE: (.+?)\\)","errorType":"exception","errorClass":"TypeORMError","httpStatus":null,"severity":"error","filePath":"packages/@n8n/typeorm/src/query-builder/QueryBuilder.ts","lineNumber":1013,"sourceCode":"\t\t\t\treturn '(' + condition.parameters.join(' OR ') + ')';\n\t\t}\n\n\t\tthrow new TypeError(`Unsupported FindOperator ${FindOperator.constructor.name}`);\n\t}\n\n\tprotected createCteExpression(): string {\n\t\tif (!this.hasCommonTableExpressions()) {\n\t\t\treturn '';\n\t\t}\n\t\tconst databaseRequireRecusiveHint =\n\t\t\tthis.connection.driver.cteCapabilities.requiresRecursiveHint;\n\n\t\tconst cteStrings = this.expressionMap.commonTableExpressions.map((cte) => {\n\t\t\tconst cteBodyExpression =\n\t\t\t\ttypeof cte.queryBuilder === 'string' ? cte.queryBuilder : cte.queryBuilder.getQuery();\n\t\t\tif (typeof cte.queryBuilder !== 'string') {\n\t\t\t\tif (cte.queryBuilder.hasCommonTableExpressions()) {\n\t\t\t\t\tthrow new TypeORMError(`Nested CTEs aren't supported (CTE: ${cte.alias})`);\n\t\t\t\t}\n\t\t\t\tif (\n\t\t\t\t\t!this.connection.driver.cteCapabilities.writable &&\n\t\t\t\t\t!InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)\n\t\t\t\t) {\n\t\t\t\t\tthrow new TypeORMError(\n\t\t\t\t\t\t`Only select queries are supported in CTEs in ${this.connection.options.type} (CTE: ${cte.alias})`,\n\t\t\t\t\t);\n\t\t\t\t}\n\t\t\t\tthis.setParameters(cte.queryBuilder.getParameters());\n\t\t\t}\n\t\t\tlet cteHeader = this.escape(cte.alias);\n\t\t\tif (cte.options.columnNames) {\n\t\t\t\tconst escapedColumnNames = cte.options.columnNames.map((column) => this.escape(column));\n\t\t\t\tif (InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)) {\n\t\t\t\t\tif (\n\t\t\t\t\t\tcte.queryBuilder.expressionMap.selects.length &&\n\t\t\t\t\t\tcte.options.columnNames.length !== cte.queryBuilder.expressionMap.selects.length","sourceCodeStart":995,"sourceCodeEnd":1031,"githubUrl":"https://github.com/n8n-io/n8n/blob/5ac6606e81f67bb9534255570cd4e86fd8101eee/packages/@n8n/typeorm/src/query-builder/QueryBuilder.ts#L995-L1031","documentation":"createCteExpression() throws when a CTE's queryBuilder itself has common table expressions (cte.queryBuilder.hasCommonTableExpressions()). SQL does not allow a WITH clause inside the body of another WITH clause entry; each CTE must be a flat sibling. TypeORM enforces this at query build time rather than emitting invalid SQL.","triggerScenarios":"Calling .addCommonTableExpression(qbA, 'a') where qbA was itself built with .addCommonTableExpression(qbB, 'b'). The inner CTE nests inside the outer, which the database would reject.","commonSituations":"Composing reusable QueryBuilders that each add their own CTEs and then embedding one inside another's CTE list. Refactoring a flat CTE list into a nested builder by mistake.","solutions":["Flatten all CTEs into a single WITH list on the outermost QueryBuilder; do not chain CTE-bearing builders as CTE bodies.","If a CTE body needs another CTE, hoist that inner CTE to the outer builder's addCommonTableExpression list.","Use raw SQL strings for CTE bodies if composition is complex, ensuring no nested WITH.","Refactor shared subqueries into separate top-level CTEs referenced by name."],"exampleFix":"// before\nconst inner = dataSource.createQueryBuilder().select(...)\n  .addCommonTableExpression(deeperQb, 'deep');\nconst outer = dataSource.createQueryBuilder().select(...)\n  .addCommonTableExpression(inner, 'inner'); // throws: nested CTE\n\n// after — flatten\nconst outer = dataSource.createQueryBuilder().select(...)\n  .addCommonTableExpression(deeperQb, 'deep')\n  .addCommonTableExpression(innerFlatQb, 'inner');","handlingStrategy":"validation","validationCode":"function isFlatCteBody(qb: { hasCommonTableExpressions: () => boolean }): boolean {\n  return !qb.hasCommonTableExpressions();\n}\nif (!isFlatCteBody(bodyQb)) throw new Error('CTE body must not itself contain CTEs; flatten them to the outer builder');","typeGuard":"function isFlatCteBody(qb: { hasCommonTableExpressions: () => boolean }): qb is { hasCommonTableExpressions: () => false } {\n  return !qb.hasCommonTableExpressions();\n}","tryCatchPattern":null,"preventionTips":["Flatten all CTEs onto the outermost QueryBuilder.","Hoist inner CTEs from reusable subquery builders before composing.","Use raw SQL strings for CTE bodies if composition is complex.","Add a helper that collects CTEs from all composed builders into one flat list."],"tags":["query-builder","cte","sql"],"backgroundTag":null,"analyzedSha":"5ac6606e81f67bb9534255570cd4e86fd8101eee","analyzedAt":"2026-08-12T05:26:35.080Z","schemaVersion":2},"datasetVersion":"2026-08-12T18:17:37.767Z"}