Subject Re: [firebird-support] CAST() in Group By clause causes no records to be returned
Author Jason Dodson
> You should be getting an exception here. You can't GROUP BY one ordering
> of a column and then try to order that set by a different ordering of the
> same column. (casting to any varchar would give the same ordering, not a
> different one...)
> Do you happen to be using the Fb 1.5.3 release candidate 2 and isql with
> the -q switch? (It's become ultra-user-friendly - it doesn't deliver error
> messages at all. :-> )

I am using Firebird 1.5.2 on Gentoo Linux. This wasn't JUST through ISQL. As a
matter of fact, ISQL was the last client tool I tried before posting this
message. Others that I have tried are IBOConsole and EMS Firebird manager.

> To fix the isql problem, go back to Fb 1.5.2 until the Fb 1.5.3
> release. To fix the SQL problem, push the cast expression up into the
> column selection list.
> Select Cast(SortedBy as integer) as TotalGroup, sum(MyFees) as FeesTotal,
> sum(MyTaxes) as TaxesTotal
> From MyTable
> Group By 1
> Order By 1;
> Now, unless you've got something else in the GROUP BY spec that's bending
> the output order of the groups, you shouldn't need the ORDER BY
> anyway....but you did mention that you'd simplified the example.
> ./heLen

I appreciate the help. I found several ways to get what I needed. The original
attempt was quick and dirty. I figured my original SQL would raise an exception,
so it actually executing and returning 0 records was suprising. Since you are
more or less confirming this to be a bug, I will check out the latest 1.5.3
release and see if it acts the same way. Providing it does, I will file a bug