| Bug #112231 | Inconsistent result for OVER() when used in stored procedure over InnoDB table | ||
|---|---|---|---|
| Submitted: | 31 Aug 2023 17:14 | Modified: | 5 Dec 2023 14:12 |
| Reporter: | Marcos Albe (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 8.0.33, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | innodb, regression | ||
[31 Aug 2023 18:11]
Marcos Albe
Forgot to mention: The issue doesn't happen in 8.0.32 (even with InnoDB) The issue doesn't happen if we run the same query outside the stored procedure (even in 8.0.33 with InnoDB)
[31 Aug 2023 20:38]
Leonardo Fernandes
Here is a bit more information about this bug:
It is repeatable on 8.0.34 and not exclusive to temporary tables. Any InnoDB Table will cause this issue.
It seems to be related to a table reference not being cleaned up after the procedure finishes, as executing FLUSH TABLES (or disconnecting and reconnection) resets the result back to 1.
mysql [localhost:51072] {msandbox} (test) > CALL spTestBad();
+---+-----------------------+
| 1 | (SUM( 1 ) OVER()) + 0 |
+---+-----------------------+
| 1 | 1 |
+---+-----------------------+
1 row in set (0.00 sec)
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:51072] {msandbox} (test) > CALL spTestBad();
+---+-----------------------+
| 1 | (SUM( 1 ) OVER()) + 0 |
+---+-----------------------+
| 1 | 2 |
+---+-----------------------+
1 row in set (0.00 sec)
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:51072] {msandbox} (test) > flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:51072] {msandbox} (test) > CALL spTestBad();
+---+-----------------------+
| 1 | (SUM( 1 ) OVER()) + 0 |
+---+-----------------------+
| 1 | 1 |
+---+-----------------------+
1 row in set (0.01 sec)
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:51072] {msandbox} (test) > CALL spTestBad();
+---+-----------------------+
| 1 | (SUM( 1 ) OVER()) + 0 |
+---+-----------------------+
| 1 | 2 |
+---+-----------------------+
1 row in set (0.00 sec)
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[1 Sep 2023 6:00]
MySQL Verification Team
Hello Marcos, Thank you for the report and feedback. Verified as described. Thanks, Umesh
[5 Dec 2023 8:59]
Aaditya Dubey
Hi Team,
Please find the culprit commit which is probably responsible for this issue:
commit 248c7f7c227e76f3422378ca3b9e10d141dcfc86 (HEAD)
Date: Wed Feb 8 01:37:23 2023 +0100
Bug#35060385 Item::update_used_tables should also update the PROP_SUBQUERY - for transforms
Missing update of flag PROP_SUBQUERY when we replace subqueries with
fields: update_used_tables didn't do it (right). Cf. the call in
Query_block::replace_subquery_in_expr:
new_item->update_used_tables();
didn't get the correct result earlier.
The solution is to properly update all other accumulated properties
that may be changed due to item substitution.
Change-Id: I8df46a7325303885829dc10e73902cacb71d671b
[5 Dec 2023 14:12]
Roy Lyseng
Fixed in the upcoming 8.0.36 release under the heading of Bug#112231.
Description: Simple window function use within stored procedure will return inconsistent results after first execution, only when table is InnoDB; In the snippet shown below, if we make tbTestBad2 temporary table use ENGINE=MyISAM then the problem goes away How to repeat: DROP TEMPORARY TABLE IF EXISTS tbTestBad2; CREATE TEMPORARY TABLE tbTestBad2 (id int unsigned) ENGINE InnoDB; INSERT INTO tbTestBad2 SELECT 1 AS id; DROP PROCEDURE IF EXISTS spTestBad; DELIMITER $$ CREATE PROCEDURE IF NOT EXISTS spTestBad() BEGIN SELECT 1, (SUM( 1 ) OVER()) + 0 FROM tbTestBad2 ; SELECT * FROM tbTestBad2; END$$ DELIMITER ; CALL spTestBad(); CALL spTestBad();