Repository: sqlc-dev/sqlc
Pull request #2873 (2023-10-17 21:48:32)

feat(compiler): Support LEFT JOIN on aliased table
Fixes https://github.com/sqlc-dev/sqlc/issues/1897


Patch:
diff --git a/Makefile b/Makefile
index 3cd60dc97..3f1245147 100644
--- a/Makefile
+++ b/Makefile
@@ -33,7 +33,7 @@ sqlc-gen-json:
 	go build -o ~/bin/sqlc-gen-json ./cmd/sqlc-gen-json
 
 start:
-	docker-compose up -d
+	docker compose up -d
 
 fmt:
 	go fmt ./...
diff --git a/docker-compose.yml b/docker-compose.yml
index 07540c873..3087dcc6b 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -6,7 +6,7 @@ services:
       - "3306:3306"
     restart: always
     environment:
-      MYSQL_DATABASE: mysql
+      MYSQL_DATABASE: dinotest
       MYSQL_ROOT_PASSWORD: mysecretpassword
       MYSQL_ROOT_HOST: '%'
 
@@ -16,7 +16,7 @@ services:
       - "3305:3306"
     restart: always
     environment:
-      MYSQL_DATABASE: mysql
+      MYSQL_DATABASE: dinotest
       MYSQL_ROOT_PASSWORD: mysecretpassword
       MYSQL_ROOT_HOST: '%'
     profiles:
