Repository: stephenafamo/bob (go)

Issue:
Subqueries in select lists should be wrapped in parenthesis
This is a [documentation I found about it](https://www.w3resource.com/PostgreSQL/postgresql-subqueries.php).

```
A subquery must be enclosed in parentheses. 
```

```go
psql.Select(
    sm.Columns("id"),
    sm.Columns(psql.Select(
        sm.Columns("address"),
        sm.From("users_address ua"),
        sm.Where("users.id = ua.id"),
    )),
    sm.From("users"),
    sm.Where(psql.Quote("id").In(psql.Arg(100, 200, 300))),
)
```

output query:

```sql
SELECT 
id, SELECT 
address
FROM users_address ua
WHERE users.id = ua.id

FROM users
WHERE ("id" IN ($1, $2, $3))
```


Discussion:

Patch:
diff --git a/clause/from.go b/clause/from.go
--- a/clause/from.go
+++ b/clause/from.go
@@ -99,17 +99,10 @@ func (f From) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
 		w.Write([]byte("LATERAL "))
 	}
 
-	_, isQuery := f.Table.(bob.Query)
-	if isQuery {
-		w.Write([]byte("("))
-	}
 	args, err := bob.Express(w, d, start, f.Table)
 	if err != nil {
 		return nil, err
 	}
-	if isQuery {
-		w.Write([]byte(")"))
-	}
 
 	if f.WithOrdinality {
 		w.Write([]byte(" WITH ORDINALITY"))
diff --git a/dialect/mysql/dialect/insert.go b/dialect/mysql/dialect/insert.go
--- a/dialect/mysql/dialect/insert.go
+++ b/dialect/mysql/dialect/insert.go
@@ -71,18 +71,18 @@ func (i InsertQuery) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, err
 	}
 
 	// Either this or the values will get expressed
-	valArgs, err := bob.ExpressSlice(w, d, start+len(args), i.Sets, "\nSET ", "\n", " ")
+	setArgs, err := bob.ExpressSlice(w, d, start+len(args), i.Sets, "\nSET ", "\n", " ")
 	if err != nil {
 		return nil, err
 	}
-	args = append(args, valArgs...)
+	args = append(args, setArgs...)
 
 	// Either this or SET will get expressed
-	setArgs, err := bob.ExpressIf(w, d, start+len(args), i.Values, len(i.Sets) == 0, "\n", " ")
+	valArgs, err := bob.ExpressIf(w, d, start+len(args), i.Values, len(i.Sets) == 0, "\n", " ")
 	if err != nil {
 		return nil, err
 	}
-	args = append(args, setArgs...)
+	args = append(args, valArgs...)
 
 	// The aliases
 	if i.RowAlias != "" {
@@ -104,7 +104,6 @@ func (i InsertQuery) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, err
 		}
 	}
 
