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].

[Jun Omae]

#13216 was closed as a duplicate.

[Jun Omae]

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

@@ -272 +272 @@
 
     def cast(self, column, type):
         # Temporary hack needed for the union of selects in the search module
-        return 'CAST(%s AS %s)' % (column, _type_map.get(type, type))
+        if type in ('int', 'int64'):
+            fmt = "(CASE WHEN %(column)s IS NULL THEN NULL " \
+                  "WHEN %(column)s ~ '^[-+]?[0-9]+$' " \
+                  "THEN CAST(%(column)s AS %(type)s)" \
+                  "ELSE 0 END)"
+        else:
+            fmt = 'CAST(%(column)s AS %(type)s)'
+        return fmt % {'column': column, 'type': _type_map.get(type, type)}
 
     def concat(self, *args):
         return '||'.join(args)

[Ryan J Ollos]

Replying to Jun Omae:

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.

Looks interesting. Should we create a new ticket and target 1.4-stable?

[Ryan J Ollos]

Replying to Ryan J Ollos:

Looks interesting. Should we create a new ticket and target 1.4-stable?

⇒ #13279.