TicketQuery for empty field of type time is incorrect

[Ryan J Ollos]

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

@@ -510 +510 @@
                     col = 'c.' + db.quote(name)
                 value = value[len(mode) + neg:]
 
-                if name in self.time_fields:
+                if name in self.time_fields and value:
                     if '..' in value:
                         (start, end) = [each.strip() for each in
                                         value.split('..', 1)]

[Jun Omae]

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.

[Ryan J Ollos]

The proposed changes work well per my testing.

[Jun Omae]

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

[Jun Omae]

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: ""

[Jun Omae]

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