Bug #110076 VALUES Statement with dependent subquery is wrong

• Submitted: 15 Feb 2023 10:58
• Modified: 4 Jan 2024 0:04
• Reporter: Feng Liyuan
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[15 Feb 2023 10:58] Feng Liyuan

Description:
I try to use the values statement to find the max value of a row, but it seems not to work on MySQL 8.

How to repeat:
https://dbfiddle.uk/cge2e2P-

CREATE TABLE mytable(
   ID INTEGER  NOT NULL
  ,a  VARCHAR(4) NOT NULL
  ,b  VARCHAR(4) NOT NULL
  ,c  VARCHAR(3) NOT NULL
);
INSERT INTO mytable(ID,a,b,c) VALUES (1,'a1','b1','c1');
INSERT INTO mytable(ID,a,b,c) VALUES (2,'a2','b2','c2');

mysql> select ID, (select max(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+------+
| ID | max  |
+----+------+
|  1 | c1   |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

The 'max' column of second row should be 'c2'

I test it in postgres it works fine: https://dbfiddle.uk/nbpDA-Tc

[15 Feb 2023 11:19] MySQL Verification Team

Hello Feng Liyuan,

Thank you for the report and feedback.

regards,
Umesh

[4 Jan 2024 0:04] Jon Stephens

Fixed in MySQL 8.3.0.

See BUG#109252 for info.

Closed.

[27 Feb 2024 17:17] Jon Stephens

Also fixed in MySQL 8.0.37 and 8.4.0.

Changelog entry as above.