Generated sqlite query to update on conflict fails with missing param

[joeriddles]

Version

1.27.0

What happened?

Using the schema and query to update a row on insertion conflict with the following Go test fails:

package db

import (
	"context"
	"database/sql"
	"os"
	"testing"
	"time"

	_ "modernc.org/sqlite"
)

func TestSavePin(t *testing.T) {
	_, err := os.Create("test.sqlite")
	if err != nil {
		t.Fatal(err.Error())
	}

	db, err := sql.Open("sqlite", "test.sqlite")
	if err != nil {
		t.Fatal(err.Error())
	}

	schema, err := os.ReadFile("../schema.sql")
	if err != nil {
		t.Fatal(err.Error())
	}

	_, err = db.Exec(string(schema))
	if err != nil {
		t.Fatal(err.Error())
	}

	q := New(db)

	pin, err := q.SavePin(context.Background(), SavePinParams{
		Title:     "title",
		Url:       sql.NullString{String: "url", Valid: true},
		ImageUrl:  sql.NullString{},
		CreatedAt: time.Now().UnixMilli(),
	})
	if err != nil {
		t.Fatal(err.Error())
	}
	_ = pin
}

The test fails with this output:

--- FAIL: TestSavePin (0.00s)
    query_test.go:43: missing argument with index 5
FAIL
FAIL    github.com/joeriddles/patina/server/db  0.521s
FAIL

Relevant log output

--- FAIL: TestSavePin (0.00s)
    query_test.go:43: missing argument with index 5
FAIL
FAIL    github.com/joeriddles/patina/server/db  0.521s
FAIL

Database schema

-- schema.sql
CREATE TABLE pins (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  url TEXT,
  image_url TEXT,
  created_at INTEGER NOT NULL
);

CREATE UNIQUE INDEX unique_url_idx ON pins (url);

SQL queries

-- name: SavePin :one
INSERT INTO pins (
  title, url, image_url, created_at
) VALUES (
  ?, ?, ?, ?
)
ON CONFLICT (url)
DO UPDATE SET title = ?, image_url = ?
RETURNING *;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"
        emit_json_tags: true

Playground URL

https://play.sqlc.dev/p/f96a629951bbdfebc68f4cfd2810025dc258c82e832ad2b8d6792946b71379f1

https://play.sqlc.dev/p/a5be075614744d3a900e38b3061c790b5962f4966f1b09be4d57ec28aa65c792 (named params)

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[joeriddles]

The generated query is missing two params. It should have six total.

func (q *Queries) SavePin(ctx context.Context, arg SavePinParams) (Pin, error) {
	row := q.db.QueryRowContext(ctx, savePin,
		arg.Title,
		arg.Url,
		arg.ImageUrl,
		arg.CreatedAt,
	)
	var i Pin
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Url,
		&i.ImageUrl,
		&i.CreatedAt,
	)
	return i, err
}

Manually updating the generated query to the following fixes the error:

func (q *Queries) SavePin(ctx context.Context, arg SavePinParams) (Pin, error) {
	row := q.db.QueryRowContext(ctx, savePin,
		arg.Title,
		arg.Url,
		arg.ImageUrl,
		arg.CreatedAt,
		arg.Title,
		arg.ImageUrl,
	)
	var i Pin
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Url,
		&i.ImageUrl,
		&i.CreatedAt,
	)
	return i, err
}

[joeriddles]

I forked and wrote a test that fails with the same error: https://github.com/joeriddles/sqlc/tree/joeriddles/3834

Diff: main...joeriddles:sqlc:joeriddles/3834

[joeriddles]

Using the excluded.<col_name> syntax does work: https://www.sqlite.org/lang_upsert.html#:~:text=%20to%20use%20the%20value%20that%20would%20have%20been%20inserted%20had%20the%20constraint%20not%20failed%2C%20add%20the%20special%20%22excluded.%22%20table%20qualifier%20to%20the%20column%20name.%20

