sqlx::query!(..) inferring wrong `Option` NULLable-types #1852

DaAitch · 2022-05-04T00:40:11Z

Environment:

PostgreSQL 14.1
sqlx = { version = "0.5", features = [ "runtime-async-std-native-tls", "postgres", "offline", "time" ] }

I don't think this issue is related to OS-specifics, so please let me know, if you need more information here.

Description

My DB schema:

CREATE TABLE department (
  id              SERIAL PRIMARY KEY,
  name            TEXT NOT NULL
);

CREATE TABLE person (
  id              SERIAL PRIMARY KEY,
  name            TEXT NOT NULL,
  department_id   INT references department(id) NOT NULL
);

CREATE TABLE training (
  id              SERIAL PRIMARY KEY,
  name            TEXT NOT NULL
);

CREATE TABLE person_training (
  trainee_person_id   INT references person(id) NOT NULL,
  training_id         INT references training(id) NOT NULL,
  PRIMARY KEY (trainee_person_id, training_id),

  advisor_person_id   INT references person(id) NULL,
  progress            REAL NOT NULL
);

CREATE TABLE person_training_log (
  id                  SERIAL PRIMARY KEY,
  trainee_person_id   INT references person(id) NOT NULL,
  training_id         INT references training(id) NOT NULL,
  author_person_id    INT references person(id) NOT NULL,
  comment             TEXT NOT NULL,
  created             TIMESTAMP NOT NULL,
  updated             TIMESTAMP NOT NULL
);

