total_found for a query with GROUP BY x HAVING y

[mytskine]

I think that it's impossible to fetch the number of matching groups for some queries. Please correct me if I'm wrong.

My use case is like this: SELECT *, (...) someexpr FROM mytable GROUP BY someid HAVING someexpr LIMIT 10. The results are paginated, hence the LIMIT. The pagination also needs to display the total number of results. When the select has no HAVING condition, I can just send a SHOW META and read total_found.

With HAVING, that meta value is irrelevant, as documented. A query may return 5 groups (out of 5, since the limit is 10), but with a total_found of 20 or whatever.

Even if I where to send a second SELECT, I can't see any way to count the groups. Manticore's SQL does not allow a COUNT(*) on a sub-query. Is there a way to count the grouped results when the query filters the groups?

[mytskine]

tl;dr: In case of HAVING, the total_found value of SHOW META is wrong. Is there any workaround?

[sanikolaev]

@mytskine Here' how to reproduce this from scratch:

manticore-load \
--quiet \
--drop \
--batch-size=1000 \
--total=10000 \
--init="CREATE TABLE test(a int)" \
--load="INSERT INTO test(id,a) VALUES(<increment>,<int/1/100>)"

# <int/1/100> means there're 100 different random values for "a"

Threads ; Batch     ; Time        ; Total Docs  ; Docs/Sec    ; Avg QPS     ; p99 QPS     ; p95 QPS     ; p5 QPS      ; p1 QPS      ; Lat Avg     ; Lat p50     ; Lat p95     ; Lat p99     ;
1       ; 1000      ; 00:00       ; 10000       ; 686533      ; 0           ; 0           ; 0           ; 0           ; 0           ; 1.4         ; 1.5         ; 1.5         ; 1.5         ;


mysql -v -P9306 -h0 -e "select *, a, count(*) as c from test group by a having c > 1000; show meta"
--------------
select *, a, count(*) as c from test group by a having c > 1000
--------------

--------------
show meta
--------------

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| total          | 0     |
| total_found    | 100   |
| total_relation | eq    |
| time           | 0.001 |
+----------------+-------+

Expected: total_found = 0.

mysql -v -P9306 -h0 -e "select *, a, count(*) as c from test group by a having c = 105; show meta"
--------------
select *, a, count(*) as c from test group by a having c = 105
--------------

+------+------+------+
| id   | a    | c    |
+------+------+------+
|   33 |   76 |  105 |
|  110 |   39 |  105 |
+------+------+------+
--------------
show meta
--------------

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| total          | 2     |
| total_found    | 100   |
| total_relation | eq    |
| time           | 0.001 |
+----------------+-------+

Expected: total_found = 2. I can't think of a simple workaround. It's better to open an issue for this, and we'll see if there's anything we can improve.

[mytskine]

Thanks. The feature request is #3428.