diff --git a/docs/guides/development.md b/docs/guides/development.md
index 5140f086e..7f0311583 100644
--- a/docs/guides/development.md
+++ b/docs/guides/development.md
@@ -24,7 +24,7 @@ These tests require locally-running database instances. Run these databases
 using [Docker Compose](https://docs.docker.com/compose/).
 
 ```
-docker-compose up -d
+docker compose up -d
 ```
 
 The tests use the following environment variables to connect to the
diff --git a/docs/requirements.txt b/docs/requirements.txt
index 3a2b3a1b2..20e1188ec 100644
--- a/docs/requirements.txt
+++ b/docs/requirements.txt
@@ -24,4 +24,4 @@ sphinxcontrib-serializinghtml==1.1.9
 sphinxcontrib-htmlhelp==2.0.4
 sphinxcontrib-jsmath==1.0.1
 sphinxcontrib-qthelp==1.0.6
-urllib3==2.0.6
+urllib3==2.0.7
diff --git a/go.mod b/go.mod
index 05edd4d2d..532b10ff9 100644
--- a/go.mod
+++ b/go.mod
@@ -10,7 +10,6 @@ require (
 	github.com/go-sql-driver/mysql v1.7.1
 	github.com/google/cel-go v0.18.1
 	github.com/google/go-cmp v0.6.0
-	github.com/jackc/pgconn v1.14.1
 	github.com/jackc/pgx/v4 v4.18.1
 	github.com/jackc/pgx/v5 v5.4.3
 	github.com/jinzhu/inflection v1.0.0
@@ -22,19 +21,20 @@ require (
 	github.com/spf13/pflag v1.0.5
 	github.com/xeipuuv/gojsonschema v1.2.0
 	golang.org/x/sync v0.4.0
-	google.golang.org/grpc v1.58.3
+	google.golang.org/grpc v1.59.0
 	google.golang.org/protobuf v1.31.0
 	gopkg.in/yaml.v3 v3.0.1
 )
 
 require (
 	github.com/benbjohnson/clock v1.3.5 // indirect
+	github.com/jackc/pgconn v1.14.1 // indirect
 	github.com/jackc/puddle/v2 v2.2.1 // indirect
 	github.com/pingcap/failpoint v0.0.0-20220801062533-2eaa32854a6c // indirect
 	github.com/xeipuuv/gojsonpointer v0.0.0-20180127040702-4e3ac2762d5f // indirect
 	github.com/xeipuuv/gojsonreference v0.0.0-20180127040603-bd5ef7bd5415 // indirect
-	google.golang.org/genproto/googleapis/api v0.0.0-20230803162519-f966b187b2e5 // indirect
-	google.golang.org/genproto/googleapis/rpc v0.0.0-20230803162519-f966b187b2e5 // indirect
+	google.golang.org/genproto/googleapis/api v0.0.0-20230822172742-b8732ec3820d // indirect
+	google.golang.org/genproto/googleapis/rpc v0.0.0-20230822172742-b8732ec3820d // indirect
 )
 
 require (
@@ -47,7 +47,7 @@ require (
 	github.com/jackc/pgpassfile v1.0.0 // indirect
 	github.com/jackc/pgproto3/v2 v2.3.2 // indirect
 	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
-	github.com/jackc/pgtype v1.14.0
+	github.com/jackc/pgtype v1.14.0 // indirect
 	github.com/pingcap/errors v0.11.5-0.20210425183316-da1aaba5fb63 // indirect
 	github.com/pingcap/log v1.1.0 // indirect
 	github.com/pingcap/tidb/parser v0.0.0-20231010133155-38cb4f3312be
diff --git a/go.sum b/go.sum
index f780baa8d..a5e0c2934 100644
--- a/go.sum
+++ b/go.sum
@@ -100,7 +100,6 @@ github.com/jackc/pgx/v5 v5.4.3/go.mod h1:Ig06C2Vu0t5qXC60W8sqIthScaEnFvojjj9dSlj
 github.com/jackc/puddle v0.0.0-20190413234325-e4ced69a3a2b/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
 github.com/jackc/puddle v0.0.0-20190608224051-11cab39313c9/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
 github.com/jackc/puddle v1.1.3/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
-github.com/jackc/puddle v1.3.0 h1:eHK/5clGOatcjX3oWGBO/MpxpbHzSwud5EWTSCI+MX0=
 github.com/jackc/puddle v1.3.0/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
 github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
 github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
@@ -140,8 +139,6 @@ github.com/pingcap/failpoint v0.0.0-20220801062533-2eaa32854a6c h1:CgbKAHto5CQgW
 github.com/pingcap/failpoint v0.0.0-20220801062533-2eaa32854a6c/go.mod h1:4qGtCB0QK0wBzKtFEGDhxXnSnbQApw1gc9siScUl8ew=
 github.com/pingcap/log v1.1.0 h1:ELiPxACz7vdo1qAvvaWJg1NrYFoY6gqAh/+Uo6aXdD8=
 github.com/pingcap/log v1.1.0/go.mod h1:DWQW5jICDR7UJh4HtxXSM20Churx4CQL0fwL/SoOSA4=
-github.com/pingcap/tidb/parser v0.0.0-20230815160630-b69fa21942d1 h1:FvX5fDJ32eblK9f6KJIOVujjtu3FDEfcRGHsEDfJ4kI=
-github.com/pingcap/tidb/parser v0.0.0-20230815160630-b69fa21942d1/go.mod h1:pWA6mNa/o7UTDKrg+4H75NdpRgpWRTox/cqQjaQ4ZBU=
 github.com/pingcap/tidb/parser v0.0.0-20231010133155-38cb4f3312be h1:4HUBkIZs+4j+tbXGm5/B0yjB66OTz218HDKA6VrhO7U=
 github.com/pingcap/tidb/parser v0.0.0-20231010133155-38cb4f3312be/go.mod h1:cwq4bKUlftpWuznB+rqNwbN0xy6/i5SL/nYvEKeJn4s=
 github.com/pkg/errors v0.8.1/go.mod h1:bwawxfHBFNV+L2hUp1rHADufV3IMtnDRdf1r5NINEl0=
@@ -300,12 +297,12 @@ golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8T
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
-google.golang.org/genproto/googleapis/api v0.0.0-20230803162519-f966b187b2e5 h1:nIgk/EEq3/YlnmVVXVnm14rC2oxgs1o0ong4sD/rd44=
-google.golang.org/genproto/googleapis/api v0.0.0-20230803162519-f966b187b2e5/go.mod h1:5DZzOUPCLYL3mNkQ0ms0F3EuUNZ7py1Bqeq6sxzI7/Q=
-google.golang.org/genproto/googleapis/rpc v0.0.0-20230803162519-f966b187b2e5 h1:eSaPbMR4T7WfH9FvABk36NBMacoTUKdWCvV0dx+KfOg=
-google.golang.org/genproto/googleapis/rpc v0.0.0-20230803162519-f966b187b2e5/go.mod h1:zBEcrKX2ZOcEkHWxBPAIvYUWOKKMIhYcmNiUIu2ji3I=
-google.golang.org/grpc v1.58.3 h1:BjnpXut1btbtgN/6sp+brB2Kbm2LjNXnidYujAVbSoQ=
-google.golang.org/grpc v1.58.3/go.mod h1:tgX3ZQDlNJGU96V6yHh1T/JeoBQ2TXdr43YbYSsCJk0=
+google.golang.org/genproto/googleapis/api v0.0.0-20230822172742-b8732ec3820d h1:DoPTO70H+bcDXcd39vOqb2viZxgqeBeSGtZ55yZU4/Q=
+google.golang.org/genproto/googleapis/api v0.0.0-20230822172742-b8732ec3820d/go.mod h1:KjSP20unUpOx5kyQUFa7k4OJg0qeJ7DEZflGDu2p6Bk=
+google.golang.org/genproto/googleapis/rpc v0.0.0-20230822172742-b8732ec3820d h1:uvYuEyMHKNt+lT4K3bN6fGswmK8qSvcreM3BwjDh+y4=
+google.golang.org/genproto/googleapis/rpc v0.0.0-20230822172742-b8732ec3820d/go.mod h1:+Bk1OCOj40wS2hwAMA+aCW9ypzm63QTBBHp6lQ3p+9M=
+google.golang.org/grpc v1.59.0 h1:Z5Iec2pjwb+LEOqzpB2MR12/eKFhDPhuqW91O+4bwUk=
+google.golang.org/grpc v1.59.0/go.mod h1:aUPDwccQo6OTjy7Hct4AfBPD1GptF4fyUjIkQ9YtF98=
 google.golang.org/protobuf v0.0.0-20200109180630-ec00e32a8dfd/go.mod h1:DFci5gLYBciE7Vtevhsrf46CRTquxDuWsQurQQe4oz8=
 google.golang.org/protobuf v0.0.0-20200221191635-4d8936d0db64/go.mod h1:kwYJMbMJ01Woi6D6+Kah6886xMZcty6N08ah7+eCXa0=
 google.golang.org/protobuf v0.0.0-20200228230310-ab0ca4ff8a60/go.mod h1:cfTl7dwQJ+fmap5saPgwCLgHXTUD7jkjRqWcaiX5VyM=
diff --git a/internal/compiler/output_columns.go b/internal/compiler/output_columns.go
index 0ee3b3e18..49b166137 100644
--- a/internal/compiler/output_columns.go
+++ b/internal/compiler/output_columns.go
@@ -403,7 +403,8 @@ func (c *Compiler) outputColumns(qc *QueryCatalog, node ast.Node) ([]*Column, er
 				continue
 			}
 			for _, f := range n.FromClause.Items {
-				if res := isTableRequired(f, col, tableRequired); res != tableNotFound {
+				res := isTableRequired(f, col, tableRequired)
+				if res != tableNotFound {
 					col.NotNull = res == tableRequired
 					break
 				}
@@ -423,10 +424,12 @@ const (
 func isTableRequired(n ast.Node, col *Column, prior int) int {
 	switch n := n.(type) {
 	case *ast.RangeVar:
-		if n.Alias == nil && *n.Relname == col.Table.Name {
-			return prior
+		tableMatch := *n.Relname == col.Table.Name
+		aliasMatch := true
+		if n.Alias != nil && col.TableAlias != "" {
+			aliasMatch = *n.Alias.Aliasname == col.TableAlias
 		}
-		if n.Alias != nil && *n.Alias.Aliasname == col.TableAlias && *n.Relname == col.Table.Name {
+		if aliasMatch && tableMatch {
 			return prior
 		}
 	case *ast.JoinExpr:
@@ -673,13 +676,13 @@ func outputColumnRefs(res *ast.ResTarget, tables []*Table, node *ast.ColumnRef)
 			continue
 		}
 		for _, c := range t.Columns {
+
 			if c.Name == name {
 				found += 1
 				cname := c.Name
 				if res.Name != nil {
 					cname = *res.Name
 				}
-
 				cols = append(cols, &Column{
 					Name:         cname,
 					Type:         c.Type,
diff --git a/internal/engine/postgresql/analyzer/analyze.go b/internal/engine/postgresql/analyzer/analyze.go
index ab34eb348..fe07b840e 100644
--- a/internal/engine/postgresql/analyzer/analyze.go
+++ b/internal/engine/postgresql/analyzer/analyze.go
@@ -101,7 +101,7 @@ func (a *Analyzer) columnInfo(ctx context.Context, field pgconn.FieldDescription
 	if ok {
 		return cinfo.(*pgColumn), nil
 	}
-	rows, err := a.pool.Query(ctx, columnQuery, field.TableOID, field.TableAttributeNumber)
+	rows, err := a.pool.Query(ctx, columnQuery, field.TableOID, int16(field.TableAttributeNumber))
 	if err != nil {
 		return nil, err
 	}


Test patch:
diff --git a/internal/endtoend/testdata/join_left_table_alias/issue.md b/internal/endtoend/testdata/join_left_table_alias/issue.md
new file mode 100644
index 000000000..77e67d124
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/issue.md
@@ -0,0 +1,1 @@
+https://github.com/sqlc-dev/sqlc/issues/1897
diff --git a/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/db.go b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/db.go
new file mode 100644
index 000000000..8a010ccc4
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.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/join_left_table_alias/postgresql/pgx/go/models.go b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/models.go
new file mode 100644
index 000000000..11461a006
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/models.go
@@ -0,0 +1,16 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.0
+
+package querytest
+
+import ()
+
+type Bar struct {
+	FooID int64
+	Info  string
+}
+
+type Foo struct {
+	ID int64
+}
diff --git a/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/query.sql.go b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/query.sql.go
new file mode 100644
index 000000000..2de45039a
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/go/query.sql.go
@@ -0,0 +1,48 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.0
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+const joinBar = `-- name: JoinBar :one
+SELECT f.id, info
+FROM foo f
+LEFT JOIN bar b ON b.foo_id = f.id
+`
+
+type JoinBarRow struct {
+	ID   int64
+	Info pgtype.Text
+}
+
+func (q *Queries) JoinBar(ctx context.Context) (JoinBarRow, error) {
+	row := q.db.QueryRow(ctx, joinBar)
+	var i JoinBarRow
+	err := row.Scan(&i.ID, &i.Info)
+	return i, err
+}
+
+const joinBarAlias = `-- name: JoinBarAlias :one
+SELECT f.id, b.info
+FROM foo f
+LEFT JOIN bar b ON b.foo_id = f.id
+`
+
+type JoinBarAliasRow struct {
+	ID   int64
+	Info pgtype.Text
+}
+
+func (q *Queries) JoinBarAlias(ctx context.Context) (JoinBarAliasRow, error) {
+	row := q.db.QueryRow(ctx, joinBarAlias)
+	var i JoinBarAliasRow
+	err := row.Scan(&i.ID, &i.Info)
+	return i, err
+}
diff --git a/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/query.sql b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/query.sql
new file mode 100644
index 000000000..ad61f17b7
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/query.sql
@@ -0,0 +1,9 @@
+-- name: JoinBar :one
+SELECT f.id, info
+FROM foo f
+LEFT JOIN bar b ON b.foo_id = f.id;
+
+-- name: JoinBarAlias :one
+SELECT f.id, b.info
+FROM foo f
+LEFT JOIN bar b ON b.foo_id = f.id;
diff --git a/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/schema.sql b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/schema.sql
new file mode 100644
index 000000000..296159099
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/schema.sql
@@ -0,0 +1,9 @@
+CREATE TABLE foo (
+	id BIGINT PRIMARY KEY
+);
+
+CREATE TABLE bar
+(
+    foo_id  BIGINT NOT NULL,
+    info    TEXT NOT NULL
+);
diff --git a/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/sqlc.yaml b/internal/endtoend/testdata/join_left_table_alias/postgresql/pgx/sqlc.yaml
new file mode 100644
index 000000000..5dc63e3f9
--- /dev/null
+++ b/internal/endtoend/testdata/join_left_table_alias/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/select_system/issue.md b/internal/endtoend/testdata/select_system/issue.md
new file mode 100644
index 000000000..7e536076f
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/issue.md
@@ -0,0 +1,1 @@
+https://github.com/sqlc-dev/sqlc/issues/1745
diff --git a/internal/endtoend/testdata/select_system/pgx/exec.json b/internal/endtoend/testdata/select_system/pgx/exec.json
new file mode 100644
index 000000000..e5dfda781
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/exec.json
@@ -0,0 +1,3 @@
+{
+    "contexts": ["managed-db"]
+}
diff --git a/internal/endtoend/testdata/select_system/pgx/go/db.go b/internal/endtoend/testdata/select_system/pgx/go/db.go
new file mode 100644
index 000000000..8a010ccc4
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.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/select_system/pgx/go/models.go b/internal/endtoend/testdata/select_system/pgx/go/models.go
new file mode 100644
index 000000000..b52387cf1
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/go/models.go
@@ -0,0 +1,13 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.0
+
+package querytest
+
+import (
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+type Test struct {
+	ID pgtype.Int4
+}
diff --git a/internal/endtoend/testdata/select_system/pgx/go/query.sql.go b/internal/endtoend/testdata/select_system/pgx/go/query.sql.go
new file mode 100644
index 000000000..fadb461a6
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/go/query.sql.go
@@ -0,0 +1,41 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.22.0
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+const getSystemColumns = `-- name: GetSystemColumns :one
+SELECT
+  tableoid, xmin, cmin, xmax, cmax, ctid
+FROM test
+`
+
+type GetSystemColumnsRow struct {
+	Tableoid pgtype.Uint32
+	Xmin     pgtype.Uint32
+	Cmin     pgtype.Uint32
+	Xmax     pgtype.Uint32
+	Cmax     pgtype.Uint32
+	Ctid     pgtype.TID
+}
+
+func (q *Queries) GetSystemColumns(ctx context.Context) (GetSystemColumnsRow, error) {
+	row := q.db.QueryRow(ctx, getSystemColumns)
+	var i GetSystemColumnsRow
+	err := row.Scan(
+		&i.Tableoid,
+		&i.Xmin,
+		&i.Cmin,
+		&i.Xmax,
+		&i.Cmax,
+		&i.Ctid,
+	)
+	return i, err
+}
diff --git a/internal/endtoend/testdata/select_system/pgx/query.sql b/internal/endtoend/testdata/select_system/pgx/query.sql
new file mode 100644
index 000000000..11ddeb749
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/query.sql
@@ -0,0 +1,4 @@
+-- name: GetSystemColumns :one
+SELECT
+  tableoid, xmin, cmin, xmax, cmax, ctid
+FROM test;
diff --git a/internal/endtoend/testdata/select_system/pgx/schema.sql b/internal/endtoend/testdata/select_system/pgx/schema.sql
new file mode 100644
index 000000000..7518997c9
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/pgx/schema.sql
@@ -0,0 +1,3 @@
+CREATE TABLE test (
+  id INT
+);
diff --git a/internal/endtoend/testdata/select_system/pgx/sqlc.yaml b/internal/endtoend/testdata/select_system/pgx/sqlc.yaml
new file mode 100644
index 000000000..5dc63e3f9
--- /dev/null
+++ b/internal/endtoend/testdata/select_system/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"