query! and query_as! macros with postgres no work #2587

neo-clon · 2023-07-05T20:39:20Z

Bug Description

I try use query! and query_as! and cant compile my project

error: error occurred while decoding column 0: expected value at line 12 column 5
  --> src/main.rs:26:17
   |
26 |         let _ = sqlx::query!(r#"select 1 as "id?""#) 
   |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `benchmarks_db` (bin "benchmarks_db") due to previous error

Minimal Reproduction

Create new project with cargo new
Add the next dependencies

[dependencies]
chrono = { version = "0.4.26", features = ["serde"] }
serde = { version = "1.0.166", features = ["derive"] }
serde_json = "1.0.100"
sqlx = { version = "0.7.0", features = [
    "postgres",
    "runtime-tokio-native-tls",
    "chrono",
    "macros",
    "bigdecimal",
] }
tokio = { version = "1.29.1", features = ["full"] }

Copy code from README.md

    let pool = PgPoolOptions::new()
        .max_connections(100)
        .connect("postgres://user:superpass@upcloudserver:11550/mydb?sslmode=require").await?;

   let _ = sqlx::query!(r#"select 1 as "id?""#)
            .fetch_one(&pool)
            .await?;

Get the intellisense and compiler error.

Info

SQLx version: "0.7.0"
SQLx features enabled: "postgres,runtime-tokio-native-tls,chrono,macros,bigdecimal"
Database server and version: "PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc, a 2c4e4d7dba p 865014ca71, 64-bit"
Operating system: "Arch Linux x86_64 with kernel 6.4.1-arch2-1"
rustc --version: "rustc 1.70.0 (90c541806 2023-05-31)"

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

abonander · 2023-07-05T21:00:51Z

What is the output of EXPLAIN (VERBOSE, FORMAT JSON) select 1 as "id?" from this database server?

valkum · 2023-07-06T11:32:08Z

Currently running into the same issue.
I am on MacOS with Postgres version 15.

Output of the explain query

EXPLAIN (VERBOSE, FORMAT JSON) SELECT "version", "description" FROM "_sqlx_migrations" ORDER BY "version" DESC LIMIT 1


[{"Plan":{"Node Type":"Limit","Parallel Aware":false,"Async Capable":false,"Startup Cost":0.14,"Total Cost":0.23,"Plan Rows":1,"Plan Width":27,"Output":["version","description"],"Plans":[{"Node Type":"Index Scan","Parent Relationship":"Outer","Parallel Aware":false,"Async Capable":false,"Scan Direction":"Backward","Index Name":"_sqlx_migrations_pkey","Relation Name":"_sqlx_migrations","Schema":"public","Alias":"_sqlx_migrations","Startup Cost":0.14,"Total Cost":18.25,"Plan Rows":207,"Plan Width":27,"Output":["version","description"]}]},"Query Identifier":3578844340514301400}]

neo-clon · 2023-07-06T17:17:29Z

Sure, i have this output

[
  {
    "Plan": {
      "Node Type": "Result",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 0.00,
      "Total Cost": 0.01,
      "Plan Rows": 1,
      "Plan Width": 4,
      "Output": ["1"]
    },
    "Query Identifier": 1147616880456321454
  }
]

zeroows · 2023-07-07T22:52:23Z

@mehcode
Hello, first, I would like to thank you for your work with this crate. Second, I would like to ask how to start investigating this issue to solve it.

Please note that using sqlx::query_as doesn't give an error but calling cargo sqlx prepare gives **warning**: no queries found

kyrias · 2023-07-13T12:47:24Z

We're also hitting this after upgrading to 0.7. It's very strange because seemingly arbitrary changes to the query will make it either happen or not.

This is the smallest I've been able to reduce the query to while still having it happen:

SELECT
    u.email,
    u.employee_id,
    f.id,
    nt.token
FROM users u
LEFT JOIN files f
    ON u.id = f.user_id
LEFT JOIN nfc_tokens nt
    ON u.id = nt.user_id
ORDER BY u.created_at ASC

error: error occurred while decoding column 0: expected value at line 104 column 5
   --> elofleet/src/storage/users.rs:392:17
    |
392 |           let _ = ::sqlx::query_as!(
    |  _________________^
393 | |             User,
394 | |             r#"
395 | |                 SELECT
...   |
406 | |             "#,
407 | |         );
    | |_________^
    |
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `::sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

Removing any of the selected columns or the ORDER BY clause makes it not happen. Reordering the columns makes no difference.

EXPLAIN (VERBOSE, FORMAT JSON)

[
  {
    "Plan": {
      "Node Type": "Sort",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 100243.03,
      "Total Cost": 100547.53,
      "Plan Rows": 121800,
      "Plan Width": 836,
      "Output": ["u.email", "u.employee_id", "f.id", "nt.token", "u.created_at"],
      "Sort Key": ["u.created_at"],
      "Plans": [
        {
          "Node Type": "Hash Join",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Join Type": "Left",
          "Startup Cost": 14.88,
          "Total Cost": 25.49,
          "Plan Rows": 121800,
          "Plan Width": 836,
          "Output": ["u.email", "u.employee_id", "f.id", "nt.token", "u.created_at"],
          "Inner Unique": true,
          "Hash Cond": "(u.id = nt.user_id)",
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Join Type": "Left",
              "Startup Cost": 0.15,
              "Total Cost": 9.21,
              "Plan Rows": 580,
              "Plan Width": 774,
              "Output": ["u.email", "u.employee_id", "u.created_at", "u.id", "f.id"],
              "Inner Unique": true,
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Relation Name": "users",
                  "Schema": "public",
                  "Alias": "u",
                  "Startup Cost": 0.00,
                  "Total Cost": 1.01,
                  "Plan Rows": 1,
                  "Plan Width": 758,
                  "Output": ["u.id", "u.created_at", "u.updated_at", "u.password_hash", "u.first_name", "u.last_name", "u.job_title", "u.management_role", "u.fleet_role", "u.employee_id", "u.department_id", "u.shift_id", "u.site_id", "u.license_expiry_date", "u.expiry_logout", "u.email", "u.ghost_user"]
                },
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Scan Direction": "Forward",
                  "Index Name": "files_user_id_idx",
                  "Relation Name": "files",
                  "Schema": "public",
                  "Alias": "f",
                  "Startup Cost": 0.15,
                  "Total Cost": 8.17,
                  "Plan Rows": 1,
                  "Plan Width": 32,
                  "Output": ["f.id", "f.data", "f.created_at", "f.updated_at", "f.vehicle_type_id", "f.user_id", "f.public_setting_id"],
                  "Index Cond": "(f.user_id = u.id)"
                }
              ]
            },
            {
              "Node Type": "Hash",
              "Parent Relationship": "Inner",
              "Parallel Aware": false,
              "Async Capable": false,
              "Startup Cost": 12.10,
              "Total Cost": 12.10,
              "Plan Rows": 210,
              "Plan Width": 94,
              "Output": ["nt.token", "nt.user_id"],
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Relation Name": "nfc_tokens",
                  "Schema": "public",
                  "Alias": "nt",
                  "Startup Cost": 0.00,
                  "Total Cost": 12.10,
                  "Plan Rows": 210,
                  "Plan Width": 94,
                  "Output": ["nt.token", "nt.user_id"]
                }
              ]
            }
          ]
        }
      ]
    },
    "JIT": {
      "Functions": 13,
      "Options": {
        "Inlining": false,
        "Optimization": false,
        "Expressions": true,
        "Deforming": true
      }
    }
  }
]

Platform: AMD64 Linux
PostgreSQL: 15.3
SQLx: 0.7.0

ryanmcgrath · 2023-07-14T00:48:28Z

FWIW, I have this same issue in the following query:

select distinct on(project_id) project_id from projects;

Removing the distinct clause has things work fine tho:

I'm able to work around it, but it is an unfortunate oddity that had no real way to debug until I removed it on a hunch. Seems like something in the macro changed? This was fine in 0.6.x.

Also worth noting that this does not happen when I compile locally on macOS, but when I moved to a Linux box and compile it, the issue comes up. The environments are otherwise the same (cargo, postgres version, etc).

zackartz · 2023-07-14T22:20:16Z

same thing here, going back to 0.6.3 for now

valkum · 2023-07-19T18:57:10Z

This seems to be related to #2626
Removing pg_stat_statements for now fixes the issue for me.

kyrias · 2023-07-20T07:42:56Z

I do not have pg_stat_statements loaded.

mrl5 · 2023-07-20T17:42:44Z

I think that fix introduced in #2626 should solve it for both JIT, Query Identifier or any other additional property

matthiasdebernardini · 2023-07-26T13:30:16Z

I also had this problem, had to revert back to 6 for now

msdrigg · 2023-07-31T13:40:09Z

Also seeing this

abonander · 2023-07-31T21:53:29Z

closed by #2670.

msdrigg · 2023-08-01T13:00:08Z

Any plan for when we'll get a release covering this? I am blocked from update to 7.x on this.

ryanmcgrath · 2023-08-18T04:57:05Z

Is there a release schedule planned that would include this?

msdrigg · 2023-09-27T18:09:50Z

Release 7.0.2 fixes this

neo-clon added the bug label Jul 5, 2023

abonander added db:postgres Related to PostgreSQL macros labels Jul 5, 2023

valkum mentioned this issue Jul 20, 2023

fix(postgres): sqlx prepare fails if shared_preload_libraries=pg_stat_statements #2626

Closed

mrl5 mentioned this issue Jul 20, 2023

postgres: sqlx prepare fails if shared_preload_libraries=pg_stat_statements #2622

Closed

abonander mentioned this issue Jul 31, 2023

fix: ignore extra fields in Postgres describe parsing #2670

Merged

abonander closed this as completed Jul 31, 2023

kyrias mentioned this issue Aug 3, 2023

error occurred while decoding column 0: expected value at line 16 column 5 #2679

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

query! and query_as! macros with postgres no work #2587

query! and query_as! macros with postgres no work #2587

neo-clon commented Jul 5, 2023

abonander commented Jul 5, 2023

valkum commented Jul 6, 2023 •

edited

neo-clon commented Jul 6, 2023

zeroows commented Jul 7, 2023 •

edited

kyrias commented Jul 13, 2023 •

edited

ryanmcgrath commented Jul 14, 2023 •

edited

zackartz commented Jul 14, 2023

valkum commented Jul 19, 2023

kyrias commented Jul 20, 2023

mrl5 commented Jul 20, 2023 •

edited

matthiasdebernardini commented Jul 26, 2023

msdrigg commented Jul 31, 2023

abonander commented Jul 31, 2023

msdrigg commented Aug 1, 2023

ryanmcgrath commented Aug 18, 2023

msdrigg commented Sep 27, 2023

query! and query_as! macros with postgres no work #2587

query! and query_as! macros with postgres no work #2587

Comments

neo-clon commented Jul 5, 2023

Bug Description

Minimal Reproduction

Info

abonander commented Jul 5, 2023

valkum commented Jul 6, 2023 • edited

neo-clon commented Jul 6, 2023

zeroows commented Jul 7, 2023 • edited

kyrias commented Jul 13, 2023 • edited

ryanmcgrath commented Jul 14, 2023 • edited

zackartz commented Jul 14, 2023

valkum commented Jul 19, 2023

kyrias commented Jul 20, 2023

mrl5 commented Jul 20, 2023 • edited

matthiasdebernardini commented Jul 26, 2023

msdrigg commented Jul 31, 2023

abonander commented Jul 31, 2023

msdrigg commented Aug 1, 2023

ryanmcgrath commented Aug 18, 2023

msdrigg commented Sep 27, 2023

valkum commented Jul 6, 2023 •

edited

zeroows commented Jul 7, 2023 •

edited

kyrias commented Jul 13, 2023 •

edited

ryanmcgrath commented Jul 14, 2023 •

edited

mrl5 commented Jul 20, 2023 •

edited