Oleg Smirnov added a comment - 2022-07-20 11:04 An attempt to construct a dataset to demonstrate a non-legitimate usage of 'not exists'. CREATE TABLE t1 (a INT NOT NULL , b INT NOT NULL ); CREATE TABLE t2 (a INT NOT NULL , b INT NOT NULL ); CREATE TABLE t3 (a INT NOT NULL , b INT NOT NULL );   INSERT INTO t1 VALUES (1,1), (2,1); INSERT INTO t2 VALUES (1,1), (1,2); INSERT INTO t3 VALUES (1,2), (2,1);   SET join_cache_level=0; We expect the 2nd line of this dataset will be skipped if condition t2.a IS NULL will be applied due to the 'not exists' optimization: MariaDB [mdev27624_2]> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a; + ---+---+------+------+ | a | b | a | b | + ---+---+------+------+ | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 2 | | 2 | 1 | NULL | NULL | + ---+---+------+------+ So the t3 row {1,2} will not match the ON condition and will be NULL-complemented: MariaDB [mdev27624_2]> SELECT * FROM t3 LEFT JOIN (t1 LEFT JOIN t2 ON t1.a=t2.a) -> ON t3.a = t2.a AND t3.b=t2.b -> WHERE t2.a IS NULL ; + ---+---+------+------+------+------+ | a | b | a | b | a | b | + ---+---+------+------+------+------+ | 2 | 1 | NULL | NULL | NULL | NULL | + ---+---+------+------+------+------+ But this does not happen, the result is correct.

Sergei Petrunia added a comment - 2022-07-20 18:00 It seems my yesterday statement that Not Exists doesn't shortcut anything for nested outer joins was incorrect. Here's an example: set join_cache_level=0; create table t21(t21_a int); insert into t21 values (1);   create table t22 (t22_a int, t22_b int); insert into t22 values (1,10); insert into t22 values (1,11);   create table t23 (t23_b int not null, t23_c int not null); insert into t23 values (10, 100), (10, 101), (10, 102), (10, 103), (10, 104), (10, 105), (10, 106), (10, 107), (10, 108);     create table t23null(t23_b int, t23_c int); insert into t23null select * from t23;   create or replace temporary table t_initial as select table_schema, table_name, rows_fetched from sys.schema_table_statistics;   select * from t21 left join (t22 left join t23 on t22_b=t23_b) on (t21_a= t22_a) where t23_b is null;   select NEW.table_name, NEW.rows_fetched - ORIG.rows_fetched as DIFF from sys.schema_table_statistics NEW, t_initial ORIG where NEW.table_name=ORIG.table_name and NEW.table_schema=ORIG.table_schema and NEW.table_schema=database() having DIFF>0;   create or replace temporary table t_initial as select table_schema, table_name, rows_fetched from sys.schema_table_statistics; select * from t21 left join (t22 left join t23null on t22_b=t23_b) on (t21_a= t22_a) where t23_b is null; select NEW.table_name, NEW.rows_fetched - ORIG.rows_fetched as DIFF from sys.schema_table_statistics NEW, t_initial ORIG where NEW.table_name=ORIG.table_name and NEW.table_schema=ORIG.table_schema and NEW.table_schema=database() having DIFF>0; This shows +------------+------+ | table_name | DIFF | +------------+------+ | t21 | 2 | | t22 | 3 | | t23 | 10 | +------------+------+ and +------------+------+ | table_name | DIFF | +------------+------+ | t21 | 2 | | t22 | 3 | | t23null | 18 | +------------+------+ which shows shortcutting is done. Perhaps it's different from what I expected it to be.

Igor Babaev (Inactive) added a comment - 2022-10-20 02:05 I cannot approve the fix as it just prohibits using not_exists optimization together with join buffer. I'm assigning this bug to myself for a proper resolution.

Oleksandr Byelkin added a comment - 2023-01-12 16:43 OK to push

Igor Babaev (Inactive) added a comment - 2023-01-17 06:01 The patch for this bug was pushed into 10.3. I should be merged upstream as it is.