Repository: stephenafamo/bob (go)

Issue:
[psql] Issues with window functions
The `window` argument to `Function.Over` is unused. 
https://github.com/stephenafamo/bob/blob/main/dialect/psql/dialect/function.go#L76

Additionally, `OVER ()` with an empty window definition is a valid statement, at least for PostgreSQL but is currently not possible due to `wr.def.Valid()` 
https://github.com/stephenafamo/bob/blob/main/dialect/psql/dialect/function.go#L122





Discussion:

Patch:
diff --git a/clause/window.go b/clause/window.go
--- a/clause/window.go
+++ b/clause/window.go
@@ -23,26 +23,6 @@ type WindowDef struct {
 	Frame
 }
 
-func (wi WindowDef) Valid() bool {
-	if wi.From != "" {
-		return true
-	}
-
-	if len(wi.orderBy) != 0 {
-		return true
-	}
-
-	if len(wi.partitionBy) != 0 {
-		return true
-	}
-
-	if wi.Frame.Defined {
-		return true
-	}
-
-	return false
-}
-
 func (wi *WindowDef) SetFrom(from string) {
 	wi.From = from
 }
diff --git a/dialect/mysql/dialect/function.go b/dialect/mysql/dialect/function.go
--- a/dialect/mysql/dialect/function.go
+++ b/dialect/mysql/dialect/function.go
@@ -34,7 +34,7 @@ func (f *Function) Filter(e ...any) *Function {
 	return f
 }
 
-func (f *Function) Over(window string) *functionOver {
+func (f *Function) Over() *functionOver {
 	fo := &functionOver{
 		function: f,
 	}
@@ -77,7 +77,7 @@ func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any,
 		return nil, err
 	}
 
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, wr.def.Valid(), "OVER (", ")")
+	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
 	if err != nil {
 		return nil, err
 	}
diff --git a/dialect/psql/dialect/function.go b/dialect/psql/dialect/function.go
--- a/dialect/psql/dialect/function.go
+++ b/dialect/psql/dialect/function.go
@@ -73,7 +73,7 @@ func (f *Function) FilterWhere(e ...any) *functionOver {
 	return fo
 }
 
-func (f *Function) Over(window string) *functionOver {
+func (f *Function) Over() *functionOver {
 	fo := &functionOver{
 		function: f,
 	}
@@ -119,7 +119,7 @@ func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any,
 		return nil, err
 	}
 
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, wr.def.Valid(), "OVER (", ")")
+	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
 	if err != nil {
 		return nil, err
 	}
diff --git a/dialect/sqlite/dialect/function.go b/dialect/sqlite/dialect/function.go
--- a/dialect/sqlite/dialect/function.go
+++ b/dialect/sqlite/dialect/function.go
@@ -34,7 +34,7 @@ func (f *Function) Filter(e ...any) *Function {
 	return f
 }
 
-func (f *Function) Over(window string) *functionOver {
+func (f *Function) Over() *functionOver {
 	fo := &functionOver{
 		function: f,
 	}
@@ -77,7 +77,7 @@ func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any,
 		return nil, err
 	}
 
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, wr.def.Valid(), "OVER (", ")")
+	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
 	if err != nil {
 		return nil, err
 	}
