Incorrect search results based on time range

[qiuwenhuifx]

### This is a query based on time period Click Show Raw SQL to display the SQL（md5 is an extra function I added to hide the SQL text），The result set differs from the slow log，the result is incorrect

SELECT checksum AS checksum, md5(LEFT(dimension.sample, 20)) AS snippet
-> , ROUND(SUM(Rows_examined_sum) / SUM(rows_sent_sum), 2) AS index_ratio
-> , SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg
-> , ROUND(SUM(Rows_sent_sum) / SUM(ts_cnt), 0) AS rows_sent_avg
-> , SUM(ts_cnt) AS ts_cnt, SUM(Query_time_sum) AS Query_time_sum
-> , SUM(Lock_time_sum) AS Lock_time_sum, SUM(Rows_sent_sum) AS Rows_sent_sum
-> , SUM(Rows_examined_sum) AS Rows_examined_sum, SUM(Full_scan_sum) AS Full_scan_sum
-> , SUM(Tmp_table_sum) AS Tmp_table_sum, SUM(Filesort_sum) AS Filesort_sum
-> FROM global_query_review fact
-> JOIN global_query_review_history dimension USING (checksum)
-> WHERE dimension.hostname_max = "hostname-aaa"
-> AND dimension.ts_min >= "2021-10-09 13:00:00"
-> AND dimension.ts_min <= "2021-10-09 19:00:00"
-> GROUP BY checksum
-> ORDER BY Query_time_sum DESC
-> LIMIT 20;
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
| checksum | snippet | index_ratio | query_time_avg | rows_sent_avg | ts_cnt | Query_time_sum | Lock_time_sum | Rows_sent_sum | Rows_examined_sum | Full_scan_sum | Tmp_table_sum | Filesort_sum |
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
| 11070395044304735197 | 144af85b1febd5eea29f1f55627dc8cd | 11928.59 | 2.0607059001922607 | 240 | 2 | 4.1214118003845215 | 0.00013699999544769526 | 480 | 5725724 | NULL | NULL | NULL |
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
1 row in set (0.01 sec)

### If I change ts_min to ts_max to get more rows, this is also the correct result

SELECT checksum AS checksum, md5(LEFT(dimension.sample, 20)) AS snippet
-> , ROUND(SUM(Rows_examined_sum) / SUM(rows_sent_sum), 2) AS index_ratio
-> , SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg
-> , ROUND(SUM(Rows_sent_sum) / SUM(ts_cnt), 0) AS rows_sent_avg
-> , SUM(ts_cnt) AS ts_cnt, SUM(Query_time_sum) AS Query_time_sum
-> , SUM(Lock_time_sum) AS Lock_time_sum, SUM(Rows_sent_sum) AS Rows_sent_sum
-> , SUM(Rows_examined_sum) AS Rows_examined_sum, SUM(Full_scan_sum) AS Full_scan_sum
-> , SUM(Tmp_table_sum) AS Tmp_table_sum, SUM(Filesort_sum) AS Filesort_sum
-> FROM global_query_review fact
-> JOIN global_query_review_history dimension USING (checksum)
-> WHERE dimension.hostname_max = "hostname-aaa"
-> AND dimension.ts_max >= "2021-10-09 13:00:00"
-> AND dimension.ts_max <= "2021-10-09 19:00:00"
-> GROUP BY checksum
-> ORDER BY Query_time_sum DESC
-> LIMIT 20;
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
| checksum | snippet | index_ratio | query_time_avg | rows_sent_avg | ts_cnt | Query_time_sum | Lock_time_sum | Rows_sent_sum | Rows_examined_sum | Full_scan_sum | Tmp_table_sum | Filesort_sum |
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
| 12743526763180229305 | 5faa406212f3c73009303ba851a288b1 | NULL | 1.546236866154918 | 0 | 59567 | 92104.69140625 | 4.741690993309021 | 0 | 109273536512 | NULL | NULL | NULL |
| 5253522201880917319 | 0e8bcd23515b2b89a626931c88635f10 | NULL | 165.2709270974864 | 0 | 23 | 3801.2313232421875 | 0.002383999992161989 | 0 | 2218476736 | NULL | NULL | NULL |
| 5997253274987855628 | 8d3bf62e9c27fd5351847720597e865e | 1860.99 | 3.478203957910696 | 1000 | 724 | 2518.2196655273438 | 0.04947599861770868 | 724000 | 1347358016 | NULL | NULL | NULL |
| 17023047582206463798 | 9006ea6bf83f368379c53a622a6790e9 | 619101.62 | 2.3912056750199926 | 1 | 127 | 303.68312072753906 | 0.015751000493764877 | 151 | 93484344 | NULL | NULL | NULL |
| 10399670704722124502 | 727fc474ae052c716aa09e8b23cd33a2 | 736096.50 | 2.3611790213997907 | 1 | 127 | 299.86973571777344 | 0.023529999889433384 | 127 | 93484256 | NULL | NULL | NULL |
| 2733142671693210148 | 16bd0ff3c77a018193ead1675d4215cf | 1872668.15 | 1.2641746952848614 | 1 | 53 | 67.00125885009766 | 0.0024400000111199915 | 53 | 99251412 | NULL | NULL | NULL |
| 11070395044304735197 | 144af85b1febd5eea29f1f55627dc8cd | 11928.59 | 2.0607059001922607 | 240 | 2 | 4.1214118003845215 | 0.00013699999544769526 | 480 | 5725724 | NULL | NULL | NULL |
+----------------------+----------------------------------+-------------+--------------------+---------------+--------+--------------------+------------------------+---------------+-------------------+---------------+---------------+--------------+
7 rows in set (0.00 sec)

[qiuwenhuifx]

I have tried to make this modification, Search by time result is correct

for this file: config.inc.php
'dimension' => array(
'extra_fields' => 'where',
'hostname_max' => 'clear|where',
'ts_min' => 'date_range|reldate|clear|where',
'pivot-hostname_max' => 'clear|pivot|select',
'pivot-checksum' => 'clear|pivot|select',
change to
'dimension' => array(
'extra_fields' => 'where',
'hostname_max' => 'clear|where',
'ts_max' => 'date_range|reldate|clear|where',
'pivot-hostname_max' => 'clear|pivot|select',
'pivot-checksum' => 'clear|pivot|select',

for this file:AnemometerModel.php
switch ($type)
{
case 'time':
return 'ts_min';
case 'hostname':
return 'hostname_max';
case 'fingerprint':
return 'fingerprint';
default:
return $type;
}
change to

switch ($type)
{
case 'time':
return 'ts_max';
case 'hostname':
return 'hostname_max';
case 'fingerprint':
return 'fingerprint';
default:
return $type;
}