MySQL Bugs: #109115: prepared VALUES ROW in subquery with only string params returns empty

Bug #109115	prepared VALUES ROW in subquery with only string params returns empty
Submitted:	16 Nov 2022 20:53	Modified:	23 Nov 2022 12:53
Reporter:	Sean Fraser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
When using prepared statements with a subquery containing the VALUES ROW table statement with placeholders returns results with empty values if every bound parameter is a string.

In our use case, we are using the table statement with a UNION ALL to the results of another subquery, though I can confirm this behaviour is consistent with any subquery usage as long as every bound parameter is a string.

How to repeat:
/* Scenarios That do NOT behave as expected */

-- FAILS, using VALUES ROWS in a subquery with alphabetic string values
-- returns rows with empty values
PREPARE stmt1 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = "b";
SET @b = "a";
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;

-- FAILS, using VALUES ROWS in a subquery with numeric string values
-- returns rows with empty values
PREPARE stmt2 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = "1";
SET @b = "2";
EXECUTE stmt2 USING @a, @b;
DEALLOCATE PREPARE stmt2;

/* Scenarios that do behave as Expected */

-- PASSES, using VALUES ROWS not in a subquery with alphabetic string values
-- returns rows with a and b respectively
PREPARE stmt3 FROM 'VALUES ROW(?), ROW(?)';
SET @a = "a";
SET @b = "b";
EXECUTE stmt3 USING @a, @b;
DEALLOCATE PREPARE stmt3;

-- PASSES, using VALUES ROWS not in a subquery with numeric string values
--returns rows with 1 and 2 respectively
PREPARE stmt4 FROM 'VALUES ROW(?), ROW(?)';
SET @a = "1";
SET @b = "2";
EXECUTE stmt4 USING @a, @b;
DEALLOCATE PREPARE stmt4;

-- PASSES, using VALUES ROWS in a subquery with mix of integer and numeric string values (int first)
-- returns rows with 1 and 2 respectively
PREPARE stmt5 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = 1;
SET @b = "2";
EXECUTE stmt5 USING @a, @b;
DEALLOCATE PREPARE stmt5;

-- PASSES, using VALUES ROWS in a subquery with integer values
-- returns rows with 1 and 2 respectively
PREPARE stmt6 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = 1;
SET @b = 2;
EXECUTE stmt6 USING @a, @b;
DEALLOCATE PREPARE stmt6;

-- PASSES, using VALUES ROWS in a subquery with numeric string values (string first)
-- returns rows with 1 and 2 respectively
PREPARE stmt7 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = "1";
SET @b = 2;
EXECUTE stmt7 USING @a, @b;
DEALLOCATE PREPARE stmt7;

-- PASSES, using VALUES ROWS in a subquery with mix of integer and alphabetic string values
-- returns rows with a and 2 respectively
PREPARE stmt8 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?)) AS sub';
SET @a = "a";
SET @b = 2;
EXECUTE stmt8 USING @a, @b;
DEALLOCATE PREPARE stmt8;

Hi Mr. Fraser,

Thank you for your bug report.

We can't repeat it with our latest release, 8.0.31:

ysql> PREPARE stmt1 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?))
    '> AS sub';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = "b";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = "a";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b;
+------+
| id   |
+------+
| b    |
| a    |
+------+
2 rows in set (0.01 sec)

mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM 'SELECT column_0 as id FROM (VALUES ROW(?), ROW(?))
    '> AS sub';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = "1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = "2";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt2 USING @a, @b;
+------+
| id   |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt2;
Query OK, 0 rows affected (0.00 sec)

Can't repeat, as it is already fixed.

I just tested on a docker container with the latest version and can confirm that it appears to be fixed in that version. Thank you, This can be closed.

Mr Fraser,

You are truly welcome.