Modify

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

Description

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

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

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


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

 
Note: See TracTickets for help on using tickets.