nullable value in record is not an option

[LuckyTurtleDev]

Bug Description

Auto generated Record do not us Option for nullable value. The doc say I should create a issue for this.

Minimal Reproduction

repo with bug: https://github.com/LuckyTurtleDev/mstickerpicker/tree/59488942bddbff961cffc4a3c40e43524496c660

CREATE TABLE users (
  id SERIAL UNIQUE,
  mxid TEXT UNIQUE NOT NULL,
  PRIMARY KEY (mxid)
);

CREATE TABLE files (
  hash BYTEA UNIQUE NOT NULL,
  mxc TEXT UNIQUE NOT NULL,
  id BIGSERIAL UNIQUE,
  PRIMARY KEY (id)
);

CREATE INDEX
ON files(hash);

CREATE TABLE file_owner (
  user_id INTEGER NOT NULL,
  file_id BIGINT NOT NULL,
  FOREIGN KEY(file_id)
    REFERENCES files(id)
    ON DELETE CASCADE,
  FOREIGN KEY(user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
);

CREATE INDEX
ON file_owner(user_id);

CREATE INDEX
ON file_owner(file_id);

SELECT * from users;
 id | mxid
----+------
 -4 | test
(1 row)

SELECT * from files;
   hash   |  mxc  | id
----------+-------+----
 \xeeaee0 | test  |  1
 \x00     | test2 |  2
(2 rows)

SELECT * from file_owner;
 user_id | file_id
---------+---------
(0 rows)

-- user_id can be null:
SELECT *
FROM files AS a LEFT JOIN file_owner AS b
ON a.id = b.file_id
WHERE a.hash ='\x00'::bytea AND (b.user_id = 2 OR b.user_id IS NULL);
 hash |  mxc  | id | user_id | file_id
------+-------+----+---------+---------
 \x00 | test2 |  2 |         |
(1 row)

-- same query used as in rust:
SELECT a.mxc, a.id, b.user_id
FROM files AS a LEFT JOIN file_owner AS b
ON a.id = b.file_id
WHERE a.hash ='\x00'::bytea AND (b.user_id = 2 OR b.user_id IS NULL);
  mxc  | id | user_id
-------+----+---------
 test2 |  2 |
(1 row)

	let entry = query!(r#"
		SELECT a.mxc, a.id, b.user_id
		FROM files AS a LEFT JOIN file_owner AS b
		ON a.id = b.file_id
		WHERE a.hash=($1) AND (b.user_id = ($2) OR b.user_id IS NULL)
		"#, hash, user_id)
		.fetch_optional(&*SQL_POOL)
		.await?;
	if let Some(entry) = entry {
		if entry.user_id.is_none() {
			// ERROR:  user_id is from type i32 and not from Option<i32>
		}
	}

EXPLAIN (VERBOSE, FORMAT JSON) SELECT a.mxc, a.id, b.user_id
                FROM files AS a LEFT JOIN file_owner AS b
                ON a.id = b.file_id
                WHERE a.hash ='\x00'::bytea AND (b.user_id = 2 OR b.user_id IS NULL);
                         QUERY PLAN
-------------------------------------------------------------
 [                                                          +
   {                                                        +
     "Plan": {                                              +
       "Node Type": "Nested Loop",                          +
       "Parallel Aware": false,                             +
       "Async Capable": false,                              +
       "Join Type": "Left",                                 +
       "Startup Cost": 4.38,                                +
       "Total Cost": 23.08,                                 +
       "Plan Rows": 1,                                      +
       "Plan Width": 44,                                    +
       "Output": ["a.mxc", "a.id", "b.user_id"],            +
       "Inner Unique": false,                               +
       "Filter": "((b.user_id = 2) OR (b.user_id IS NULL))",+
       "Plans": [                                           +
         {                                                  +
           "Node Type": "Index Scan",                       +
           "Parent Relationship": "Outer",                  +
           "Parallel Aware": false,                         +
           "Async Capable": false,                          +
           "Scan Direction": "Forward",                     +
           "Index Name": "files_hash_idx",                  +
           "Relation Name": "files",                        +
           "Schema": "public",                              +
           "Alias": "a",                                    +
           "Startup Cost": 0.15,                            +
           "Total Cost": 8.17,                              +
           "Plan Rows": 1,                                  +
           "Plan Width": 40,                                +
           "Output": ["a.hash", "a.mxc", "a.id"],           +
           "Index Cond": "(a.hash = '\\x00'::bytea)"        +
         },                                                 +
         {                                                  +
           "Node Type": "Bitmap Heap Scan",                 +
           "Parent Relationship": "Inner",                  +
           "Parallel Aware": false,                         +
           "Async Capable": false,                          +
           "Relation Name": "file_owner",                   +
           "Schema": "public",                              +
           "Alias": "b",                                    +
           "Startup Cost": 4.23,                            +
           "Total Cost": 14.79,                             +
           "Plan Rows": 10,                                 +
           "Plan Width": 12,                                +
           "Output": ["b.user_id", "b.file_id"],            +
           "Recheck Cond": "(a.id = b.file_id)",            +
           "Plans": [                                       +
             {                                              +
               "Node Type": "Bitmap Index Scan",            +
               "Parent Relationship": "Outer",              +
               "Parallel Aware": false,                     +
               "Async Capable": false,                      +
               "Index Name": "file_owner_file_id_idx",      +
               "Startup Cost": 0.00,                        +
               "Total Cost": 4.23,                          +
               "Plan Rows": 10,                             +
               "Plan Width": 0,                             +
               "Index Cond": "(b.file_id = a.id)"           +
             }                                              +
           ]                                                +
         }                                                  +
       ]                                                    +
     }                                                      +
   }                                                        +
 ]
(1 row)

Info

• SQLx version: 0.7.3
• SQLx features enabled: default-features = false, features = ["runtime-tokio", "tls-rustls", "sqlx-postgres", "sqlx-macros", "migrate", "postgres", "macros"]
• Database server and version: postgres (PostgreSQL) 16.1
• Operating system: arch linux
• rustc --version: 1.76.0 (07dca489a 2024-02-04)

[spencerbart]

I'm running into the same bug