Why does PooledConn::exec return only one row

[soulflyman]

Hi,
I want to fetch some rows from a MariaDB, but I only get one row as result.
I'm using a statement with named parameters. Here is my example:

#[derive(Debug)]
#[allow(non_snake_case)]
struct EdRow {
    MESS_NR: Option<f64>,
    TAG_MITTEL: Option<f64>,
}

impl FromRow for EdRow {
    fn from_row_opt(mut row: Row) -> core::result::Result<Self, FromRowError>
    where Self: Sized {
        Ok(
            EdRow {
            MESS_NR: row.take("MESS_NR"),
            TAG_MITTEL: row.take("TAG_MITTEL"),
        })
    }
}

[...]

let _ppids = "1401,1874";
let statement = conn.prep("SELECT MESS_NR, SUM(TAG_MITTEL) as TAG_MITTEL from t2022.ed where MESS_NR in (:ppids) group by MESS_NR").unwrap();

let result_set = conn.exec(statement, params!("ppids" => _ppids)).unwrap();
dbg!(&result_set);

This returns the following result_set:

[src\main.rs:87] &result_set = [
    EdRow {
        MESS_NR: Some(
            1874.0,
        ),
        TAG_MITTEL: Some(
            722.1881000000001,
        ),
    },
]

But there should be two rows because I want results for ppid 1401 and 1874.
So I played around and hard coded the IDs into the query of the statement and used the parameter for some silly is not condition, so I can still run it as a statement and be sure that it is not a problem of the statement execution itself.

[...]

let _ppids = "1401,1874";
let statement = conn.prep("SELECT MESS_NR, SUM(TAG_MITTEL) as TAG_MITTEL from t2022.ed where MESS_NR = 1874 or MESS_NR = 1401 and Datum not in (:ppids) group by MESS_NR").unwrap();

let result_set = conn.exec(statement, params!("ppids" => _ppids)).unwrap();
dbg!(&result_set);

And the result was as expected, one row for each supplied ID.

[src\main.rs:86] &result_set = [
    EdRow {
        MESS_NR: Some(
            1401.0,
        ),
        TAG_MITTEL: Some(
            494.7964999999999,
        ),
    },
    EdRow {
        MESS_NR: Some(
            1874.0,
        ),
        TAG_MITTEL: Some(
            722.1881000000001,
        ),
    },
]

Executing the following query in HeidiSQL or some other DB-Tool will also return both rows:

SELECT MESS_NR, AVG(TAG_MITTEL) AS TAG_MITTEL from t2022.ed where MESS_NR in (1401,1874) group by MESS_NR;

So why does conn.exec() return only one row in my first example?

[soulflyman]

It seems that using an in condition in the where clause creates multiple result sets. At least is this the only thing that would explain this behavior. I can't find any hint for multiple result sets in the MariaDb documentation when using in.

[blackbeam]

Hi.

Prepared statements (whether params are named or positional) are not string substitution. Mysql server will replace each parameter with corresponding value taken from the given params. I.e. in your case server will execute in ('1401,1874') (not in (1401,1874)).

[soulflyman]

Oh, I didn't know that, thank you for the clarification.