TicketQuery for empty custom date

[Mo]

Hello,

we have a custom field defined like

[ticket-custom]
duedate = time
duedate.format = date
duedate.label = Due date
duedate.order = 2

However it does not work to search for that field like it is possible for Created for instance:

[[TicketQuery(format=table,status=new,created=lastmonth..thismonth)]]

works.

[[TicketQuery(format=table,status=new,duedate=lastmonth..thismonth)]]

returns invalid input syntax for integer: ""

Actually due date can also be empty. How to search for a duedate=""?

[RjOllos]

On Wednesday, September 5, 2018 at 5:22:45 AM UTC-7, Mo wrote:

Hello,

we have a custom field defined like

[ticket-custom]
duedate = time
duedate.format = date
duedate.label = Due date
duedate.order = 2

However it does not work to search for that field like it is possible for Created for instance:

[[TicketQuery(format=table,status=new,created=lastmonth..thismonth)]]

works.

[[TicketQuery(format=table,status=new,duedate=lastmonth..thismonth)]]

returns invalid input syntax for integer: ""

The Query for duedate works for me in Trac 1.2.4dev.

 

You have to be running Trac 1.2.x to use fields of type time. Assuming you are, I would take a look in the logs next. Perhaps the issue is caused by a plugin.

Actually due date can also be empty. How to search for a duedate=""?

Looks like a defect:

https://trac.edgewall.org/ticket/13078

- Ryan

 

[Mo]

Am Montag, 17. September 2018 07:24:35 UTC+2 schrieb RjOllos:

[[TicketQuery(format=table,status=new,duedate=lastmonth..thismonth)]]

returns invalid input syntax for integer: ""

The Query for duedate works for me in Trac 1.2.4dev.

 

You have to be running Trac 1.2.x to use fields of type time. Assuming you are, I would take a look in the logs next. Perhaps the issue is caused by a plugin.

Nothing to see in the logs with Log Leve DEBUG.
On the browser I see the error:

Error: Macro TicketQuery(format=table,status=new,duedate=lastmonth..thismonth) failedinvalid input syntax for integer: ""

However this is Trac 1.2, maybe the issue is solved with 1.2.4.

[RjOllos]

I tested this evening with Trac 1.2 and  could not reproduce.

I would try selectively disabling plugins to see if the issue goes away.

- Ryan

[Jun Omae]

Are you using PostgreSQL?
Same error with stack trace raises by query page,
http://your-trac-site/query?duedate=lastmonth..thismonth

The issue is caused by CAST(... AS bigint) with non-integer text on PostgreSQL.
Therefore, it is not fixed in 1.2-stable and trunk.


04:39:02 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
04:39:02 PM Trac[util] DEBUG: args: [1533049200000000L, 1535727600000000L]
04:39:02 PM Trac[util] DEBUG: execute exception: DataError('invalid
input syntax for integer: ""\n',)
04:36:46 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: ""


Ad hoc patch:
====
diff --git a/trac/ticket/query.py b/trac/ticket/query.py
index c65f2bfc8..8f28351de 100644
--- a/trac/ticket/query.py
+++ b/trac/ticket/query.py
@@ -31,7 +31,7 @@ from trac.db import get_column_names
from trac.mimeview.api import IContentConverter, Mimeview
from trac.resource import Resource
from trac.ticket.api import TicketSystem, translation_deactivated
-from trac.ticket.model import Milestone
+from trac.ticket.model import Milestone, _datetime_to_db_str
from trac.ticket.roadmap import group_milestones
from trac.util import Ranges, as_bool
from trac.util.datefmt import (datetime_now, from_utimestamp,
@@ -496,13 +496,14 @@ class Query(object):
def get_timestamp(date):
if date:
try:
- return to_utimestamp(user_time(req, parse_date, date))
+ return user_time(req, parse_date, date)
except TracError as e:
errors.append(unicode(e))
return None

def get_constraint_sql(name, value, mode, neg):
- if name not in custom_fields:
+ is_custom_field = name in custom_fields
+ if not is_custom_field:
col = 't.' + name
elif use_joins:
col = db.quote(name) + '.value'
@@ -516,23 +517,26 @@ class Query(object):
value.split('..', 1)]
else:
(start, end) = (value.strip(), '')
- col_cast = db.cast(col, 'int64')
start = get_timestamp(start)
end = get_timestamp(end)
+ clause = args = None
if start is not None and end is not None:
- return ("%s(%s>=%%s AND %s<%%s)"
- % ('NOT ' if neg else '', col_cast, col_cast),
- (start, end))
+ clause = "%s(%s>=%%s AND %s<%%s)" % \
+ ('NOT ' if neg else '', col, col)
+ args = (start, end)
elif start is not None:
- return ("%s%s>=%%s"
- % ('NOT ' if neg else '', col_cast),
- (start, ))
+ clause = "%s%s>=%%s" % ('NOT ' if neg else '', col)
+ args = (start,)
elif end is not None:
- return ("%s%s<%%s"
- % ('NOT ' if neg else '', col_cast),
- (end, ))
+ clause = "%s%s<%%s" % ('NOT ' if neg else '', col)
+ args = (end,)
else:
return None
+ if is_custom_field:
+ args = [_datetime_to_db_str(arg, True) for arg in args]
+ else:
+ args = [to_utimestamp(arg) for arg in args]
+ return clause, args

def split_words(splittable):
return [w.strip() for wl in
====

--
Jun Omae <jun...@gmail.com> (大前 潤)

[Mo]

Thanks for reproducing.
Yes it's Postgres.
Should I file this as bug report in order to not miss your patch proposal?

[Jun Omae]

Hi,

I noticed the issue has been filed at
https://trac.edgewall.org/ticket/13078, now.
I'll post the patch to the ticket.

Thanks.