RSS Feed

Plugin Developer Discussion

I'm building some queries and I have one which uses AddInnerJoinSelect like this:

    CBugQuery bugQuery = api.Bug.NewBugQuery();
    bugQuery.AddInnerJoinSelect(GetUserPublicProjects(), "Project", "Project.ixProject=Bug.ixProject");

Now it is true that GetUserPublicProjects itself has a table alias "Project" already, but since it is a nested select query, this doesn't matter at all (it is not exposed to the outer query anyways). The expected query (simplified) would be something like:

SELECT *
FROM Bug
JOIN (
  SELECT *
  FROM Project
  WHERE ixProject=@ixProject
) Project ON (Bug.ixProject = Project.ixProject)

The names and aliases of the tables inside the nested query are meaningless outside of it, but FogBugz seems not to respect this.

The exact exception is:

FogCreek.FogBugz.Exception.FogBugzException: Invalid syntax: Join table "Project" already exists.

Server stack trace:
  at FogCreek.FogBugz.Database.CSqlValidator.AssertValidJoinOnList(String s, IList listsTablesLeft, String sTableRight)
  at FogCreek.FogBugz.Database.CSelectQuery.AddJoinUnsafe(String sSql, String sAlias, String sJoin, String sSqlOn)
  at FogCreek.FogBugz.Database.CSelectQuery.AddJoinSelect(CSelectQuery query, String sAlias, String sJoin, String sSqlOn)
  at FogCreek.FogBugz.Database.CSelectQuery.AddInnerJoinSelect(CSelectQuery query, String sAlias, String sSqlOn)

My first assumption (that the inner select is the problem) seems to be wrong. The CBugQuery class (or, to be exact, FogCreek.FogBugz.CBug.FillQuery) already joins a bunch of other tables and this seems to cause the problem.

This was unexpected for me since CBug does not expose any of the additional selected fields from joined tables. In FillQuery, I found the following:

  oQuery.AddInnerJoin("Project", "Bug.ixProject = Project.ixProject", null);
    oQuery.AddSelect("Project.sProject AS sProject");
    oQuery.AddSelect("Project.ixGroup AS ixGroup");
    oQuery.AddInnerJoin("Area", "Bug.ixArea = Area.ixArea", null);
    oQuery.AddSelect("Area.sArea AS sArea");
    oQuery.AddInnerJoin("Person", "Bug.ixPersonAssignedTo = PersonAssignedTo.ixPerson", "PersonAssignedTo");
    oQuery.AddSelect("PersonAssignedTo.sFullName AS sPersonAssignedTo");
    oQuery.AddSelect("PersonAssignedTo.sEmail AS sEmailAssignedTo");
    oQuery.AddSelect("PersonAssignedTo.fNotify AS fNotify");
    oQuery.AddInnerJoin("Status", "Bug.ixStatus = Status.ixStatus", null);
    oQuery.AddSelect("Status.sStatus AS sStatus");
    oQuery.AddInnerJoin("Priority", "Bug.ixPriority = Priority.ixPriority", null);
    oQuery.AddSelect("Priority.sPriority AS sPriority");
    oQuery.AddLeftJoin("BugView", "Bug.ixBug = BugView.ixBug AND BugView.ixPerson = @BUGVIEW_ixPerson", null);
    oQuery.AddSelect("BugView.ixBugEvent AS ixBugEventLastView");
    oQuery.AddSelect("BugView.dt AS dtLastView");
    oQuery.SetParam("BUGVIEW_ixPerson", __Global.__Current.GetPersonID(), "I");
    oQuery.AddInnerJoin("FixFor", "Bug.ixFixFor = FixFor.ixFixFor", null);
    oQuery.AddSelect("FixFor.sFixFor AS sFixFor");
    oQuery.AddSelect("FixFor.dt AS dtFixFor");

The docs don't tell me anything about the joined tables, and therefore trying to join them fails. However, how can I be sure that those joins will also exist and remain unchanged (e.g. no other alias or whatever) in future versions? I think you should address this somehow, at least via proper documentation.

Hi Arsène-
You're right, this is one of those places where the interface definition is, by necessity, a little bit fuzzy. We would probably not major-version-bump the API for a minor change to the structure of the joins on this query. We'll document this policy better.
Thanks!
Brett