Double variable casting in SQL statements

[pchemguy]

Why do you generate double variable casting when its type explicitly specified:

CAST(CAST(? AS TEXT) AS NUMERIC)

instead of

CAST(? AS NUMERIC)

?

Can I avoid implicit casting CAST(? AS TEXT)?
Do you provide a means to see submitted SQL for debug purposes?

[lovasoa]

Hi !
This is a good question. When binding variables, we use casting to let the engine know the expected type of the expression in the prepared statement. We could remove the casting when there is another cast, that would make the generated sql look slightly better, but this has simply never been implemented.

Can I avoid implicit casting CAST(? AS TEXT)?

Currently, you can't. Has it caused issues for you ? Or did you find it confusing when debugging ?

Do you provide a means to see submitted SQL for debug purposes?

Yes ! You can enable verbose logging by setting the environment variable RUST_LOG=sqlpage=debug

[pchemguy]

It has not caused any real problems so far, though reading through the rendered statement, when an error has occurred, is slightly more complicated.

Actually, SQLite provides dedicated interfaces for binding variables of different types (but, you are, probably, aware of this). I understand that using a single interface with casting is probably a simpler way to support multiple databases.