MySQL Bugs: #110847: Prepared SELECT returns different results on consecutive executions.

Bug #110847	Prepared SELECT returns different results on consecutive executions.
Submitted:	27 Apr 2023 15:01	Modified:	15 May 2023 22:06
Reporter:	Steve H	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Executing the same prepared statement twice gives two different results.

The statements below output this: 
+--------+
| result |
+--------+
|      1 |
|      1 |
+--------+
+--------+
| result |
+--------+
|   NULL |
|   NULL |
+--------+
+-----------+
| VERSION() |
+-----------+
| 8.0.33    |
+-----------+

Expected outcome is that the second execution matches the first. 

This is changed behaviour since 8.0.33. It does not happen in 8.0.32.

Testing using docker tags mysql:8.0.32 and mysql:8.0.33

How to repeat:
CREATE DATABASE IF NOT EXISTS window_lag_bug;
USE window_lag_bug; 
DROP TABLE IF EXISTS `data_table`;
CREATE TABLE IF NOT EXISTS `data_table` (`uid` INT) ENGINE=InnoDB;
INSERT INTO `data_table` (`uid`) VALUES (100), (101);

PREPARE stmt1 FROM "SELECT (LAST_VALUE(a.`uid`) OVER `outer_window`) = a.`uid` as `result`
FROM 
	(	
		SELECT 
		LAG(`uid`) OVER `inner_window` AS `uid_lag`,
		`uid` AS `uid`
		FROM data_table
		WINDOW `inner_window` AS (ORDER BY `uid`)
	) AS a 
	WINDOW `outer_window` AS (ORDER BY a.`uid`)
";
EXECUTE stmt1;
EXECUTE stmt1;
SELECT VERSION();
DROP DATABASE window_lag_bug;

Hello Steve H,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

- 8.0.33

mysql> DROP DATABASE window_lag_bug;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE IF NOT EXISTS window_lag_bug;
Query OK, 1 row affected (0.01 sec)

mysql> USE window_lag_bug;
Database changed
mysql> DROP TABLE IF EXISTS `data_table`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `data_table` (`uid` INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `data_table` (`uid`) VALUES (100), (101);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> PREPARE stmt1 FROM "SELECT (LAST_VALUE(a.`uid`) OVER `outer_window`) = a.`uid` as `result`
    "> FROM
    "> (
    ">
Display all 758 possibilities? (y or n)
    "> SELECT
    ">
Display all 758 possibilities? (y or n)
    "> LAG(`uid`) OVER `inner_window` AS `uid_lag`,
    ">
Display all 758 possibilities? (y or n)
    "> `uid` AS `uid`
    ">
Display all 758 possibilities? (y or n)
    "> FROM data_table
    ">
Display all 758 possibilities? (y or n)
    "> WINDOW `inner_window` AS (ORDER BY `uid`)
    "> ) AS a
    "> WINDOW `outer_window` AS (ORDER BY a.`uid`)
    "> ";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1;
+--------+
| result |
+--------+
|      1 |
|      1 |
+--------+
2 rows in set (0.00 sec)

mysql> EXECUTE stmt1;
+--------+
| result |
+--------+
|   NULL |
|   NULL |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

-- 8.0.32 looks like regression in latest GA 

mysql> CREATE DATABASE IF NOT EXISTS window_lag_bug;
Query OK, 1 row affected (0.01 sec)

mysql> USE window_lag_bug;
Database changed
mysql> DROP TABLE IF EXISTS `data_table`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `data_table` (`uid` INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `data_table` (`uid`) VALUES (100), (101);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> PREPARE stmt1 FROM "SELECT (LAST_VALUE(a.`uid`) OVER `outer_window`) = a.`uid` as `result` FROM  (SELECT LAG(`uid`) OVER `inner_window` AS `uid_lag`, `uid` AS `uid` FROM data_table WINDOW `inner_window` AS (ORDER BY `uid`)) AS a WINDOW `outer_window` AS (ORDER BY a.`uid`)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1;
+--------+
| result |
+--------+
|      1 |
|      1 |
+--------+
2 rows in set (0.00 sec)

mysql> EXECUTE stmt1;
+--------+
| result |
+--------+
|      1 |
|      1 |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

Posted by developer:
 
Bisect says:

commit ca5275ff6e778d62d04bd2b3c5956775f7eead28
Author: Dag Wanvik <dag.wanvik@oracle.com>
Date:   Wed Feb 8 01:37:23 2023 +0100

    Bug#35060385 Item::update_used_tables should also update the PROP_SUBQUERY - for transforms

Documented fix as follows in the MySQL 8.1.0 changelog:

    A SELECT within a prepared statement unexpectedly returned
    different results on successive executions.

Closed.

Also fixed in MySQL 8.0.36.