Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect search results based on time range #208

Open
qiuwenhuifx opened this issue Oct 9, 2021 · 1 comment
Open

Incorrect search results based on time range #208

qiuwenhuifx opened this issue Oct 9, 2021 · 1 comment

Comments

@qiuwenhuifx
Copy link

qiuwenhuifx commented Oct 9, 2021

### 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
Copy link
Author

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;
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant