[issue2551119] Bug in Query-Generator for filter (rdbms backend)

New submission from Ralf Schlatterbeck:

There is a feature in the query generator that can combine ID search with boolean expressions AND, OR, NOT, see commit f1fe6fd0aa61. This change came without a regression test and breaks for certain cases. Without a test of the *intended* feature I dare not touch that code. As far as I understand this uses *negative* IDs to implement a prefix or suffix expression parser.

My use-case that breaks:

We have two tables, time_project and time_wp with the following data structures in roundup (excerpt):

time_project

  wps = Multilink('time_wp')
  status = Link('project_status')
  name = String()

time_wp

  bookers = Multilink(user)

Now a user enters a query with a work-package name that doesn't exist in the web-interface. The resulting call to filter has the following arguments:

sort = [('+', 'name')]
filterspec = {'status': ['2', '1'], 'wps.bookers': ['2428'], 'wps': []}

This produces a traceback:
<class 'psycopg2.errors.UndefinedTable'>: missing FROM-clause entry for table "_time_wp3" LINE 1: ...t from _time_wp where _time_wp.__retired__=0) and _time_wp3.... ^

...

  return render(ob, econtext.vars)
  File "/usr/local/lib/python2.7/dist-packages/roundup/cgi/PageTemplates/Expressions.py", line 99, in render
    ob = ob()
  File "/usr/local/lib/python2.7/dist-packages/roundup/cgi/templating.py", line 3257, in batch
    l = [id for id in klass.filter(matches, filterspec, sort, group)
  File "/usr/local/lib/python2.7/dist-packages/roundup/backends/rdbms_common.py", line 2858, in filter
    sq = self._filter_sql (search_matches, filterspec, sort, group,
  File "/usr/local/lib/python2.7/dist-packages/roundup/backends/rdbms_common.py", line 233, in sql
    cursor.execute(sql, args)
UndefinedTable: missing FROM-clause entry for table "_time_wp3"
LINE 1: ...t from _time_wp where _time_wp.__retired__=0) and _time_wp3....
                                                             ^

It looks like the mentioned buggy code is triggered by the empty wps multilink in the search.
Note how it tries to materialize the whole time_wp table (which contains 43665 entries in our case!) in a subquery.
And it doesn't seem to correctly plug into the query generator: the from clause is missing an entry which is later referenced.

I'd appreciate if someone using this extended query feature can turn on SQL statement logging (either in roundup or in the backend database) and provide me with pairs of filter calls and the generated SQL. Then I'd write an regression test for the intended behaviour.

@Bernhard Reiter: Do you still use roundup with that feature? Examples of usage would be welcome!

----------
assignee: schlatterbeck
components: Database, Test
messages: 7124
nosy: ber, schlatterbeck
severity: normal
status: new
title: Bug in Query-Generator for filter (rdbms backend)

_________________________________________________
Roundup tracker <[email protected]>
<https://issues.roundup-tracker.org/issue2551119>
_________________________________________________