I need to make a query with a subselect and some joins, where (in my opinion) sqlx has a bug.
At first here is a query which is smaller, but part of the larger query, which doesn't work.
This smaller one here works and also has a left join and subselect. sqlx correctly understands, that both select fields may be NULL, so great.

        let trainings = query!(r#"
            SELECT
                ptl_last.created            AS last_log_created,
                ptl_last.comment            AS last_log_comment
            FROM
                person_training pt
            LEFT JOIN (
                SELECT
                    ptl.trainee_person_id,
                    ptl.training_id,
                    ptl.created,
                    ptl.comment,
                    ROW_NUMBER() OVER(
                        PARTITION BY
                            ptl.trainee_person_id, ptl.training_id
                        ORDER BY
                            ptl.created
                        DESC
                    ) as i
                FROM
                    person_training_log ptl
            ) ptl_last
            ON
                ptl_last.i = 1 AND
                ptl_last.training_id = pt.training_id AND
                ptl_last.trainee_person_id = pt.trainee_person_id
            WHERE
                pt.trainee_person_id = 9
        "#).fetch_all(&self.config.db_pool).await.unwrap();

        if let Some(created) = trainings[0].last_log_created {
            // ok compiles, it's an Option
        }

Now when I make the actual query it fails:

let trainings = query!(r#"
            SELECT
                t.id                        AS training_id,
                t.name                      AS training_name,
                pt.progress                 AS training_progress,
                p_a.name					AS advisor_name,
                ptl_last.created            AS last_log_created,
                ptl_last.comment            AS last_log_comment,
                p_ptl_last_a.name           AS last_log_author_name
            FROM
                person_training pt
            INNER JOIN
                training t
            ON
                pt.training_id = t.id
            LEFT JOIN
                person p_a
            ON
                p_a.id = pt.advisor_person_id
            LEFT JOIN (
                SELECT
                    ptl.trainee_person_id,
                    ptl.training_id,
                    ptl.created,
                    ptl.author_person_id,
                    ptl.comment,
                    ROW_NUMBER() OVER(
                        PARTITION BY
                            ptl.trainee_person_id, ptl.training_id
                        ORDER BY
                            ptl.created
                        DESC
                    ) as i
                FROM
                    person_training_log ptl
            ) ptl_last
            ON
                ptl_last.i = 1 AND
                ptl_last.training_id = pt.training_id AND
                ptl_last.trainee_person_id = pt.trainee_person_id
            LEFT JOIN
                person p_ptl_last_a
            ON
                p_ptl_last_a.id = ptl_last.author_person_id
            WHERE
                pt.trainee_person_id = $1
        "#, person_id)
            .fetch_all(&self.config.db_pool)
            .await
            .map_err(sqlx_error_into_status)?;

        if let Some(created) = trainings[0].last_log_created {
            // 312 |             if let Some(created) = trainings[0].last_log_created {
            //     |                    ^^^^^^^^^^^^^   ----------------------------- this expression has type `PrimitiveDateTime`
            //     |                    |
            //     |                    expected struct `PrimitiveDateTime`, found enum `Option`
            //     |
            //     = note: expected struct `PrimitiveDateTime`
            //                 found enum `Option<_>`
        }

It's almost the same context: field of a left-joined table (which should always be consider nullable I guess?). And indeed .await returns Err(..) with message "error occurred while decoding column 4: unexpected null; try decoding as an Option".

Checking generated meta data, sqlx doesn't get the nullable fields correctly.

sqlx-data.json:

{
  "a05641face9dea9a20ab8d564a723f893ffb3dc8b490cd00c0843694558caf39": {
    "describe": {
      "columns": [
        {
          "name": "last_log_created",
          "ordinal": 0,
          "type_info": "Timestamp"
        },
        {
          "name": "last_log_comment",
          "ordinal": 1,
          "type_info": "Text"
        }
      ],
      "nullable": [
        true,
        true
      ],
      "parameters": {
        "Left": []
      }
    },
    "query": "\n            SELECT\n                ptl_last.created            AS last_log_created,\n                ptl_last.comment            AS last_log_comment\n            FROM\n                person_training pt\n            LEFT JOIN (\n                SELECT\n                    ptl.trainee_person_id,\n                    ptl.training_id,\n                    ptl.created,\n                    ptl.comment,\n                    ROW_NUMBER() OVER(\n                        PARTITION BY\n                            ptl.trainee_person_id, ptl.training_id\n                        ORDER BY\n                            ptl.created\n                        DESC\n                    ) as i\n                FROM\n                    person_training_log ptl\n            ) ptl_last\n            ON\n                ptl_last.i = 1 AND\n                ptl_last.training_id = pt.training_id AND\n                ptl_last.trainee_person_id = pt.trainee_person_id\n            WHERE\n                pt.trainee_person_id = 9\n        "
  },
  "209ab5928f26319c8c7780d6ea469563086d7e09ba7d766e3441c2bbbff189ff": {
    "describe": {
      "columns": [
        {
          "name": "training_id",
          "ordinal": 0,
          "type_info": "Int4"
        },
        {
          "name": "training_name",
          "ordinal": 1,
          "type_info": "Text"
        },
        {
          "name": "training_progress",
          "ordinal": 2,
          "type_info": "Float4"
        },
        {
          "name": "advisor_name",
          "ordinal": 3,
          "type_info": "Text"
        },
        {
          "name": "last_log_created",
          "ordinal": 4,
          "type_info": "Timestamp"
        },
        {
          "name": "last_log_comment",
          "ordinal": 5,
          "type_info": "Text"
        },
        {
          "name": "last_log_author_name",
          "ordinal": 6,
          "type_info": "Text"
        }
      ],
      "nullable": [
        false,
        false,
        false,
        false,
        false,
        false,
        false
      ],
      "parameters": {
        "Left": [
          "Int4"
        ]
      }
    },
    "query": "\n            SELECT\n                t.id                        AS training_id,\n                t.name                      AS training_name,\n                pt.progress                 AS training_progress,\n                p_a.name\t\t\t\t\tAS advisor_name,\n                ptl_last.created            AS last_log_created,\n                ptl_last.comment            AS last_log_comment,\n                p_ptl_last_a.name           AS last_log_author_name\n            FROM\n                person_training pt\n            INNER JOIN\n                training t\n            ON\n                pt.training_id = t.id\n            LEFT JOIN\n                person p_a\n            ON\n                p_a.id = pt.advisor_person_id\n            LEFT JOIN (\n                SELECT\n                    ptl.trainee_person_id,\n                    ptl.training_id,\n                    ptl.created,\n                    ptl.author_person_id,\n                    ptl.comment,\n                    ROW_NUMBER() OVER(\n                        PARTITION BY\n                            ptl.trainee_person_id, ptl.training_id\n                        ORDER BY\n                            ptl.created\n                        DESC\n                    ) as i\n                FROM\n                    person_training_log ptl\n            ) ptl_last\n            ON\n                ptl_last.i = 1 AND\n                ptl_last.training_id = pt.training_id AND\n                ptl_last.trainee_person_id = pt.trainee_person_id\n            LEFT JOIN\n                person p_ptl_last_a\n            ON\n                p_ptl_last_a.id = ptl_last.author_person_id\n            WHERE\n                pt.trainee_person_id = $1\n        "
  }
}

Is it possible to have a workaround maybe without offline queries in this case, but still use the feature for the rest of the project?

Thank you
Philipp

The text was updated successfully, but these errors were encountered:

liolin · 2022-05-31T09:25:18Z

I have a similar error with simpler setup. When using the macro (query_as!) the null-ability is not correct. When using the function (query_as) the null-ability is correct. The second one can be used as an workaround in my case.

sqlite3 (3.38.5 2022-05-06 15:25:27 78d9c993d404cdfaa7fdd2973fa1052e3da9f66215cff9c5540ebe55c407alt1)
sqlx = { version = "0.5", features = ["runtime-tokio-rustls", "postgres", "sqlite", "offline"]}

My database schema:

CREATE TABLE tblmedia(
  id INTEGER PRIMARY KEY NOT NULL,
  title VARCHAR NOT NULL,
  raiting INTEGER,
  file_name VARCHAR NOT NULL,
  media_type TEXT NOT NULL
);

CREATE TABLE tblbook(
  id INTEGER PRIMARY KEY NOT NULL,
  author VARCHAR,

  FOREIGN KEY (id) REFERENCES tblmedia(id) ON DELETE CASCADE
);

CREATE VIEW book AS
SELECT
  m.id as id,
  m.title as title,
  b.author as author,
  m.raiting as raiting,
  m.file_name as file_name
FROM tblbook b
LEFT JOIN tblmedia m ON m.id = b.id;

The struct:

#[derive(Debug, PartialEq, Eq, sqlx::FromRow)]
pub struct Book {
    pub id: i64,
    pub author: Option<String>,
    pub title: String,
    pub raiting: Option<i64>,
    pub file_name: String,
}

The SQL queries

// Does work
sqlx::query_as(r#"SELECT id, author, title, raiting, file_name FROM book;"#)
.fetch_all(connection)
.await;

// Does not work
sqlx::query_as!(
  Book,
  r#"SELECT id, author, title, raiting, file_name FROM book"#
)
.fetch_all(connection)
.await;

The error

error[E0308]: mismatched types
  --> src/database/book.rs:33:9
   |
33 | /         sqlx::query_as!(
34 | |             Book,
35 | |             r#"SELECT id, author, title, raiting, file_name FROM book"#
36 | |         )
   | |_________^ expected struct `String`, found enum `Option`
   |
   = note: expected struct `String`
                found enum `Option<String>`

abonander · 2022-07-15T06:23:46Z

@DaAitch @liolin would both of you please provide the output of EXPLAIN (VERBOSE, FORMAT JSON) <query> as that is what the Postgres driver uses for nullability analysis.

liolin · 2022-07-15T10:52:28Z

@abonander , when I execute EXPLAIN (VERBOSE, FORMAT JSON) SELECT id, author, title, raiting, file_name FROM book; I get the following output:

[
  {
    "Plan": {
      "Node Type": "Hash Join",
      "Parallel Aware": false,
      "Async Capable": false,
      "Join Type": "Left",
      "Startup Cost": 23.73,
      "Total Cost": 49.77,
      "Plan Rows": 1270,
      "Plan Width": 104,
      "Output": ["m.id", "b.author", "m.title", "m.raiting", "m.file_name"],
      "Inner Unique": true,
      "Hash Cond": "(b.id = m.id)",
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Relation Name": "tblbook",
          "Schema": "public",
          "Alias": "b",
          "Startup Cost": 0.00,
          "Total Cost": 22.70,
          "Plan Rows": 1270,
          "Plan Width": 36,
          "Output": ["b.id", "b.author"]
        },
        {
          "Node Type": "Hash",
          "Parent Relationship": "Inner",
          "Parallel Aware": false,
          "Async Capable": false,
          "Startup Cost": 16.10,
          "Total Cost": 16.10,
          "Plan Rows": 610,
          "Plan Width": 72,
          "Output": ["m.id", "m.title", "m.raiting", "m.file_name"],
          "Plans": [
            {
              "Node Type": "Seq Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Relation Name": "tblmedia",
              "Schema": "public",
              "Alias": "m",
              "Startup Cost": 0.00,
              "Total Cost": 16.10,
              "Plan Rows": 610,
              "Plan Width": 72,
              "Output": ["m.id", "m.title", "m.raiting", "m.file_name"]
            }
          ]
        }
      ]
    }
  }
]

abonander · 2022-07-15T19:16:56Z

That's actually not a false positive, look back at the view you created:

CREATE VIEW book AS
SELECT
  m.id as id,
  m.title as title,
  b.author as author,
  m.raiting as raiting,
  m.file_name as file_name
FROM tblbook b
LEFT JOIN tblmedia m ON m.id = b.id;

Most of those columns are coming from the m table brought in by a left join, which means they may not be present.

Since you're selecting from tblbook which is foreign-keyed to tblmedia, you would always expect there to be a corresponding tblmedia row if there is a tblbook row, which would be INNER JOIN, not LEFT JOIN.

liolin · 2022-07-20T13:05:48Z

Ahh. Thank you for pointing out my mistake. And I'm sorry for bothering you with this false positive.

jeanlucthumm · 2022-08-09T03:57:22Z

Running into something similar:

CREATE TABLE Donations (
    id INTEGER PRIMARY KEY,
    user TEXT NOT NULL,
    amount REAL NOT NULL,
    message TEXT,
    timestamp REAL NOT NULL
);

For the query I have:

 sqlx::query_as!(DonationRow,"SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ?", limit)

And the struct:

#[derive(Debug)]
#[allow(dead_code)]
struct DonationRow {
    id: i64,
    user: String,
    amount: f64,
    message: Option<String>,
    timestamp: f64,
}

And the error:

error[E0308]: mismatched types
  --> src/main.rs:50:55
   |
50 |               Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
   |  _______________________________________________________^
51 | |                 DonationRow,
52 | |                 "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | |                 limit
54 | |             ),
   | |_____________^ expected `i64`, found enum `Option`
   |
   = note: expected type `i64`
              found enum `Option<i64>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0308]: mismatched types
  --> src/main.rs:50:55
   |
50 |               Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
   |  _______________________________________________________^
51 | |                 DonationRow,
52 | |                 "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | |                 limit
54 | |             ),
   | |_____________^ expected struct `String`, found enum `Option`
   |
   = note: expected struct `String`
                found enum `Option<String>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0308]: mismatched types
  --> src/main.rs:50:55
   |
