Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 4/29/14, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> On 29/04/2014 09:59, David Noel wrote:
>> "select page.*, coalesce((select COUNT(*) from sentence where
>> sentence."PageURL" = page."URL" group by page."URL"), 0) as
>> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
>> THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100"
>
> In all honesty, this query is very badly written. It seems like it was
> ported from some other
> system. The inner group by in the coalesce is redundant since the result is
> always one row,
> moreover, it is wrong since coalesce accepts a scalar value, it hits the eye
> at first sight.
> Additionally, ''<>'' always returns false, what's the purpose of the CASE
> statement?

Ok, thanks for the heads up. It confused me, too. It's code I'm just
picking up from another developer, so I don't know why it was done the
way it was done. I'm not super proficient with SQL but I'll take a
stab at rewriting it.

> Try to re-write the query in a good form, and then perform EXPLAIN ANALYZE
> on both systems to see what's wrong.

Will do. Thanks for the advice.