Bug #102047 Case json behave differently than direct comparison
Submitted: 22 Dec 2020 9:03 Modified: 23 Dec 2020 5:02
Reporter: Zhifeng Hu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: case, json

[22 Dec 2020 9:03] Zhifeng Hu 
Description:
Two semantically equivalent SQL gives different result.

How to repeat:
drop table t1 if exists;
create table t1(f1 json);
insert into t1(f1) values ('"asd"'),('"asdf"'),('"asasas"');
select f1 from t1 where json_extract(f1,"$") = "asd"; -- returns "asd"
select f1 from t1 where case json_extract(f1,"$") when "asd" then 1 else 0 end; -- return empty rows

Suggested fix:
N/A
[22 Dec 2020 14:09] MySQL Verification Team 
Hi Mr. Hu,

Thank you for your bug report.

However, this is not a bug.

CASE has only two syntax variants. In one it accepts a constant, while in the other it accepts a condition.

Instead, you are using a function, which is not supported by SQL. This is all described in chapter 12.5.

Not a bug.
[22 Dec 2020 14:20] MySQL Verification Team 
When I used the correct syntax, I have got the result that was required:

First query:

f1
"asd"

Second query:

f1
"asd"

Not a bug.
[23 Dec 2020 5:02] Zhifeng Hu 
would you please provide the SQL after the correct syntax?
[23 Dec 2020 13:28] MySQL Verification Team 
Hi Mr. Hu,

Thank you for asking the question.

However, this is not a forum for free support, but a forum for bug reporting and this is not a bug.