reproduce:
roundup-demo tracker with mysql backend (well, any
tracker with mysql will do).

1. create an issue with file in it (skip if you have one)
2. look up the file id
3. search with the default search settings (that is,
with sorting and grouping)
4. modify the resulting url: add
&files=<file-id-from-step-2>
5. mysql fails

Files used just an example of multilink.

Query from step 3 (ok):
select _issue.id,rhs0_._order,_issue._activity 
from _issue LEFT OUTER JOIN _priority as rhs0_ on
_issue._priority=rhs0_.id  
where _issue.__retired__ <> 1 
order by rhs0_._order,_issue._activity desc
()

Query from 4 (fails):
select distinct(_issue.id),rhs0_._order,_issue._activity 
from _issue,issue_files LEFT OUTER JOIN _priority as
rhs0_ on _issue._priority=rhs0_.id  
where _issue.id=issue_files.nodeid and
issue_files.linkid in (%s) and _issue.__retired__ <> 1 
order by rhs0_._order,_issue._activity desc
('1',)

If you don't use grouping, it works again:
select distinct(_issue.id),_issue._activity 
from _issue,issue_files   
where _issue.id=issue_files.nodeid and
issue_files.linkid in (%s) and _issue.__retired__ <> 1 
order by _issue._activity desc
('1',)


Problem with step 4's query is with the 'where'
clause's table order and 'order by'. order by
references _issue, but sees only issue_files and rhs_0
tables (I think, SQL isn't my specialty).

Logged In: YES 
user_id=150272

In MySQL 5.0, the LEFT JOIN syntax is more strict.  I fixed
this by patching back_mysql.py to wrap the tables in the
FROM clause in parentheses (see below).  I have no idea
whether this works for previous versions of MySQL, but it
works for 5.0:

***
/cygdrive/d/incoming/roundup-1.1.2/roundup/backends/back_mysql.py
Wed Apr 26 22:40:19 2006
--- back_mysql.py	Tue Aug 15 13:16:04 2006
***************
*** 783,789 ****
              order = ''
          cols = ','.join(cols)
          loj = ' '.join(loj)
!         sql = 'select %s from %s %s %s%s'%(cols, frum,
loj, where, order)
          args = tuple(args)
          self.db.sql(sql, args)
          l = self.db.cursor.fetchall()
--- 783,789 ----
              order = ''
          cols = ','.join(cols)
          loj = ' '.join(loj)
!         sql = 'select %s from (%s) %s %s%s'%(cols, frum,
loj, where, order)
          args = tuple(args)
          self.db.sql(sql, args)
          l = self.db.cursor.fetchall()

Logged In: YES 
user_id=870843

Ah, forgot to mention mysql version, 5.0.21.

And big thank you to Mark.

Logged In: YES 
user_id=34818

I've just added a new test for reproducing this to the
test-suite in the roundup CVS. Can somebody with mySQL 5.0
installed verify that the suite really fails the new test
with mySQL 5.0, I've got only 4.1 to test and this does not
fail. To run the new test:

- Get roundup from CVS
- In the checked out repository run
  python run_tests.py -vv test \
  '^testFilteringMultilinkAndGroup$'

this test produces a query very similar to the failing query
in the original report, so it should fail with mySQL 5.0

Closing this 3 year old bug:
- My test framework now has mysql 5 (debian lenny)
- my test from 2006 doesn't fail
- nobody has complained again

So if this is still a problem, please open a new bug-report with a
step-by-step howto reproduce.