Anemometer icon indicating copy to clipboard operation
Anemometer copied to clipboard

Published 20 hours ago • verified publisher icon box

Incorrect search results based on time range

Open qiuwenhuifx opened this issue 2 years ago • 1 comments

### 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 avatar Oct 09 '21 16:10 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; }

qiuwenhuifx avatar Oct 09 '21 17:10 qiuwenhuifx