BigSerial doesn't generate int64 for params

[EnchanterIO]

I have a table with BIGSERIAL ID:

CREATE TABLE datastore_index (
      id BIGSERIAL PRIMARY KEY,
      key VARCHAR(1000) UNIQUE NOT NULL,
      size INT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL
);

The SQLC v1.9.0 generates correct model with int64:

type DatastoreIndex struct {
	ID        int64
	Key       string
	Size      int32
	CreatedAt time.Time
}

But when generating a query with offset:

SELECT id, key, size, created_at FROM datastore_index ORDER BY id ASC OFFSET $1 LIMIT $2;

The generated Params is int32, it should be big int:

type GetNextParams struct {
	Offset int32
	Limit  int32
}

Am I missing something @kyleconroy ?

Playground

https://play.sqlc.dev/p/2d1cc2e453aef91c65e4ca2bfd56a55fc758f2ab47a392ca235c5bb66f92e599

[kyleconroy]

I tested this on PostgreSQL 13. In this case the arguments to limit and offset are treated as bigints, not ints.

kyle=# \d+ authors;
                                                Table "public.authors"
 Column |  Type  | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+--------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | bigint |           | not null | nextval('authors_id_seq'::regclass) | plain    |              | 
 name   | text   |           | not null |                                     | extended |              | 
 bio    | text   |           |          |                                     | extended |              | 
Indexes:

kyle=# select count(*) from authors offset 11232147483647999;
 count 
-------
(0 rows)

kyle=# select count(*) from authors limit 11232147483647999;
 count 
-------
     1
(1 row)
# select count(*) from authors limit 1121231231232147483647999;
ERROR:  bigint out of range
kyle=# select count(*) from authors offset 1121231231232147483647999;
ERROR:  bigint out of range

Changing this will break a lot of existing code, so I'm going to have to think about how to do it.

[EnchanterIO]

Sounds good.

And a sub-question @kyleconroy. If I define SERIAL on the above table, the model generates int32 ID, why the following query:

type DatastoreIndex struct {
	ID        int32
	Key       string
	Size      int32
	CreatedAt time.Time
}

-- name: CountAll :one
SELECT COUNT(*) FROM datastore_index;

Generates Querier interface returning int64?

CountAll(ctx context.Context) (int64, error)

[EnchanterIO]

FYI: Coming back to this issue year later, it's working on v1.16 by rewriting the query from:

SELECT id, key, size, created_at FROM datastore_index WHERE id > $1::int and id <= $1::int + $2::int

to:

SELECT id, key, size, created_at FROM datastore_index WHERE id > $1::bigserial and id <= $1::bigserial + $2::bigserial