Results out of order on graph since update to 0.16

[mmomjian]

I have a strange issue where the results since the update are showing up "before" the migrated results on graphs. Please see the attached images.

speedtesttracker=# select * from results order by created_at desc limit 3;
 id  | service |  ping  | download |  upload  | comments |                                                                                                                                                                                                                                                                                                                                                                                                                                                      data                                                                                                                                                                                                                                                                                                                                                                                                                                                       |  status   | scheduled |     created_at      |     updated_at
-----+---------+--------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+---------------------+---------------------
 216 | ookla   |  7.344 |  7948981 | 10748894 |          | {"type":"result","timestamp":"2024-02-20T16:50:04Z","ping":{"jitter":1.484,"latency":7.344,"low":5.932,"high":9.56},"download":{"bandwidth":7948981,"bytes":74052168,"elapsed":9500,"latency":{"iqm":17.998,"low":9.562,"high":245.445,"jitter":7.39}},"upload":{"bandwidth":10748894,"bytes":101840736,"elapsed":9808,"latency":{"iqm":17.019,"low":9.458,"high":263.194,"jitter":6.949}},"ce":9938-83f33dae872d","persisted":true}}          | completed | f         | 2024-02-20 16:50:04 | 2024-02-20 16:50:04
 215 | ookla   | 17.721 | 12661247 | 12349043 |          | {"type":"result","timestamp":"2024-02-20T06:07:51Z","ping":{"jitter":15.931,"latency":17.721,"low":9.117,"high":45.788},"download":{"bandwidth":12661247,"bytes":71715344,"elapsed":5602,"latency":{"iqm":119.056,"low":19.924,"high":247.805,"jitter":35.75}},"upload":{"bandwidth":12349043,"bytes":171125596,"elapsed":15009,"latency":{"iqm":23.528,"low":10.077,"high":365.168,"jitter":16.977}},""persisted":true}} | completed | f         | 2024-02-20 06:07:51 | 2024-02-20 06:07:51
 214 | ookla   | 10.215 | 13768046 | 14817872 |          | {"type":"result","timestamp":"2024-02-20T01:15:20Z","ping":{"jitter":2.709,"latency":10.215,"low":9.445,"high":19.346},"download":{"bandwidth":13768046,"bytes":73959220,"elapsed":5301,"latency":{"iqm":171.129,"low":9.352,"high":246.491,"jitter":53.652}},"upload":{"bandwidth":14817872,"bytes":153968824,"elapsed":10705,"latency":{"iqm":19.076,"low":13.981,"high":124.628,"jitter":2.004}},"packetLoss":0,"isp":"9f4e61a4-4655-47f4-94ab-f7ec5c51d614","url":"https://www.speedtest.net/result/c/9f4e61a4-4655-47f4-94ab-f7ec5c51d614","persisted":true}}      +| completed | t         | 2024-02-19 20:15:21 | 2024-02-20 06:04:08
     |         |        |          |          |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |           |           |                     |
(3 rows)

[alexjustesen]

I had a random thought at work today #1195 thinking that bps to mbps or gbps might screw with table sorting and charts. I think this might have just validated that assumption.

[alexjustesen]

@mmomjian I wasn't able to reproduce this in v0.16.4. Did you do any manual data moves or update the created_at column in the results table?

These queries sort by latest() which is based on the created_at column.

[mmomjian]

@alexjustesen The only query I ran was update results set status = 'completed' where status = 'failed';. Would you like a bigger dump of the results DB?

[alexjustesen]

Nope that should of been fine. Did you restart the container after making the update just in case it's a caching issue?

[mmomjian]

Yes, I just restarted it again and it's the same behavior - I just ran another new test and it appears on the left side.

[mmomjian]

So I'm not 100% sure why, but the order of the entries on the graph seems to correspond with the order in which they are returned from the PG database. Is it possible that the graphs are not being sorted when selected and just by luck, this wasn't an issue before the change in result structure?

speedtesttracker=# select id,status,created_at,updated_at from results where created_at > now() - interval '7 day';
 id  |  status   |     created_at      |     updated_at
-----+-----------+---------------------+---------------------
 216 | completed | 2024-02-20 16:50:04 | 2024-02-20 16:50:04
 217 | completed | 2024-02-20 20:15:21 | 2024-02-20 20:15:21
 218 | completed | 2024-02-21 02:09:57 | 2024-02-21 02:09:57
 219 | completed | 2024-02-21 02:15:27 | 2024-02-21 02:15:27
 215 | completed | 2024-02-20 06:07:51 | 2024-02-20 06:07:51
 191 | completed | 2024-02-14 02:15:21 | 2024-02-20 06:04:08
 192 | completed | 2024-02-14 08:15:28 | 2024-02-20 06:04:08
 193 | completed | 2024-02-14 14:15:23 | 2024-02-20 06:04:08
 194 | completed | 2024-02-14 20:15:28 | 2024-02-20 06:04:08
 195 | completed | 2024-02-15 02:15:21 | 2024-02-20 06:04:08
 196 | completed | 2024-02-15 08:15:24 | 2024-02-20 06:04:08
 197 | completed | 2024-02-15 14:15:21 | 2024-02-20 06:04:08
 198 | completed | 2024-02-15 20:15:25 | 2024-02-20 06:04:08
 199 | completed | 2024-02-16 02:15:29 | 2024-02-20 06:04:08
 200 | completed | 2024-02-16 08:15:30 | 2024-02-20 06:04:08
 201 | completed | 2024-02-16 14:15:22 | 2024-02-20 06:04:08
 202 | completed | 2024-02-16 20:15:28 | 2024-02-20 06:04:08
 203 | completed | 2024-02-17 02:15:27 | 2024-02-20 06:04:08
 204 | completed | 2024-02-17 08:15:31 | 2024-02-20 06:04:08
 205 | completed | 2024-02-17 14:15:27 | 2024-02-20 06:04:08
 206 | completed | 2024-02-17 20:15:31 | 2024-02-20 06:04:08
 207 | completed | 2024-02-18 02:15:23 | 2024-02-20 06:04:08
 208 | completed | 2024-02-18 08:15:24 | 2024-02-20 06:04:08
 209 | completed | 2024-02-18 14:15:32 | 2024-02-20 06:04:08
 210 | completed | 2024-02-18 20:15:27 | 2024-02-20 06:04:08
 211 | completed | 2024-02-19 02:15:24 | 2024-02-20 06:04:08
 212 | completed | 2024-02-19 08:15:32 | 2024-02-20 06:04:08
 213 | completed | 2024-02-19 14:15:22 | 2024-02-20 06:04:08
 214 | completed | 2024-02-19 20:15:21 | 2024-02-20 06:04:08
(29 rows)

[mmomjian]

This can even be seen with ID 215, which is before the other new entries, but appears after it on the graph (2024-02-20 06:07:51 / 01:07 local time)

[mmomjian]

I don't see a ORDER BY specification in speedtest-tracker/app/Filament/Widgets/RecentSpeedChartWidget.php (or Ping, or Jitter)

        $results = Result::query()
            ->select(['id', 'download', 'upload', 'created_at'])
            ->where('status', '=', ResultStatus::Completed)
            ->when($this->filter == '24h', function ($query) {
                $query->where('created_at', '>=', now()->subDay());
            })
            ->when($this->filter == 'week', function ($query) {
                $query->where('created_at', '>=', now()->subWeek());
            })
            ->when($this->filter == 'month', function ($query) {
                $query->where('created_at', '>=', now()->subMonth());
            })
            ->get();

[alexjustesen]

I think you happened on a bug no one has noticed yet, the current sql statement actually doesn't order by latest (aka the created_at date). This never cropped up earlier because there wouldn't be an instance of the PK being out of order from the timestamp.

select `id`, `download`, `upload`, `created_at` from `results` where `status` = ? and `created_at` >= ?

Source: https://github.com/alexjustesen/speedtest-tracker/blob/main/app/Filament/Widgets/RecentSpeedChartWidget.php#L39-L51

Good catch, fix incoming.

[mmomjian]

Confirming that this bug is fixed. Thanks!