Implement the MVA return type for the UDF function

General Discussions - English
1

Hi all,

I posted my problem in this discussion: Using IF function in FACET - #2 by Jan_Malak.

I can’t find a solution to my problem of computing facets.

The only way it might work is with UDF, but it can’t return MVA values.

My question is: how difficult would it be to add MVA support for UDFs?

And why is it not supported?

I’ve seen many users on various forums who have failed to resolve their problems due to a lack of flexibility in UDF.

Supporting MVA or JSON-type return values would dramatically increase the usability of Manticore Search.

2

We live in the age of AI. Why don’t you try to build a prototype with Cursor or something similar?
If you succeed and it works in general, the core team will be happy to review and polish your PR.

3

The related PR is MVA and Float Vector UDF support by sanikolaev · Pull Request #3865 · manticoresoftware/manticoresearch · GitHub

Here’s how you can check it out:

git clone https://github.com/manticoresoftware/manticoresearch.git
cd manticoresearch
git checkout udf_mva
docker run -v $(pwd):/manticore -it --rm ghcr.io/manticoresoftware/manticoresearch:test-kit-udf_mva bash
apt -y install gcc
cd /manticore/src/
gcc -fPIC -shared -o udfexample.so udfexample.c
cp udfexample.so /usr/local/lib/manticore/
searchd

mysql -v -P9306 -h0 -e "CREATE FUNCTION makemva RETURNS MULTI SONAME 'udfexample.so'"
mysql -v -P9306 -h0 -e "CREATE FUNCTION makemva64 RETURNS MULTI64 SONAME 'udfexample.so'"
mysql -v -P9306 -h0 -e "CREATE FUNCTION makefloatvec RETURNS FLOAT_VECTOR SONAME 'udfexample.so'"

manticore-load \
--batch-size=1000 \
--threads=5 \
--total=1000000 \
--init="CREATE TABLE test(id bigint, name text, type int)" \
--load="INSERT INTO test(id,name,type) VALUES(<increment>,'<text/10/100>',<int/1/100>)"

Result:

root@28d6cf82d3e8:/manticore/src# mysql -v -P9306 -h0 -e "SELECT id, makemva64() as mva64_values FROM test"
mysql -v -P9306 -h0 -e "SELECT id, makefloatvec() as float_vec FROM test limit 3\G"
mysql -v -P9306 -h0 -e "SELECT id, type FROM test facet makemva()"
--------------
SELECT id, makemva64() as mva64_values FROM test
--------------

+--------+-------------------------------------------------------------------------------------------+
| id     | mva64_values                                                                              |
+--------+-------------------------------------------------------------------------------------------+
| 983001 | 6879,6201,2735,9183,9587,6641,1279,6224,3610,8417,8739,9100                               |
| 720001 | 7437,7501,5864,2819,6472,8889,3192,1540,5297,1395,5730,1659,8895,6124,2967                |
| 920001 | 8669,6130,6919,1129                                                                       |
| 342001 | 7452,3033,1293                                                                            |
|  67001 | 9689,6074,5482,5165,5971,3540,6329,2183,405,8971,3129,2484                                |
| 279001 | 3131                                                                                      |
| 669001 | 3345,2273,4364,7882,4972,8391,796,6855,4672,5712,8179,3722,1362                           |
| 405001 | 9854                                                                                      |
| 572001 | 9219,7974,5574,9491,890,1402,3087,8027,9818,593,7396,9976,4479,6834,2641,5,3835           |
|  36001 | 2717,2910,1188,1462,6568,766,3330,338,6546,1865,2642,2628,150,222,740,9738,7222,9858,8283 |
| 279002 | 9026,3669                                                                                 |
|  67002 | 5251,1953,3453,6536,5232,7720,9913,7312,7738,8875,3941,3903,7857                          |
| 983002 | 1806,5751,5971,3589,9164,2305,3387,7555,2096,286,618,7139,2515                            |
| 720002 | 7641,2311,1198,9703,7615,8692,4120,4028,7560,5636,1854,6092                               |
|  36002 | 9295,4405,3006,3833,6740,5490,8758,9844,7208,3828,7983,3261,4585,6631,1434,1896,4029      |
| 405002 | 4485,1308,3582                                                                            |
| 342002 | 4366,9573,7280,1485,3501,3352,4666,7925,232,4756,8948,4506,6369,2703,4976,1989            |
| 572002 | 7253,2741,6571,7264,3924,724,7209                                                         |
| 669002 | 9790,7935                                                                                 |
| 920002 | 9822,5892,392,6070,1296,7420,3988,6254,3310,7145,662,6306                                 |
+--------+-------------------------------------------------------------------------------------------+
--------------
SELECT id, makefloatvec() as float_vec FROM test limit 3
--------------

