MySQL Bugs: #104527: SELECT incorrectly shows NULL first time for user-defined variable

Bug #104527	SELECT incorrectly shows NULL first time for user-defined variable
Submitted:	4 Aug 2021 1:42	Modified:	4 Aug 2021 12:53
Reporter:	George McGinn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any (20.04)
Assigned to:		CPU Architecture:	Any (Dell Optiplex 990)
Tags:	arithmetic, MySQL, MySQL Workbench, User-Defined Variables

Description:
When running a SELECT query that creates a user-defined variable and then uses it to calculate another user-defined variable percentage field from the mySQL command prompt (and in Workbench), the first user-defined field populates correctly, but the second user-defined variable comes up NULL the first time. When I re-enter the exact same SELECT query immediately after it, it works fine.

However, when I run this from a program (written in C/C++ or QB64), it comes up NULL all the time.

In the SELECT below, @SLUG is being calculated based on rows already existing plus the user-defined variable, @NBRGM:=COUNT(playerName). @SLUG is NULL for every row entry the first time it runs, but is properly calculated the second time (See SLUG-PCT column below).

In the runs below, the exact same SELECT query is run back-to-back and shows different (second run is correct) results. I have also run the same query in Workbench and get the same results.

I have even tried to do a SET for both @SLUG and @NBRGM before the query and it does not solve the issue.

I have do exhaustive searches in the bug database and the forum, and cannot find this issue. Since the SELECT query works on the second run, I do not believe it is an issue with the SELECT statement itself. 

I listed this as "Serious" because there is no work-around. If I execute this query from a program source, it never works.

The output from the run in mySQL run in Linux Terminal is:

mysql> SELECT playerName, SUM(atbat) 'AB', SUM(runs) 'R', SUM(hits) 'H', SUM(rbi) 'RBI', SUM(doubles) '2B', SUM(triples) '3B', SUM(homeruns) 'HR', SUM(walks) 'BB', SUM(strikeouts) 'K', SUM(hitbypitch) 'HBP', SUM(sacrifices) 'SAC', SUM(putouts) 'PO', SUM(assists) 'AST', SUM(errors) 'E', @NBRGM:=COUNT(playerName) '#GAMES', @SLUG:=FORMAT(SUM((((hits-(doubles+triples+homeruns))+(doubles*2)+(triples*3)+(homeruns*4))/atbat)/@NBRGM),3) 'SLUG-PCT', FORMAT(AVG(hits/atbat),3) 'AVG' FROM batting WHERE teamName='Bluejays' GROUP BY playerName;
Query OK, 0 rows affected (0.00 sec)

+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
| playerName | AB   | R    | H    | RBI  | 2B   | 3B   | HR   | BB   | K    | HBP  | SAC  | PO   | AST  | E    | #GAMES | SLUG-PCT | AVG   |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
| Biggio     |   15 |    6 |    6 |    9 |    3 |    0 |    0 |    3 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.400 |
| Bichette   |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.000 |
| Guerrero   |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.000 |
| Gurriel    |    9 |    6 |    3 |    3 |    0 |    0 |    3 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.333 |
| Grichuk    |   12 |    3 |    3 |    3 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.250 |
| Smoak      |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    9 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.000 |
| Hernandez  |   12 |    0 |    6 |    0 |    3 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.500 |
| Jansen     |    9 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.000 |
| Drudy      |    3 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | 0.000 |
| Tellez     |    4 |    0 |    2 |    2 |    0 |    0 |    0 |    0 |    0 |    0 |    2 |    0 |    0 |    0 |      2 | NULL     | 0.500 |
| McKinney   |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | NULL  |
| Maile      |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | NULL  |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
12 rows in set, 14 warnings (0.00 sec)

mysql> SELECT playerName, SUM(atbat) 'AB', SUM(runs) 'R', SUM(hits) 'H', SUM(rbi) 'RBI', SUM(doubles) '2B', SUM(triples) '3B', SUM(homeruns) 'HR', SUM(walks) 'BB', SUM(strikeouts) 'K', SUM(hitbypitch) 'HBP', SUM(sacrifices) 'SAC', SUM(putouts) 'PO', SUM(assists) 'AST', SUM(errors) 'E', @NBRGM:=COUNT(playerName) '#GAMES', @SLUG:=FORMAT(SUM((((hits-(doubles+triples+homeruns))+(doubles*2)+(triples*3)+(homeruns*4))/atbat)/@NBRGM),3) 'SLUG-PCT', FORMAT(AVG(hits/atbat),3) 'AVG' FROM batting WHERE teamName='Bluejays' GROUP BY playerName;
Query OK, 0 rows affected (0.00 sec)

