[#595] Whups produces invalid SQLs on ORACLE
Summary Whups produces invalid SQLs on ORACLE
Queue Whups
Type Bug
State Resolved
Priority 2. Medium
Owners Chuck Hagenbuch <chuck (at) horde (dot) org>
Requester rvs (at) angara (dot) ru
Created 09/16/2004 (1482 days ago)
Due
Updated 09/17/2004 (1481 days ago)
Assigned 09/16/2004 (1482 days ago)
Resolved 09/17/2004 (1481 days ago)
Attachments
Milestone
Patch No

History
09/17/2004 Chuck Hagenbuch State ⇒ Resolved
 
09/17/2004 Chuck Hagenbuch Comment #2 Reply to this comment
Should be fixed in CVS.
09/16/2004 Jan Schneider Priority ⇒ 2. Medium
 
09/16/2004 Jan Schneider Assigned to Chuck Hagenbuch
State ⇒ Assigned
 
09/16/2004 rvs (at) angara (dot) ru Comment #1
State ⇒ Unconfirmed
Type ⇒ Bug
Summary ⇒ Whups produces invalid SQLs on ORACLE
Queue ⇒ Whups
Priority ⇒ 3. High
Reply to this comment
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@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@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/Driver/sql.php"]