I’m experiencing an issue where a specific record appears in a detailed query [Query 1] but is missing in a broader query [Query 2] with similar conditions on an Amazon Aurora MySQL database. Here are the details:

Specific Query: [Query 1]

This query successfully retrieves the expected record: [The below query returns 1 row]

SELECT * 
FROM data_alerts.log_table 
WHERE incident_uuid = 63756315 
  AND run_status_flag = 'FAILED'
  AND job_name = 'lambda:LH_lambda'
  AND incident_category = 'LH'
  AND created_at_time >= CURDATE() - INTERVAL 1 DAY;

Broader Query: [Query 2]

However, when I run this broader query, the record is missing:

SELECT job_details 
FROM data_alerts.log_table 
WHERE incident_category = 'LH'
  AND run_status_flag = 'FAILED'
  AND created_at_time >= CURDATE() - INTERVAL 1 DAY
  AND job_name = 'lambda:LH_lambda'
ORDER BY created_at_time;

I also tried this query without the DISTINCT
clause, but the specific record still does not appear in the results. Here’s the adjusted broader query:

SELECT job_details, incident_uuid, created_at_time 
FROM data_alerts.log_table 
WHERE incident_category = 'LH'
  AND run_status_flag = 'FAILED'
  AND created_at_time >= CURDATE() - INTERVAL 1 DAY
  AND job_name = 'lambda:LH_lambda'
ORDER BY created_at_time;

Does anyone have any idea, why the record gets dropped in the results of second query