*************************** 1. row ***************************
       id: 983001
float_vec: 99.500000,34.299999,5.700000,26.700001,19.600000,27.799999,67.099998,36.500000,91.699997,19.799999,7.200000,84.300003,28.600000,42.799999,52.099998,18.799999,30.500000,75.500000,45.500000,4.300000,97.699997,52.200001,33.299999,54.500000,11.300000,9.500000,26.000000,98.599998,62.700001,36.099998,86.900002,97.400002,5.700000,92.599998,24.100000,25.299999,55.700001,26.400000,61.900002,47.400002,81.400002,4.300000,31.799999,45.200001,82.400002,19.100000,64.099998,12.900000,29.799999,44.799999,17.200001,27.500000,97.099998,85.699997,82.099998,43.599998,95.300003,43.299999,42.200001,93.199997,79.400002,29.200001,90.699997,20.299999,57.000000,50.000000,45.700001,12.700000,11.700000,42.799999,95.400002,93.099998,82.300003,62.400002,38.400002,64.699997,81.500000,37.700001,77.699997,11.300000,82.500000,30.100000,74.000000,14.800000,15.900000,56.099998,58.500000,46.400002,34.599998,35.900002,39.599998,49.299999,0.300000,65.500000,69.599998,57.400002,15.600000,15.300000,5.300000,27.299999,93.300003,35.900002,20.400000,75.699997,98.300003,94.000000,40.400002,15.000000,31.700001,18.100000,61.500000,14.300000,48.299999,35.599998,64.300003,99.400002,26.900000,22.799999,45.799999,61.599998,94.000000,20.600000,46.099998,94.300003,86.199997,15.700000,86.900002,37.000000
*************************** 2. row ***************************
       id: 920001
float_vec: 57.500000,56.599998,47.900002,21.900000,79.599998,66.400002,82.199997,84.800003,99.599998,97.500000,18.100000,18.100000,57.599998,33.799999,54.400002,3.400000,65.900002,68.599998,74.099998,97.099998,63.200001,59.000000,76.599998,75.599998,58.200001,43.799999,39.400002,24.700001,15.600000,89.800003,46.099998,8.300000,46.400002,29.200001,30.299999,61.299999,95.599998,47.700001,81.300003,95.199997,45.200001,34.700001,13.400000,2.800000,68.500000,3.000000,41.400002,34.400002,71.599998,50.799999,66.699997,34.900002,9.800000,43.299999,10.500000,3.200000,22.299999,49.900002,27.900000,38.000000,74.900002,74.000000,46.299999,56.599998,3.200000,11.800000,53.099998,34.000000,59.500000,34.400002,29.299999,40.000000,69.099998,22.700001,63.200001,76.699997,49.299999,55.799999,11.600000,17.600000,59.400002,61.400002,92.800003,22.600000,95.300003,98.500000,78.199997,93.400002,12.500000,1.700000,73.000000,57.200001,91.699997,49.700001,14.300000,17.500000,50.400002,5.100000,28.700001,32.400002,66.500000,4.500000,18.600000,43.099998,29.299999,26.000000,48.599998,30.400000,48.500000,32.599998,72.800003,36.799999,72.699997,61.599998,67.199997,36.400002,17.799999,50.599998,57.900002,3.200000,18.200001,6.500000,42.200001,83.599998,46.900002,90.300003,67.800003,57.599998
*************************** 3. row ***************************
       id: 720001