+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
| playerName | AB   | R    | H    | RBI  | 2B   | 3B   | HR   | BB   | K    | HBP  | SAC  | PO   | AST  | E    | #GAMES | SLUG-PCT | AVG   |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
| Biggio     |   15 |    6 |    6 |    9 |    3 |    0 |    0 |    3 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | 0.600    | 0.400 |
| Bichette   |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | 0.000    | 0.000 |
| Guerrero   |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |      3 | 0.000    | 0.000 |
| Gurriel    |    9 |    6 |    3 |    3 |    0 |    0 |    3 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | 1.333    | 0.333 |
| Grichuk    |   12 |    3 |    3 |    3 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | 1.000    | 0.250 |
| Smoak      |   12 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    9 |    0 |    0 |    0 |    0 |    0 |      3 | 0.000    | 0.000 |
| Hernandez  |   12 |    0 |    6 |    0 |    3 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |      3 | 0.750    | 0.500 |
| Jansen     |    9 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    6 |    0 |    0 |    0 |    0 |    0 |      3 | 0.000    | 0.000 |
| Drudy      |    3 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | 0.000    | 0.000 |
| Tellez     |    4 |    0 |    2 |    2 |    0 |    0 |    0 |    0 |    0 |    0 |    2 |    0 |    0 |    0 |      2 | 0.333    | 0.500 |
| McKinney   |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | NULL  |
| Maile      |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |      3 | NULL     | NULL  |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+----------+-------+
12 rows in set, 14 warnings (0.00 sec)

mysql>

How to repeat:
Here is the statements to create the table:

CREATE TABLE batting (
	teamName VARCHAR(50) NOT NULL,
	gameID VARCHAR(10) NOT NULL,
	gameNumber INT NOT NULL,
	playerName VARCHAR(100) NOT NULL,
	atbat INT NOT NULL,
	runs INT NOT NULL,
	hits INT NOT NULL,
	rbi INT NOT NULL,
	doubles INT NOT NULL,
	triples INT NOT NULL,
	homeruns INT NOT NULL,
	walks INT NOT NULL,
	strikeouts INT NOT NULL,
	sacrifices INT NOT NULL,
	hitbypitch INT NOT NULL,
	putouts INT NOT NULL,
	assists INT NOT NULL,
	errors INT NOT NULL
	);

INSERT INTO batting 
VALUES ("BlueJays", "AE01", 1, "Biggio", 5, 2, 2, 3, 1, 0, 0, 1, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Bichette", 4, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Guerrero", 4, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Gurriel", 3, 2, 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Grichuk", 4, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Smoak", 4, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Hernandez", 4, 0, 2, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Jansen", 3, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Drudy", 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Tellez", 2, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "McKinney", 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE01", 1, "Maile", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Biggio", 5, 2, 2, 3, 1, 0, 0, 1, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Bichette", 4, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Guerrero", 4, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Gurriel", 3, 2, 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Grichuk", 4, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Smoak", 4, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Hernandez", 4, 0, 2, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Jansen", 3, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Drudy", 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Tellez", 2, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "McKinney", 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE02", 2, "Maile", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Biggio", 5, 2, 2, 3, 1, 0, 0, 1, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Bichette", 4, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Guerrero", 4, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Gurriel", 3, 2, 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Grichuk", 4, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Smoak", 4, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Hernandez", 4, 0, 2, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Jansen", 3, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Drudy", 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "McKinney", 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
("BlueJays", "AE03", 3, "Maile", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

This is the SELECT query (readable):

SELECT playerName, SUM(atbat) 'AB', 
				   SUM(runs) 'R', 
				   SUM(hits) 'H', 
				   SUM(rbi) 'RBI', 
				   SUM(doubles) '2B',
				   SUM(triples) '3B',
				   SUM(homeruns) 'HR',
				   SUM(walks) 'BB',
				   SUM(strikeouts) 'K',
				   SUM(hitbypitch) 'HBP',
				   SUM(sacrifices) 'SAC',
				   SUM(putouts) 'PO',
				   SUM(assists) 'AST',
				   SUM(errors) 'E',
				   @NBRGM:=COUNT(playerName),
				   @SLUG:=FORMAT(SUM((((hits-(doubles+triples+homeruns))+(doubles*2)+(triples*3)+(homeruns*4))/atbat)/@NBRGM),3) 'SLUG-PCT',
				   FORMAT(AVG(hits/atbat),3) 'AVG'
FROM batters 
WHERE teamName='Bluejays'
GROUP BY playerName;

Suggested fix:
One thing that I notice is that when running it from a program that pipes/shells out the command, it never works. However, when I log in from the Linux terminal, or from either Workbench or DBeaver, it will fail the first time I run the query, and work the second time in every case.

This I believe has something to do with the fact that running it from a source code, the mySQL session is terminated after the query is executed, whereas from the terminal or Workbench or DBeaver, the local instance stays logged into, hence why it works on the second and subsequent tries.

The fix may be a simple as a system variable (or some other setting) not being set the first time the query runs.

Hi Mr. McGinn,

Thank you very much for your bug report.

However, if you read our Reference Manual, you will find out that in several places, like for example sub-chapter 9.4, it says loud and clear:

"
The order of evaluation for expressions involving user variables is undefined. 
"

Hence, for example, @SLUG variable can be evaluated prior to the @NBRGM variable and hence, you will not get the result you want.  Hence, if @SLUG is evaluated first and the other one second, then you will not get the result that you desired in the first run. In the second run, if data is not changed, you will get a correct result, since @NBRGM is already calculated.

That is how our Optimiser is designed to work and that does not contradict any SQL standard. Hence, this is how MySQL server works, since the introduction of user variables, back in 1999.

This report would have been a bug if one user variable would not have been evaluated from the second one in the SELECT list.

We could make it a feature request, but it would take many years for the change in behaviour to be acceptable.

Hence, this is not a bug !!!!