You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I am trying to build a small controller to a database, deliberately not using an ORM, and encountered some weird behavior when working with MySQL BINARY columns.
And even though I have searched the internet quite some time, it seems to me that noone else encountered this issue before, at least I was not able to find any other mentions of that, making me think (and hope) that I just did something wrong in my setup.
So I set up a test table only containing a column 'guid binary(16) not null' with the following statement: create table test(guid binary(16) not null);
I have also placed the necessary DATABASE_URL into a .env file, to get compile-time type checking in the query! and query_as! macros.
The problem is the following:
When i do sqlx::query!( "SELECT guid FROM test" ) .fetch_all(&mut conn) .await?;
and iterate over the rows, the attribute 'guid' is given to me as type 'serde_json::Value' by the rust-analyzer extension in VSCode.
When I instead do sqlx::query!( "SELECT CAST(guid AS BINARY) AS guid FROM test" ) .fetch_all(&mut conn) .await?;
that attribute suddenly becomes a 'Option<Vec<u8, Global>>'.
According to the documentation of the MySQL types here, a BINARY column should be mapped to either &[u8] or Vec.
What's confusing me is that 'guid' already is a BINARY column, so a cast to BINARY should not change anything concerning the type mapping.
What's also interesting is that when trying to use the query_as! macro, the results are similar.
When working with the TestOption struct (shown below in reproduction steps) and the CAST within the SELECT statement, it works wonderfully.
But when I use the simple Test struct with just a Vec, the type checker complains:
the trait bound Vec<u8>: From<JsonValue> is not satisfied
the following other types implement trait From<T>:
<Vec as Frombytes::bytes::Bytes>
<Vec as Frombytes::bytes_mut::BytesMut>
<Vec as From>
<Vec as Fromstd::string::String>
<Vec as From<&str>>
required for JsonValue to implement Into<Vec<u8>>
This shows again, that the binary column does not get interpreted as binary data, unless "being forced to" (I guess).
Furthermore, when inspecting the results of 'cargo sqlx prepare', the columns are actually reported differently.
But for "SELECT CAST(guid AS BINARY) AS guid FROM test", i get: "columns": [ { "ordinal": 0, "name": "guid", "type_info": { "type": "VarString", "flags": "BINARY", "char_set": 63, "max_size": 16 } } ]
Yet both versions retain the 'BINARY' flag, indicating to me that it indeed gets recognized as binary data, but not necessarily treated as such.
I am not sure what exactly is going wrong. I sincerely hope that it's just a mistake on my side, but with a minimal setup like that and the docs not mentioning any more requirements to use BINARY columns, I don't think that's it.
Also, but just a side note: I have already tried working with SeaORM, which did correctly map BINARY columns to Vec.
And just for completeness: Similar errors arise when enabling the 'uuid' feature and trying to parse the column as a Uuid, the problem being that apparently an Option<Vec> can not be parsed to a Uuid, but I don't care about that for now.
Minimal Reproduction
Contents of my main.rs (the singular source file):
`use sqlx::{self, prelude::FromRow, Connection, Error, MySqlConnection};
#[tokio::main]
async fn main() -> Result<(), Error> {
let mut conn = MySqlConnection::connect("mysql://[user]:[password]@localhost/test").await?; // The same path as in the .env file
// Repeated to show every different combination tried
let res = sqlx::query!(
"SELECT guid FROM test"
)
.fetch_all(&mut conn)
.await?;
for _row in res {
// type: serde_json::Value
_row.guid; // Inspect at these points with rust-analyzer
}
// This one works
let res = sqlx::query!(
"SELECT CAST(guid AS BINARY) AS guid FROM test"
)
.fetch_all(&mut conn)
.await?;
for _row in res {
// type: Option<Vec<u8, Global>>
_row.guid;
}
// This one errors out
let res = sqlx::query_as!(Test,
"SELECT guid FROM test"
)
.fetch_all(&mut conn)
.await?;
for _row in res {
_row.guid;
}
// This one is allowed
let res = sqlx::query_as!(TestOption,
"SELECT CAST(guid AS BINARY) AS guid FROM test"
)
.fetch_all(&mut conn)
.await?;
for _row in res {
_row.guid;
}
return Ok(());
}
`
And to that a local database 'test' containing the described table 'test': create table test(guid binary(16) not null); +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | guid | binary(16) | NO | | NULL | | +-------+------------+------+-----+---------+-------+
If I am missing anything here, I'll be glad to add that.
Info
SQLx version: 0.7.4
SQLx features enabled: (according to 'cargo add sqlx':) _rt-tokio, any, json, macros, migrate, mysql, runtime-tokio, sqlx-macros, sqlx-mysql, tls-rustls (["macros", "tls-rustls", "mysql", "runtime-tokio"] being the feature selection in Cargo.toml)
Tokio version 1.37.0 with features ["full"]
Database server and version: 8.0.36 MySQL Community Server - GPL
What I forgot to mention is that interestingly, it works if I don't use macros: let res = sqlx::query_as::<_, Test>( "SELECT guid FROM test" ) .fetch_all(&mut conn) .await?;
This gives me a Vec without complaining, and I am able to print results of that using the 'hex' crate as an additional dependency.
And this snippet also works, when I enable the 'uuid' feature and change the type of my guid attribute to uuid::Uuid.
Bug Description
I am trying to build a small controller to a database, deliberately not using an ORM, and encountered some weird behavior when working with MySQL BINARY columns.
And even though I have searched the internet quite some time, it seems to me that noone else encountered this issue before, at least I was not able to find any other mentions of that, making me think (and hope) that I just did something wrong in my setup.
So I set up a test table only containing a column 'guid binary(16) not null' with the following statement:
create table test(guid binary(16) not null);
I have also placed the necessary DATABASE_URL into a .env file, to get compile-time type checking in the query! and query_as! macros.
The problem is the following:
When i do
sqlx::query!( "SELECT guid FROM test" ) .fetch_all(&mut conn) .await?;
and iterate over the rows, the attribute 'guid' is given to me as type 'serde_json::Value' by the rust-analyzer extension in VSCode.
When I instead do
sqlx::query!( "SELECT CAST(guid AS BINARY) AS guid FROM test" ) .fetch_all(&mut conn) .await?;
that attribute suddenly becomes a 'Option<Vec<u8, Global>>'.
According to the documentation of the MySQL types here, a BINARY column should be mapped to either &[u8] or Vec.
What's confusing me is that 'guid' already is a BINARY column, so a cast to BINARY should not change anything concerning the type mapping.
What's also interesting is that when trying to use the query_as! macro, the results are similar.
When working with the TestOption struct (shown below in reproduction steps) and the CAST within the SELECT statement, it works wonderfully.
But when I use the simple Test struct with just a Vec, the type checker complains:
the trait bound
Vec<u8>: From<JsonValue>
is not satisfiedthe following other types implement trait
From<T>
:<Vec as Frombytes::bytes::Bytes>
<Vec as Frombytes::bytes_mut::BytesMut>
<Vec as From>
<Vec as Fromstd::string::String>
<Vec as From<&str>>
required for
JsonValue
to implementInto<Vec<u8>>
This shows again, that the binary column does not get interpreted as binary data, unless "being forced to" (I guess).
Furthermore, when inspecting the results of 'cargo sqlx prepare', the columns are actually reported differently.
For "SELECT guid FROM test", i get:
"columns": [ { "ordinal": 0, "name": "guid", "type_info": { "type": "String", "flags": "NOT_NULL | BINARY | NO_DEFAULT_VALUE", "char_set": 63, "max_size": 16 } } ]
But for "SELECT CAST(guid AS BINARY) AS guid FROM test", i get:
"columns": [ { "ordinal": 0, "name": "guid", "type_info": { "type": "VarString", "flags": "BINARY", "char_set": 63, "max_size": 16 } } ]
Yet both versions retain the 'BINARY' flag, indicating to me that it indeed gets recognized as binary data, but not necessarily treated as such.
I am not sure what exactly is going wrong. I sincerely hope that it's just a mistake on my side, but with a minimal setup like that and the docs not mentioning any more requirements to use BINARY columns, I don't think that's it.
Also, but just a side note: I have already tried working with SeaORM, which did correctly map BINARY columns to Vec.
And just for completeness: Similar errors arise when enabling the 'uuid' feature and trying to parse the column as a Uuid, the problem being that apparently an Option<Vec> can not be parsed to a Uuid, but I don't care about that for now.
Minimal Reproduction
Contents of my main.rs (the singular source file):
`use sqlx::{self, prelude::FromRow, Connection, Error, MySqlConnection};
#[derive(FromRow)]
struct Test {
guid: Vec
}
#[derive(FromRow)]
struct TestOption {
guid: Option<Vec>
}
#[tokio::main]
async fn main() -> Result<(), Error> {
let mut conn = MySqlConnection::connect("mysql://[user]:[password]@localhost/test").await?; // The same path as in the .env file
}
`
And to that a local database 'test' containing the described table 'test':
create table test(guid binary(16) not null);
+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | guid | binary(16) | NO | | NULL | | +-------+------------+------+-----+---------+-------+
If I am missing anything here, I'll be glad to add that.
Info
rustc --version
: rustc 1.76.0 (07dca489a 2024-02-04)The text was updated successfully, but these errors were encountered: