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]
<code>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;
</code>
<code>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;
</code>
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:
<code>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;
</code>
<code>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;
</code>
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:
<code>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;
</code>
<code>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;
</code>
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