Repository: sqlc-dev/sqlc
Pull request #2976 (2023-11-14 17:21:40)

fix(codegen): Never generate return structs for :exec
Also validate `:batchone` commands to make sure they return values. Currently this causes a template error as seen here: https://play.sqlc.dev/p/bf5cff5c903377ae7d92efaeb063c8446b93fef7dac67188eb233f5f65095d41

Fixes https://github.com/sqlc-dev/sqlc/issues/2970


Patch:
diff --git a/docs/howto/overrides.md b/docs/howto/overrides.md
index def59bf30..9fd91bc08 100644
--- a/docs/howto/overrides.md
+++ b/docs/howto/overrides.md
@@ -32,11 +32,11 @@ sql:
 Each element in the `overrides` list has the following keys:
 
 - `db_type`:
-  - A database type to override. Find the full list of supported types in [postgresql_type.go](https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L12) or [mysql_type.go](https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/mysql_type.go#L12). Note that for Postgres you must use pg_catalog-prefixed names where available. And note that `db_type` and `column` are mutually exclusive.
+  - A database type to override. Find the full list of supported types in [postgresql_type.go](https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L12) or [mysql_type.go](https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/mysql_type.go#L12). Note that for Postgres you must use pg_catalog-prefixed names where available. `db_type` and `column` are mutually exclusive.
 - `column`:
-  - A column name to override. The value should be of the form `table.column` but you can also specify `schema.table.column` or `catalog.schema.table.column`. Note that `column` and `db_type` are mutually exclusive.
+  - A column name to override. The value should be of the form `table.column` but you can also specify `schema.table.column` or `catalog.schema.table.column`. `column` and `db_type` are mutually exclusive.
 - `go_type`:
-  - The fully-qualified name of a Go type to use in generated code. This is usually a string but can also be [a map](#the-go_type-map) for more complex configurations.
+  - The fully-qualified name of a Go type to use in generated code. This is usually a string but can also be [a map](#the-go-type-map) for more complex configurations.
 - `go_struct_tag`:
   - A reflect-style struct tag to use in generated code, e.g. `a:"b" x:"y,z"`.
     If you want `json` or `db` tags for all fields, use `emit_json_tags` or `emit_db_tags` instead.
diff --git a/internal/codegen/golang/result.go b/internal/codegen/golang/result.go
index b98c578c5..787329dea 100644
--- a/internal/codegen/golang/result.go
+++ b/internal/codegen/golang/result.go
@@ -312,16 +312,16 @@ func buildQueries(req *plugin.GenerateRequest, options *opts.Options, structs []
 	return qs, nil
 }
 
+var cmdReturnsData = map[string]struct{}{
+	metadata.CmdBatchMany: {},
+	metadata.CmdBatchOne:  {},
+	metadata.CmdMany:      {},
+	metadata.CmdOne:       {},
+}
+
 func putOutColumns(query *plugin.Query) bool {
-	if len(query.Columns) > 0 {
-		return true
-	}
-	for _, allowed := range []string{metadata.CmdMany, metadata.CmdOne, metadata.CmdBatchMany} {
-		if query.Cmd == allowed {
-			return true
-		}
-	}
-	return false
+	_, found := cmdReturnsData[query.Cmd]
+	return found
 }
 
 // It's possible that this method will generate duplicate JSON tag values
diff --git a/internal/sql/validate/cmd.go b/internal/sql/validate/cmd.go
index 7b9261e20..66e849de6 100644
--- a/internal/sql/validate/cmd.go
+++ b/internal/sql/validate/cmd.go
@@ -67,9 +67,11 @@ func Cmd(n ast.Node, name, cmd string) error {
 		return validateCopyfrom(n)
 	}
 	if (cmd == metadata.CmdBatchExec || cmd == metadata.CmdBatchMany) || cmd == metadata.CmdBatchOne {
-		return validateBatch(n)
+		if err := validateBatch(n); err != nil {
+			return err
+		}
 	}
-	if !(cmd == metadata.CmdMany || cmd == metadata.CmdOne) {
+	if !(cmd == metadata.CmdMany || cmd == metadata.CmdOne || cmd == metadata.CmdBatchMany || cmd == metadata.CmdBatchOne) {
 		return nil
 	}
 	var list *ast.List


Test patch:
diff --git a/internal/codegen/golang/result_test.go b/internal/codegen/golang/result_test.go
index 4156b848e..0c58525ec 100644
--- a/internal/codegen/golang/result_test.go
+++ b/internal/codegen/golang/result_test.go
@@ -50,7 +50,7 @@ func TestPutOutColumns_ForZeroColumns(t *testing.T) {
 		},
 		{
 			cmd:  metadata.CmdBatchOne,
-			want: false,
+			want: true,
 		},
 	}
 	for _, tc := range tests {
diff --git a/internal/endtoend/testdata/cte_left_join/postgresql/pgx/go/query.sql.go b/internal/endtoend/testdata/cte_left_join/postgresql/pgx/go/query.sql.go
index 776a4a958..7c7046bf2 100644
--- a/internal/endtoend/testdata/cte_left_join/postgresql/pgx/go/query.sql.go
+++ b/internal/endtoend/testdata/cte_left_join/postgresql/pgx/go/query.sql.go
@@ -28,11 +28,6 @@ FROM
 WHERE c1.name = $1
 `
 
-type BadQueryRow struct {
-	Name string
-	Bio  pgtype.Text
-}
-
 func (q *Queries) BadQuery(ctx context.Context, dollar_1 pgtype.Text) error {
 	_, err := q.db.Exec(ctx, badQuery, dollar_1)
 	return err
diff --git a/internal/endtoend/testdata/exec_no_return_struct/issue.md b/internal/endtoend/testdata/exec_no_return_struct/issue.md
new file mode 100644
index 000000000..1184d6e9e
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/issue.md
@@ -0,0 +1,1 @@
+https://github.com/sqlc-dev/sqlc/issues/2970
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/db.go b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/db.go
new file mode 100644
index 000000000..2568532b6
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.23.0
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
+)
+
+type DBTX interface {
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/models.go b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/models.go
new file mode 100644
index 000000000..385a932c1
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/models.go
@@ -0,0 +1,14 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.23.0
+
+package querytest
+
+import (
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+type Job struct {
+	TaskName string
+	LastRun  pgtype.Timestamptz
+}
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/query.sql.go b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/query.sql.go
new file mode 100644
index 000000000..b3ef23e25
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/go/query.sql.go
@@ -0,0 +1,30 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.23.0
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+)
+
+const resetTaskRun = `-- name: ResetTaskRun :exec
+UPDATE job
+SET last_run = NOW()
+FROM (
+        SELECT last_run,
+            task_name
+        FROM job AS o
+        WHERE o.task_name = $1 
+  		FOR UPDATE
+    ) AS old_values
+WHERE job.task_name = $1
+RETURNING job.last_run AS this_run,
+    old_values.last_run AS last_run
+`
+
+func (q *Queries) ResetTaskRun(ctx context.Context, taskName string) error {
+	_, err := q.db.Exec(ctx, resetTaskRun, taskName)
+	return err
+}
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/query.sql b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/query.sql
new file mode 100644
index 000000000..968ce9f7c
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/query.sql
@@ -0,0 +1,13 @@
+-- name: ResetTaskRun :exec
+UPDATE job
+SET last_run = NOW()
+FROM (
+        SELECT last_run,
+            task_name
+        FROM job AS o
+        WHERE o.task_name = $1 
+  		FOR UPDATE
+    ) AS old_values
+WHERE job.task_name = $1
+RETURNING job.last_run AS this_run,
+    old_values.last_run AS last_run;
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/schema.sql b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/schema.sql
new file mode 100644
index 000000000..6dd162bed
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/schema.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS job
+(
+    task_name text NOT NULL,
+    last_run timestamp with time zone DEFAULT now() NOT NULL
+);
diff --git a/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/sqlc.yaml b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/sqlc.yaml
new file mode 100644
index 000000000..5dc63e3f9
--- /dev/null
+++ b/internal/endtoend/testdata/exec_no_return_struct/postgresql/pgx/sqlc.yaml
@@ -0,0 +1,10 @@
+version: "2"
+sql:
+  - engine: "postgresql"
+    schema: "schema.sql"
+    queries: "query.sql"
+    gen:
+      go:
+        package: "querytest"
+        out: "go"
+        sql_package: "pgx/v5"
diff --git a/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/query.sql b/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/query.sql
index 0e98fb622..06644a2cd 100644
--- a/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/query.sql
+++ b/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/query.sql
@@ -15,3 +15,6 @@ UPDATE foo SET id = $2 WHERE id = $1;
 
 -- name: InsertFoo :one
 INSERT INTO foo (id) VALUES ($1);
+
+-- name: InsertFoo :batchone
+INSERT INTO foo (id) VALUES ($1);
diff --git a/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/stderr.txt b/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/stderr.txt
index 06ec54327..8c745b7e3 100644
--- a/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/stderr.txt
+++ b/internal/endtoend/testdata/invalid_queries_foo/pgx/v4/stderr.txt
@@ -4,4 +4,5 @@ query.sql:5:1: invalid query comment: -- name: ListFoos :one :many
 query.sql:8:1: invalid query type: :two
 query.sql:11:1: query "DeleteFoo" specifies parameter ":one" without containing a RETURNING clause
 query.sql:14:1: query "UpdateFoo" specifies parameter ":one" without containing a RETURNING clause
-query.sql:17:1: query "InsertFoo" specifies parameter ":one" without containing a RETURNING clause
\ No newline at end of file
+query.sql:17:1: query "InsertFoo" specifies parameter ":one" without containing a RETURNING clause
+query.sql:20:1: query "InsertFoo" specifies parameter ":batchone" without containing a RETURNING clause
diff --git a/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/query.sql b/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/query.sql
index 0e98fb622..06644a2cd 100644
--- a/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/query.sql
+++ b/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/query.sql
@@ -15,3 +15,6 @@ UPDATE foo SET id = $2 WHERE id = $1;
 
 -- name: InsertFoo :one
 INSERT INTO foo (id) VALUES ($1);
+
+-- name: InsertFoo :batchone
+INSERT INTO foo (id) VALUES ($1);
diff --git a/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/stderr.txt b/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/stderr.txt
index 06ec54327..8c745b7e3 100644
--- a/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/stderr.txt
+++ b/internal/endtoend/testdata/invalid_queries_foo/pgx/v5/stderr.txt
@@ -4,4 +4,5 @@ query.sql:5:1: invalid query comment: -- name: ListFoos :one :many
 query.sql:8:1: invalid query type: :two
 query.sql:11:1: query "DeleteFoo" specifies parameter ":one" without containing a RETURNING clause
 query.sql:14:1: query "UpdateFoo" specifies parameter ":one" without containing a RETURNING clause
-query.sql:17:1: query "InsertFoo" specifies parameter ":one" without containing a RETURNING clause
\ No newline at end of file
+query.sql:17:1: query "InsertFoo" specifies parameter ":one" without containing a RETURNING clause
+query.sql:20:1: query "InsertFoo" specifies parameter ":batchone" without containing a RETURNING clause