1798382 - Bugzilla queries that involve change history are rejected by MySQL when using date shortcuts and the calculated timestamp is an invalid date

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: Search, defect)

Description

[Alex Hochheiden [:ahochheiden] [he/him]]

I encountered this while using my triage bookmark: https://bugzilla.mozilla.org/buglist.cgi?chfieldfrom=-1m&emailtype1=exact&bug_status=UNCONFIRMED&bug_status=NEW&bug_status=REOPENED&email1=nobody%40mozilla.org&priority=--&query_format=advanced&product=Firefox%20Build%20System&chfield=%5BBug%20creation%5D&known_name=incoming%20bugs&component=Bootstrap%20Configuration&component=General&component=General%3A%20Unsupported%20Platforms&component=Mach%20Core&emailassigned_to1=1&query_based_on=incoming%20bugs&order=Bug%20Number&chfieldto=Now&list_id=16269958

I expected several results that I'd intentionally left open for a while, but instead got zero results.

This bug should match the query: https://bugzilla.mozilla.org/show_bug.cgi?id=1796523

Glob poked it a bit and found that it had something to do with just the change history, and a barebones example of the issue is: https://bugzilla.mozilla.org/buglist.cgi?chfieldfrom=-1m&query_format=advanced&chfield=[Bug%20creation]&chfieldto=Now&debug=1

Comment 1

[David Lawrence [:dkl]]

2022/10/31 18:56:56  ERROR | Bugzilla.App.CGI.buglist_cgi | buglist.cgi?emailassigned_to1=1&query_based_on=incoming%20bugs&chfield=%5BBug%20creation%5D&emailtype1=exact&list_id=4&priority=--&bug_status=UNCONFIRMED&bug_status=NEW&bug_status=REOPENED&chfieldfrom=-1m&product=Firefox&query_format=advanced&known_name=incoming%20bugs&email1=nobody%40mozilla.org&component=General $VAR1 = 'DBD::mysql::db selectcol_arrayref failed: Incorrect TIMESTAMP value: \'2022-09-31 18:56:56\' [for Statement "SELECT bugs.bug_id AS bug_id, bugs.bug_status AS bug_status, bugs.priority AS priority, map_assigned_to.login_name AS assigned_to
  FROM bugs
LEFT JOIN bug_group_map AS security_map  ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (1,16,11,12,15,14,13,19,18,23,28,10,5,4,9,6,7,8,29,17,3,20,26,2,27) )
LEFT JOIN cc AS security_cc  ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 1
LEFT JOIN components AS security_triage  ON bugs.component_id = security_triage.id AND security_triage.triage_owner_id = 1
INNER JOIN profiles AS map_assigned_to  ON bugs.assigned_to = map_assigned_to.userid
INNER JOIN bug_status AS map_bug_status  ON bugs.bug_status = map_bug_status.value
INNER JOIN priority AS map_priority  ON bugs.priority = map_priority.value
INNER JOIN profiles AS name_assigned_to_4  ON bugs.assigned_to = name_assigned_to_4.userid
LEFT JOIN bugs AS bugs_g7  ON bugs.bug_id = bugs_g7.bug_id
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 1)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 1
        OR security_triage.triage_owner_id IS NOT NULL
        OR bugs.qa_contact = 1)
   AND  bugs.bug_status IN (\'UNCONFIRMED\',\'NEW\',\'REOPENED\')  AND  bugs.priority IN (\'--\')  AND  bugs.product_id IN (2)  AND  bugs.component_id IN (2,4)  AND  name_assigned_to_4.login_name IN (\'[e-mail]\')  AND COALESCE(bugs.delta_ts, \'1970-01-01 00:00:00\') >= \'2022-09-31 18:56:56\' AND ( 1 = 1 AND bugs_g7.creation_ts >= \'2022-09-31 18:56:56\' )
GROUP BY bugs.bug_id
ORDER BY map_bug_status.sortkey, map_bug_status.value, map_priority.sortkey, map_priority.value, assigned_to, bug_id
LIMIT 500
"] at /app/Bugzilla/DB.pm line 71.

Comment 2

[David Lawrence [:dkl]]

Nothing has changed with respect to search and chfield timestamp handling. It has to so with the current date and 1m cause if I use 2022-09-31 explicitly (same as 1m) it works. If I choose 1w (one week) or 2m (two months) it also works as expected. If I do 1m it throws the error:

DBD::mysql::db selectcol_arrayref failed: Incorrect TIMESTAMP value: \'2022-09-31 18:56:56\'

due the part of the SQL being generated:

AND COALESCE(bugs.delta_ts, \'1970-01-01 00:00:00\') >= \'2022-09-31 18:56:56\' AND ( 1 = 1 AND bugs_g7.creation_ts >= \'2022-09-31 18:56:56\' ) GROUP BY bugs.bug_id

UPDATE: Just realized that the error is correct in that '2022-09-31 18:56:56' is not a valid date. So i think in order to fix this we would need to remap any thing timestamp that is day 31 on a month with only 30 days, we increment a day maybe. Or you would just wait til tomorrow :)

Comment 3

[David Lawrence [:dkl]]

Either way we need to make the system handle the error more gracefully either showing a descriptive error to the user, or like in the last comment just silently fix the problem for the user by backing up and additional day.

Comment 4

[BMO Automation]

Attachment: GitHub Pull Request

Comment 5

[Daniel Veditz [:dveditz] back January 5, 2026]

February must really throw a wrench into things... up to 3 days of "miss"

Comment 6

[BMO Automation]

Attachment: GitHub Pull Request

Comment 7

[David Lawrence [:dkl]]

Merged
https://github.com/mozilla-bteam/bmo/commit/de802b26a2ed811d6fc63c3be26b51e7fb81bcad

Comment 8

[David Lawrence [:dkl]]

This fix is live in production. Another nice thing about the change is even if you enter an inccorrect date explicitly like 2022-09-31 it will correct it and change it to 2022-10-01.