50 |               Some(proto::DonationsOrder::Timestamp) => sqlx::query_as!(
   |  _______________________________________________________^
51 | |                 DonationRow,
52 | |                 "SELECT * FROM Donations ORDER BY timestamp DESC LIMIT ? ",
53 | |                 limit
54 | |             ),
   | |_____________^ expected `f64`, found enum `Option`
   |
   = note: expected type `f64`
              found enum `Option<f64>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

martijnarts · 2022-10-04T12:46:44Z

I had this same problem. I ended up resolving it by explicitly marking the joined-in column as optional col as "col?".

Sharpiro · 2023-07-20T18:38:09Z

I had this same problem. I ended up resolving it by explicitly marking the joined-in column as optional col as "col?".

@0rvar

This was very helpful and worked for me, thanks. My question is, is this SQL, Postgres, or SQLX syntax? Is this documented anywhere?

This is very useful but there's no way I would have ever figured that out if I hadn't seen your comment.

0rvar · 2023-07-20T18:50:55Z

@Sharpiro I think you meant to @ someone else.

However, I believe this issue is resolved in sqlx 0.7. That has been my experience in any case. Try upgrading and see if that helps

Sharpiro · 2023-07-20T19:42:34Z

@Sharpiro I think you meant to @ someone else.

However, I believe this issue is resolved in sqlx 0.7. That has been my experience in any case. Try upgrading and see if that helps

Ahh sorry I meant to tag @martijnarts

But I'm not really saying there is a problem, I'm just curious about the col as "col?" syntax

martijnarts · 2023-07-20T21:14:10Z

@Sharpiro this is SQLx behavior, and it's documented here.

Sharpiro · 2023-07-21T00:51:04Z

@Sharpiro this is SQLx behavior, and it's documented here.

Great thanks

DaAitch · 2023-07-21T10:47:20Z

I feel sorry for opening an issue and then not actively participating for a long while.

For what I understood sqlx forwards SQL to the specific db server and get back the meta data for the query, which is a genius idea. If there are Option<..>-types where you don't expect them or the other way round, the DB seems to have taken into account more than you, or it can't understand some constraints in the SQL, so you need to put more information into the SQL, like mentioned in the comments before, so this is no issue anymore.

Thanks for clarifying.

Tienisto · 2024-02-22T21:54:13Z

How does it work with enums?

I have last_message.message_type AS "message_type?: MessageType"
but I get unexpected null; try decoding as an Option

BarneyKY · 2024-07-24T14:45:55Z

#3372 (comment)

Hi , look at my new example, i don't use any JOIN, but this problem is still

use Force Not-Null is risky cause that i don't konw whether this column is really NULLABLE when i change it

abonander added db:postgres Related to PostgreSQL macros macros:null-inference Faulty NULL inference in query macros labels Jul 15, 2022

0rvar mentioned this issue Oct 30, 2022

Add regression tests for sqlite explain #2180

Closed

DaAitch closed this as completed Jul 21, 2023

zshell31 mentioned this issue Feb 11, 2025

query!(..)/query_as!(..) strange behavior when inferring types for queries #3736

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sqlx::query!(..) inferring wrong `Option` NULLable-types #1852

sqlx::query!(..) inferring wrong `Option` NULLable-types #1852

DaAitch commented May 4, 2022 •

edited

Loading

liolin commented May 31, 2022

abonander commented Jul 15, 2022

liolin commented Jul 15, 2022

abonander commented Jul 15, 2022 •

edited

Loading

liolin commented Jul 20, 2022

jeanlucthumm commented Aug 9, 2022 •

edited

Loading

martijnarts commented Oct 4, 2022

Sharpiro commented Jul 20, 2023

0rvar commented Jul 20, 2023

Sharpiro commented Jul 20, 2023

martijnarts commented Jul 20, 2023

Sharpiro commented Jul 21, 2023

DaAitch commented Jul 21, 2023

Tienisto commented Feb 22, 2024

BarneyKY commented Jul 24, 2024

sqlx::query!(..) inferring wrong Option NULLable-types #1852

sqlx::query!(..) inferring wrong Option NULLable-types #1852

Comments

DaAitch commented May 4, 2022 • edited Loading

Environment:

Description

liolin commented May 31, 2022

abonander commented Jul 15, 2022

liolin commented Jul 15, 2022

abonander commented Jul 15, 2022 • edited Loading

liolin commented Jul 20, 2022

jeanlucthumm commented Aug 9, 2022 • edited Loading

martijnarts commented Oct 4, 2022

Sharpiro commented Jul 20, 2023

0rvar commented Jul 20, 2023

Sharpiro commented Jul 20, 2023

martijnarts commented Jul 20, 2023

Sharpiro commented Jul 21, 2023

DaAitch commented Jul 21, 2023

Tienisto commented Feb 22, 2024

BarneyKY commented Jul 24, 2024

sqlx::query!(..) inferring wrong `Option` NULLable-types #1852

sqlx::query!(..) inferring wrong `Option` NULLable-types #1852

DaAitch commented May 4, 2022 •

edited

Loading

abonander commented Jul 15, 2022 •

edited

Loading

jeanlucthumm commented Aug 9, 2022 •

edited

Loading