Prepared statement does not return correctly with JSON_EXTRACTOR about mysql

[methane]

when ran directly on the database

How did you run prepared query directly on the database, without sending query from any client?

[methane]

I assume "directly" means "not using prepared statement", and you used some client like "mysql" command. Please write a "complete step" next time.

Anyway, this is what happend in MySQL.

• json->'$.alive' : this value doesn't have boolean type, but JSON type.
• json->'$.alive' == true : MySQL convert "true" to JSON "true" automatically. So it works fine.
• json->'$.alive' == ? : MySQL doesn't convert the parameter "true". Since JSON true != BOOLEAN true (e.g. TINYINT 1), this is false.

Since TRUE is alias of 1 in MySQL, you need to write this complex code.

	query := "SELECT * FROM test_table WHERE json->'$.alive' = CAST(? AS JSON)"
	rows, err := db.Query(query, "true") // Not true, but "true"!

Full code is here.
https://github.com/methane/sandbox/blob/master/2023/go_mysql_1465/main.go

[methane]

There may be a better way. But we are expert of mysql client/server protocol, not query.
Ask support on MySQL community, not on here.

[methane]

Maybe, this is easier.

       query := "SELECT * FROM test_table WHERE CAST(json->'$.alive' AS UNSIGNED) = ?"
       rows, err := db.Query(query, true)

[boyer-victor]

I misspoke. I was issuing to the database via my mysql driver in VSCode. I just tried (in a fresh docker image for sanity) as a prepared statement ie:

PREPARE stmt FROM "SELECT * FROM test_table WHERE json->'$.alive' = ?";
SET @a = true;
EXECUTE stmt USING @a;

Hmm. Interesting though, I was aware of it being stored as a TINYINT(1), but didn't realize that there was some additional type handling that went on with prepared statements here. Thank you for the help, I will try that. (and hopefully not have to have where clauses on json fields anyways for production filtering...)

[methane]

PREPARE stmt FROM "SELECT * FROM test_table WHERE json->'$.alive' = ?";
SET @a = true;
EXECUTE stmt USING @a;

I execute it, but result is "0 rows in set (0.00 sec)".
It is same to this driver.