Postgres 8.3 - broken sql statements in all statistics sections

[jen@…]

The SQL queries used in all the statistics are substantially broken and don't work w/ Postgres. The error that I am getting for example in the Time stats section is the following:

 Trac detected an internal error:

OperationalError: ERROR:  column reference "timestamp" is ambiguous
LINE 1: SELECT  min(c.timestamp), max(c.timestamp)  FROM (downloader...
                    ^
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 423, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 197, in dispatch
  resp = chosen_handler.process_request(req)
File "build/bdist.linux-x86_64/egg/tracdownloader/web_ui.py", line 124, in process_request
File "build/bdist.linux-x86_64/egg/tracdownloader/web_ui.py", line 195, in _render_filtered_stats
File "build/bdist.linux-x86_64/egg/tracdownloader/web_ui.py", line 352, in _render_element_review
File "build/bdist.linux-x86_64/egg/tracdownloader/web_ui.py", line 424, in _render_month_year_range
File "build/bdist.linux-x86_64/egg/tracdownloader/model.py", line 987, in fetch_downloads_list
File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/pyPgSQL/PgSQL.py", line 3111, in execute
  raise OperationalError, msg

The complete SQL statement that it tries executing was:

SELECT  min(c.timestamp), max(c.timestamp)  FROM (downloader_downloaded AS d JOIN downloader_file AS f ON  f.id=d.file) AS c     WHERE  c.id > 0       ORDER BY c.timestamp;

Essentially, there are 3 problems that I see in this statement:

• as the error explains the timestamp column is ambiguous and is present in both tables that are being joined. Which of the 2 should it select?

• the next problem that will occur immediately after the first one is in the WHERE clause, which id shall it take for comparison?

• ORDER BY clause for aggregation functions min/max doesn't seems to be appropriate and postgres complains as well

Here is a sketch of the corrected statement that is at least acceptable for postgres and I am assuming for other DB's as well. I am not sure if it does what was intended:

SELECT  min(d.timestamp), max(d.timestamp)  FROM (downloader_downloaded AS d JOIN downloader_file AS f ON  f.id=d.file)      WHERE  d.id > 0

Let me know, if you need any further info/help. I am willing to fix these things on my own and submit patches.

Thanks,

Jan

[Ryan J Ollos]

Duplicate of #4214.