Feature request: ORDER BY COUNT(DISTINCT field_name) in FACET

[starinacool]

Describe the bug P01: syntax error, unexpected DISTINCT, expecting '*' near 'distinct salesman)

To Reproduce Steps to reproduce the behavior:

1. Run query: select id from listing where match('100 рублей') FACET ut distinct salesman ORDER BY count(distinct salesman) limit 0,50

Expected behavior FACET is sorted by distinct salesman

Describe the environment: Manticore 6.2.12 dc5144d35@230822 Linux manticore-002 6.1.0-13-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.55-1 (2023-09-29) x86_64 GNU/Linux

Messages from log files: NA

[sanikolaev]

Thanks for reporting, but this is not a bug. Perhaps a feature request, but to understand it better, can you please explain what you mean by distinct between ut and salesman in the query? Do you want to group by 3 fields: ut, distinct and salesman? Then you want to sort each of the 3 lists by count(distinct salesman)?

[starinacool]

SELECT id,name FROM listing WHERE match('test') FACET ut DISTINCT salesman ORDER BY count(distinct salesman) DESC

[sanikolaev]

FACET is a simplified form of GROUP BY with limited functionality. Here's what's said in the docs about sorting in FACET:

Sorting can be done on attribute name, count (using COUNT(*) ), or the special FACET() function, which provides the aggregated data values.

I believe that's it. Try using GROUP BY instead, e.g.:

mysql> drop table if exists t; create table t(a int, b int); insert into t(a,b) values(1,2),(1,1),(2,3),(2,1),(2,5); select a, count(distinct b) cnt from t group by a order by cnt desc;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(a int, b int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(a,b) values(1,2),(1,1),(2,3),(2,1),(2,5)
--------------

Query OK, 5 rows affected (0.01 sec)

--------------
select a, count(distinct b) cnt from t group by a order by cnt desc
--------------

+------+------+
| a    | cnt  |
+------+------+
|    2 |    3 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

[starinacool]

Thanks for advice. Lets make it a feature request: sort by distinct count in facets.

[sanikolaev]

Spec

drop table if exists t; create table t(a int, b int); insert into t(a,b) values(1,2),(1,1),(2,3),(2,1),(2,5),(2,5);

select * from t facet a order by count(distinct b) desc

+---------------------+------+------+
| id                  | a    | b    |
+---------------------+------+------+
| 1515634980905025746 |    1 |    2 |
| 1515634980905025747 |    1 |    1 |
| 1515634980905025748 |    2 |    3 |
| 1515634980905025749 |    2 |    1 |
| 1515634980905025750 |    2 |    5 |
| 1515634980905025751 |    2 |    5 |
+---------------------+------+------+
6 rows in set (0.00 sec)
--- 6 out of 6 results in 0ms ---

+------+-------------------+
| a    | count(distinct b) |
+------+-------------------+
|    2 |                 3 |
|    1 |                 2 |
+------+-------------------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---