float_vec: 32.500000,77.000000,78.599998,20.700001,53.900002,38.299999,7.000000,52.000000,85.400002,29.600000,13.400000,21.100000,44.799999,81.300003,57.900002,34.799999,8.800000,68.000000,68.699997,22.400000,24.400000,49.599998,2.100000,47.700001,97.099998,95.699997,72.300003,73.300003,44.599998,82.300003,10.100000,77.199997,59.400002,23.900000,97.900002,48.500000,62.200001,40.099998,0.500000,82.800003,5.000000,49.099998,4.000000,49.799999,65.699997,61.900002,84.599998,9.700000,29.900000,88.599998,32.099998,78.800003,5.600000,59.900002,5.500000,98.699997,65.599998,4.700000,51.200001,10.800000,79.199997,69.199997,86.199997,31.400000,98.699997,85.599998,48.000000,86.099998,43.900002,27.900000,8.200000,8.000000,33.099998,58.900002,78.699997,23.299999,32.299999,8.500000,4.800000,23.900000,56.900002,25.700001,45.299999,1.500000,15.300000,4.600000,1.700000,7.600000,93.900002,52.099998,41.400002,67.699997,62.099998,71.900002,61.700001,62.099998,71.300003,70.300003,22.200001,28.700001,86.500000,46.900002,62.099998,8.700000,13.400000,93.400002,66.199997,53.400002,78.800003,70.500000,17.400000,94.300003,20.700001,38.900002,60.299999,53.900002,31.100000,60.700001,73.400002,52.400002,12.900000,24.100000,24.799999,26.200001,67.000000,60.500000,14.200000,13.000000
--------------
SELECT id, type FROM test facet makemva()
--------------

+--------+------+
| id     | type |
+--------+------+
| 983001 |    7 |
| 279001 |    9 |
| 920001 |   80 |
| 405001 |   85 |
| 572001 |   14 |
| 669001 |   93 |
| 342001 |   51 |
|  36001 |   23 |
|  67001 |   60 |
| 720001 |   10 |
| 572002 |   64 |
| 983002 |   33 |
| 669002 |   84 |
| 405002 |   16 |
| 279002 |   48 |
| 342002 |    9 |
| 920002 |   80 |
| 720002 |   77 |
|  36002 |   18 |
|  67002 |   55 |
+--------+------+
+-----------+----------+
| makemva() | count(*) |
+-----------+----------+
|       100 |    10600 |
|       207 |    10512 |
|       699 |    10506 |
|       827 |    10658 |
|       356 |    10441 |
|        54 |    10263 |
|       553 |    10539 |
|       754 |    10501 |
|       153 |    10451 |
|       655 |    10454 |
|       193 |    10535 |
|        97 |    10508 |
|       422 |    10578 |
|       145 |    10571 |
|       206 |    10551 |
|       781 |    10394 |
|        47 |    10362 |
|       238 |    10561 |
|       460 |    10460 |
|       226 |    10468 |
+-----------+----------+
4

Thank you for your great work!
We will test your code in our environment over the next few days.
I will let you know how it goes.

5

Hello,

We have tested your code and everything went OK — our custom UDF has been added.

I’m not sure if this is an error, but when we use it in an HTTP JSON query through script_fields, it returns an error:
P09: syntax error, unexpected '(', expecting $end near ''attribs.variants', 'attrib_22', '{\"attrib_1\":[31]}')'
Our http json request script_fields:

{
    "script_fields": {
        "attrib_1": {
            "script": {
                "inline": "ANY(x = 31 FOR x IN attribs.attrib_1.values)"
            }
        },
        "attrib_22_composites": {
            "script": {
                "inline": "csvariantmva('attribs.variants', 'attrib_22', '{\"attrib_1\":[31]}')"
            }
        }
    }
}

Same error if we use it directly inside FACET like:

FACET csvariantmva('attribs.variants', 'attrib_22', '{\"attrib_1\":[31]}') attrib_22_composites

We cannot currently test whether it is calculating the MVA values correctly.