RSS Feed

Plugin Developer Discussion

Hi,

I'm implementing an aspect of a plugin that requires information to be stored as a join against the Person tables.

I've implemented the appropriate interfaces, and the UI and manipulation of the value against users is working fine.

However, when I go back to one of the pages for the plugin, I get an exception. It is triggered by the CPersonQuery.List() method, with the text

SQL:  SELECT Person.ixPerson AS ixPerson, Person.sFullName AS sFullName, Person.sEmail AS sEmail, Person.fNotify AS fNotify, Person.fEscalationReport AS fEscalationReport, Person.sVersion AS sVersion, Person.sComputer AS sComputer, Person.sPhone AS sPhone, Person.fExpert AS fExpert, Person.fAdministrator AS fAdministrator, Person.fDeleted AS fDeleted, Person.sFrom AS sFrom, Person.fGridView AS fGridView, Person.ixArea AS ixArea, Person.ixBugWorkingOn AS ixBugWorkingOn, Person.sVersionSeen AS sVersionSeen, Person.sContractSeen AS sContractSeen, Person.sPasswordVersion AS sPasswordVersion, Person.sSnippetKey AS sSnippetKey, Person.sLDAPUid AS sLDAPUid, Person.fMostRecentEventFirst AS fMostRecentEventFirst, Person.fCommunity AS fCommunity, Person.fConfirmed AS fConfirmed, Person.fVirtual AS fVirtual, Person.sHomepage AS sHomepage, Person.dtRegistered AS dtRegistered, Person.sLocale AS sLocale, Person.sLanguage AS sLanguage, Person.sTimeZoneKey AS sTimeZoneKey, Person.dtLastDailyTask As dtLastDailyTask, Person.dtLastScheduleMaint As dtLastScheduleMaint, Person.dtLastActivity As dtLastActivity, Person.bfHelpTips As bfHelpTips, Person.fRecurseBugChildren AS fRecurseBugChildren, Person.fPaletteExpanded AS fPaletteExpanded, Person.fPaletteModified AS fPaletteModified, Plugin_12_PersonRole.ixPerson AS Plugin_12_PersonRole_ixPerson, Plugin_12_PersonRole.ixRole AS Plugin_12_PersonRole_ixRole FROM  Person  LEFT JOIN Plugin_12_PersonRole AS Plugin_12_PersonRole ON ( Plugin_12_PersonRole.ixPerson = Person.ixPerson ) WHERE ( Person.ixPerson IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,1,-1) ) AND ( ixPerson > 1 ) AND ( Person.fDeleted = 0 )

which contains the ambigious clause ( ixPerson > 1 ).

It seems like that clause is outside of my control, so I assume that it has to be inside of the Person Select query.

Have I implemented the interfaces for joining tables to the Person tables incorrectly?

Also: (apologies)

For context, this is the specific incantation I'm using for the CPersonQuery

            CPersonQuery query = Api.Person.NewPersonQuery();
            CPerson[] users = query.List();

Hi Clayton-
That looks like a straight-up bug in our code. Thank you for reporting it. I think that you can work around this by setting ExcludeClosedPerson to False and then filtering the person with ixPerson=0 out yourself. Does that work for you?
Thanks!
Brett

Brett,

Thanks for the quick reply! I'll try out your workaround ASAP and let you know whether there are any problems.

-Clayton

Brett,

Was able to give your workaround a quick go, and it looks like that was the source of the clause in question, things are good to go for the plugin code.

However, it looks like the Maintenance Service is also running a similar query. The Administrative notifications when I signed in showed an error which seemed to also be using the same query (I've included the full text of the error at the bottom of this posting).

Can you let me know what aspect of the maintenance service here is failing? I don't want to push the plugin from staging to our production server if this is a critical process.

Thanks,
-Clayton

12/22/2009 4:59 PM (GMT-05:00): Problem mailing escalation report: Column 'ixPerson' in where clause is ambiguous
SQL:  SELECT Person.ixPerson AS ixPerson, Person.sFullName AS sFullName, Person.sEmail AS sEmail, Person.fNotify AS fNotify, Person.fEscalationReport AS fEscalationReport, Person.sVersion AS sVersion, Person.sComputer AS sComputer, Person.sPhone AS sPhone, Person.fExpert AS fExpert, Person.fAdministrator AS fAdministrator, Person.fDeleted AS fDeleted, Person.sFrom AS sFrom, Person.fGridView AS fGridView, Person.ixArea AS ixArea, Person.ixBugWorkingOn AS ixBugWorkingOn, Person.sVersionSeen AS sVersionSeen, Person.sContractSeen AS sContractSeen, Person.sPasswordVersion AS sPasswordVersion, Person.sSnippetKey AS sSnippetKey, Person.sLDAPUid AS sLDAPUid, Person.fMostRecentEventFirst AS fMostRecentEventFirst, Person.fCommunity AS fCommunity, Person.fConfirmed AS fConfirmed, Person.fVirtual AS fVirtual, Person.sHomepage AS sHomepage, Person.dtRegistered AS dtRegistered, Person.sLocale AS sLocale, Person.sLanguage AS sLanguage, Person.sTimeZoneKey AS sTimeZoneKey, Person.dtLastDailyTask As dtLastDailyTask, Person.dtLastScheduleMaint As dtLastScheduleMaint, Person.dtLastActivity As dtLastActivity, Person.bfHelpTips As bfHelpTips, Person.fRecurseBugChildren AS fRecurseBugChildren, Person.fPaletteExpanded AS fPaletteExpanded, Person.fPaletteModified AS fPaletteModified, Plugin_12_PersonRole.ixPerson AS Plugin_12_PersonRole_ixPerson, Plugin_12_PersonRole.ixRole AS Plugin_12_PersonRole_ixRole FROM  Person  LEFT JOIN Plugin_12_PersonRole AS Plugin_12_PersonRole ON ( Plugin_12_PersonRole.ixPerson = Person.ixPerson ) WHERE ( fDeleted = 0 AND fEscalationReport <> 0 AND (UNIX_TIMESTAMP('2009-12-22 21:59:04') - UNIX_TIMESTAMP(dtLastDailyTask))  >= 86400 ) AND ( Person.ixPerson IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,1,-1,-2) ) AND ( ixPerson > 1 ) AND ( Person.fDeleted = 0 )

Hi Clayton-
It looks as though that notification is from yesterday -- if you clear the message and let heartbeat run with the modified plugin code, do you see the same error?
Thanks!
brett

Brett,

This error seems to still be occurring after cleaning the error log and upgrading to FogBugz 7.10. It seems as though general queries against the Person table in the core FB code use an unqualified reference to "ixPerson" to filter out the Closed person.

We currently have users who we'd like to be able to push this new feature to, but I don't feel that it would be advisable to do so until this problem is resolved. Is there a timeline for addressing the bug?

Thanks,
-Clayton