diff --git a/website/docs/query-builder/mysql/examples/select.md b/website/docs/query-builder/mysql/examples/select.md
--- a/website/docs/query-builder/mysql/examples/select.md
+++ b/website/docs/query-builder/mysql/examples/select.md
@@ -76,7 +76,7 @@ mysql.Select(
     sm.Columns(
       "status",
       mysql.F("LEAD", "created_date", 1, mysql.F("NOW")).
-        Over("").
+        Over().
         PartitionBy("presale_id").
         OrderBy("created_date").
         Minus(mysql.Quote("created_date")).
diff --git a/website/docs/query-builder/principles.md b/website/docs/query-builder/principles.md
--- a/website/docs/query-builder/principles.md
+++ b/website/docs/query-builder/principles.md
@@ -43,7 +43,7 @@ sm.Window("").PartitionBy("presale_id").OrderBy("created_date")
 // Expressing LEAD(...) OVER(...)
 "LEAD(created_date, 1, NOW()) OVER(PARTITION BY presale_id ORDER BY created_date)"
 psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-    Over("").
+    Over().
     PartitionBy("presale_id").
     OrderBy("created_date")
 
@@ -52,7 +52,7 @@ psql.Select(
     sm.Columns(
         "status",
         psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-            Over("").
+            Over().
             PartitionBy("presale_id").
             OrderBy("created_date").
             Minus("created_date").
diff --git a/website/docs/query-builder/psql/examples/select.md b/website/docs/query-builder/psql/examples/select.md
--- a/website/docs/query-builder/psql/examples/select.md
+++ b/website/docs/query-builder/psql/examples/select.md
@@ -150,7 +150,7 @@ psql.Select(
     sm.Columns(
       "status",
       psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-        Over("").
+        Over().
         PartitionBy("presale_id").
         OrderBy("created_date").
         Minus(psql.Quote("created_date")).
@@ -251,3 +251,44 @@ psql.Select(
   sm.From("c"),
 )
 ```
+
+## Window Function Over Empty Frame
+
+SQL:
+
+```sql
+SELECT row_number() OVER () FROM c
+```
+
+Code:
+
+```go
+psql.Select(
+  sm.Columns(
+    psql.F("row_number").Over(),
+  ),
+  sm.From("c"),
+)
+```
+
+## Window Function Over Window Name
+
+SQL:
+
+```sql
+SELECT avg(salary) OVER (w)
+FROM c 
+WINDOW w AS (PARTITION BY depname ORDER BY salary)
+```
+
+Code:
+
+```go
+psql.Select(
+  sm.Columns(
+    psql.F("avg", "salary").Over().From("w"),
+  ),
+  sm.From("c"),
+  sm.Window("w").PartitionBy("depname").OrderBy("salary"),
+)
+```
diff --git a/website/docs/query-builder/sqlite/examples/select.md b/website/docs/query-builder/sqlite/examples/select.md
--- a/website/docs/query-builder/sqlite/examples/select.md
+++ b/website/docs/query-builder/sqlite/examples/select.md
@@ -92,7 +92,7 @@ sqlite.Select(
     sm.Columns(
       "status",
       sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW")).
-        Over("").
+        Over().
         PartitionBy("presale_id").
         OrderBy("created_date").
         Minus(sqlite.Quote("created_date")).


Test patch:
diff --git a/dialect/mysql/select_test.go b/dialect/mysql/select_test.go
--- a/dialect/mysql/select_test.go
+++ b/dialect/mysql/select_test.go
@@ -50,7 +50,7 @@ func TestSelect(t *testing.T) {
 					sm.Columns(
 						"status",
 						mysql.F("LEAD", "created_date", 1, mysql.F("NOW")).
-							Over("").
+							Over().
 							PartitionBy("presale_id").
 							OrderBy("created_date").
 							Minus(mysql.Quote("created_date")).
diff --git a/dialect/psql/select_test.go b/dialect/psql/select_test.go
--- a/dialect/psql/select_test.go
+++ b/dialect/psql/select_test.go
@@ -88,7 +88,7 @@ func TestSelect(t *testing.T) {
 					sm.Columns(
 						"status",
 						psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-							Over("").
+							Over().
 							PartitionBy("presale_id").
 							OrderBy("created_date").
 							Minus(psql.Quote("created_date")).
@@ -140,6 +140,27 @@ func TestSelect(t *testing.T) {
 				sm.From("c"),
 			),
 		},
+		"Window function over empty frame": {
+			ExpectedSQL: "SELECT row_number() OVER () FROM c",
+			Query: psql.Select(
+				sm.Columns(
+					psql.F("row_number").Over(),
+				),
+				sm.From("c"),
+			),
+		},
+		"Window function over window name": {
+			ExpectedSQL: `SELECT avg(salary) OVER (w)
+FROM c 
+WINDOW w AS (PARTITION BY depname ORDER BY salary)`,
+			Query: psql.Select(
+				sm.Columns(
+					psql.F("avg", "salary").Over().From("w"),
+				),
+				sm.From("c"),
+				sm.Window("w").PartitionBy("depname").OrderBy("salary"),
+			),
+		},
 	}
 
 	testutils.RunTests(t, examples, formatter)
diff --git a/dialect/sqlite/select_test.go b/dialect/sqlite/select_test.go
--- a/dialect/sqlite/select_test.go
+++ b/dialect/sqlite/select_test.go
@@ -57,7 +57,7 @@ func TestSelect(t *testing.T) {
 					sm.Columns(
 						"status",
 						sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW")).
-							Over("").
+							Over().
 							PartitionBy("presale_id").
 							OrderBy("created_date").
 							Minus(sqlite.Quote("created_date")).