With `derived_merge=off`, derived table column pruning drops `c2`, and the `c3 IN (c2 << c2)` implicit cast chain makes single-table and rewritten queries return inconsistent results (1 row vs 0 rows).

1. 1. 1. Problem Description
         With `optimizer_switch='derived_merge=off'`, a single-table query and a multi-table rewritten query return different results for the same logical expression `c3 IN (c2 << c2)`. The discrepancy is caused by two independent issues combined:

1. *Implicit type conversion chain*: `c2` is `ENUM('a','b','c')`. When participating in the `<<` bitwise operation, MariaDB converts it to an integer using its index value (`'a' → 1`), so `c2 << c2` yields the integer `2`. During `IN` comparison, `c3` is `TIMESTAMP`, and MariaDB does not convert `c3` to a number; instead, it converts the right-hand integer `2` to `DATETIME`. Since `2` is not a valid datetime literal, it is truncated to the zero date `'0000-00-00 00:00:00'`, producing the warning `Warning 1292: Truncated incorrect datetime value: '2'`. Thus `c3 IN (2)` is equivalent to `c3 = '0000-00-00 00:00:00'`, which matches the row whose `c3` is the zero date.
2. *Derived table materialization column pruning*: The original rewritten query only projects `c3` in the outer query. With `derived_merge=off`, the derived table `source` is materialized, and the optimizer only keeps columns needed by the outer query. Since `c2` is not projected by the outer query, `c2` is missing or becomes `NULL` in the materialized result. Consequently, `c2 << c2` becomes `NULL << NULL`, which evaluates to `NULL`; then `c3 IN (NULL)` evaluates to `NULL`, and `WHERE NULL` filters out all rows, returning 0 rows.

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

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

CREATE TABLE source (
vp_rowid BIGINT PRIMARY KEY,
c0 SET('a','b','c') NOT NULL,
c1 DATE NOT NULL,
c2 ENUM('a','b','c') NOT NULL,
c3 TIMESTAMP NOT NULL
) ENGINE=MyISAM;

INSERT INTO source VALUES
(1, '', '0000-00-00', 'a', '0000-00-00 00:00:00');
INSERT INTO source VALUES
(2, 'a', '2000-01-03', 'b', '2000-01-01 00:00:02');

CREATE TABLE l (
vp_rowid BIGINT PRIMARY KEY,
c0 SET('a','b','c') NOT NULL,
c2 ENUM('a','b','c') NOT NULL
) ENGINE=MyISAM;

CREATE TABLE r (
vp_rowid BIGINT PRIMARY KEY,
c1 DATE NOT NULL,
c3 TIMESTAMP NOT NULL
) ENGINE=MyISAM;

INSERT INTO l SELECT vp_rowid, c0, c2 FROM source;
INSERT INTO r SELECT vp_rowid, c1, c3 FROM source;

SET SESSION optimizer_switch='derived_merge=off';

– Single-table query
SELECT 'direct' AS tag, x
FROM (
SELECT DISTINCT c3 AS x
FROM source
WHERE c3 IN (c2 << c2)
) q;

– Multi-table rewritten query
SELECT 'rewrite' AS tag, x
FROM (
SELECT DISTINCT c3 AS x
FROM (
SELECT
l.vp_rowid,
l.c0,
r.c1,
l.c2,
r.c3
FROM l
JOIN (
SELECT r0.*
FROM r r0
WHERE r0.vp_rowid IN (
SELECT vp_rowid FROM l
)
) r ON l.vp_rowid = r.vp_rowid
) source
WHERE c3 IN (c2 << c2)
) q;
```

1. 1. 1. Expected Behavior
         The single-table query and the multi-table rewritten query should return identical results. Whether `derived_merge=off` or column pruning is applied, the evaluation semantics of `c3 IN (c2 << c2)` should remain consistent.

1. 1. 1. Actual Behavior

• Single-table query: returns 1 row with value `0000-00-00 00:00:00`.
• Multi-table rewritten query: returns 0 rows.

1. 1. 1. Error Information and/or Stack Trace
         The single-table query produces the following warning:
         ```
         Warning 1292: Truncated incorrect datetime value: '2'
         ```