Unexpected behavior with MVP fields and IN-conditions

Hello everyone,

I’m trying to check two MVP/“multi” fields using an IN() condition each. I’m using the JSON-Interface via the official PHP-Classes. My initial attempt is the following:

{
  'index': 'my_index',
  'limit': 10,
  'offset': 0,
  'sort': [
    { 'b_top_list': { 'order' => 'desc' } },
    { 'stamp_start': { 'order' => 'desc' } },
    { 'id': { 'order' => 'desc' } }    
  ],
  'query': {
    'bool': {
      'must': [
        { 'in': { 'FK_KAT': 64409 } },
        { 'in': { 'FK_MAN_MULTI': [ 247 ] } },
        {
          'bool': {
            'should': [
              { 'in': { 'FK_PRODUCT_ALL': [ 247 ] } },
              { 'in': { 'FK_PRODUCT_MULTI': [ 2608 ] } }
            ]
          }
        }
      ]
    }
  }
}

The important fields are the following types: FK_KAT = int, FK_MAN_MULTI = multi, FK_PRODUCT_ALL = multi, FK_PRODUCT_MULTI = multi

The intended behavior is to mach each entry that fits the following conditions:

  • FK_KAT is qual to 64409
  • FK_MAN_MULTI contains the value 247
  • (FK_PRODUCT_ALL contains the value 247) OR (FK_PRODUCT_MULTI contains the value 2608)

However for some reason this also matches entries which have both FK_PRODUCT_ALL and FK_PRODUCT_MULTI empty. Here a truncated result set from the above example query:

[
  {
    '_id': 46542,
    '_score': 1,
    '_source': {
      'fk_kat': 64409,
      'fk_man_multi': [ 247 ],
      'fk_product_all': [],
      'fk_product_multi': []
    }
  },
  {
    '_id': 46304,
    '_score': 1,
    '_source': {
      'fk_kat': 64409,
      'fk_man_multi': [ 247 ],
      'fk_product_all': [],
      'fk_product_multi': [ 2608 ]
    }
  },
  {
    '_id': 46193,
    '_score': 1,
    '_source': {
      'fk_kat': 64409,
      'fk_man_multi': [ 247 ],
      'fk_product_all': [],
      'fk_product_multi': []
    }
  }
]

I would have expected only the entry with the id 46304 to be matching the query and did not manage to get any query matching that behavior.

My latest desperate try to get it fixed was the replacing the bool > should condition by the following:

{
  'product_match': {
    'script': {
      'inline': '(LENGTH(FK_PRODUCT_ALL) > 0 AND IN(FK_PRODUCT_ALL,247)) OR (LENGTH(FK_PRODUCT_MULTI) > 0 AND IN(FK_PRODUCT_MULTI,2608))'
    }
  }
}

Unfortunally that still returns the same 3 results regardless.

I’d appreciate any tips and gladly provide any further information required to solve this issue. Thanks in advance.

Hi

This can be related to this issue https://github.com/manticoresoftware/manticoresearch/issues/720 . Or at least you can find some examples there and experiment further with the query.