Edgewall Software
Modify

Opened 15 months ago

Closed 15 months ago

Last modified 5 weeks ago

#13078 closed defect (fixed)

TicketQuery for empty field of type time is incorrect

Reported by: Ryan J Ollos Owned by: Jun Omae
Priority: normal Milestone: 1.2.4
Component: query system Version:
Severity: normal Keywords: postgresql ticketquery
Cc: Branch:
Release Notes:

Fix an error raised when custom field of type time is constrained by range on PostgreSQL.

API Changes:

Description

The issue was raised in gdiscussion:trac-users:e7NvYuzgQKk. For a field of type time (e.g. duedate), [[TicketQuery(duedate=)]] does not retrn the correct results.

It looks like the following patch might work:

  • trac/ticket/query.py

    diff --git a/trac/ticket/query.py b/trac/ticket/query.py
    index c65f2bfc8..ca1f1d496 100644
    a b class Query(object):  
    510510                    col = 'c.' + db.quote(name)
    511511                value = value[len(mode) + neg:]
    512512
    513                 if name in self.time_fields:
     513                if name in self.time_fields and value:
    514514                    if '..' in value:
    515515                        (start, end) = [each.strip() for each in
    516516                                        value.split('..', 1)]

Attachments (0)

Change History (7)

comment:1 by Jun Omae, 15 months ago

Keywords: postgresql added

Root cause is that the error is raised when CAST(... AS bigint) is evaluated for non-integer text on PostgreSQL.

05:31:11 PM Trac[util] DEBUG: SQL: SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value,"duedate".value AS "duedate"
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS "duedate" ON ("duedate".ticket=t.id AND "duedate".name='duedate')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
WHERE (((CAST("duedate".value AS bigint)>=%s AND CAST("duedate".value AS bigint)<%s)))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id) AS x
05:31:11 PM Trac[util] DEBUG: args: [1533049200000000L, 1535727600000000L]
05:31:11 PM Trac[util] DEBUG: execute exception: DataError('invalid input syntax for integer: ""\n',)
05:31:11 PM Trac[main] ERROR: [192.168.11.19] Internal Server Error: <RequestWithSession "GET '/query?duedate=lastmonth..thismonth'">, referrer None
Traceback (most recent call last):
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/web/main.py", line 647, in _dispatch_request
    dispatcher.dispatch(req)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/web/main.py", line 248, in dispatch
    resp = chosen_handler.process_request(req)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 1016, in process_request
    return self.display_html(req, query)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 1114, in display_html
    tickets = query.execute(req)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 303, in execute
    self.num_items = self._count(sql, args)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 284, in _count
    % sql, args)[0][0]
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
DataError: invalid input syntax for integer: ""
DataError: invalid input syntax for integer: ""

We just have to prevent uses of CAST() for custom fields. Proposed changes in jomae.git@t13078+1.2-stable.

comment:2 by Ryan J Ollos, 15 months ago

The proposed changes work well per my testing.

comment:3 by Jun Omae, 15 months ago

Owner: set to Jun Omae
Status: newassigned

Thanks for the testing. I'm going to push the changes.

comment:4 by Jun Omae, 15 months ago

The original reporter says nothing to see the log about the error but I confirm the following with ERROR level is logged.

12:26:18 PM Trac[formatter] ERROR: Macro TicketQuery(duedate=lastmonth..thismonth) failed for <Resource 'ticket'>:
Traceback (most recent call last):
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/wiki/formatter.py", line 822, in _macro_formatter
    return macro.ensure_inline(macro.process(args), in_paragraph)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/wiki/formatter.py", line 396, in process
    text = self.processor(text)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/wiki/formatter.py", line 368, in _macro_processor
    text)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 1410, in expand_macro
    tickets = query.execute(req)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 303, in execute
    self.num_items = self._count(sql, args)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/ticket/query.py", line 284, in _count
    % sql, args)[0][0]
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/venv/trac/1.2.3/lib/python2.6/site-packages/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
DataError: invalid input syntax for integer: ""
DataError: invalid input syntax for integer: ""

comment:5 by Jun Omae, 15 months ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [16825] and merged in [16826].

comment:6 by Jun Omae, 5 weeks ago

#13216 was closed as a duplicate.

comment:7 by Jun Omae, 5 weeks ago

Another solution is using CASE ... END and regular expression operator. I think we could make CAST(... AS int) in PostgreSQL robust and same behavior in other databases.

trac=> SELECT (CASE
trac(>         WHEN value IS NULL THEN NULL
trac(>         WHEN value ~ '[-+]?[0-9]+$' THEN CAST(value AS int)
trac(>         ELSE 0 END) AS cast,
trac->        value
trac-> FROM unnest(ARRAY[NULL, '42', '+123', '-789', 'text', '']) AS value
trac-> ;
 cast | value
------+-------
      |
   42 | 42
  123 | +123
 -789 | -789
    0 | text
    0 |
(6 rows)

  • trac/db/postgres_backend.py

    diff --git a/trac/db/postgres_backend.py b/trac/db/postgres_backend.py
    index 3b5c9e92c..3775a9aa7 100644
    a b class PostgreSQLConnection(ConnectionBase, ConnectionWrapper):  
    272272
    273273    def cast(self, column, type):
    274274        # Temporary hack needed for the union of selects in the search module
    275         return 'CAST(%s AS %s)' % (column, _type_map.get(type, type))
     275        if type in ('int', 'int64'):
     276            fmt = "(CASE WHEN %(column)s IS NULL THEN NULL " \
     277                  "WHEN %(column)s ~ '^[-+]?[0-9]+$' " \
     278                  "THEN CAST(%(column)s AS %(type)s)" \
     279                  "ELSE 0 END)"
     280        else:
     281            fmt = 'CAST(%(column)s AS %(type)s)'
     282        return fmt % {'column': column, 'type': _type_map.get(type, type)}
    276283
    277284    def concat(self, *args):
    278285        return '||'.join(args)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to as closed The owner will be changed from Jun Omae to the specified user.

Add Comment


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