Why is 1 and 0 are returned as string?
With the following:
I get
isUsed as "1" or "0" whereas I would expect a boolean.19 Replies
I tried
... then cast(1 as bit) else ... or with tinyint, but I get the error:
syntax error at position 294 near 'tinyint'You get "1" or "0" because you are type casting, but not changing the type returned from the database at runtime.
I suggest you use
.mapWith() to runtime map your query to what you want:
But I thought
1 should be at least an integer
Why a string in the end result?
I understand I'm only type casting, but even with sql<number>... I receive a string ("1" or "0")
I did find out mapWith and I'm using as follow:
Because Boolean will always return true on a non empty string
What I don't understand is why the end result is a string, whereas it should be at least a number OoWhen you use the sql magic operator, drizzle does not have any information of the type that the database/driver will return. It will just put whatever the db returned through the driver in your
isUsed keyIf I run the equivalent query in a MySQL client, I get a number 1 or 0.
Depending on your dialect, you could cast it a db level with:
Yup that's what I would expect. In this case the db returns integer. Yet drizzle provides me string. It seems there's an inconvenient conversion made by Drizzle. At least, that's how it looks like
Drizzle is returning the value without any transformation, this is somehting mysql2 driver does unfortunately
Same happens with for example
count(*)I already tried
cast(1 as signed) and still get a string
I probably mispoke, maybe it is as the mysql2 driverWhat about:
anyhow, I wonder if this could be considered as a bug, since it's not a 1 to 1 result compared to the pure SQL
let me try
still the same 🙈
It is not a bug IMO. It's just a design decision by mysql2 driver. Maybe they're trying to avoid loosing precision with stuff like bigint, IDK
mmh ok, I'll move on with .mapWith then. I'll dig in parallel the reason, cause I feel this is wrong, if not a bug 🙂
thanks for the sparring session 🙏
https://github.com/sidorares/node-mysql2/issues/935 <--- Check that one
GitHub
API incompatibility re aggregate functions sum and avg return strin...
SELECT SUM(amt), AVG(amt) FROM MYTABLE; mysqljs returns numeric columns but mysql2 returns strings.
Just to give you an example
But I think I read a parameter you can pass, might be worth trying
In particular this comment: https://github.com/sidorares/node-mysql2/issues/935#issuecomment-474621854
GitHub
API incompatibility re aggregate functions sum and avg return strin...
SELECT SUM(amt), AVG(amt) FROM MYTABLE; mysqljs returns numeric columns but mysql2 returns strings.
I'm not saying this is your issue, but just an example
At least it seems to support your suggestion that it is a deliberate choice
I think .mapWith is the right trade-off here 🙂
glad I could help