[Trac-tickets] Re: [The Trac Project] #2164: DB error when submitting Custom Query using custom fields against Postgres

[The Trac Project]

#2164: DB error when submitting Custom Query using custom fields against Postgres
-------------------------------------+--------------------------------------
 Reporter:  abudinsz at nascentric.com  |        Owner:  daniel
     Type:  defect                   |       Status:  new   
 Priority:  high                     |    Milestone:        
Component:  report system            |      Version:  0.9b2 
 Severity:  critical                 |   Resolution:        
 Keywords:  postgresql custom query  |  
-------------------------------------+--------------------------------------
Old description:

> I added a single custom field  in trac.ini called "customer". When
> attempting to submit a Custom Query where the "customer" field is used as
> a condition I get a DB error. This problem occured (with slightly
> different behavior) by using either of the two recommended postgresql
> python bindings.
>
> Trac.ini (excerpt):
> [ticket-custom]
> customer = select
> customer.label = Customer
> customer.options = none|Company1|Company2|Company3|Company4
> customer.value = 0
>
> Environment:
> PostgreSQL - 7.4.8
> Python - 2.3.5
> Apache - 2.0.46
> Trac - 0.9b2 (also happened with 0.9b1)
>
> Traceback:
> Traceback (most recent call last):
>   File "/opt/trac/lib/python2.3/site-
> packages/trac/web/modpython_frontend.py", line 206, in handler
>     dispatch_request(mpr.path_info, mpr, env)
>   File "/opt/trac/lib/python2.3/site-packages/trac/web/main.py", line
> 139, in dispatch_request
>     dispatcher.dispatch(req)
>   File "/opt/trac/lib/python2.3/site-packages/trac/web/main.py", line
> 107, in dispatch
>     resp = chosen_handler.process_request(req)
>   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
> 393, in process_request
>     self.display_html(req, query)
>   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
> 507, in display_html
>     tickets = query.execute(db)
>   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
> 140, in execute
>     cursor.execute(sql)
> ProgrammingError: ERROR:  relation reference "customer" cannot be used in
> an expression
>
> SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS
> owner,t.type AS type,t.priority AS priority,t.time AS time,t.changetime
> AS changetime,priority.value AS priority_value,customer.value AS customer
> FROM ticket AS t
>   LEFT OUTER JOIN ticket_custom AS customer ON (id=customer.ticket AND
> customer.name='customer')
>   LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
> priority.name=priority)
> WHERE COALESCE(t.status,'') IN ('new','assigned','reopened') AND
> COALESCE(customer,'')='' AND COALESCE(t.owner,'')!='myuser'
> ORDER BY COALESCE(t.priority,'')='',priority.value,t.id

New description:

 I added a single custom field  in trac.ini called "customer". When
 attempting to submit a Custom Query where the "customer" field is used as
 a condition I get a DB error. This problem occured (with slightly
 different behavior) by using either of the two recommended postgresql
 python bindings.

 Trac.ini (excerpt):
 {{{
 [ticket-custom]
 customer = select
 customer.label = Customer
 customer.options = none|Company1|Company2|Company3|Company4
 customer.value = 0
 }}}

 Environment:
  * PostgreSQL - 7.4.8
  * Python - 2.3.5
  * Apache - 2.0.46
  * Trac - 0.9b2 (also happened with 0.9b1)

 {{{
 Traceback:
 Traceback (most recent call last):
   File "/opt/trac/lib/python2.3/site-
 packages/trac/web/modpython_frontend.py", line 206, in handler
     dispatch_request(mpr.path_info, mpr, env)
   File "/opt/trac/lib/python2.3/site-packages/trac/web/main.py", line 139,
 in dispatch_request
     dispatcher.dispatch(req)
   File "/opt/trac/lib/python2.3/site-packages/trac/web/main.py", line 107,
 in dispatch
     resp = chosen_handler.process_request(req)
   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
 393, in process_request
     self.display_html(req, query)
   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
 507, in display_html
     tickets = query.execute(db)
   File "/opt/trac/lib/python2.3/site-packages/trac/ticket/query.py", line
 140, in execute
     cursor.execute(sql)
 ProgrammingError: ERROR:  relation reference "customer" cannot be used in
 an expression

 SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS
 owner,t.type AS type,t.priority AS priority,t.time AS time,t.changetime AS
 changetime,priority.value AS priority_value,customer.value AS customer
 FROM ticket AS t
   LEFT OUTER JOIN ticket_custom AS customer ON (id=customer.ticket AND
 customer.name='customer')
   LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
 priority.name=priority)
 WHERE COALESCE(t.status,'') IN ('new','assigned','reopened') AND
 COALESCE(customer,'')='' AND COALESCE(t.owner,'')!='myuser'
 ORDER BY COALESCE(t.priority,'')='',priority.value,t.id
 }}}

Comment (by cmlenz):

 (reformatting of description)

-- 
Ticket URL: <http://projects.edgewall.com/trac/ticket/2164>
The Trac Project <http://trac.edgewall.com/>