/* name: SaveBook :one */
INSERT INTO books (
    author_id,
    isbn,
    book_type,
    title,
    yr,
    available,
    tag
) VALUES (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?
)
ON CONFLICT (isbn)
DO UPDATE SET author_id = excluded.author_id, book_type = excluded.book_type, title = excluded.title, yr = excluded.yr, available = excluded.available, tag = excluded.tag
RETURNING *;

[joeriddles]

Looks to be related to:

• name parameter not resolved in do statement #3439
• upsert with arg or narg doesn't work in sqlite3 #3508 ?

[joeriddles]

Debugging this... it looks like the OnConflictingCause field in the query's ast.Node is nil, when it should be populated.

[sherif-elmetainy]

I am having the exact same problem.

My query

-- name: UpsertStatistics :exec
INSERT INTO datasource_statistics
(object_id, object_type, period_start, period_end,
 total_bytes_collected, total_bytes_forecast,
 total_events_collected, total_events_forecast, created_timestamp)
VALUES (:object_id, :object_type, :period_start, :period_end,
        :total_bytes_collected, :total_bytes_forecast,
        :total_events_collected, :total_events_forecast, :now)
ON CONFLICT (object_id, object_type, period_start, period_end)
    DO UPDATE SET total_bytes_collected  = total_bytes_collected + :total_bytes_collected,
                  total_bytes_forecast   = total_bytes_forecast + :total_bytes_forecast,
                  total_events_collected = total_events_collected + :total_events_collected,
                  total_events_forecast  = total_events_forecast + :total_events_forecast,
                  updated_timestamp      = :now;

The go code generated is:

const upsertStatistics = `-- name: UpsertStatistics :exec
INSERT INTO datasource_statistics
(object_id, object_type, period_start, period_end,
 total_bytes_collected, total_bytes_forecast,
 total_events_collected, total_events_forecast, created_timestamp)
VALUES (?1, ?2, ?3, ?4,
        ?5, ?6,
        ?7, ?8, ?9)
ON CONFLICT (object_id, object_type, period_start, period_end)
    DO UPDATE SET total_bytes_collected  = total_bytes_collected + :total_bytes_collected,
                  total_bytes_forecast   = total_bytes_forecast + :total_bytes_forecast,
                  total_events_collected = total_events_collected + :total_events_collected,
                  total_events_forecast  = total_events_forecast + :total_events_forecast,
                  updated_timestamp      = :now
`
type UpsertStatisticsParams struct {
	ObjectID             string
	ObjectType           string
	PeriodStart          time.Time
	PeriodEnd            time.Time
	TotalBytesCollected  int64
	TotalBytesForecast   int64
	TotalEventsCollected int64
	TotalEventsForecast  int64
	Now                  time.Time
}

func (q *Queries) UpsertStatistics(ctx context.Context, arg *UpsertStatisticsParams) error {
	_, err := q.db.ExecContext(ctx, upsertStatistics,
		arg.ObjectID,
		arg.ObjectType,
		arg.PeriodStart,
		arg.PeriodEnd,
		arg.TotalBytesCollected,
		arg.TotalBytesForecast,
		arg.TotalEventsCollected,
		arg.TotalEventsForecast,
		arg.Now,
	)
	return err
}

The proper query in golang should be:

const upsertStatistics = `-- name: UpsertStatistics :exec
INSERT INTO datasource_statistics
(object_id, object_type, period_start, period_end,
 total_bytes_collected, total_bytes_forecast,
 total_events_collected, total_events_forecast, created_timestamp)
VALUES (?1, ?2, ?3, ?4,
        ?5, ?6,
        ?7, ?8, ?9)
ON CONFLICT (object_id, object_type, period_start, period_end)
    DO UPDATE SET total_bytes_collected  = total_bytes_collected + ?5,
                  total_bytes_forecast   = total_bytes_forecast + ?6,
                  total_events_collected = total_events_collected + ?7,
                  total_events_forecast  = total_events_forecast + ?8,
                  updated_timestamp      = ?9
`

I solved my problem by writing the query manually