createMissing*tickets functions fail, if no states for this ticket type are assigned to given project

[a-tze]

Issue migrated from trac ticket # 139

component: database | priority: major | resolution: fixed

2017-03-04 15:40:11: @pegro created the issue

---

When I try to create a ticket manually, this error message occurs:

ERROR: division by zero CONTEXT: SQL statement "SELECT CEIL(SUM(ts2.percent_progress) / (SELECT SUM(ts3.percent_progress) FROM tbl_ticket_state ts3 JOIN tbl_project_ticket_state pts ON pts.ticket_type # ts3.ticket_type AND pts.ticket_statets3.ticket_state WHERE ts3.ticket_type # ts1.ticket_type AND pts.project_idparam_project_id) * 100) FROM tbl_ticket_state ts1 JOIN tbl_project_ticket_state pts ON pts.ticket_type # ts1.ticket_type AND pts.ticket_statets1.ticket_state JOIN tbl_ticket_state ts2 ON ts1.ticket_type # ts2.ticket_type AND ts1.sort >= ts2.sort JOIN tbl_project_ticket_state pts2 ON pts2.ticket_typets2.ticket_type AND pts2.ticket_state # ts2.ticket_state AND pts2.project_idpts.project_id WHERE pts.project_id # param_project_id AND ts1.ticket_typeparam_ticket_type AND ts1.ticket_state # param_ticket_state GROUP BY ts1.ticket_state, ts1.ticket_type, ts1.sort" PL/pgSQL function ticket_state_progress(bigint,enum_ticket_type,enum_ticket_state) line 5 at SQL statement SQL statement "SELECT SUM(percent_progress) / COUNT(id) FROM ( SELECT t.id, ticket_state_progress(t.project_id, t.ticket_type, t.ticket_state) AS percent_progress FROM tbl_ticket t WHERE t.idparam_ticket_id AND t.parent_id IS NOT NULL UNION SELECT t.id, ticket_state_progress(t.project_id, t.ticket_type, t.ticket_state) AS percent_progress FROM tbl_ticket t WHERE t.parent_id # param_ticket_id ) as all_tickets" PL/pgSQL function ticket_progress(bigint) line 5 at SQL statement SQL statement "UPDATE tbl_ticket SET progressticket_progress(NEW.id) WHERE id # NEW.id" PL/pgSQL function update_ticket_progress() line 3 at SQL statement SQL statement "INSERT INTO tbl_ticket (parent_id, project_id, fahrplan_id, ticket_type, ticket_state) (SELECT t1.id as parent_id, t1.project_id, t1.fahrplan_id, 'recording' as ticket_type, ticket_state_initial(param_project_id, 'recording') AS ticket_state FROM tbl_ticket t1 LEFT JOIN tbl_ticket t2 ON t2.parent_idt1.id AND t2.ticket_type # 'recording' WHERE t1.ticket_type'meta' AND t1.project_id # param_project_id GROUP BY t1.id HAVING COUNT(t2.id)0)" PL/pgSQL function create_missing_recording_tickets(bigint) line 7 at SQL statement (SELECT create_missing_recording_tickets('18'))

In the current project all states for the ticket type "recording" are unchecked (unassigned)

[a-tze]

2017-03-04 19:09:45: @jjeising changed status from new to assigned

[a-tze]

2017-03-04 19:09:45: @jjeising set owner to atze

[a-tze]

2017-03-05 21:26:45: @a-tze

[a-tze]

2017-03-05 21:26:45: @a-tze commented

---

Creating a ticket with no available state for the particular ticket type seems kind of impossible. E.g. if you do this in an empty project, you get over div by zero but get a FK violation because there is no state available.

What is the expected behaviour? Failing silently would end up in an orphaned meta ticket, as long as there are at least meta ticket states enabled.

[a-tze]

2017-03-05 21:41:10: @a-tze commented

---

Ah, the locked state must be active. Now I can reproduce.

[a-tze]

2017-03-05 21:49:29: @pegro

[a-tze]

2017-03-05 21:49:29: @pegro commented

---

I have a project, where I haven't assigned any states to recording tickets since I don't need recording tickets.

The expected behavior would be skipping creating any recording ticket without throwing an exception.

[a-tze]

2017-03-05 22:08:43: @a-tze commented

---

Please confirm that "locked" is checked. Actually you should get the FK violation.

ACK on expected behaviour.

[a-tze]

2017-03-05 22:31:10: @a-tze set resolution to fixed

[a-tze]

2017-03-05 22:31:10: @a-tze changed status from assigned to closed

[a-tze]

2017-03-05 22:31:10: @a-tze commented

---

Fixed via changeset:e6f74988 and changeset:674cddb5e.