sidorares / sidorares/node-mysql2
JSON_EXTRACT comparison/casting issue
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 4.4k
- Forks
- 680
- Avg merge
- 9h 7m
- Merged PRs (30d)
- 59
Description
Using MySQL 5.7, it seems like calling JSON_EXTRACT(field, path+) for an integer from a json array isn't allowing me to compare with an integer.
Query:
SELECT *
FROM table
WHERE JSON_EXTRACT(json, "$[2]") >= ?;
The value in the json field is just a simple [1,2,3,4,5].
I try executing the query with both a string and integer value. Both result in now results.
If I insert the query into MySQL Workbench, it works fine. If I screw the prepared statement route and just put the integer in the query in place of the ?, it works fine.
They way around it so far is casting the JSON_EXTRACT value into UNSIGNED for the comparison to work:
SELECT *
FROM table
WHERE CAST(JSON_EXTRACT(json, "$[2]") AS UNSIGNED) >= ?;
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No source file or test is named. Reproduce the MySQL 5.7 query with the supplied JSON array through a prepared statement, comparing a bound string and integer parameter with the literal query and the CAST workaround. Done means the parameterized JSON_EXTRACT comparison returns the expected rows without requiring an explicit CAST.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, node.js, typescript
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100