Topic awaiting preservation: MySQL query ORDER BY quirks (Page 1 of 1)

Cross post from the GN

Although my problem seems quirky to me I'm sure there's a reason for the behavior, I just haven't been able to find it. Anyway here goes.

I'm trying to do a SELECT from a table that does some simple mathematical equations on 5 of the columns and then assigns the value of those equations to an alias "ranking". I'll show you the query and then finish explaining the problem I'm having.


code:
--------------------------------------------------------------------------------

SELECT link_id, link_url, link_title, link_description, link_count, (((link_vote_1*1)+(link_vote_2*2)+(link_vote_3*3)+(link_vote_4*4)+(link_vote_5*5))/(link_vote_1 + link_vote_2 + link_vote_3 + link_vote_4 + link_vote_5)) as ranking from link_main WHERE link_approved = 'yes' and external = 'yes' ORDER BY ranking DESC
--------------------------------------------------------------------------------

When I run this query but use any of the other columns (i.e., link_count) in the ORDER BY clause the query returns all the rows I expect it to, and has the proper results listed from the mathematical equation in the ranking column.

But, when I run the query as it's written with ranking in the ORDER BY clause it only returns a portion of the rows I expect it to (and less than it did using another column to ORDER BY) and there are no values in any of the rows in the ranking field.

To add to my confusion on this, I am already using a query in another place in the code that uses "ranking" in the ORDER BY clause that does return the expected results. I'll post that query also in case there's something in it that my give someone a hint to the problem.

code:
--------------------------------------------------------------------------------

SELECT link_main.*, link_node_link.link_id, (((link_vote_1*1)+(link_vote_2*2)+(link_vote_3*3)+(link_vote_4*4)+(link_vote_5*5))/(link_vote_1 + link_vote_2 + link_vote_3 + link_vote_4 + link_vote_5)) AS ranking, link_count, link_approved FROM link_main, link_node_link WHERE link_node_link.node_id = $node AND link_node_link.link_id = link_main.link_id AND link_approved = 'yes' AND external = 'yes' ORDER BY ranking DESC
--------------------------------------------------------------------------------

If I have failed to help you understand my problem, just say so and I'll try to clarify it.

Thanks for taking the time!


-Butcher-

You can see the thread at the GN here:
www.gurusnetwork.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic;f=20;t=000061

All help is gratefully received

___________________
Emps

FAQs: Emperor

Any chance of posting the schema for the two tables in question?

Judging by the equation, I guess you're calculating an average score for each link (total score / number of votes). If any of the rows hasn't received a vote yet then link_vote_1 + link_vote_2 + ... + link_vote_5 = 0, and you'll be dividing by zero. I don't know how MySQL qould cope with that, and it's possible that the second sql has requirements that exclude links with no votes...?

stinx: That doesn't seem to be the problem as hardly any of the links have votes but the only search which has real problems is the one where the ORDER BY is ranking - there are 2 other searchs which order by different things and another search (the second SQL) which has 3 variations in the ORDER BY which work just fine.

Thanks for that though - we'll have a look at that. Now you mention it I'm not sure why it doesn't screw up (but it doesn't).

___________________
Emps

FAQs: Emperor

I just tried to recreate the tables from what I read in the sql, but I get the feeling it's a different layout. Both sql statements work fine (dividing by zero results in a null value).

I'm assuming the link_vote_# fields are in link_main, but what is the significance of link_node_link ?

Again, table definitions would be a help

stinx: OK your wish is my command - I'll go and grab them.

The link_node_link is a 2 column table which associates the links with any number of nodes.

___________________
Emps

FAQs: Emperor

OK I presume butcher won't mind me posting this if it gets the problem fixed quickly :

the main link table is:

CREATE TABLE link_main (

   link_id mediumint(9) NOT NULL auto_increment,

   link_url varchar(60) NOT NULL,

   link_title varchar(50) NOT NULL,

   link_description mediumtext NOT NULL,

   external varchar(5) NOT NULL,

   link_vote_1 mediumint(8) DEFAULT '0' NOT NULL,

   link_vote_2 mediumint(8) DEFAULT '0' NOT NULL,

   link_vote_3 mediumint(8) DEFAULT '0' NOT NULL,

   link_vote_4 mediumint(8) DEFAULT '0' NOT NULL,

   link_vote_5 mediumint(8) DEFAULT '0' NOT NULL,

   link_date datetime,

   link_count mediumint(8) DEFAULT '0' NOT NULL,

   link_adder mediumint(8) DEFAULT '0' NOT NULL,

   link_approved enum('yes','no') DEFAULT 'no' NOT NULL,

   PRIMARY KEY (link_id),

   UNIQUE link_id (link_id),

   UNIQUE link_url (link_url),

   KEY link_id_2 (link_id)

);

and link_node_link is:

CREATE TABLE link_node_link (

   link_id mediumint(8) DEFAULT '0' NOT NULL,

   node_id mediumint(8) DEFAULT '0' NOT NULL,

   KEY link_id (link_id, node_id)

);

Thanks again for looking into this

___________________
Emps

FAQs: Emperor

Nope... tried it, and it works fine - both of the statements, ordered by ranking or by link_count.

So... it's either some weird data in there, or a problem with MySQL itself...?!

stinx: Thanks for testing things - that has certainly given us something to think about - butcher has it running on his home PC as well as online and they both have the same problem. Time to nose through the data I suppose

Thanks again

___________________
Emps

FAQs: Emperor

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

InI: Cheers. The same problem happens when the query is run from phpMyAdmin (so it wasn't anything in the PHP messing with things) and it is possible to fix the problem by loading the results into a temporary table and then sorting them from there but I got all imperial and insisted that there must be a solution within the SQL once we had spotted where the problem occured (sorry butcher ).

Key violation? Now there is something to think about.

Thanks

___________________
Emps

FAQs: Emperor

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

Cross thank you from the GN

Just in case anyone was following this thread, or for those who may come later with the same problem...

I've found a solution that works and I'm relatively sure that the problem was the way MySQL was dealing with the ranking column that resulted from the math equation being done on the link_vote columns.

I found out after doing a CREATE TABLE SELECT .... that the column type that MySQL was making for ranking was in fact a float which is why I was getting confusing returns.

To fix the problem I've used concat() function on the link_vote equation to force the column type to a varchar. I also added an and clause to make sure that there were actually votes recorded for the row so I didn't get any division by 0 problems or NULL results returned. I was then able to get the results I expected.

Here's the final out come;

select *, concat((((link_vote_1*1)+(link_vote_2*2)+(link_vote_3*3)+(link_vote_4*4)+(link_vote_5*5))/(link_vote_1 + link_vote_2 + link_vote_3 + link_vote_4 + link_vote_5))) as ranking from link_main where (link_vote_1 + link_vote_2 + link_vote_3 + link_vote_4 + link_vote_5) > 0 and link_approved = 'yes' and external = 'yes' order by ranking

Thanks to all who tried to help.

-Butcher-

[This message has been edited by butcher (edited 10-05-2002).]