RSS Feed

Plugin Developer Discussion

YAY, my first actual plugin related post!

The following code:

var q = api.Database.NewSelectQuery("BugEvent");
q.AddGroupBy("YEAR(dt), MONTH(dt), DAY(dt)");
q.AddOrderBy("YEAR(dt) ASC, MONTH(dt) ASC, DAY(dt) ASC");
q.AddSelect("COUNT(ixBugEvent) AS c, YEAR(dt) AS ixYear, MONTH(dt) AS ixMonth, DAY(dt) AS ixDay");
q.AddWhere("ixBug IN (" + srgixbug + ")");

throws the following exception, but it is valid code in MSSQL, MySql, and Jet:

System.ArgumentException: Invalid GROUP BY expression 'YEAR(dt), MONTH(dt), DAY(dt)':  Invalid syntax near '( dt ) , MONTH ( dt ) , DAY ( dt )'

Server stack trace:
  at FogCreek.FogBugz.Database.CSqlValidator.AssertValid(String s, String sType)
  at FogCreek.FogBugz.Database.CSqlValidator.AssertValidGroupByList(String s)
  at FogCreek.FogBugz.Database.CSelectQuery.AddGroupBy(String sSqlGroupBy)
  at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
  at FogCreek.FogBugz.Database.CSelectQuery.AddGroupBy(String sSqlGroupBy)
  at ActivityGraph.ActivityGraphPlugin.<GetData>d__9.MoveNext()
  at ActivityGraph.ActivityGraphPlugin.<FormatData>d__0.MoveNext()
  at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
  at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
  at ActivityGraph.ActivityGraphPlugin.GeneratePlots(CBug[] rgbug, String& result)
  at ActivityGraph.ActivityGraphPlugin.GenerateGraph(CBug[] rgbug, String& result)
  at ActivityGraph.ActivityGraphPlugin.BugDisplayEditTop(CBug[] rgbug, BugEditMode nMode, Boolean fPublic)
  at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [1]:
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
  at FogCreek.FogBugz.Plugins.Interfaces.IPluginBugDisplay.BugDisplayEditTop(CBug[] rgbug, BugEditMode nMode, Boolean fPublic)
  at FogCreek.FogBugz.__Global.Lambda_PluginTopHTML_8.__Run(IPluginBugDisplay i) in c:\src\FogBugz\distribution\src-Website\editBug2.was:line 740
  at FogCreek.FogBugz.__Global.PluginBugDialogItems(Function`2 fx) in c:\src\FogBugz\distribution\src-Website\editBug2.was:line 694

Hi Stefan, I've opened a case for this and will get back to you once we've figured this out.

From looking at the CSqlValidator class. It looks like the code was written with the assumption that the GROUP BY clause can reference columns in the SELECT section, and many times it may work out that way, but the GROUP BY clause references only the tables and columns in the FROM clause.

Allowing subqueries might solve the problem in the majority of situations. So instead of this:

var q = api.Database.NewSelectQuery("BugEvent");
q.AddGroupBy("YEAR(dt), MONTH(dt), DAY(dt)");
q.AddOrderBy("YEAR(dt) ASC, MONTH(dt) ASC, DAY(dt) ASC");
q.AddSelect("COUNT(ixBugEvent) AS c, YEAR(dt) AS ixYear, MONTH(dt) AS ixMonth, DAY(dt) AS ixDay");
q.AddWhere("ixBug IN (" + srgixbug + ")");

A plugin might do this:

var qbe = api.Database.NewSelectQuery("BugEvent");
qbe.AddSelect("ixBugEvent, YEAR(dt) AS ixYear, MONTH(dt) AS ixMonth, DAY(dt) AS ixDay");
qbe.AddWhere("ixBug IN (" + srgixbug + ")");

var q = api.Database.NewSelectQuery(qbe);
q.AddGroupBy("ixYear, ixMonth, ixDay");
q.AddOrderBy("ixYear, ixMonth, ixDay");
q.AddSelect("COUNT(ixBugEvent) AS c, ixYear, ixMonth, ixDay");

The result is much easier to read code. I don't know about MySql, but I feel confident that the MSSQL execution plan would be identitical.

Hi Stefan,

I'm a few hundred miles away from my computer just now, so appologies for not trying this myself.

What happens if the original group by is changed to use the aliases from the select (ixYear, ixMonth, ixDay) instead of date functions?

MSSQL complains because it doesn't know that ixYear, ixMonth, or ixDay are. This is because the values in the GroupBy clause are resolved only in the context of the From Clause.

I've loosened up the restrictions on the SQL validator, so ORDER BY and GROUP BY clauses now permit expressions.  I also added "YEAR", "MONTH", and "DAY" to the list of function supported in all our DBMSs.

With the next release, the query above should work with one change:

q.AddOrderBy("YEAR(BugEvent.dt) ASC, MONTH(BugEvent.dt) ASC, DAY(BugEvent.dt) ASC");

We now require that columns in the ORDER BY clause specify the table name and column, because of limitations in JET/Access.

cool thanks!