whats wrong with this sql query?

[Red Squirrel]

I fully tested an app on dev and it works fine, I upload it on live, go to compile, run, and it craps out. Using mysql++ which is very sensible to crashes. Even the sillyest sql error will cause a crash.

For some reason this whole query fails, I really can't see what's wrong with it:

"SELECT stresponse,max(stonline) as maxonline, max(stavgresponse) as maxresponse FROM stats WHERE stshardid='" + shardidstr + "' ORDER BY stdate DESC LIMIT " + BitStream::Int2Str(MAXROWS) + ";")

error:
[Apr-09-2009 10:53:08pm] Graph update get max data query error
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause


It's saying I need to use group, but wtf? since when? It works on dev why not on live...

I am running a slightly different version of SQL on live, but it wont let me update.

On live I have 5.0.45 but on dev I have 5.0.77. It wont let me update it on live for some reason, it says no package found when I go yum update mysql-server.

 

[PhaZe]

you have a regular column stresponse which may or may not return more than 1 value

the max (whatever) you have there will return 1 value

group by stresponse.

On your dev version, does it repeat the values for stresponse or what?

 

[MrChad]

I have no idea why that would work on your dev system. When columns are selected with aggregate functions, a GROUP BY clause has to be used. In this case, it looks like you want "GROUP BY stresponse"

SELECT stresponse, max(stonline) as maxonline, max(stavgresponse) as maxresponse FROM stats WHERE stshardid=? GROUP BY stresponse

 

[Red Squirrel]

I removed stresponse and it fixed the issue. Not sure why it worked on one but not the other. Not even sure how that ended up there in first place. was not using it.


I also noticed lot of other stuff that is different from system to system.

For example I can grab stuff and typecast it to unsigned int on dev, but same code on prod wont compile. Ended up pulling the data as string then converting from there. Kinda sucks they can't keep things consistent.

 

[MrChad]

It may have to do with differences in the data between your two systems. For instance, if I have a VARCHAR column COL_A in dev:

COL_A
--------
'1'
'2'
'3'

I can cast COL_A to an int and probably get results back. The same cast in production might not work:

COL_A
--------
'1'
'2'
'b'
'3'

The third row here will cause the casting operation to fail. You have to be careful with type casts and conversions in SQL code for these reasons. Unless you are certain of the data you are selecting, your query could fail unexpectedly.