-	// Either this or the values will get expressed
 	updateArgs, err := bob.ExpressSlice(w, d, start+len(args), i.DuplicateKeyUpdate,
 		"\nON DUPLICATE KEY UPDATE\n", ",\n", "")
 	if err != nil {
diff --git a/query.go b/query.go
--- a/query.go
+++ b/query.go
@@ -9,6 +9,12 @@ import (
 	"github.com/stephenafamo/scan"
 )
 
+// To pervent unnecessary allocations
+const (
+	openPar  = "("
+	closePar = ")"
+)
+
 type Query interface {
 	// It should satisfy the Expression interface so that it can be used
 	// in places such as a sub-select
@@ -84,7 +90,11 @@ func (b BaseQuery[E]) WriteQuery(w io.Writer, start int) ([]any, error) {
 // Satisfies the Expression interface, but uses its own dialect instead
 // of the dialect passed to it
 func (b BaseQuery[E]) WriteSQL(w io.Writer, _ Dialect, start int) ([]any, error) {
-	return b.Expression.WriteSQL(w, b.Dialect, start)
+	w.Write([]byte(openPar))
+	args, err := b.Expression.WriteSQL(w, b.Dialect, start)
+	w.Write([]byte(closePar))
+
+	return args, err
 }
 
 // MustBuild builds the query and panics on error
diff --git a/website/docs/query-builder/mysql/examples/insert.md b/website/docs/query-builder/mysql/examples/insert.md
--- a/website/docs/query-builder/mysql/examples/insert.md
+++ b/website/docs/query-builder/mysql/examples/insert.md
@@ -26,6 +26,30 @@ mysql.Insert(
 )
 ```
 
+## Insert From Select
+
+SQL:
+
+```sql
+INSERT INTO films SELECT * FROM tmp_films WHERE (`date_prod` < ?)
+```
+
+Args:
+
+* `"1971-07-13"`
+
+Code:
+
+```go
+mysql.Insert(
+  im.Into("films"),
+  im.Query(mysql.Select(
+    sm.From("tmp_films"),
+    sm.Where(mysql.Quote("date_prod").LT(mysql.Arg("1971-07-13"))),
+  )),
+)
+```
+
 ## Bulk Insert
 
 SQL:
diff --git a/website/docs/query-builder/mysql/examples/update.md b/website/docs/query-builder/mysql/examples/update.md
--- a/website/docs/query-builder/mysql/examples/update.md
+++ b/website/docs/query-builder/mysql/examples/update.md
@@ -91,10 +91,10 @@ Code:
 mysql.Update(
   um.Table("employees"),
   um.Set("sales_count").To("sales_count + 1"),
-  um.Where(mysql.Quote("id").EQ(mysql.Group(mysql.Select(
+  um.Where(mysql.Quote("id").EQ(mysql.Select(
     sm.Columns("sales_person"),
     sm.From("accounts"),
     sm.Where(mysql.Quote("name").EQ(mysql.Arg("Acme Corporation"))),
-  )))),
+  ))),
 )
 ```
diff --git a/website/docs/query-builder/psql/examples/insert.md b/website/docs/query-builder/psql/examples/insert.md
--- a/website/docs/query-builder/psql/examples/insert.md
+++ b/website/docs/query-builder/psql/examples/insert.md
@@ -26,6 +26,30 @@ psql.Insert(
 )
 ```
 
+## Insert From Select
+
+SQL:
+
+```sql
+INSERT INTO films SELECT * FROM tmp_films WHERE "date_prod" < $1
+```
+
+Args:
+
+* `"1971-07-13"`
+
+Code:
+
+```go
+psql.Insert(
+  im.Into("films"),
+  im.Query(psql.Select(
+    sm.From("tmp_films"),
+    sm.Where(psql.Quote("date_prod").LT(psql.Arg("1971-07-13"))),
+  )),
+)
+```
+
 ## Bulk Insert
 
 SQL:
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
@@ -188,3 +188,27 @@ psql.Select(
       In(psql.ArgGroup(100, 200), psql.ArgGroup(300, 400))),
 )
 ```
+
+## Simple select with limit and offset as argument
+
+SQL:
+
+```sql
+SELECT id, name FROM users LIMIT $1 OFFSET $2
+```
+
+Args:
+
+* `10`
+* `15`
+
+Code:
+
+```go
+psql.Select(
+  sm.Columns("id", "name"),
+  sm.From("users"),
+  sm.Offset(psql.Arg(15)),
+  sm.Limit(psql.Arg(10)),
+)
+```
diff --git a/website/docs/query-builder/sqlite/examples/insert.md b/website/docs/query-builder/sqlite/examples/insert.md
--- a/website/docs/query-builder/sqlite/examples/insert.md
+++ b/website/docs/query-builder/sqlite/examples/insert.md
@@ -26,6 +26,30 @@ sqlite.Insert(
 )
 ```
 
+## Insert From Select
+
+SQL:
+
+```sql
+INSERT INTO films SELECT * FROM tmp_films WHERE ("date_prod" < ?1)
+```
+
+Args:
+
+* `"1971-07-13"`
+
+Code:
+
+```go
+sqlite.Insert(
+  im.Into("films"),
+  im.Query(sqlite.Select(
+    sm.From("tmp_films"),
+    sm.Where(sqlite.Quote("date_prod").LT(sqlite.Arg("1971-07-13"))),
+  )),
+)
+```
+
 ## Bulk Insert
 
 SQL:
diff --git a/website/docs/query-builder/sqlite/examples/update.md b/website/docs/query-builder/sqlite/examples/update.md
--- a/website/docs/query-builder/sqlite/examples/update.md
+++ b/website/docs/query-builder/sqlite/examples/update.md
@@ -70,10 +70,10 @@ sqlite.Update(
   um.TableAs("employees", "e"),
   um.TableNotIndexed(),
   um.Set("sales_count").To("sales_count + 1"),
-  um.Where(sqlite.Quote("id").EQ(sqlite.Group(sqlite.Select(
+  um.Where(sqlite.Quote("id").EQ(sqlite.Select(
     sm.Columns("sales_person"),
     sm.From("accounts"),
     sm.Where(sqlite.Quote("name").EQ(sqlite.Arg("Acme Corporation"))),
-  )))),
+  ))),
 )
 ```


Test patch:
diff --git a/dialect/mysql/insert_test.go b/dialect/mysql/insert_test.go
--- a/dialect/mysql/insert_test.go
+++ b/dialect/mysql/insert_test.go
@@ -5,6 +5,7 @@ import (
 
 	"github.com/stephenafamo/bob/dialect/mysql"
 	"github.com/stephenafamo/bob/dialect/mysql/im"
+	"github.com/stephenafamo/bob/dialect/mysql/sm"
 	testutils "github.com/stephenafamo/bob/test_utils"
 )
 
@@ -18,6 +19,17 @@ func TestInsert(t *testing.T) {
 			ExpectedSQL:  "INSERT INTO films VALUES (?, ?, ?, ?, ?, ?)",
 			ExpectedArgs: []any{"UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 mins"},
 		},
+		"insert from select": {
+			Query: mysql.Insert(
+				im.Into("films"),
+				im.Query(mysql.Select(
+					sm.From("tmp_films"),
+					sm.Where(mysql.Quote("date_prod").LT(mysql.Arg("1971-07-13"))),
+				)),
+			),
+			ExpectedSQL:  "INSERT INTO films SELECT * FROM tmp_films WHERE (`date_prod` < ?)",
+			ExpectedArgs: []any{"1971-07-13"},
+		},
 		"bulk insert": {
 			Query: mysql.Insert(
 				im.Into("films"),
diff --git a/dialect/mysql/update_test.go b/dialect/mysql/update_test.go
--- a/dialect/mysql/update_test.go
+++ b/dialect/mysql/update_test.go
@@ -48,11 +48,11 @@ func TestUpdate(t *testing.T) {
 			Query: mysql.Update(
 				um.Table("employees"),
 				um.Set("sales_count").To("sales_count + 1"),
-				um.Where(mysql.Quote("id").EQ(mysql.Group(mysql.Select(
+				um.Where(mysql.Quote("id").EQ(mysql.Select(
 					sm.Columns("sales_person"),
 					sm.From("accounts"),
 					sm.Where(mysql.Quote("name").EQ(mysql.Arg("Acme Corporation"))),
-				)))),
+				))),
 			),
 		},
 	}
diff --git a/dialect/psql/insert_test.go b/dialect/psql/insert_test.go
--- a/dialect/psql/insert_test.go
+++ b/dialect/psql/insert_test.go
@@ -5,6 +5,7 @@ import (
 
 	"github.com/stephenafamo/bob/dialect/psql"
 	"github.com/stephenafamo/bob/dialect/psql/im"
+	"github.com/stephenafamo/bob/dialect/psql/sm"
 	testutils "github.com/stephenafamo/bob/test_utils"
 )
 
@@ -18,6 +19,17 @@ func TestInsert(t *testing.T) {
 			ExpectedSQL:  "INSERT INTO films VALUES ($1, $2, $3, $4, $5, $6)",
 			ExpectedArgs: []any{"UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 mins"},
 		},
+		"insert from select": {
+			Query: psql.Insert(
+				im.Into("films"),
+				im.Query(psql.Select(
+					sm.From("tmp_films"),
+					sm.Where(psql.Quote("date_prod").LT(psql.Arg("1971-07-13"))),
+				)),
+			),
+			ExpectedSQL:  `INSERT INTO films SELECT * FROM tmp_films WHERE "date_prod" < $1`,
+			ExpectedArgs: []any{"1971-07-13"},
+		},
 		"bulk insert": {
 			Query: psql.Insert(
 				im.Into("films"),
diff --git a/dialect/sqlite/insert_test.go b/dialect/sqlite/insert_test.go
--- a/dialect/sqlite/insert_test.go
+++ b/dialect/sqlite/insert_test.go
@@ -5,6 +5,7 @@ import (
 
 	"github.com/stephenafamo/bob/dialect/sqlite"
 	"github.com/stephenafamo/bob/dialect/sqlite/im"
+	"github.com/stephenafamo/bob/dialect/sqlite/sm"
 	testutils "github.com/stephenafamo/bob/test_utils"
 )
 
@@ -18,6 +19,17 @@ func TestInsert(t *testing.T) {
 			ExpectedSQL:  "INSERT INTO films VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
 			ExpectedArgs: []any{"UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 mins"},
 		},
+		"insert from select": {
+			Query: sqlite.Insert(
+				im.Into("films"),
+				im.Query(sqlite.Select(
+					sm.From("tmp_films"),
+					sm.Where(sqlite.Quote("date_prod").LT(sqlite.Arg("1971-07-13"))),
+				)),
+			),
+			ExpectedSQL:  `INSERT INTO films SELECT * FROM tmp_films WHERE ("date_prod" < ?1)`,
+			ExpectedArgs: []any{"1971-07-13"},
+		},
 		"bulk insert": {
 			Query: sqlite.Insert(
 				im.Into("films"),
diff --git a/dialect/sqlite/update_test.go b/dialect/sqlite/update_test.go
--- a/dialect/sqlite/update_test.go
+++ b/dialect/sqlite/update_test.go
@@ -43,11 +43,11 @@ func TestUpdate(t *testing.T) {
 				um.TableAs("employees", "e"),
 				um.TableNotIndexed(),
 				um.Set("sales_count").To("sales_count + 1"),
-				um.Where(sqlite.Quote("id").EQ(sqlite.Group(sqlite.Select(
+				um.Where(sqlite.Quote("id").EQ(sqlite.Select(
 					sm.Columns("sales_person"),
 					sm.From("accounts"),
 					sm.Where(sqlite.Quote("name").EQ(sqlite.Arg("Acme Corporation"))),
-				)))),
+				))),
 			),
 		},
 	}