I just discovered a simple query that has been in my code for many
years now breaks.  

An example query generated by my old code was:
{code}
   SELECT MAX("Round") AS R FROM "Diveheets" WHERE "Meet"=2 AND
"Event"=10 AND "CumPoints">0
{code}

It used to return the highest "Round" with a score.  Sadly it now
returns a NULL no matter the data.

The fix seems to be to modify the MAX clause to include a CAST:
   MAX(CAST("Round" AS INT))

Is this new?  I do not see it in the 2.26 release notes.
Or have I just been lucky in the past, before Tim fixed some bug?  

Yes, it seems that both MAX() and MIN() now need a cast.

But AVG(), COUNT() and SUM do not (apparently).  But some of those will
be implicit.

If these are not listed in the changes, then email Tim direct to report it as an issue.

--

Matthew Jones

I agree with Matthew Jones

That sounds like a bug. I use MAX very widely, and it will be nasty to have to recode all my SQL with CAST.

Hope Tim can fix this swiftly.

I was hoping someone could confirm my observation before I contact Tim.

In the meantime I have added a CAST to my usage of MAX and MIN.  
Only about 60-70 instances, but they included use on INT, FLOAT and
VARCHAR(2) columns so I did have to eyeball them.

The only variant I have not tested are two instances of MAX(COALESC())
in my source .. but I guess they too will now fail.

OK, I will fire off an email....

Hmm, odder and buggier..

I think it only needs a CAST if there is a WHERE clause.
If I replace the WHERE by a GROUP BY and HAVING, the CAST is not
needed.  Yuk.

Malcolm,

<< An example query generated by my old code was:
{code}
   SELECT MAX("Round") AS R FROM "Diveheets" WHERE "Meet"=2 AND
"Event"=10 AND "CumPoints">0
{code}

It used to return the highest "Round" with a score.  Sadly it now returns a NULL no matter the data. >>

This is a bug in 2.26 B1, and a 2.26 B2 is being uploaded today with a fix.

The bug is: MAX on an indexed column with a WHERE clause, single table in the query.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

>
> The bug is: MAX on an indexed column with a WHERE clause, single
> table in the query.
>

Thanks.  I have emailed you with more details.
(Darn it, I should have left my sources unaltered!)

Fixed in B2 indeed.