Eq the void instead of operator result to use multi-value MySQL index 8.4

[mmo2112]

Hi, Im using MySQL 8.4. The multi-value index for json is not work when combining with operator, here are 2 analyze:

explain analyze select * from platforms ae1_0 where JSON_CONTAINS(ae1_0.tags->'$[*]', '["RareTag1","RareTag2"]') =1;
[+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (json_contains(cast(json_extract(tags,_utf8mb4'$[*]') as char(255) array),json'["RareTag1", "RareTag2"]') = 1)  (cost=37582 rows=1) (actual time=29.1..29.1 rows=0 loops=1)
    -> Table scan on ae1_0  (cost=37582 rows=1) (actual time=0.0375..22 rows=6000 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> explain analyze select * from platforms ae1_0 where JSON_CONTAINS(ae1_0.tags->'$[*]', '["RareTag1","RareTag2"]');
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: json_contains(cast(json_extract(tags,_utf8mb4'$[*]') as char(255) array),json'["RareTag1", "RareTag2"]')  (cost=4.32 rows=2) (actual time=0.0487..0.0487 rows=0 loops=1)
    -> Index range scan on ae1_0 using idx_tags_mv over ('RareTag2' MEMBER OF (json_extract(tags,_utf8mb4'$[*]'))) OR ('RareTag1' MEMBER OF (json_extract(tags,_utf8mb4'$[*]')))  (cost=4.32 rows=2) (actual time=0.0479..0.0479 rows=0 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)](url)

But when I use the custom registered function for example:
cb.where("json_contains(a.tags, :tags)").eqLiteral(true).setParameter("tags", tags);
or
cb.where("json_contains(a.tags, :tags)").eqLiteral(1).setParameter("tags", tags);
It will build a query to ... = true or = 1, and it will not use my index. How to use eqLiternal("None") or eq("None") or just mark it as void to ignore the operator result of my custom function. Thanks so much for the help.

[beikov]

Hi,

unfortunately, Blaze-Persistence currently has no way to render an expression as predicate directly. The only thing you can do, is to render json_contains(..) and 1 in your JpqlFunction and then use it like cb.where("json_contains(a.tags, :tags)").eqLiteral(1). That way, it will render in SQL as json_contains(..) and 1=1 which will work fine.

[mmo2112]

I have not cleared your context. I think the problem is that if I'm using the JpqlFunction to treat it as a custom-registered function, it may return a class type:
public Class<?> getReturnType(Class<?> firstArgumentType)
And the eqLiteral will map it to = Literal:

 where
        ae1_0.active=? 
        and  json_contains (ae1_0.tags->'$[*]', ?)= LITERAL

The main problem is that the operator = LITERAL will not use the index. :-(

[beikov]

I know, which is why I am suggesting that you workaround this problem by appending and 1 after rendering the json_contains function, so that you end up in SQL with this

 where
        ae1_0.active=? 
        and  json_contains (ae1_0.tags->'$[*]', ?) and 1=1

[mmo2112]

Got it, thanks so much for your help, and have a nice day. :3