[Bug] (json) incorrect results of json_contains

[lucienlgl]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.1.8.3

What's Wrong?

When I was using version 2.1.8, I encountered a situation where the json_contains function returned incorrect results.

Here is my test cases:

1. SELECT JSON_CONTAINS('{}', '{"key": "key1", "value": "value1"}');
   --- return: 1
2. SELECT JSON_CONTAINS('{"2":"2","1":"1","3":"3"}', '{"key": "value"}');
   --- return: 1
3. SELECT * FROM table WHERE JSON_CONTAINS('["1","2","3"]', '"1"') and JSON_CONTAINS('["1","2","3"]', '"2"') limit 100;
   --- return: [HY000][1105] errCode = 2, detailMessage = Unexpected exception: Not support comparison between JSONB literals

I found a similar issue, but I'm not sure if it's the same problem: #53291.
I would like to confirm if this is the same problem? If so, is there any plan to fix version 2.1.x ?

What You Expected?

1. SELECT JSON_CONTAINS('{}', '{"key": "key1", "value": "value1"}');
   --- return: 0
2. SELECT JSON_CONTAINS('{"2":"2","1":"1","3":"3"}', '{"key": "value"}');
   --- return: 0
3. SELECT * FROM table WHERE JSON_CONTAINS('["1","2","3"]', '"1"') and JSON_CONTAINS('["1","2","3"]', '"2"') limit 100;
   --- return: successfully executed and returned data

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[WangYa002]

你好，你这个问题和#53291. 本质是一样的，你可以考虑更新版本，如果版本更新后解决了问题，你是否可以给我一个PR。
You can try the following workarounds to bypass this check:

Method 1: Explicit Type Casting Try explicitly casting the string literals to JSON. This can sometimes help the optimizer handle the types correctly:

SQL

SELECT JSON_CONTAINS(CAST('["1","2"]' AS JSON), CAST('"1"' AS JSON));
Method 2: Force Runtime Evaluation (Recommended) Wrap the parameters with a function like concat (adding an empty string). This tricks the optimizer into treating the parameters as dynamic expressions rather than constant literals, forcing the database to evaluate them at runtime:

SQL

-- Append an empty string so it is not treated as a pure literal
SELECT JSON_CONTAINS(concat('', '["1","2","3"]'), '"1"');
Please let me know if these workarounds work for your specific query.

[lucienlgl]

I tried using a higher version 3.0.8, the case 1&2 met the expectations, but case 3 still returned the same error message. Is this issue consistent with #53291 as well?

Case3: SELECT * FROM table WHERE JSON_CONTAINS('["1","2","3"]', '"1"') and JSON_CONTAINS('["1","2","3"]', '"2"') limit 100;
--- return: [HY000][1105] errCode = 2, detailMessage = Not support comparison between JSONB literals

[WangYa002]

Here's a professional and clear reply you can send via email: Hi Lucien, Thanks for testing this on version 3.0.8 and sharing your results. I can confirm that the behavior you're seeing is consistent with my observations. Cases 1 and 2 work as expected, but Case 3 still throws the Not support comparison between JSONB literals error. This does appear to be related to the underlying issue in #53291. It seems the fix for #59692 addressed single JSON_CONTAINS calls, but the problem persists when chaining multiple JSON_CONTAINS functions with AND / OR logic in the WHERE clause. The query planner is still incorrectly treating the boolean return values as JSONB types for comparison. As a temporary workaround before the core issue is fixed, you can use JSON_ARRAY_CONTAINS_AGG to combine the conditions: sql SELECT * FROM table WHERE JSON_ARRAY_CONTAINS_AGG('["1","2","3"]', '"1"', '"2"') limit 100; Or explicitly cast the result to BOOLEAN : sql SELECT * FROM table WHERE CAST(JSON_CONTAINS(...) AS BOOLEAN) AND CAST(JSON_CONTAINS(...) AS BOOLEAN) limit 100; Let's keep an eye on the PRs linked to #53291 for the permanent fix. Best regards, wangyang002

…

---- Replied Message ---- | From | ***@***.***> | | Date | 02/05/2026 16:34 | | To | ***@***.***> | | Cc | ***@***.***>***@***.***> | | Subject | Re: [apache/doris] [Bug] (json) incorrect results of json_contains (Issue #59692) | lucienlgl left a comment (apache/doris#59692) I tried using a higher version 3.0.8, the case 1&2 met the expectations, but case 3 still returned the same error message. Is this issue consistent with #53291 as well? Case3: SELECT * FROM table WHERE JSON_CONTAINS('["1","2","3"]', '"1"') and JSON_CONTAINS('["1","2","3"]', '"2"') limit 100; --- return: [HY000][1105] errCode = 2, detailMessage = Not support comparison between JSONB literals — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>

[JamesIsHuang]

@lucienlgl @WangYa002

I checked the original issue and the earlier fix in #53291.

My understanding is:

• The incorrect-result cases for single JSON_CONTAINS calls (case 1 and case 2 in this issue) were already fixed by #53291.
• The remaining problem is the reopened case 3: chained predicates such as JSON_CONTAINS(...) AND JSON_CONTAINS(...) can still fail in FE with Not support comparison between JSONB literals.

I opened #61097 for this remaining case:
#61097

This PR focuses on the FE-side JSON literal comparison path that is triggered during optimization, so the planner does not crash before execution reaches BE.

[github-actions]

We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!