Edgewall Software
Modify

Opened 14 years ago

Closed 14 years ago

Last modified 4 years ago

#2164 closed defect (fixed)

DB error when submitting Custom Query using custom fields against Postgres

Reported by: abudinsz@… Owned by: Christopher Lenz
Priority: high Milestone: 0.9
Component: ticket system Version: 0.9b2
Severity: critical Keywords: postgresql, custom, query
Cc: Branch:
Release Notes:
API Changes:

Description (last modified by Christopher Lenz)

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

Attachments (0)

Change History (12)

comment:1 by Christopher Lenz, 14 years ago

Description: modified (diff)

(reformatting of description)

comment:2 by anonymous, 14 years ago

Resolution: fixed
Status: newclosed

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

comment:3 by anonymous, 14 years ago

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

     
    211211            value = sql_escape(value[len(mode and '!' or '' + mode):])
    212212            if name not in custom_fields:
    213213                name = 't.' + name
     214           else:
     215               name= name+'.value'
    214216            if mode == '~' and value:
    215217                return "COALESCE(%s,'') %sLIKE '%%%s%%'" % (
    216218                       name, neg and 'NOT ' or '', value)
     
    240242                if k not in custom_fields:
    241243                    col = 't.'+k
    242244                else:
    243                     col = k
     245                    col = k+'.value'
    244246                clauses.append("COALESCE(%s,'') %sIN (%s)"
    245247                               % (col, neg and 'NOT ' or '', inlist))
    246248            elif len(v) > 1:
     
    269271            if name not in custom_fields:
    270272                col = 't.'+name
    271273            else:
    272                 col = name
     274                col = name+'.value'
    273275            if name == 'id':
    274276                # FIXME: This is a somewhat ugly hack.  Can we also have the
    275277                #        column type for this?  If it's an integer, we do first

comment:4 by Christopher Lenz, 14 years ago

Milestone: 0.9
Resolution: fixed
Status: closedreopened

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

comment:5 by Christopher Lenz, 14 years ago

Component: report systemticket system

comment:6 by Christopher Lenz, 14 years ago

Owner: changed from daniel to Christopher Lenz
Status: reopenednew

comment:7 by jnorthrup<AT>io-informatics.com, 14 years ago

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.

comment:8 by anonymous, 14 years ago

Milestone: 0.90.9.1

comment:9 by anonymous, 14 years ago

Milestone: 0.9.10.9

comment:10 by Christopher Lenz, 14 years ago

Status: newassigned

comment:11 by Christopher Lenz, 14 years ago

Resolution: fixed
Status: assignedclosed

Patch applied in [2405]. Thanks!

comment:12 by Ryan J Ollos, 4 years ago

Keywords: postgresql custom query → postgresql, custom, query

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christopher Lenz.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.