Bug #121468 Row IN returns NULL instead of FALSE when a candidate contains a CTE scalar subquery

• Submitted: 9 Oct 12:17
• Modified: 13 Oct 6:15
• Reporter: jinxin gui
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.11, 9.7.2, 26.07
• OS: Linux
• CPU Architecture: Any
• Tags: cte, IN, row

[9 Oct 12:17] jinxin gui

Description:
A row IN expression returns NULL when one candidate row contains a scalar subquery reading from a CTE. Replacing the scalar subquery with its value, 3, makes the same expression return 0.
Both candidate rows differ from ROW(10, 2) in their first element, so neither can match. The NULL in the second element of the first candidate should not affect the result.

How to repeat:
CREATE TABLE t (pk INT PRIMARY KEY);
INSERT INTO t VALUES (1), (2), (3);

WITH a AS (SELECT COUNT(*) FROM t) SELECT ROW(10, 2) IN (ROW((SELECT * FROM a), NULL), ROW(1, 2)) AS result;

SELECT ROW(10, 2) IN (ROW(3, NULL), ROW(1, 2)) AS result;

Actual result:
The first query returns NULL. The second query returns 0.
Expected result:
Both queries should return 0. The scalar subquery returns 3, and the first element of each candidate row differs from 10.

[13 Oct 6:15] Chaithra Marsur Gopala Reddy

Hi jinxin gui,

Thank you for the test case. Verified as described.