0

When I execute SQL statements using rust sqlx, I find that the returned results are incorrect. For the same statements, when I use pysql, I get results consistent with those obtained from the MySQL client query. I don't know why this is happening; it seems like there might be an issue with certain SQL functions.

This is SQL:

SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog 
WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()

In rust sqlx:

let sql = format!(r#"SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()"#);
match sqlx::query(sql.as_str())
    .fetch_one(&pool)
    .await {
    Ok(res) => {
        log::info!("res: {:?}", res);
        log::info!("res: {:?}, {:?}", res.get::<i32, _>(0), res.get::<Option<String>, _>(1));
    }
    Err(e) => {
        log::error!("{e}");
        return;
    }
};

The sqlx output:

[2023-07-20 15:30:35] INFO [evi_saver::save_evi_layer::test] src/save_evi_layer.rs:437: res: MySqlRow { row: Row { storage: b"\x08\0\0\0\0\0\0\0\0", values: [Some(1..9), None] }, format: Binary, columns: [MySqlColumn { ordinal: 0, name: COUNT(*), type_info: MySqlTypeInfo { type: LongLong, flags: ColumnFlags(NOT_NULL | BINARY), char_set: 63, max_size: Some(21) }, flags: Some(ColumnFlags(NOT_NULL | BINARY)) }, MySqlColumn { ordinal: 1, name: MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')), type_info: MySqlTypeInfo { type: VarString, flags: ColumnFlags(0x0), char_set: 224, max_size: Some(76) }, flags: Some(ColumnFlags(0x0)) }], column_names: {COUNT(*): 0, MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')): 1} }
[2023-07-20 15:30:35] INFO [evi_saver::save_evi_layer::test] src/save_evi_layer.rs:438: res: 0, None

In pymysql:

cursor = db.cursor()
sql = """SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()"""
cursor.execute(sql)
res = cursor.fetchall()
print(res)

The pymysql output:

((39, '2023-07-20 15:08:33'),)

mysql client output:

mysql> SELECT COUNT(*), MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW();
+----------+--------------------------------------------------+
| COUNT(*) | MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) |
+----------+--------------------------------------------------+
|       39 | 2023-07-20 15:08:33                              |
+----------+--------------------------------------------------+
1 row in set (0.03 sec)

Info

SQLx version: [0.7.1]
SQLx features enabled: ["mysql", "runtime-tokio", "runtime-tokio-native-tls"]
Database server and version: [MySQL 5.7.42-0ubuntu0.18.04.1 (Ubuntu)] (MySQL / Postgres / SQLite <x.y.z>)
Operating system: [ubuntu0.18.04.1]
rustc --version: [rustc 1.72.0-nightly (839e9a6e1 2023-07-02)]
Yuri Astrakhan
  • 8,808
  • 6
  • 63
  • 97
wengang yang
  • 23
  • 3
  • 2
    Your SQL statement is invalid. The `AND NOW()` should be `DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')`. Beyond that your "where" clause is inefficient but that, obviously, isn't relevant to your question. I'm just pointing out that you should rewrite it to eliminate the `DATE_FORMAT` calls. – Kurtis Rader Jul 21 '23 at 02:32
  • Thank you for pointing out the issue. I have identified the specific reason: `SET time_zone = SYSTEM;`. – wengang yang Jul 21 '23 at 09:01

1 Answers1

1
let mut pool = sqlx::mysql::MySqlPoolOptions::new()
                    // set timezone 
                    .after_connect(|conn, _meta| Box::pin(async move {
                        conn.execute("SET time_zone = SYSTEM;").await?;
                        Ok(())
                    }))
                    .max_connections(2).connect(mysql_url.as_str()).await.unwrap();
wengang yang
  • 23
  • 3