DB error when submitting Custom Query using custom fields against Postgres

[abudinsz@…]

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

[cmlenz]

(reformatting of description)

[anonymous]

Index: /usr/src/trac-devel/trac/trac/ticket/query.py =================================================================== --- /usr/src/trac-devel/trac/trac/ticket/query.py (revision 2318) +++ /usr/src/trac-devel/trac/trac/ticket/query.py (working copy) @@ -211,6 +211,8 @@

value = sql_escape(value[len(mode and '!' or + mode):]) if name not in custom_fields:

name = 't.' + name

+ else: + name= name+'.value'

if mode == '~' and value:

return "COALESCE(%s,) %sLIKE '%%%s%%'" % (

name, neg and 'NOT ' or , value)

@@ -240,7 +242,7 @@

if k not in custom_fields:

col = 't.'+k

else:

- col = k + col = k+'.value'

clauses.append("COALESCE(%s,) %sIN (%s)"

% (col, neg and 'NOT ' or , inlist))

elif len(v) > 1:

@@ -269,7 +271,7 @@

if name not in custom_fields:

col = 't.'+name

else:

- col = name + col = name+'.value'

if name == 'id':

# FIXME: This is a somewhat ugly hack. Can we also have the # column type for this? If it's an integer, we do first

[anonymous]

usr/src/trac-devel/trac/trac/ticket/query.py

@@ -211 +211 @@
             value = sql_escape(value[len(mode and '!' or '' + mode):])
             if name not in custom_fields:
                 name = 't.' + name
+           else:
+               name= name+'.value'
             if mode == '~' and value:
                 return "COALESCE(%s,'') %sLIKE '%%%s%%'" % (
                        name, neg and 'NOT ' or '', value)
@@ -240 +242 @@
                 if k not in custom_fields:
                     col = 't.'+k
                 else:
-                    col = k
+                    col = k+'.value'
                 clauses.append("COALESCE(%s,'') %sIN (%s)"
                                % (col, neg and 'NOT ' or '', inlist))
             elif len(v) > 1:
@@ -269 +271 @@
             if name not in custom_fields:
                 col = 't.'+name
             else:
-                col = name
+                col = name+'.value'
             if name == 'id':
                 # FIXME: This is a somewhat ugly hack.  Can we also have the
                 #        column type for this?  If it's an integer, we do first

[cmlenz]

This shouldn't be closed unless a fix is checked in.

[jnorthrup<AT>io-informatics.com]

thanks for the quick notice. I have a rather aggressively patched postgres+timetracking v9 devel readonly tree scrutinized against pharma/FDA project requirements. Would gladly share my progress.

email me if this is interesting.

[cmlenz]

Patch applied in [2405]. Thanks!