Session variables in MySQL query - unreliable?

Here's a thing...the first time I run this query, the WHERE clause  "WHERE team_rank <=6" is ignored, it shows all the data.  Running second and subsequent times, it limits to 6.

Now if I leave it and come back to it, once again it shows all the data.

Are session variables known to be unreliable in MySQL?  I have tried in both MySQL Workbench and SQL Maestro for MySQL.

DROP TEMPORARY TABLE IF EXISTS scores;
/* You can't do the union directly for the create table, 
   but you can make it a sub-select: */
CREATE TEMPORARY TABLE scores AS
SELECT * FROM
(
SELECT home_team AS Team, home_score AS f_score, away_score AS a_score, match_date,
       CASE 
            WHEN home_score > away_score THEN 'W'  
            WHEN home_score < away_score THEN 'L' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
        UNION
    SELECT away_team AS Team, away_score AS f_score, home_score AS a_score, match_date,
       CASE 
            WHEN home_score > away_score THEN 'L'  
            WHEN home_score < away_score THEN 'W' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
) AS the_data;

SELECT Team, sum(1) as 'P', sum(CASE WHEN wdl = 'W' THEN 1 ELSE 0 END) AS 'W',
	    sum(CASE WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'D', sum(CASE WHEN wdl = 'L' THEN 1 ELSE 0 END) AS 'L',
        sum(f_score) AS 'F', sum(a_score) as 'A', sum(f_score - a_score) AS 'GD',
        sum(CASE WHEN wdl = 'W' THEN 3 WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'Pts'
FROM
(
    SELECT Team, f_score, a_score, match_date, wdl,
    @team_rank := IF (@current_team = Team,
                      @team_rank + 1, 1)
                  AS 'team_rank',
    @current_team := Team AS 'Current_team'
    FROM scores
    ORDER BY Team, match_date DESC
) AS the_outer_data
WHERE team_rank <= 6
GROUP BY Team
ORDER BY Pts DESC, GD DESC, F DESC, Team ASC;

Open in new window

LVL 3
colinspursAsked:
Who is Participating?
 
SharathData EngineerCommented:
Assign default values to the variables.
DROP TEMPORARY TABLE IF EXISTS scores;
/* You can't do the union directly for the create table, 
   but you can make it a sub-select: */
CREATE TEMPORARY TABLE scores AS
SELECT * FROM
(
SELECT home_team AS Team, home_score AS f_score, away_score AS a_score, match_date,
       CASE 
            WHEN home_score > away_score THEN 'W'  
            WHEN home_score < away_score THEN 'L' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
        UNION
    SELECT away_team AS Team, away_score AS f_score, home_score AS a_score, match_date,
       CASE 
            WHEN home_score > away_score THEN 'L'  
            WHEN home_score < away_score THEN 'W' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
) AS the_data;

SELECT Team, sum(1) as 'P', sum(CASE WHEN wdl = 'W' THEN 1 ELSE 0 END) AS 'W',
	    sum(CASE WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'D', sum(CASE WHEN wdl = 'L' THEN 1 ELSE 0 END) AS 'L',
        sum(f_score) AS 'F', sum(a_score) as 'A', sum(f_score - a_score) AS 'GD',
        sum(CASE WHEN wdl = 'W' THEN 3 WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'Pts'
FROM
(
    SELECT Team, f_score, a_score, match_date, wdl,
    @team_rank := IF (@current_team = Team,
                      @team_rank + 1, 1)
                  AS 'team_rank',
    @current_team := Team AS 'Current_team'
    FROM scores, (SELECT @team_rank := 0, @current_team := 'dummy') AS t1
    ORDER BY Team, match_date DESC
) AS the_outer_data
WHERE team_rank <= 6
GROUP BY Team
ORDER BY Pts DESC, GD DESC, F DESC, Team ASC;

Open in new window

1
 
colinspursAuthor Commented:
Perfect - thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.