[Tickets #595] NEW: Whups produces invalid SQLs on ORACLE

[bugs at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=595
-----------------------------------------------------------------------
 Ticket     | 595
 Created By | rvs at angara.ru
 Summary    | Whups produces invalid SQLs on ORACLE
 Queue      | Whups
 State      | Unconfirmed
 Priority   | 3. High
 Type       | Bug
 Owners     | 
-----------------------------------------------------------------------


rvs at angara.ru (2004-09-16 08:05) wrote:

While trying HEAD snapshot version of whups:
The reason is requesting in SELECT statement aliase names starting
from underscore. In Oracle the column/alias name can't start from
underscore unless it is surrounded by double quotation marks.
The error produced is "ORA-00911: invalid character".

Suggest change alias names or quote them correctly.
Please find below the log message from horde.log.

Sep 16 23:49:25 HORDE [error] [whups] DB Error: unknown error: SELECT
whups_tickets.ticket_id as id, whups_tickets.ticket_summary as summary,
whups_tickets.user_id_requester, whups_tickets.state_id as state,
whups_tickets.type_id as type, whups_tickets.priority_id as priority,
whups_tickets.queue_id as queue, whups_tickets.ticket_timestamp AS
timestamp, whups_types.type_name AS _type_name, whups_states.state_name AS
_state_name, whups_states.state_category AS _state_category,
whups_queues.queue_name AS _queue_name, whups_priorities.priority_name AS
_priority_name, whups_versions.version_name AS _version_name,
whups_versions.version_description AS _version_description,
MAX(w1.log_timestamp) AS _date_assigned, MAX(w2.log_timestamp) AS
_date_resolved FROM whups_tickets LEFT JOIN whups_ticket_owners ON
whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND
whups_ticket_owners.ticket_owner = 'user:s.rozinov at sibron.ru' INNER JOIN
whups_types ON whups_tickets.type_id = whups_types.type_id INNER JOIN
whups_states ON whups_tickets.state_id = whups_states.state_id INNER JOIN
whups_queues ON whups_tickets.queue_id = whups_queues.queue_id INNER JOIN
whups_priorities ON whups_tickets.priority_id = whups_priorities.priority_id
INNER JOIN whups_states state2 ON whups_tickets.type_id = state2.type_id
LEFT JOIN whups_versions ON whups_tickets.version_id =
whups_versions.version_id LEFT JOIN whups_logs w1 ON w1.ticket_id =
whups_tickets.ticket_id AND w1.log_type = 'state' AND w1.log_value =
state2.state_id AND state2.state_category = 'assigned' LEFT JOIN whups_logs
w2 ON whups_states.state_category = 'resolved' AND w2.ticket_id =
whups_tickets.ticket_id AND w2.log_type = 'state' AND w2.log_value =
state2.state_id AND state2.state_category = 'resolved' WHERE
(whups_tickets.user_id_requester = 's.rozinov at sibron.ru') AND
((whups_tickets.type_id = whups_states.type_id AND whups_tickets.state_id =
whups_states.state_id AND whups_states.state_category <> 'resolved')) AND
(whups_ticket_owners.ticket_id IS NULL) GROUP BY whups_tickets.ticket_id,
whups_tickets.ticket_summary, whups_tickets.user_id_requester,
whups_tickets.state_id, whups_tickets.type_id, whups_tickets.priority_id,
whups_tickets.queue_id, whups_tickets.ticket_timestamp,
whups_types.type_name, whups_states.state_name, whups_states.state_category,
whups_queues.queue_name, whups_priorities.priority_name,
whups_versions.version_name, whups_versions.version_description ORDER BY
whups_tickets.priority_id, whups_tickets.ticket_timestamp
[nativecode=ORA-00911: invalid character] [on line 741 of
"/var/optpart/local/apache/http/beta.sibron.ru/htdocs/rvs/horde/whups/lib/Dr
iver/sql.php"]