With derived_merge=off and condition_pushdown_for_derived=on, derived table condition pushdown mishandles BIT(1) IS NOT NULL vs DATE '0000-00-00', changing result from 1 row to 0 rows.

1. 1. 1. Problem Description
         With `optimizer_switch='derived_merge=off,condition_pushdown_for_derived=on'`, MariaDB pushes the outer condition `(b IS NOT NULL) IN (d)` down into a materialized derived table built from double CTEs + JOIN, and incorrectly handles the comparison between `BIT(1)`'s `IS NOT NULL` and `DATE '0000-00-00'`, changing the result from 1 row to 0 rows. Disabling `condition_pushdown_for_derived` restores the correct result.

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

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

SET SESSION optimizer_switch =
'derived_merge=off,condition_pushdown_for_derived=on';

– Unsplit single table
CREATE TABLE source_2152 (
rid BIGINT NOT NULL,
b BIT(1) NOT NULL,
d DATE NOT NULL,
UNIQUE KEY (rid),
KEY idx_source_2152 (rid, b)
) ENGINE=InnoDB;

– Two tables after vertical split
CREATE TABLE left_2152 (
rid BIGINT PRIMARY KEY,
b BIT(1) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE right_2152 (
rid BIGINT PRIMARY KEY,
d DATE NOT NULL
) ENGINE=InnoDB;

INSERT INTO source_2152 VALUES (1, 0, '0000-00-00');
INSERT INTO left_2152 VALUES (1, 0);
INSERT INTO right_2152 VALUES (1, '0000-00-00');

– Query 1: single-table structure, actually returns 1 row: 0000-00-00
SELECT d
FROM source_2152
WHERE (b IS NOT NULL) IN (d);

– Query 2: multi-table structure with double CTE + JOIN, actually returns 0 rows
SELECT d
FROM (
WITH
l AS (SELECT * FROM left_2152),
r AS (SELECT * FROM right_2152)
SELECT l.rid, l.b, r.d
FROM l
JOIN r ON l.rid = r.rid
) AS split_2152
WHERE (b IS NOT NULL) IN (d);
```

1. 1. 1. Expected Behavior
         Query 1 (single-table) and Query 2 (double CTE + JOIN multi-table structure) should return the same result, i.e., both should return 1 row `0000-00-00`.

1. 1. 1. Actual Behavior

• Query 1 (single-table): returns 1 row `0000-00-00`.
• Query 2 (double CTE + JOIN): returns 0 rows.

1. 1. 1. Execution Plan
         *Single-table query:*
         ```
         id select_type table type key rows Extra
         1 SIMPLE source_2152 ALL NULL 1 Using where; Using temporary
         ```

*Multi-table query:*
```
id select_type table type key ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL 2 Using where; Using temporary
4 DERIVED <derived2> ALL NULL NULL 2 Using temporary
4 DERIVED <derived3> ref key0 l.rid,func 1 Using where
2 DERIVED left_2152 ALL NULL NULL 1 Using temporary
3 DERIVED right_2152 ALL NULL NULL 1 Using temporary
```

1. 1. 1. Additional Analysis
         The issue centers on derived table condition pushdown. It can be verified with the following control:

```sql
SET SESSION optimizer_switch =
'derived_merge=off,condition_pushdown_for_derived=off';

SELECT d
FROM (
WITH
l AS (SELECT * FROM left_2152),
r AS (SELECT * FROM right_2152)
SELECT l.rid, l.b, r.d
FROM l
JOIN r ON l.rid = r.rid
) AS split_2152
WHERE (b IS NOT NULL) IN (d);
```

After disabling `condition_pushdown_for_derived`, this query was measured to return again:
```
0000-00-00
```