FACET return null row


#1

Hi there,

I just installed manticoresearch 2.6.1 to test it, made a delta/main structure and noticed that adding FACET query after WHERE condition that return 0 results, a row will be added
±-------------±-------------±------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
±-----±--------±----------±---------±-----±-------------±-------±----------±-------±-----------±-------±-------±------------±--------------±---------±---------------±-------------±-------------±------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query is: select * from main where is_banned=1 FACET user_type
is_banned=1 returns empty set without FACET.


#2

That’s not the expected behaviour,it should return an empty result set, we opened a bug ticket about: https://github.com/manticoresoftware/manticoresearch/issues/54

Thanks for the finding!


#3

Hello! The issue was fixed. You’re welcome to check


#4

Yep, working now. I replaced sphinx with manticore on the devel server to see if is 100% compatible and scalable.
I was wondering if you plan to add bitwise support for where conditions, right now i’m using if(column&bit) as sort_column for filtering, but would be great to filter directly.


#5

We’re having in backlog to make expression as filter, i.e. to write any expression at filter without select alias then filter on that column. That is more general and fits his will too.
We’re also planning to implement expression direct at WHERE clause. You can also make an issue in our github bugtracker to force the process https://github.com/manticoresoftware/manticoresearch/issues


#6

Just found another bug, in sphinxsearch I was using dynamic max_matches:

$query->limit($offset, $page_size);
if($offset+$page_size>0)  $query->option('max_matches', $offset+$page_size);

All good, except that this max_matches is applied to facet and it shouldn’t. For facet I used to limit by FACET package LIMIT X


#7

Thanks for reporting, I’ve added it to our backlog, you can watch the progress in Github https://github.com/manticoresoftware/manticoresearch/issues/59


#8

Another one:
id | name | cities(mva) | work_city_id(int)

MySQL [(none)]> SELECT * FROM rt_index WHERE work_city_id > 0 LIMIT 0, 45 FACET work_city_id AS cities ORDER BY COUNT(*) DESC LIMIT 0, 999;
ERROR 1064 (42000): index rt_index: alias ‘cities’ must be unique (conflicts with another alias)

When I replace * with columns concatenated (SELECT id, name, cities, work_city_id FROM rt_index WHERE work_city_id > 0 LIMIT 0, 45 FACET work_city_id AS cities ORDER BY COUNT(*) DESC LIMIT 0, 999;) , all good.


#9

“All good, except that this max_matches is applied to facet and it shouldn’t. For facet I used to limit by FACET package LIMIT X”

The FACET reuse the result of the main query. This is why facets are very fast and this is why the number of facets that can be returned is limited by the query max_matches. LIMIT just say how many facets should be returned, but you can’t have it greater than query max_matches. You need to increase max_matches if you need a higher limit in facets.

On the aliases, please submit a github issue about it.


#10

I got this problem after migrating from sphinxsearch, so I assumed this was normal behaviour. Maybe they had a bug, not mantis :slight_smile:


#11

Facets ‘obey’ max_matches in sphinx too, only in some earlier version they were hardcoded to 1000 (the default max_matches), but that was fixed from what I remember in 2.2.11.

The problem in your case is at low $offset values. You could do a ($offset+$page_size>$min_groups)?$offset+$page_size: $min_groups , where $min_groups is a value that can cover the number of groups you could have in a facet.