Use of UNION invalidates SELECT results

Dev Shed Forums Sponsor:

September 13th, 2012, 05:57 PM

JugglingReferee

Contributing User

Join Date: Feb 2001

Posts: 52

Time spent in forums: 20 h 23 m 12 sec
Reputation Power: 13

Use of UNION invalidates SELECT results

Related to my previous thread: http://forums.devshed.com/mysql-help-4/having-or-group-by-or-something-else-929995.html

So I did use the UNION keyword to merge together 3 SELECT statements.

(SELECT)

UNION

(SELECT)

UNION

(SELECT)

Initially, the correct and expected output was generated.

However, just the other day, the ranked-with-ties results from the first SELECT statement is incorrect.

I'm 99.99% sure it's not a bug in the SELECT statement. Which leads me to believe that it's a bug in the engine that is returning the results.

The entire command is:

Code:

(SELECT
    IF(@LastScore9 <> Score, @CurrentRank9 := @CurrentRank9 + @NextRank9, @CurrentRank9) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
  IF(@LastScore9 = Score, @NextRank9 := @NextRank9 + 1, @NextRank9 := 1) as AddToGetNextRank9,
    @LastScore9 := Score AS Score
FROM Results
JOIN (SELECT @CurrentRank9 := 1, @LastScore9 := 0, @NextRank9 := 0) r
WHERE Date='2012-09-11 19:00:00' AND Games=9
ORDER BY Score DESC)

UNION

(SELECT
    IF(@LastScore8 <> Score, @CurrentRank8 := @CurrentRank8 + @NextRank8, @CurrentRank8) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
    IF(@LastScore8 = Score, @NextRank8 := @NextRank8 + 1, @NextRank8 := 1) as AddToGetNextRank8,
    @LastScore8 := Score AS Score
FROM Results
JOIN (SELECT @CurrentRank8 := 1, @LastScore8 := 0, @NextRank8 := 0) r
WHERE Date='2012-09-11 19:00:00' AND Games=8
ORDER BY Score DESC)

UNION

(SELECT
    IF(@LastScore7 <> Score, @CurrentRank7 := @CurrentRank7 + @NextRank7, @CurrentRank7) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
    IF(@LastScore7 = Score, @NextRank7 := @NextRank7 + 1, @NextRank7 := 1) as AddToGetNextRank7,
    @LastScore7 := Score AS Score
FROM Results
JOIN (SELECT @CurrentRank7 := 1, @LastScore7 := 0, @NextRank7 := 0) r
WHERE Date='2012-09-11 19:00:00' AND Games=7
ORDER BY Score DESC)

The output is here: http://www.halscrib.com/index.php/web-club/results?Tournament=2012-09-11+19%3A00%3A00

You can see that for the case of the people playing 9 games, there is SHEPHERD and FRANWARD. FRANWARD has 6 points, so she should be ranked higher than SHEPHERD. This is evident by her Score being higher.

But it ranks SHEPHERD ahead of FRANWARD.

If I run the first SELECT statement in phpMyAdmin by itself, it returns the correct ranking information.

Any ideas?