feat: RedShift allows TOP before DISTINCT, see https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html

manticore-projects · manticore-projects · commit 13e61a726a87 · 2024-04-20T20:45:34.000+07:00
Signed-off-by: Andreas Reichel &lt;andreas@manticore-projects.com&gt;
diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -2342,6 +2342,11 @@ PlainSelect PlainSelect() #PlainSelect:
 
     [ LOOKAHEAD(2) first = First() { plainSelect.setFirst(first); } ]
 
+    // Redshift allows TOP before DISTINCT
+    // https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html
+    // @Todo: reflect the order when de-parsing
+    [ LOOKAHEAD(2) top = Top() { plainSelect.setTop(top);    } ]
+
     [ LOOKAHEAD(2)
         (
         <K_ALL>
diff --git a/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java b/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java
@@ -16,6 +16,7 @@
 import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
 import static net.sf.jsqlparser.test.TestUtils.assertStatementCanBeDeparsedAs;
 import static org.assertj.core.api.Assertions.assertThat;
+import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
 import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertFalse;
 import static org.junit.jupiter.api.Assertions.assertInstanceOf;
@@ -24,7 +25,6 @@
 import static org.junit.jupiter.api.Assertions.assertNull;
 import static org.junit.jupiter.api.Assertions.assertSame;
 import static org.junit.jupiter.api.Assertions.assertTrue;
-import static org.junit.jupiter.api.Assertions.fail;
 
 import java.io.IOException;
 import java.io.StringReader;
@@ -1080,13 +1080,15 @@ public void testDistinctTop() throws JSQLParserException {
 
     @Test
     public void testDistinctTop2() {
-        String statement = "SELECT TOP 5 DISTINCT myid, mycol FROM mytable WHERE mytable.col = 9";
-        try {
-            parserManager.parse(new StringReader(statement));
-            fail("sould not work");
-        } catch (JSQLParserException ex) {
-            // expected to fail
-        }
+        // valid on Redshift
+        // https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html
+        String sqlStr = "SELECT TOP 5 DISTINCT myid, mycol FROM mytable WHERE mytable.col = 9";
+        assertDoesNotThrow(new Executable() {
+            @Override
+            public void execute() throws Throwable {
+                CCJSqlParserUtil.parse(sqlStr);
+            }
+        });
     }
 
     @Test
