Skip to content

Commit 9de7074

Browse files
denglimingdengliming
authored
fix: support SQL Server ORDER BY ... FOR XML PATH parsing in subqueries (#2392)
1 parent fb36cc6 commit 9de7074

4 files changed

Lines changed: 28 additions & 23 deletions

File tree

‎src/main/java/net/sf/jsqlparser/statement/select/Select.java‎

Lines changed: 8 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,12 @@
99
*/
1010
package net.sf.jsqlparser.statement.select;
1111

12+
import java.util.ArrayList;
13+
import java.util.Arrays;
14+
import java.util.Collection;
15+
import java.util.Iterator;
16+
import java.util.List;
17+
import java.util.Optional;
1218
import net.sf.jsqlparser.expression.Alias;
1319
import net.sf.jsqlparser.expression.Expression;
1420
import net.sf.jsqlparser.expression.ExpressionVisitor;
@@ -17,13 +23,6 @@
1723
import net.sf.jsqlparser.statement.Statement;
1824
import net.sf.jsqlparser.statement.StatementVisitor;
1925

20-
import java.util.ArrayList;
21-
import java.util.Arrays;
22-
import java.util.Collection;
23-
import java.util.Iterator;
24-
import java.util.List;
25-
import java.util.Optional;
26-
2726
public abstract class Select extends ASTNodeAccessImpl implements Statement, Expression, FromItem {
2827
protected Table forUpdateTable = null;
2928
protected List<WithItem<?>> withItemsList;
@@ -381,12 +380,12 @@ public StringBuilder appendTo(StringBuilder builder) {
381380

382381
appendTo(builder, alias, null, pivot, unPivot);
383382

383+
builder.append(orderByToString(oracleSiblings, orderByElements));
384+
384385
if (forClause != null) {
385386
forClause.appendTo(builder);
386387
}
387388

388-
builder.append(orderByToString(oracleSiblings, orderByElements));
389-
390389
if (limitBy != null) {
391390
builder.append(limitBy);
392391
}

‎src/main/java/net/sf/jsqlparser/util/deparser/SelectDeParser.java‎

Lines changed: 12 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,13 @@
99
*/
1010
package net.sf.jsqlparser.util.deparser;
1111

12+
import static java.util.stream.Collectors.joining;
13+
14+
import java.lang.reflect.InvocationTargetException;
15+
import java.util.ArrayList;
16+
import java.util.Iterator;
17+
import java.util.List;
18+
import java.util.Optional;
1219
import net.sf.jsqlparser.expression.Alias;
1320
import net.sf.jsqlparser.expression.Expression;
1421
import net.sf.jsqlparser.expression.ExpressionVisitor;
@@ -33,10 +40,10 @@
3340
import net.sf.jsqlparser.statement.piped.PivotPipeOperator;
3441
import net.sf.jsqlparser.statement.piped.RenamePipeOperator;
3542
import net.sf.jsqlparser.statement.piped.SelectPipeOperator;
43+
import net.sf.jsqlparser.statement.piped.SetOperationPipeOperator;
3644
import net.sf.jsqlparser.statement.piped.SetPipeOperator;
3745
import net.sf.jsqlparser.statement.piped.TableSamplePipeOperator;
3846
import net.sf.jsqlparser.statement.piped.UnPivotPipeOperator;
39-
import net.sf.jsqlparser.statement.piped.SetOperationPipeOperator;
4047
import net.sf.jsqlparser.statement.piped.WherePipeOperator;
4148
import net.sf.jsqlparser.statement.piped.WindowPipeOperator;
4249
import net.sf.jsqlparser.statement.select.Distinct;
@@ -71,14 +78,6 @@
7178
import net.sf.jsqlparser.statement.select.WithItem;
7279
import net.sf.jsqlparser.statement.update.UpdateSet;
7380

74-
import java.lang.reflect.InvocationTargetException;
75-
import java.util.ArrayList;
76-
import java.util.Iterator;
77-
import java.util.List;
78-
import java.util.Optional;
79-
80-
import static java.util.stream.Collectors.joining;
81-
8281
@SuppressWarnings({"PMD.CyclomaticComplexity", "PMD.NPathComplexity"})
8382
public class SelectDeParser extends AbstractDeParser<PlainSelect>
8483
implements SelectVisitor<StringBuilder>, SelectItemVisitor<StringBuilder>,
@@ -316,10 +315,6 @@ public <S> StringBuilder visit(PlainSelect plainSelect, S context) {
316315
builder.append(plainSelect.getWindowDefinitions().stream()
317316
.map(WindowDefinition::toString).collect(joining(", ")));
318317
}
319-
if (plainSelect.getForClause() != null) {
320-
plainSelect.getForClause().appendTo(builder);
321-
}
322-
323318
Alias alias = plainSelect.getAlias();
324319
if (alias != null) {
325320
builder.append(alias);
@@ -335,6 +330,10 @@ public <S> StringBuilder visit(PlainSelect plainSelect, S context) {
335330

336331
deparseOrderByElementsClause(plainSelect, plainSelect.getOrderByElements());
337332

333+
if (plainSelect.getForClause() != null) {
334+
plainSelect.getForClause().appendTo(builder);
335+
}
336+
338337
if (plainSelect.isEmitChanges()) {
339338
builder.append(" EMIT CHANGES");
340339
}

‎src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4256,14 +4256,14 @@ PlainSelect PlainSelect() #PlainSelect:
42564256
[ LOOKAHEAD(2) groupBy=GroupByColumnReferences() { plainSelect.setGroupByElement(groupBy); }]
42574257
[ LOOKAHEAD(2) having=Having() { plainSelect.setHaving(having); }]
42584258
[ LOOKAHEAD(2) qualify=Qualify() {plainSelect.setQualify(qualify); }]
4259-
[ LOOKAHEAD(2) forClause = ForClause() {plainSelect.setForClause(forClause);} ]
42604259
[ LOOKAHEAD(<K_ORDER> <K_SIBLINGS> <K_BY>) orderByElements = OrderByElements() { plainSelect.setOracleSiblings(true); plainSelect.setOrderByElements(orderByElements); } ]
42614260
[ LOOKAHEAD(2) <K_WINDOW>
42624261
windowName = RelObjectName() <K_AS> winDef = windowDefinition() { List<WindowDefinition> winDefs = new ArrayList<WindowDefinition>(); winDefs.add(winDef.withWindowName(windowName)); }
42634262
( LOOKAHEAD(2) "," windowName = RelObjectName() <K_AS> winDef = windowDefinition() { winDefs.add(winDef.withWindowName(windowName)); } )*
42644263
{ plainSelect.setWindowDefinitions(winDefs); }
42654264
]
42664265
[ LOOKAHEAD(<K_ORDER> <K_BY>) orderByElements = OrderByElements() { plainSelect.setOrderByElements(orderByElements); } ]
4266+
[ LOOKAHEAD(2) forClause = ForClause() {plainSelect.setForClause(forClause);} ]
42674267
[ LOOKAHEAD(2) <K_EMIT> <K_CHANGES> { plainSelect.setEmitChanges(true); } ]
42684268
[ LOOKAHEAD(7) limit = LimitBy() { plainSelect.setLimitBy(limit); } ]
42694269
[ LOOKAHEAD(<K_LIMIT>) limit = LimitWithOffset() { plainSelect.setLimit(limit); } ]

‎src/test/java/net/sf/jsqlparser/statement/select/ForClauseTest.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,13 @@ void testForXMLPath() throws JSQLParserException {
3030
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
3131
}
3232

33+
@Test
34+
void testForXMLPathAfterOrderByInSubSelect() throws JSQLParserException {
35+
String sqlStr =
36+
"SELECT STUFF((SELECT ',' + name FROM class ORDER BY id FOR XML PATH('')),1,1,'') AS names FROM users";
37+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
38+
}
39+
3340
@Test
3441
void testForXMLRaw() throws JSQLParserException {
3542
String sqlStr =

0 commit comments

Comments
 (0)