feat: RedShift specific Window function IGNORE | RESPECT NULLS

manticore-projects · manticore-projects · commit 321c88098a75 · 2024-04-21T01:57:02.000+07:00
Signed-off-by: Andreas Reichel &lt;andreas@manticore-projects.com&gt;
diff --git a/src/main/java/net/sf/jsqlparser/expression/AnalyticExpression.java b/src/main/java/net/sf/jsqlparser/expression/AnalyticExpression.java
@@ -277,7 +277,7 @@ public String toString() {
             havingClause.appendTo(b);
         }
 
-        if (nullHandling != null) {
+        if (nullHandling != null && !ignoreNullsOutside) {
             switch (nullHandling) {
                 case IGNORE_NULLS:
                     b.append(" IGNORE NULLS");
@@ -287,6 +287,7 @@ public String toString() {
                     break;
             }
         }
+
         if (funcOrderBy != null) {
             b.append(" ORDER BY ");
             b.append(funcOrderBy.stream().map(OrderByElement::toString).collect(joining(", ")));
@@ -310,8 +311,15 @@ public String toString() {
             }
         }
 
-        if (isIgnoreNullsOutside()) {
-            b.append("IGNORE NULLS ");
+        if (nullHandling != null && ignoreNullsOutside) {
+            switch (nullHandling) {
+                case IGNORE_NULLS:
+                    b.append(" IGNORE NULLS ");
+                    break;
+                case RESPECT_NULLS:
+                    b.append(" RESPECT NULLS ");
+                    break;
+            }
         }
 
         switch (type) {
diff --git a/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java b/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java
@@ -823,7 +823,7 @@ public void visit(AnalyticExpression aexpr) {
             havingClause.getExpression().accept(this);
         }
 
-        if (aexpr.getNullHandling() != null) {
+        if (aexpr.getNullHandling() != null && !aexpr.isIgnoreNullsOutside()) {
             switch (aexpr.getNullHandling()) {
                 case IGNORE_NULLS:
                     buffer.append(" IGNORE NULLS");
@@ -858,8 +858,15 @@ public void visit(AnalyticExpression aexpr) {
             }
         }
 
-        if (aexpr.isIgnoreNullsOutside()) {
-            buffer.append("IGNORE NULLS ");
+        if (aexpr.getNullHandling() != null && aexpr.isIgnoreNullsOutside()) {
+            switch (aexpr.getNullHandling()) {
+                case IGNORE_NULLS:
+                    buffer.append(" IGNORE NULLS ");
+                    break;
+                case RESPECT_NULLS:
+                    buffer.append(" RESPECT NULLS ");
+                    break;
+            }
         }
 
         switch (aexpr.getType()) {
diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -69,10 +69,14 @@ import net.sf.jsqlparser.statement.grant.*;
 import java.util.*;
 import java.util.AbstractMap.SimpleEntry;
 
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
 /**
  * The parser generated by JavaCC
  */
 public class CCJSqlParser extends AbstractJSqlParser<CCJSqlParser> {
+    public final static Logger LOGGER = Logger.getLogger(CCJSqlParser.class.getName());
     public int bracketsCounter = 0;
     public int caseCounter = 0;
     public boolean interrupted = false;
@@ -5050,7 +5054,16 @@ void windowFun(AnalyticExpression retval):{
      WindowDefinition winDef;
 } {
     (
-        [<K_IGNORE> <K_NULLS> { retval.setIgnoreNullsOutside(true); } ]
+        [
+            (
+                <K_IGNORE> <K_NULLS> { retval.setNullHandling(Function.NullHandling.IGNORE_NULLS); retval.setIgnoreNullsOutside(true); }
+            )
+            |
+            (
+                 <K_RESPECT> <K_NULLS> {  retval.setNullHandling(Function.NullHandling.RESPECT_NULLS); retval.setIgnoreNullsOutside(true); }
+            )
+        ]
+
         <K_OVER> {retval.setType(AnalyticType.OVER);}
         |
         <K_WITHIN> <K_GROUP> {retval.setType(AnalyticType.WITHIN_GROUP);}
diff --git a/src/test/java/net/sf/jsqlparser/statement/select/WindowFunctionTest.java b/src/test/java/net/sf/jsqlparser/statement/select/WindowFunctionTest.java
@@ -18,8 +18,23 @@ public class WindowFunctionTest {
     public void testListAggOverIssue1652() throws JSQLParserException {
         String sqlString =
                 "SELECT\n" +
-                "    LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) OVER (PARTITION BY d.PART_COL) AS MY_LISTAGG\n" +
-                "FROM cte_dummy_data d";
+                        "    LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) OVER (PARTITION BY d.PART_COL) AS MY_LISTAGG\n"
+                        +
+                        "FROM cte_dummy_data d";
+
+        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlString, true);
+    }
+
+    @Test
+    public void RedshiftRespectIgnoreNulls() throws JSQLParserException {
+        String sqlString =
+                "select venuestate, venueseats, venuename,\n"
+                        + "first_value(venuename) ignore nulls\n"
+                        + "over(partition by venuestate\n"
+                        + "order by venueseats desc\n"
+                        + "rows between unbounded preceding and unbounded following) AS first\n"
+                        + "from (select * from venue where venuestate='CA')\n"
+                        + "order by venuestate;";
 
         TestUtils.assertSqlCanBeParsedAndDeparsed(sqlString, true);
     }

Original file line number	Diff line number	Diff line change
`@@ -277,7 +277,7 @@ public String toString() {`
`277`	`277`	`havingClause.appendTo(b);`
`278`	`278`	`}`
`279`	`279`
`280`		`- if (nullHandling != null) {`
	`280`	`+ if (nullHandling != null && !ignoreNullsOutside) {`
`281`	`281`	`switch (nullHandling) {`
`282`	`282`	`case IGNORE_NULLS:`
`283`	`283`	`b.append(" IGNORE NULLS");`
`@@ -287,6 +287,7 @@ public String toString() {`
`287`	`287`	`break;`
`288`	`288`	`}`
`289`	`289`	`}`
	`290`	`+`
`290`	`291`	`if (funcOrderBy != null) {`
`291`	`292`	`b.append(" ORDER BY ");`
`292`	`293`	`b.append(funcOrderBy.stream().map(OrderByElement::toString).collect(joining(", ")));`
`@@ -310,8 +311,15 @@ public String toString() {`
`310`	`311`	`}`
`311`	`312`	`}`
`312`	`313`
`313`		`- if (isIgnoreNullsOutside()) {`
`314`		`- b.append("IGNORE NULLS ");`
	`314`	`+ if (nullHandling != null && ignoreNullsOutside) {`
	`315`	`+ switch (nullHandling) {`
	`316`	`+ case IGNORE_NULLS:`
	`317`	`+ b.append(" IGNORE NULLS ");`
	`318`	`+ break;`
	`319`	`+ case RESPECT_NULLS:`
	`320`	`+ b.append(" RESPECT NULLS ");`
	`321`	`+ break;`
	`322`	`+ }`
`315`	`323`	`}`
`316`	`324`
`317`	`325`	`switch (type) {`
Original file line number	Diff line number	Diff line change
`@@ -823,7 +823,7 @@ public void visit(AnalyticExpression aexpr) {`
`823`	`823`	`havingClause.getExpression().accept(this);`
`824`	`824`	`}`
`825`	`825`
`826`		`- if (aexpr.getNullHandling() != null) {`
	`826`	`+ if (aexpr.getNullHandling() != null && !aexpr.isIgnoreNullsOutside()) {`
`827`	`827`	`switch (aexpr.getNullHandling()) {`
`828`	`828`	`case IGNORE_NULLS:`
`829`	`829`	`buffer.append(" IGNORE NULLS");`
`@@ -858,8 +858,15 @@ public void visit(AnalyticExpression aexpr) {`
`858`	`858`	`}`
`859`	`859`	`}`
`860`	`860`
`861`		`- if (aexpr.isIgnoreNullsOutside()) {`
`862`		`- buffer.append("IGNORE NULLS ");`
	`861`	`+ if (aexpr.getNullHandling() != null && aexpr.isIgnoreNullsOutside()) {`
	`862`	`+ switch (aexpr.getNullHandling()) {`
	`863`	`+ case IGNORE_NULLS:`
	`864`	`+ buffer.append(" IGNORE NULLS ");`
	`865`	`+ break;`
	`866`	`+ case RESPECT_NULLS:`
	`867`	`+ buffer.append(" RESPECT NULLS ");`
	`868`	`+ break;`
	`869`	`+ }`
`863`	`870`	`}`
`864`	`871`
`865`	`872`	`switch (aexpr.getType()) {`