[phpesp-dev] [ phpesp-Bugs-2674394 ] Dashboard returns no surveys due to ambiguous column in SQL

[SourceForge.net <no...@so...>]

Bugs item #2674394, was opened at 2009-03-09 04:03
Message generated for change (Settings changed) made by bishopb
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=108956&aid=2674394&group_id=8956

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: kswartz (kswartz)
>Assigned to: bishop (bishopb)
Summary: Dashboard returns no surveys due to ambiguous column in SQL

Initial Comment:
I'm using PHP version 2.1.2 (which isn't in the Group box, by the way -- that should be fixed.)

This SQL is executed in survey_get_in_realm():

SELECT DISTINCT tblSurvey.id, tblSurvey.name, tblSurvey.title, tblSurvey.owner, tblSurvey.open_date, tblSurvey.close_date
FROM survey AS tblSurvey
JOIN access AS tblAccess
  ON tblAccess.survey_id=tblSurvey.id
WHERE (tblSurvey.realm = 'auto' OR tblAccess.realm = 'auto')
AND $where

However, $where is computed in survey_fetch_limiting_where as this:

(((status & 3)) AND (NOT (status & 4)) AND (owner='kswartz' OR realm IS NULL)) ORDER BY id DESC

When you combine the two, the SQL statement fails because of an ambiguous reference to realm, which appears in both of the tables being joined. As a result, when users log in to take a survey in my install, they never see anything listed.

(I'm using MySQL 5.0, but this error would happen with any database.)

The easiest fix is one line:
184c184
<      $where = survey_fetch_limiting_where();
---
>      $where = survey_fetch_limiting_where('status','owner','tblSurvey.realm');


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=108956&aid=2674394&group_id=8956