sidorares / sidorares/node-mysql2

JSON_EXTRACT comparison/casting issue

Open
#348 13 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.