MySQL types with UNSIGNED modifier don't translate to unsigned golang types #1761

Version

1.14.0

What happened?

The example schema, below, illustrates several issues with the MySQL/golang numeric type mappings used to generate models right now:

1. The input domain for UNSIGNED types are truncated to N/2 - 1 due to golang's 2s' compliment encoding of signed integers: e.g. an INTEGER UNSIGNED MySQL type has a domain of 0..4294967295, but the parameter-type generated for an Exec statement in golang has an int32 field, with a domain of -2147483648..2147483647.

2. In addition to limiting the upper bound of possible values, the MySQL Driver rejects negative values with an error in this case even though golang compiles them as valid code:

   id, err := queries.CreateTestdata(context.Background(), generated.CreateTestdataParams{
     Somekey:       rand.Int31(),
     LittleNumber:  200,
     RegularNumber: -2147483648,
   })
   
   if err != nil {
     panic(err)
   }

   results in

   panic: Error 1264: Out of range value for column 'regular_number' at row 1
   

3. Existing table rows with values outside of their respective signed type's 2s' compliment upper bound will be inaccessible for code generated by sqlc. The MySQL driver will always return a range error when it attempts to coerce unsigned values in the upper half of the type's domain into signed values. Even assuming that all of the system's input data was also truncated by the behavior above, DEFAULT or AUTO INCREMENT expressions may still generate values that break the generated code; e.g. my_id BIGINT UNSIGNED DEFAULT UUID_SHORT().

Relevant log output

No response

Database schema

CREATE TABLE testdata (
  somekey INTEGER PRIMARY KEY AUTO_INCREMENT,

  little_number   TINYINT UNSIGNED NOT NULL, -- 8-bit
  regular_number  INTEGER UNSIGNED NOT NULL, -- 32-bit
  huge_number     BIGINT  UNSIGNED NOT NULL  -- 64-bit
) ENGINE = InnoDB;

SQL queries

-- name: CreateTestdata :execlastid
INSERT INTO testdata (little_number, regular_number, huge_number) VALUES (?, ?, ?);

-- name: GetTestdata :one
SELECT * FROM testdata WHERE somekey = ? LIMIT 1;

Configuration

---
version: '2'
sql:
  - schema: schema
    queries: queries
    engine: mysql
    gen:
      go:
        out: pkg/storage/generated

Playground URL

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

What operating system are you using?

Linux, macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go