Opened 6 years ago

Closed 15 months ago

#4216 closed defect (duplicate)

Postgres 8.3 - broken sql statements in all statistics sections

Reported by: jen@… Owned by: rjollos
Priority: highest Component: TracDownloaderPlugin
Severity: blocker Keywords:
Cc: Trac Release: 0.11


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/", line 423, in _dispatch_request
File "/usr/lib/python2.5/site-packages/trac/web/", line 197, in dispatch
  resp = chosen_handler.process_request(req)
File "build/bdist.linux-x86_64/egg/tracdownloader/", line 124, in process_request
File "build/bdist.linux-x86_64/egg/tracdownloader/", line 195, in _render_filtered_stats
File "build/bdist.linux-x86_64/egg/tracdownloader/", line 352, in _render_element_review
File "build/bdist.linux-x86_64/egg/tracdownloader/", line 424, in _render_month_year_range
File "build/bdist.linux-x86_64/egg/tracdownloader/", line 987, in fetch_downloads_list
File "/usr/lib/python2.5/site-packages/trac/db/", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/trac/db/", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/pyPgSQL/", 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 AS c     WHERE > 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      WHERE > 0

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



Attachments (0)

Change History (2)

comment:1 Changed 15 months ago by rjollos

  • Owner changed from peca to rjollos
  • Status changed from new to assigned

comment:2 Changed 15 months ago by rjollos

  • Resolution set to duplicate
  • Status changed from assigned to closed

Add Comment

Modify Ticket

as closed .
The resolution will be deleted. Next status will be 'reopened'.

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.