Merge branch 'master' of github.com:JSQLParser/JSqlParser

manticore-projects · manticore-projects · commit 3c8da305186b · 2022-04-04T07:08:17.000+07:00
diff --git a/README.md b/README.md
@@ -22,9 +22,7 @@ Please provide feedback on:
 * API changes: extend visitor with return values (https://github.com/JSQLParser/JSqlParser/issues/901)
 
 ## News
-* Released version **4.2** of JSqlParser
-* Released version **4.1** of JSqlParser
-* Released version **4.0** of JSqlParser
+* Released version **4.3** of JSqlParser
 * The array parsing is the default behaviour. Square bracket quotation has to be enabled using 
   a parser flag (**CCJSqlParser.withSquareBracketQuotation**).
 * due to an API change the version will be 3.0
@@ -58,6 +56,9 @@ Also I would like to know about needed examples or documentation stuff.
 
 * support for **timestamp with local time zone**
 * improved support for quoted identifiers in casts
+* support for **top with ties**
+* support for operators **<->** and **<#>**
+* improvement of test methods
 
 Additionally, we have fixed many errors and improved the code quality and the test coverage.
 
diff --git a/build.gradle b/build.gradle
@@ -29,19 +29,19 @@ repositories {
 }
 
 dependencies {
-    testImplementation 'commons-io:commons-io:2.6'
-    testImplementation 'junit:junit:4.13.1'
-    testImplementation 'org.mockito:mockito-core:2.28.2'
-    testImplementation 'org.assertj:assertj-core:3.16.1'
-    testImplementation 'org.apache.commons:commons-lang3:3.10'
-    testImplementation 'com.h2database:h2:1.4.200'
+    testImplementation 'commons-io:commons-io:2.11.0'
+    testImplementation 'junit:junit:4.13.2'
+    testImplementation 'org.mockito:mockito-core:4.3.1'
+    testImplementation 'org.assertj:assertj-core:3.22.0'
+    testImplementation 'org.apache.commons:commons-lang3:3.12.0'
+    testImplementation 'com.h2database:h2:2.1.210'
     
     // for JaCoCo Reports
-    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.7.1'
+    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.8.2'
     testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine'
     
     // https://mvnrepository.com/artifact/org.mockito/mockito-junit-jupiter
-    testImplementation 'org.mockito:mockito-junit-jupiter:4.1.0'
+    testImplementation 'org.mockito:mockito-junit-jupiter:4.3.1'
     
     // enforce latest version of JavaCC
     javacc 'net.java.dev.javacc:javacc:7.0.10'
@@ -115,7 +115,7 @@ jacocoTestCoverageVerification {
             limit {
                 counter = 'LINE'
                 value = 'MISSEDCOUNT'
-                maximum = 5500
+                maximum = 5513
                 }
             excludes = [
                     'net.sf.jsqlparser.util.validation.*',
diff --git a/pom.xml b/pom.xml
@@ -114,7 +114,7 @@
                 <configuration>
                     <rulesets>
                         <!-- Custom local file system rule set -->
-                        <ruleset>${basedir}/ruleset.xml</ruleset>
+                        <ruleset>${project.basedir}/ruleset.xml</ruleset>
                     </rulesets>
                     <excludes>
                         <exclude>**/*Bean.java</exclude>
diff --git a/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitor.java b/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitor.java
@@ -188,4 +188,6 @@ public interface ExpressionVisitor {
     void visit(AllValue allValue);
 
     void visit(IsDistinctExpression isDistinctExpression);
+
+    void visit(GeometryDistance geometryDistance);
 }
diff --git a/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapter.java b/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapter.java
@@ -492,12 +492,10 @@ public void visit(UnPivot unpivot) {
 
     @Override
     public void visit(AllColumns allColumns) {
-        allColumns.accept((ExpressionVisitor) this);
     }
 
     @Override
     public void visit(AllTableColumns allTableColumns) {
-        allTableColumns.accept((ExpressionVisitor) this);
     }
 
     @Override
@@ -634,7 +632,12 @@ public void visit(ConnectByRootOperator connectByRootOperator) {
     public void visit(OracleNamedFunctionParameter oracleNamedFunctionParameter) {
         oracleNamedFunctionParameter.getExpression().accept(this);
     }
-    
+
+    @Override
+    public void visit(GeometryDistance geometryDistance) {
+        visitBinaryExpression(geometryDistance);
+    }
+
     public void visit(ColumnDefinition columnDefinition) {
        columnDefinition.accept(this);
      }
diff --git a/src/main/java/net/sf/jsqlparser/expression/operators/relational/GeometryDistance.java b/src/main/java/net/sf/jsqlparser/expression/operators/relational/GeometryDistance.java
@@ -0,0 +1,28 @@
+/*-
+ * #%L
+ * JSQLParser library
+ * %%
+ * Copyright (C) 2004 - 2022 JSQLParser
+ * %%
+ * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
+ * #L%
+ */
+package net.sf.jsqlparser.expression.operators.relational;
+
+import net.sf.jsqlparser.expression.ExpressionVisitor;
+
+public class GeometryDistance extends ComparisonOperator {
+
+    public GeometryDistance() {
+        super("<->");
+    }
+
+    public GeometryDistance(String operator) {
+        super(operator);
+    }
+
+    @Override
+    public void accept(ExpressionVisitor expressionVisitor) {
+        expressionVisitor.visit(this);
+    }
+}
diff --git a/src/main/java/net/sf/jsqlparser/statement/select/Top.java b/src/main/java/net/sf/jsqlparser/statement/select/Top.java
@@ -15,6 +15,7 @@ public class Top {
 
     private boolean hasParenthesis = false;
     private boolean isPercentage = false;
+    private boolean isWithTies = false;
     private Expression expression;
 
     public Expression getExpression() {
@@ -41,6 +42,14 @@ public void setPercentage(boolean percentage) {
         this.isPercentage = percentage;
     }
 
+    public void setWithTies(boolean withTies) {
+        this.isWithTies = withTies;
+    }
+
+    public boolean isWithTies() {
+        return isWithTies;
+    }
+
     @Override
     public String toString() {
         String result = "TOP ";
@@ -59,6 +68,10 @@ public String toString() {
             result += " PERCENT";
         }
 
+        if (isWithTies) {
+            result += " WITH TIES";
+        }
+
         return result;
     }
 
diff --git a/src/main/java/net/sf/jsqlparser/util/TablesNamesFinder.java b/src/main/java/net/sf/jsqlparser/util/TablesNamesFinder.java
@@ -1031,4 +1031,9 @@ public void visit(PurgeStatement purgeStatement) {
     public void visit(AlterSystemStatement alterSystemStatement) {
         // no tables involved in this statement
     }
+
+    @Override
+    public void visit(GeometryDistance geometryDistance) {
+        visitBinaryExpression(geometryDistance);
+    }
 }
diff --git a/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java b/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java
@@ -34,6 +34,7 @@
 import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
 import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
 import net.sf.jsqlparser.expression.operators.relational.FullTextSearch;
+import net.sf.jsqlparser.expression.operators.relational.GeometryDistance;
 import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
 import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
 import net.sf.jsqlparser.expression.operators.relational.InExpression;
@@ -996,4 +997,9 @@ public void visit(IsDistinctExpression isDistinctExpression) {
                 isDistinctExpression.getStringExpression() +
                 isDistinctExpression.getRightExpression());
     }
+
+    @Override
+    public void visit(GeometryDistance geometryDistance) {
+        visitOldOracleJoinBinaryExpression(geometryDistance, " " + geometryDistance.getStringExpression() + " ");
+    }
 }
diff --git a/src/main/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidator.java b/src/main/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidator.java
@@ -30,6 +30,7 @@
 import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
 import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
 import net.sf.jsqlparser.expression.operators.relational.FullTextSearch;
+import net.sf.jsqlparser.expression.operators.relational.GeometryDistance;
 import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
 import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
 import net.sf.jsqlparser.expression.operators.relational.InExpression;
@@ -586,4 +587,8 @@ public void visit(IsDistinctExpression isDistinctExpression) {
         isDistinctExpression.getRightExpression().accept(this);
     }
 
+    @Override
+    public void visit(GeometryDistance geometryDistance) {
+        visitOldOracleJoinBinaryExpression(geometryDistance, " <-> ");
+    }
 }
diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -421,6 +421,7 @@ TOKEN: /* SQL Keywords. prefixed with K_ to avoid name clashes */
 |   <K_WHERE:"WHERE">
 |   <K_WINDOW:"WINDOW">
 |   <K_WITH:"WITH">
+|   <K_WITH_TIES:"WITH TIES">
 |   <K_WITHIN:"WITHIN">
 |   <K_WITHOUT:"WITHOUT">
 |   <K_WORK:"WORK">
@@ -2903,6 +2904,7 @@ Top Top():
             { top.setParenthesis(true);}
        ")"
     ) [ LOOKAHEAD(2) <K_PERCENT>            { top.setPercentage(true); } ]
+      [ LOOKAHEAD(2) <K_WITH_TIES>            { top.setWithTies(true); }]
     {
         return top;
     }
@@ -3110,6 +3112,8 @@ Expression RegularCondition() #RegularCondition:
         | <OP_CONCAT> { result = new JsonOperator("||"); }
         | "-" { result = new JsonOperator("-"); }
         | "-#" { result = new JsonOperator("-#"); }
+        | "<->" { result = new GeometryDistance("<->"); }
+        | "<#>" { result = new GeometryDistance("<#>"); }
     )
 
     ( LOOKAHEAD(2) <K_PRIOR> rightExpression=ComparisonItem() { oraclePrior = EqualsTo.ORACLE_PRIOR_END; }
diff --git a/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java b/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java
@@ -663,6 +663,22 @@ public void testTopWithParenthesis() throws JSQLParserException {
         assertStatementCanBeDeparsedAs(select, statement);
     }
 
+    @Test
+    public void testTopWithTies() throws JSQLParserException {
+        final String statement = "SELECT TOP (5) PERCENT WITH TIES columnName1, columnName2 FROM tableName";
+        final Select select = (Select) parserManager.parse(new StringReader(statement));
+
+        final PlainSelect selectBody = (PlainSelect) select.getSelectBody();
+
+        final Top top = selectBody.getTop();
+        assertEquals("5", top.getExpression().toString());
+        assertTrue(top.hasParenthesis());
+        assertTrue(top.isPercentage());
+        assertTrue(top.isWithTies());
+
+        assertStatementCanBeDeparsedAs(select, statement);
+    }
+
     @Test
     public void testTopWithJdbcParameter() throws JSQLParserException {
         String statement = "SELECT TOP ?1 * FROM mytable WHERE mytable.col = 9";
@@ -1004,9 +1020,12 @@ public void testDistinctTop2() {
     public void testDistinctWithFollowingBrackets() throws JSQLParserException {
         Select select = (Select) assertSqlCanBeParsedAndDeparsed("SELECT DISTINCT (phone), name FROM admin_user");
         PlainSelect selectBody = (PlainSelect) select.getSelectBody();
+        Distinct distinct = selectBody.getDistinct();
+
         assertThat(selectBody.getDistinct())
                 .isNotNull()
-                .hasFieldOrPropertyWithValue("getOnSelectItems", null);
+                .hasFieldOrPropertyWithValue("onSelectItems", null);
+
         assertThat(selectBody.getSelectItems().get(0).toString())
                 .isEqualTo("(phone)");
     }
@@ -2803,6 +2822,12 @@ public void testNotEqualsTo() throws JSQLParserException {
         assertSqlCanBeParsedAndDeparsed("SELECT * FROM foo WHERE a <> b");
     }
 
+    @Test
+    public void testGeometryDistance() throws JSQLParserException {
+        assertSqlCanBeParsedAndDeparsed("SELECT * FROM foo ORDER BY a <-> b");
+        assertSqlCanBeParsedAndDeparsed("SELECT * FROM foo ORDER BY a <#> b");
+    }
+
     @Test
     public void testJsonExpression() throws JSQLParserException {
         assertSqlCanBeParsedAndDeparsed("SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram");
diff --git a/src/test/java/net/sf/jsqlparser/test/TestUtils.java b/src/test/java/net/sf/jsqlparser/test/TestUtils.java
@@ -51,6 +51,12 @@ public class TestUtils {
     private static final Pattern SQL_COMMENT_PATTERN = Pattern.
             compile("(--.*$)|(/\\*.*?\\*/)", Pattern.MULTILINE);
 
+    private static final Pattern SQL_SANITATION_PATTERN
+            = Pattern.compile("(\\s+)", Pattern.MULTILINE);
+
+    private static final Pattern SQL_SANITATION_PATTERN2
+            = Pattern.compile("\\s*([!/,()=+\\-*|\\]<>])\\s*", Pattern.MULTILINE);
+
     /**
      * @param statement
      * @return the parsed {@link Statement}
@@ -94,10 +100,17 @@ public static void assertStatementCanBeDeparsedAs(Statement parsed, String state
     }
 
     public static void assertStatementCanBeDeparsedAs(Statement parsed, String statement, boolean laxDeparsingCheck) {
-        assertEquals(buildSqlString(statement, laxDeparsingCheck),
-                buildSqlString(parsed.toString(), laxDeparsingCheck));
+        String sanitizedInputSqlStr = buildSqlString(parsed.toString(), laxDeparsingCheck);
+        String sanitizedStatementStr = buildSqlString(statement, laxDeparsingCheck);
+
+        assertEquals(sanitizedStatementStr, sanitizedInputSqlStr);
 
-        assertDeparse(parsed, statement, laxDeparsingCheck);
+        StringBuilder builder = new StringBuilder();
+        parsed.accept( new StatementDeParser(builder) );
+
+        String sanitizedDeparsedStr = buildSqlString(builder.toString(), laxDeparsingCheck);
+
+        assertEquals(sanitizedStatementStr, sanitizedDeparsedStr);
     }
 
     /**
@@ -245,12 +258,19 @@ public static void assertDeparse(Statement stmt, String statement, boolean laxDe
     }
 
     public static String buildSqlString(final String originalSql, boolean laxDeparsingCheck) {
-        String sql = SQL_COMMENT_PATTERN.matcher(originalSql).replaceAll("");
         if (laxDeparsingCheck) {
-            return sql.replaceAll("\\s", " ").replaceAll("\\s+", " ").
-                    replaceAll("\\s*([!/,()=+\\-*|\\]<>])\\s*", "$1").toLowerCase().trim();
+            // remove comments
+            String sanitizedSqlStr = SQL_COMMENT_PATTERN.matcher(originalSql).replaceAll("");
+
+            // redundant white space
+            sanitizedSqlStr = SQL_SANITATION_PATTERN.matcher(sanitizedSqlStr).replaceAll(" ");
+
+            // replace some more stuff
+            sanitizedSqlStr = SQL_SANITATION_PATTERN2.matcher(sanitizedSqlStr).replaceAll("$1");
+            return sanitizedSqlStr.trim().toLowerCase();
         } else {
-            return sql;
+            // remove comments only
+            return SQL_COMMENT_PATTERN.matcher(originalSql).replaceAll("");
         }
     }
 
diff --git a/src/test/java/net/sf/jsqlparser/util/deparser/StatementDeParserTest.java b/src/test/java/net/sf/jsqlparser/util/deparser/StatementDeParserTest.java
@@ -23,11 +23,7 @@
 import net.sf.jsqlparser.statement.execute.Execute;
 import net.sf.jsqlparser.statement.insert.Insert;
 import net.sf.jsqlparser.statement.replace.Replace;
-import net.sf.jsqlparser.statement.select.OrderByElement;
-import net.sf.jsqlparser.statement.select.Select;
-import net.sf.jsqlparser.statement.select.SelectBody;
-import net.sf.jsqlparser.statement.select.SubSelect;
-import net.sf.jsqlparser.statement.select.WithItem;
+import net.sf.jsqlparser.statement.select.*;
 import net.sf.jsqlparser.statement.update.Update;
 import net.sf.jsqlparser.statement.update.UpdateSet;
 import net.sf.jsqlparser.statement.upsert.Upsert;
@@ -370,4 +366,19 @@ public void shouldUseProvidedDeparsersWhenDeParsingIfThenStatement() throws JSQL
         statementDeParser.deParse(ifElseStatement);
     }
 
+    @Test
+    public void testIssue1500AllColumns() throws JSQLParserException {
+        String sqlStr = "select count(*) from some_table";
+        Select select = (Select) CCJSqlParserUtil.parse(sqlStr);
+        PlainSelect selectBody = (PlainSelect) select.getSelectBody();
+        selectBody.accept(new SelectDeParser());
+    }
+
+    @Test
+    public void testIssue1500AllTableColumns() throws JSQLParserException {
+        String sqlStr = "select count(a.*) from some_table a";
+        Select select = (Select) CCJSqlParserUtil.parse(sqlStr);
+        PlainSelect selectBody = (PlainSelect) select.getSelectBody();
+        selectBody.accept(new SelectDeParser());
+    }
 }

Original file line number	Diff line number	Diff line change
`@@ -188,4 +188,6 @@ public interface ExpressionVisitor {`
`188`	`188`	`void visit(AllValue allValue);`
`189`	`189`
`190`	`190`	`void visit(IsDistinctExpression isDistinctExpression);`
	`191`	`+`
	`192`	`+ void visit(GeometryDistance geometryDistance);`
`191`	`193`	`}`
Original file line number	Diff line number	Diff line change
`@@ -492,12 +492,10 @@ public void visit(UnPivot unpivot) {`
`492`	`492`
`493`	`493`	`@Override`
`494`	`494`	`public void visit(AllColumns allColumns) {`
`495`		`- allColumns.accept((ExpressionVisitor) this);`
`496`	`495`	`}`
`497`	`496`
`498`	`497`	`@Override`
`499`	`498`	`public void visit(AllTableColumns allTableColumns) {`
`500`		`- allTableColumns.accept((ExpressionVisitor) this);`
`501`	`499`	`}`
`502`	`500`
`503`	`501`	`@Override`
`@@ -634,7 +632,12 @@ public void visit(ConnectByRootOperator connectByRootOperator) {`
`634`	`632`	`public void visit(OracleNamedFunctionParameter oracleNamedFunctionParameter) {`
`635`	`633`	`oracleNamedFunctionParameter.getExpression().accept(this);`
`636`	`634`	`}`
`637`		`-`
	`635`	`+`
	`636`	`+ @Override`
	`637`	`+ public void visit(GeometryDistance geometryDistance) {`
	`638`	`+ visitBinaryExpression(geometryDistance);`
	`639`	`+ }`
	`640`	`+`
`638`	`641`	`public void visit(ColumnDefinition columnDefinition) {`
`639`	`642`	`columnDefinition.accept(this);`
`640`	`643`	`}`
Original file line number	Diff line number	Diff line change
`@@ -1031,4 +1031,9 @@ public void visit(PurgeStatement purgeStatement) {`
`1031`	`1031`	`public void visit(AlterSystemStatement alterSystemStatement) {`
`1032`	`1032`	`// no tables involved in this statement`
`1033`	`1033`	`}`
	`1034`	`+`
	`1035`	`+ @Override`
	`1036`	`+ public void visit(GeometryDistance geometryDistance) {`
	`1037`	`+ visitBinaryExpression(geometryDistance);`
	`1038`	`+ }`
`1034`	`1039`	`}`