You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "lucienlgl (via GitHub)" <gi...@apache.org> on 2026/01/08 13:44:44 UTC
[I] [Bug] (json) incorrect results of json_contains [doris]
lucienlgl opened a new issue, #59692:
URL: https://github.com/apache/doris/issues/59692
### Search before asking
- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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: https://github.com/apache/doris/pull/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
- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "lucienlgl (via GitHub)" <gi...@apache.org>.
lucienlgl closed issue #59692: [Bug] (json) incorrect results of json_contains
URL: https://github.com/apache/doris/issues/59692
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "lucienlgl (via GitHub)" <gi...@apache.org>.
lucienlgl commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-3847704695
> 你好，你这个问题和[#53291](https://github.com/apache/doris/pull/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.
我
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "WangYa002 (via GitHub)" <gi...@apache.org>.
WangYa002 commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-3851909905
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: ***@***.***>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-5518386089
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!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "lucienlgl (via GitHub)" <gi...@apache.org>.
lucienlgl commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-3851879359
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 https://github.com/apache/doris/pull/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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "JamesIsHuang (via GitHub)" <gi...@apache.org>.
JamesIsHuang commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-4009973206
@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:
https://github.com/apache/doris/pull/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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "WangYa002 (via GitHub)" <gi...@apache.org>.
WangYa002 commented on issue #59692:
URL: https://github.com/apache/doris/issues/59692#issuecomment-3767724513
你好，你这个问题和https://github.com/apache/doris/pull/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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] (json) incorrect results of json_contains [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #59692: [Bug] (json) incorrect results of json_contains
URL: https://github.com/apache/doris/issues/59692
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org