Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column
---+---
Reporter: olemis| Owner: gjm
Type: defect| Status: accepted
Priority: blocker | Milestone: Release 8
Component: multiproduct |Version:
Resolution:| Keywords: ticket query, sorting
---+---
Comment (by olemis):
This is what I did , I applied [attachment:t654_r1551811_query_sort.diff
this patch] like shown below
{{{#!sh
$ hg qapplied
t654/t654_r1551811_query_sort.diff
$ hg log -r qparent
changeset: 506:ac7476a0b5da
tag: qparent
tag: svn-1551811
user:rjollos@13f79535-47bb-0310-9956-ffa450edef68
date:Wed Dec 18 02:31:10 2013 +
summary: 0.8dev: Added Bootstrap styling to the diff form.
}}}
... and , even if rows seem to be sorted by id , priority ... , I noticed
this error in the logs
[[span(Log entry, class=label label-important)]]
{{{
2014-01-05 21:35:59,541 Trac[theme] ERROR: BH: Quick create ticket failed
column product specified more than once
LINE 1: ...one,component,version,resolution,time,changetime, product) V...
}}}
[[span(Traceback, class=label label-important)]]
{{{#!py
Traceback (most recent call last):
File /path/to/bloodhound/bloodhound_theme/bhtheme/theme.py, line 595,
in process_request
product, tid = self.create(req, summary, desc, attrs, True)
File /path/to/bloodhound/bloodhound_theme/bhtheme/theme.py, line 638,
in create
t.insert()
File /path/to/bloodhound/trac/trac/ticket/model.py, line 241, in
insert
[values[name] for name in std_fields])
File
/path/to/bloodhound/bloodhound_multiproduct/multiproduct/dbcursor.py,
line 145, in execute
return self.cursor.execute(sql, args=args)
File
/path/to/bloodhound/bloodhound_multiproduct/multiproduct/dbcursor.py,
line 85, in execute
return super(BloodhoundIterableCursor,
self).execute(translate_sql(self.env, sql), args=args)
File /path/to/bloodhound/trac/trac/db/util.py, line 65, in execute
return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: column product specified more than once
LINE 1: ...one,component,version,resolution,time,changetime, product) V...
^
}}}
--
Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:13
Apache Bloodhound https://issues.apache.org/bloodhound/
The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column
---+---
Reporter: olemis| Owner: gjm
Type: defect| Status: accepted
Priority: blocker | Milestone: Release 8
Component: multiproduct |Version:
Resolution:| Keywords: ticket query, sorting
---+---
Comment (by gjm):
OK, I think I am finally getting to the bottom of this. The issue appears
to be that in SQL you cannot rely on the ordering of a subquery to
maintain order in the outer query.
If we don't wish to adjust the sql within {{{trac.ticket.query.Query}}}
then the only ways I can think of to fix this at the moment involve
capturing and editing the sql produced by that module rather than wrapping
changes around it - and if we are going to do that then we might as well
do the job properly so I am currently looking at replacing the overriding
{{{multiproduct.ticket.query.Query.execute}}} method with something a
little simpler and overriding
{{{multiproduct.ticket.query.Query.get_sql}}} like so:
{{{#!python
def get_sql(self, *args, **kwargs):
sql, args = super(ProductQuery, self).get_sql(*args, **kwargs)
parts = sql.split('\n')
enum_match = re.compile(r^\s*LEFT OUTER JOIN enum
rAS (?Ptype\b\w+\b) ON \()
enum_replace = (LEFT OUTER JOIN enum AS \gtype ON
(\gtype.product=t.product AND )
sql = '\n'.join(enum_match.sub(enum_replace, part) for part in
parts)
return sql, args
def execute(self, *args, **kwargs):
old_mp_schema_enabled = self.env._multiproduct_schema_enabled
results = super(ProductQuery, self).execute(*args, **kwargs)
self.env._multiproduct_schema_enabled = old_mp_schema_enabled
return results
}}}
The regex might be a little over-the-top and there may well be better ways
of attacking the problem, particularly as there is some reliance on a
fairly specific string to search for but it seems worth reporting sooner
rather than later.
--
Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:11
Apache Bloodhound https://issues.apache.org/bloodhound/
The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column
---+---
Reporter: olemis| Owner: gjm
Type: defect| Status: accepted
Priority: blocker | Milestone: Release 8
Component: multiproduct |Version:
Resolution:| Keywords: ticket query, sorting
---+---
Comment (by olemis):
Replying to [comment:11 gjm]:
OK, I think I am finally getting to the bottom of this. The issue
appears to be that in SQL you cannot rely on the ordering of a subquery to
maintain order in the outer query.
If we don't wish to adjust the sql within {{{trac.ticket.query.Query}}}
then the only ways I can think of to fix this at the moment involve
capturing and editing the sql produced by that module rather than wrapping
changes around it
This issue is only happening in that particular instance in production
code . I will create a backup with relevant data so as to test your
suggestion in my local working copy . I'll report my findings soon after
that .
[...]
--
Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:12
Apache Bloodhound https://issues.apache.org/bloodhound/
The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column ---+--- Reporter: olemis| Owner: gjm Type: defect| Status: accepted Priority: blocker | Milestone: Release 8 Component: multiproduct |Version: Resolution:| Keywords: ticket query, sorting ---+--- Comment (by rjollos): #689 closed as a duplicate. -- Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:10 Apache Bloodhound https://issues.apache.org/bloodhound/ The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column
---+---
Reporter: olemis| Owner: gjm
Type: defect| Status: accepted
Priority: blocker | Milestone: Release 8
Component: multiproduct |Version:
Resolution:| Keywords: ticket query, sorting
---+---
Comment (by gjm):
I've been considering this one for a while now. The immediate problem is
that lists of enums (Type, Status, Resolution, Priority and Severity for
example) are not available as the query to select them is restricted to
the product that they are performed in. This makes a bit less sense at the
top level where there are none.
There are a number of ways to deal with this, one of which includes
providing an extra variable on the env object to determine whether to
force a db_direct_query and decorate the !AbstractEnum.select method to
choose when no product is selected. This would look something like:
{{{
#!python
import copy
from trac.ticket import model
from functools import wraps
def direct_query_override(f):
@classmethod
@wraps(f)
def decorator(cls, env, *args, **kwargs):
kwargs.pop('db', None)
denv = copy.copy(env)
if not getattr(denv, 'product', None):
denv._db_direct_override = True
return f.__get__(True).im_func(cls, denv, *args, **kwargs)
return decorator
model.AbstractEnum.select =
direct_query_override(model.AbstractEnum.select)
}}}
That still leaves a bit to do about including:
* duplicate removal
* restricting returned values based on permissions
* decide whether we care that enums may end up with different priorities
in different products
--
Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:8
Apache Bloodhound https://issues.apache.org/bloodhound/
The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column ---+--- Reporter: olemis| Owner: gjm Type: defect| Status: accepted Priority: blocker | Milestone: Release 8 Component: multiproduct |Version: Resolution:| Keywords: ticket query, sorting ---+--- Changes (by gjm): * owner: nobody = gjm * status: new = accepted -- Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:7 Apache Bloodhound https://issues.apache.org/bloodhound/ The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column ---+--- Reporter: olemis| Owner: nobody Type: defect| Status: new Priority: blocker | Milestone: Release 8 Component: multiproduct |Version: Resolution:| Keywords: ticket query, sorting ---+--- Comment (by gjm): olemis: think you can help us tie this down? I don't see this behaviour on bh-demo1.apache.org -- Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:4 Apache Bloodhound https://issues.apache.org/bloodhound/ The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column
#654: Rows in ticket query result set not sorted by column ---+--- Reporter: olemis| Owner: nobody Type: defect| Status: new Priority: blocker | Milestone: Release 8 Component: multiproduct |Version: Resolution:| Keywords: ticket query, sorting ---+--- Comment (by olemis): Replying to [comment:4 gjm]: olemis: think you can help us tie this down? I don't see this behaviour on bh-demo1.apache.org what I notice is 1. at https://bh-demo1.apache.org/query ''Priority'' column header is missing , replaced by ''Ticket'' I think I'll have to take a look into the DB . Recall that I'm noticing these errors on an instance upgraded through the following path 0.5 = 0.6 = 0.7 = 0.8-dev @ /trunk . So maybe this is some kind of DB corruption after performing the last upgrade step i.e. after adding product-specific ticket seq . I have this suspicion mainly considering that I'm also experiencing the issue mentioned in #668 and the location in source code where these negative int ticket IDs are located ^citation needed^. -- Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:5 Apache Bloodhound https://issues.apache.org/bloodhound/ The Apache Bloodhound issue tracker
Re: [Apache Bloodhound] #654: Rows in ticket query result set not sorted by column (was: No sorting of columns in /query)
#654: Rows in ticket query result set not sorted by column ---+--- Reporter: olemis| Owner: nobody Type: defect| Status: new Priority: blocker | Milestone: Release 8 Component: multiproduct |Version: Resolution:| Keywords: ticket query, sorting ---+--- -- Ticket URL: https://issues.apache.org/bloodhound/ticket/654#comment:3 Apache Bloodhound https://issues.apache.org/bloodhound/ The Apache Bloodhound issue tracker
