Having Clause in MySql 3.23.24

Anon

In the process of upgrading mysql from 3.22.32 to 3.23.24 our sql seems to have broken.

SELECT sum(evalgames) as evaluatedgames,newrank.pos,round((avg(standing)),2) avg_standing
FROM newrank
WHERE
newrank.pos = 'FH'
GROUP BY newrank.pid
HAVING !(evaluatedgames between 0 and 1)
ORDER BY standing DESC LIMIT 10;

Works perfectly on the old system and not the new. All the fingers point to the 'HAVING' clause not evaluating the 'SUM()'. Is there a work around for this?

Please don't reply with it works fine on mine, as I know it works on some versions of MySql.

Thanks,

Paymon Yau

[deleted]

That's what you get for not using the proper syntax from the start :-)

Either use this
HAVING (evaluatedgames NOT BETWEEN 0 AND 1)

or this:

HAVING (iid<0) OR (iid>1)

Anon

I don't suppose you actually tried my sql? :-)

There's nothing wrong with it, it was actually a bug with that particular version of mysql that didn't evalutate the sum().

It works with earlier and later versions of the MySql server. I just have to badger our system admin to update the rpm builds now.

[deleted]

Ofcourse I tried it in MySQL, and my version did accept the excamation mark, but you posted:
"Please don't reply with it works fine on mine, as I know it works on some versions of MySql."

so I gave some other options. (which are really a goo idea to start using instead of the MYSQL specific syntax. Sooner or later you will start using a real database and it will not understand any of the MYSQL specific stuff)

Anon

I've used other databases such as Oracle and Sybase, none of my sql in MySQL is portable. I'll imagine I'll have to rewrite it all anyway so I'm not too bothered.

Anyway writing bad SQL keeps me in a job, until the company goes under. :-)

[deleted]

lol

Somehow I think you can think of better ways to spend your time than re-typing SQL queries :-)

katzkek

Ah, Vince, go easy on the kid... he's probably got a boss like mine. (Knows just enough about what you're doing to micromanage...)

[deleted]

🙂

didn't mean to sound angry or anything, just pointing out that it's better to be prepared.

Anon

Unfortunately in the business (internet) I'm in, I'll be surprised if the systems I write will be around in 6-12 months. With lack of resources and time, we have to push things out as quickly as possible. Now if I was working for a big company I would do things differently.

"If a thing is worth doing, it's worth doing well - unless doing it well takes so long that it isn't worth doing any more. Then you just do it 'good enough'."

Programming Perl
Wall and Schwartz

[deleted]

"If a thing is worth doing, it's worth doing well - unless doing it well takes so long that it isn't worth doing any more. Then you just do it 'good enough'."

Now if they only gave a few pointers on how to find out when it's not worth doing anymore. :-)

In my opinion, the fact that something you wrote will not be around after a year does not warrant a 'good enough' approach.

The point being: if you spend some more time doing things 'right' you'll find that doing things 'right' often means you can do things a lot faster and simpler than doing it 'good enough'.

Think of things like database abstraction layers and the SQL-92 (or later) standard.
Tases time to learn/develop, but once you have it the rest of your development benefits bigtime.

or for ex:
Many people use MySQL because it is quick and easy to install. And then they spend many hours trying to make their script so that it doesn't mess up the database because MySQL has no referencial integrety.
PostgreSQL takes longer to setup, but does have referencial integrety, which means that your script does not have to do intergrety checks and can be a lot simpler, which means takes less time to develop.