Bug #112172 Left join query null value assertion error
Submitted: 24 Aug 2023 6:21 Modified: 25 Aug 2023 1:49
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.1.0, 8.0.34 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: left join, nullable, primairy key

[24 Aug 2023 6:21] Pedro Ferreira 
Description:
Run the following queries:

CREATE TABLE t0 (c0 INT PRIMARY KEY);
CREATE TABLE t1 (c0 INT PRIMARY KEY);
SELECT 1 FROM (SELECT CAST(t0.c0 AS UNSIGNED) FROM t0 WHERE (SELECT 1 FROM t1) = t0.c0) x(x) LEFT JOIN t1 ON t1.c0 = x.x WHERE 1 = x.x;

The SELECT query will trigger an assertion error at sql/item_func.cc:1933
assert(!null_value || is_nullable());

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.
[24 Aug 2023 6:37] MySQL Verification Team 
Hello Pedro Ferreira,

Thank you for the report and feedback.
Observed that 8.0.34 debug build is affected.

regards,
Umesh
[25 Aug 2023 1:49] Pedro Ferreira 
Typo in the title