[#MDEV-30080] Wrong result with LEFT JOINs involving constant tables

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (b INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1),(1);

CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM;

ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;

SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;

DROP TABLE t1, t2, t3;

10.5 9d388192
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
a b c
1 NULL NULL
SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
COUNT(*)
2

The expected result is (unless I'm mistaken)

a	b	c

1	1	NULL

1	1	NULL

so the actual result set is wrong both in the number of rows and in the values, while the COUNT result is correct.

Reproducible on 10.5+. Could not reproduce on 10.4.

Plan on 10.5
EXPLAIN FORMAT=JSON SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
EXPLAIN {
"query_block": {
"select_id": 1,
"const_condition": "1",
"table": {
"table_name": "t1",
"access_type": "system",
"rows": 1,
"filtered": 100
},
"table": {
"table_name": "t3",
"access_type": "const",
"possible_keys": ["PRIMARY"],
"rows": 1,
"filtered": 100,
"impossible_on_condition": true
},
"table": {
"table_name": "t2",
"access_type": "const",
"rows": 1,
"filtered": 100,
"impossible_on_condition": true
}
}
}

[MDEV-30080] Wrong result with LEFT JOINs involving constant tables Created: 2022-11-23 Updated: 2023-02-10 Resolved: 2023-01-13
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s:	10.11.2, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3