Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Has MAX() changed in 2.26? |
Sun, Jul 2 2017 9:26 AM | Permanent Link |
Malcolm Taylor | 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? |
Sun, Jul 2 2017 11:08 AM | Permanent Link |
Malcolm Taylor | 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. |
Mon, Jul 3 2017 4:40 AM | Permanent Link |
Matthew Jones | If these are not listed in the changes, then email Tim direct to report it as an issue.
-- Matthew Jones |
Mon, Jul 3 2017 9:34 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Mon, Jul 3 2017 10:06 AM | Permanent Link |
Malcolm Taylor | 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.... |
Mon, Jul 3 2017 10:35 AM | Permanent Link |
Malcolm Taylor | 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. |
Mon, Jul 3 2017 11:13 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jul 3 2017 11:25 AM | Permanent Link |
Malcolm Taylor | 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!) |
Tue, Jul 4 2017 12:35 PM | Permanent Link |
Malcolm Taylor | Fixed in B2 indeed.
|
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |