Predicate pushdown into JOIN in multi-table reconstructed path incorrectly filters out 4 rows with c0=0, returning 3 rows instead of 7.

1. 1. 1. Problem Description
         With `optimizer_switch='derived_merge=off'`, a single-table query and a multi-table reconstructed query return different results for the same WHERE condition. The single-table query returns 7 rows, while the multi-table reconstructed query returns only 3 rows, losing the 4 rows with `c0=0`. The execution plan shows that MariaDB pushes the predicate down onto the `l` table inside the JOIN, while the outer query reapplies the same predicate, causing incorrect filtering on the pushed-down path.

1. 1. 1. How to Reproduce
         Execute the following SQL:

```sql
DROP DATABASE IF EXISTS repro_mariadb818_db6;
CREATE DATABASE repro_mariadb818_db6;
USE repro_mariadb818_db6;

SET SESSION sql_mode = '';
SET SESSION optimizer_switch = 'derived_merge=off';

CREATE TABLE single_data (
c0 BIT(1),
c1 SET('a','b','c') NOT NULL
) ENGINE=MyISAM;

INSERT INTO single_data(c0, c1) VALUES
(NULL, ''), (0, 'a'), (1, 'b'), (0, 'c'),
(1, 'a,b'), (0, 'a,c'), (1, 'b,c'), (0, 'a,b,c');

CREATE TABLE part_l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 BIT(1)
) ENGINE=MyISAM;

CREATE TABLE part_r (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 SET('a','b','c') NOT NULL
) ENGINE=MyISAM;

INSERT INTO part_l
SELECT ROW_NUMBER() OVER (), c0 FROM single_data;

INSERT INTO part_r
SELECT ROW_NUMBER() OVER (), c1 FROM single_data;

– Single-table query: 7 rows
SELECT *
FROM (
SELECT ALL
CAST(((- c0) IN (c0)) AS UNSIGNED) AS x0,
c1 AS x1,
473386488 AS x2,
CAST((c0 IN (c0, 'a,b')) AS UNSIGNED) AS x3,
CAST(LPAD(
CAST(c1 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin,
'b,c',
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin
) AS UNSIGNED) AS x4
FROM single_data
WHERE (
LPAD(
CAST('2043-04-01 08:41:36' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin,
'2025-03-31',
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin
) IN (c0, '2001:db8::9504')
) XOR c0
) q
ORDER BY 1, 2, 3, 4, 5;

– Multi-table reconstructed query: 3 rows
SELECT *
FROM (
SELECT ALL
CAST(((- c0) IN (c0)) AS UNSIGNED) AS x0,
c1 AS x1,
473386488 AS x2,
CAST((c0 IN (c0, 'a,b')) AS UNSIGNED) AS x3,
CAST(LPAD(
CAST(c1 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin,
'b,c',
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin
) AS UNSIGNED) AS x4
FROM (
SELECT l.vp_rowid, l.c0, r.c1
FROM part_l l
JOIN part_r r ON l.vp_rowid = r.vp_rowid
) AS source
WHERE (
LPAD(
CAST('2043-04-01 08:41:36' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin,
'2025-03-31',
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin
) IN (c0, '2001:db8::9504')
) XOR c0
) q
ORDER BY 1, 2, 3, 4, 5;
```

1. 1. 1. Expected Behavior
         The single-table query and the multi-table reconstructed query should return the same result, i.e., both should return 7 rows.

1. 1. 1. Actual Behavior

• Single-table query: returns 7 rows.
• Multi-table reconstructed query: returns only 3 rows, losing the 4 rows with `c0=0`.

Single-table result:
```
----------------------------

----------------------------

----------------------------
```

Multi-table reconstructed result:
```
--------------------------

--------------------------

--------------------------
```

1. 1. 1. Execution Plan
         *Single-table:*
         ```
         <derived2> ALL
         single_data ALL
         ```

*Multi-table:*
```
<derived2> ALL
<derived3> ALL
l ALL
r hash_ALL #hash#PRIMARY
```

The execution plan shows that MariaDB pushes the predicate down into the derived table:
```
DERIVED ... WHERE
(LPAD(... CAST(l.c0 AS CHAR) ...)
IN (l.c0, '2001:db8::9504') XOR l.c0)
```
The outer query then reapplies the same predicate to the derived table result. The single-table or direct `l JOIN r` query returns 7 rows, but after the pushdown into the JOIN, the 4 rows with `c0=0` are incorrectly filtered out, leaving only the 3 rows with `c0=1`.

quoted text