Getting the last result of a bug that has been resolved

Question

I have been trying to get a status so i can find out how long has been taking for a bug change its status do 'resolved', so for i have the following SQL:

select bug.id,
       bug.project_id,
       DATEDIFF (FROM_UNIXTIME(his.date_modified), FROM_UNIXTIME(date_submitted)) as dias_resolucao,
       DATE_FORMAT(FROM_UNIXTIME(his.date_modified), '%Y-%m') as ano_mes
from mantis_bug_table bug
left join mantis_project_table pro on bug.project_id = pro.id
left join mantis_custom_field_string_table cus on bug.id = cus.bug_id
left join mantis_bug_history_table his on bug.id = his.bug_id
where bug.category_id = 1 and
      (cus.field_id=1 or cus.field_id is null) and      
      his.new_value = 80 and 
      his.field_name = 'status' and                 
      bug.id = 5171 and
      cus.value='Sim'      
having his.date_modified = max(his.date_modified)

The problem is that i'm unaware why does this print the following error:

[SELECT - 0 row(s), 0.000 secs]  
[Error Code: 1054, SQL State: 42S22]  
Unknown column 'his.date_modified' in 'having clause'

Running the same SQL, without the having clause brings me the following result:

╔══════╦════════════╦════════════════╦═════════╗
║  id  ║ project_id ║ dias_resolucao ║ ano_mes ║
╠══════╬════════════╬════════════════╬═════════╣
║ 5171 ║          4 ║             17 ║ 2012-12 ║
║ 5171 ║          4 ║             18 ║ 2012-12 ║
╚══════╩════════════╩════════════════╩═════════╝

Any help is appreciated.

check the existence of column `date_modified` in table `mantis_bug_history_table` — Suhel Meman, Apr 09 '13 at 13:14
his.date_modified is missing in select statement, that is why unknown column error is coming. — Dieepak, Apr 09 '13 at 13:35
@SuhelMeman it exists, as it's also being used in the select, only just being formatted. — aimbire, Apr 09 '13 at 13:42

score 2 · Accepted Answer · answered Apr 09 '13 at 13:31

2

Try it.

SELECT his.date_modified, bug.id,
       bug.project_id,
       DATEDIFF (FROM_UNIXTIME(his.date_modified), FROM_UNIXTIME(date_submitted)) AS dias_resolucao,
       DATE_FORMAT(FROM_UNIXTIME(his.date_modified), '%Y-%m') AS ano_mes
FROM mantis_bug_table bug
LEFT JOIN mantis_project_table pro ON bug.project_id = pro.id
LEFT JOIN mantis_custom_field_string_table cus ON bug.id = cus.bug_id
LEFT JOIN mantis_bug_history_table his ON bug.id = his.bug_id
WHERE bug.category_id = 1 AND
      (cus.field_id=1 OR cus.field_id IS NULL) AND      
      his.new_value = 80 AND 
      his.field_name = 'status' AND                 
      bug.id = 5171 AND
      cus.value='Sim'      
HAVING his.date_modified = MAX(his.date_modified)

answered Apr 09 '13 at 13:31

Dieepak

546
2
7

It doesn't throw a error, but still i get a message: "Empty result set fetched". – aimbire Apr 09 '13 at 13:38
1

There could be problem with max function. Replace MAX(his.date_modified) with (SELECT MAX(h.date_modified) FROM mantis_bug_history_table AS h WHERE bug.id = h.bug_id) – Dieepak Apr 09 '13 at 13:48
Thank you, i still had to tweak the HAVING clause as it wasn't taken in consideration both columns being checked in the WHERE clause, but i've edited the answer so it keeps for whomever have trouble with this in the future. – aimbire Apr 09 '13 at 14:36

dlp · Answer 2 · 2013-04-09T13:49:42.570

A couple things. First, I don't think you can use aliases in the HAVING clause by default. I might be wrong, I'm having trouble parsing the documentation here. So the HAVING line should look more like `HAVING mantis_bug_history_table.date_modified = MAX(mantis_bug_history_table.date_modified)

I could be wrong though. That said, I think you're going to need to do a self join to get the MAX date_modified. Something like this:

select bug.id,
       bug.project_id,
       DATEDIFF (FROM_UNIXTIME(his.date_modified), FROM_UNIXTIME(date_submitted)) as dias_resolucao,
       DATE_FORMAT(FROM_UNIXTIME(his.date_modified), '%Y-%m') as ano_mes
from mantis_bug_table bug
left join mantis_project_table pro on bug.project_id = pro.id
left join mantis_custom_field_string_table cus on bug.id = cus.bug_id
left join mantis_bug_history_table his on bug.id = his.bug_id
inner join (select bug_id, max(date_modified) as max_date_modified from mantis_bug_history_table group by bug_id) as his_max_dates
 on his.bug_id = his_max_dates.bug_id and his.date_modified = his_max_dates.max_date_modified
where bug.category_id = 1 and
      (cus.field_id=1 or cus.field_id is null) and      
      his.new_value = 80 and 
      his.field_name = 'status' and                 
      bug.id = 5171 and
      cus.value='Sim'

Give that a try.

Getting the last result of a bug that has been resolved

2 Answers2