sqlc
sqlc copied to clipboard
sqlc-dev
NOT EXISTS in correlated subquery doesn't generate required parameter
Version
1.30.0
What happened?
When using a NOT EXISTS clause in a correlated subquery, SQLC fails to recognize and generate the required parameter binding.
Expected behavior: The generated function should include the transponderID parameter that appears in the correlated subquery's WHERE clause.
Actual behavior: The generated function signature is missing the transponderID parameter.
Workarounds discovered:
- ✅ Using NOT IN clause works correctly and generates the parameter
- ✅ Using EXISTS (without NOT) works correctly for correlated subqueries
- ❌ NOT EXISTS fails to recognize the parameter in the correlated subquery
Relevant log output
const getSignalsConnectedToTransponder = `-- name: GetSignalsConnectedToTransponder :many
SELECT id, name, center_frequency_hz, data_rate, device_type, modulation, behavior, gain, created_at FROM signals s WHERE NOT EXISTS (SELECT 1 from signal_links sl where sl.signal_id = s.id and sl.transponder_id = ?)
`
// generated function from sqlc generate
func (q *Queries) GetSignalsConnectedToTransponder(ctx context.Context) ([]Signal, error) {
rows, err := q.db.QueryContext(ctx, getSignalsConnectedToTransponder)
if err != nil {
return nil, err
}
defer rows.Close()
items := []Signal{}
for rows.Next() {
var i Signal
if err := rows.Scan(
&i.ID,
&i.Name,
&i.CenterFrequencyHz,
&i.DataRate,
&i.DeviceType,
&i.Modulation,
&i.Behavior,
&i.Gain,
&i.CreatedAt,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
Database schema
-- Transponders table - belongs to a link
CREATE TABLE
IF NOT EXISTS transponders (
id TEXT PRIMARY KEY DEFAULT (hex (randomblob (16))),
name TEXT NOT NULL,
center_frequency_hz INTEGER NOT NULL,
bandwidth_hz INTEGER NOT NULL,
uplink_polarization TEXT NOT NULL,
downlink_polarization TEXT NOT NULL,
band TEXT NOT NULL DEFAULT 'L',
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;
-- Signals table - belongs to a transponder
CREATE TABLE
IF NOT EXISTS signals (
id TEXT PRIMARY KEY DEFAULT (hex (randomblob (16))),
name TEXT NOT NULL,
center_frequency_hz INTEGER NOT NULL,
data_rate INTEGER NOT NULL,
modulation TEXT NOT NULL,
behavior TEXT NOT NULL DEFAULT '',
gain INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
) STRICT;
-- signal_links table - Table that shows if there is a relationship btw a transponder and a signal
CREATE TABLE
IF NOT EXISTS signal_links (
transponder_id TEXT NOT NULL,
signal_id TEXT NOT NULL,
FOREIGN KEY (transponder_id) REFERENCES transponders (id),
FOREIGN KEY (signal_id) REFERENCES signals (id)
) STRICT;
SQL queries
SELECT
*
FROM
signals s
WHERE
NOT EXISTS (
SELECT
1
FROM
signal_links sl
WHERE
sl.signal_id = s.id
AND sl.transponder_id = ?
);
Configuration
version: "2"
sql:
- engine: "sqlite"
queries: "missions_db/queries"
schema: "missions_db/migrations"
gen:
go:
package: "missions_db"
out: "missions_db"
emit_json_tags: true
emit_prepared_queries: false
emit_interface: false
emit_exact_table_names: false
emit_empty_slices: true
Playground URL
No response
What operating system are you using?
Linux
What database engines are you using?
SQLite
What type of code are you generating?
Go
I am not sure yet, but I believe that pr-4126 should fix this issue.
Yes, it fixes it. You can try it out by doing the following:
- checkout #4126 and build the binary:
cd cmd/sqlc && go build -o /tmp/sqlc . - copy the files in the gist into
/tmp/dummy
cd /tmp/dummy
/tmp/sqlc generate
go mod tidy
go run .
Output:
2025/11/20 21:24:08 [{SG3 L2 High-Rate Downlink 1620003000 2000000 QAM16 burst 7 {2025-11-20 20:24:08 true}} {SG4 L2 Housekeeping 1619999000 9600 BPSK nominal 4 {2025-11-20 20:24:08 true}}]```
