WHERE c2 IS NULL on a NOT NULL primary key column is incorrectly optimized to a primary key const lookup, causing direct query to return one row while rewritten returns empty set.

1. 1. 1. Problem Description
         `c2` is defined as `DATETIME NOT NULL PRIMARY KEY`, but the zero date `'0000-00-00 00:00:00'` is inserted. The condition `WHERE c2 IS NULL` in the query should always be false according to SQL semantics. However, the optimizer incorrectly optimizes the direct query into a primary key const lookup, matching the row with the zero date. The rewritten query, which uses CTE/derived tables, does not take this incorrect optimization path and returns an empty set.

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

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

CREATE TABLE source (
vp_rowid BIGINT NOT NULL UNIQUE,
c0 DATE UNIQUE,
c1 INET6 UNIQUE,
c2 DATETIME NOT NULL UNIQUE PRIMARY KEY,
c3 INET6 NOT NULL
) ENGINE=InnoDB;

INSERT INTO source VALUES
(1,NULL,NULL,'0000-00-00 00:00:00','::1'),
(2,'2000-01-03','2001:db8::3','2000-01-01 00:00:02','2001:db8::3'),
(3,'2000-01-04','2001:db8::4','2000-01-01 00:00:03','2001:db8::4'),
(4,'2000-01-05','2001:db8::5','2000-01-01 00:00:04','2001:db8::5'),
(5,'2000-01-06','2001:db8::6','2000-01-01 00:00:05','2001:db8::6'),
(6,'2000-01-07','2001:db8::7','2000-01-01 00:00:06','2001:db8::7'),
(7,'2000-01-08','2001:db8::8','2000-01-01 00:00:07','2001:db8::8'),
(8,'2000-01-09','2001:db8::9','2000-01-01 00:00:08','2001:db8::9');

CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 DATE
) ENGINE=InnoDB;

CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 INET6,
c2 DATETIME NOT NULL,
c3 INET6 NOT NULL
) ENGINE=InnoDB;

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

CREATE INDEX idx_l_c0 ON l(c0);

SET SESSION optimizer_prune_level=1;
SET SESSION optimizer_switch='derived_merge=off';

SELECT 'direct' AS tag, c0
FROM (
SELECT DISTINCTROW c0
FROM source
WHERE c2 IS NULL
) q
ORDER BY 1;

SELECT 'rewritten' AS tag, c0
FROM (
SELECT DISTINCTROW c0
FROM (
WITH l1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn,
vp_rowid, c0
FROM l
),
r1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn,
vp_rowid, c1, c2, c3
FROM r
)
SELECT l1.vp_rowid, l1.c0, r1.c1, r1.c2, r1.c3
FROM l1
JOIN r1 ON l1.rn = r1.rn
) reconstructed
WHERE c2 IS NULL
) q
ORDER BY 1;
```

1. 1. 1. Expected Behavior
         Both queries should return an empty set, because `c2` is a `NOT NULL PRIMARY KEY` and `WHERE c2 IS NULL` is always false.

1. 1. 1. Actual Behavior

• The direct query returns one row with `c0` equal to `NULL`, displayed as `direct -> NULL`.
• The rewritten query returns an empty set, displayed as `rewritten -> empty set`.

1. 1. 1. Execution Plan
         Execution plan for the direct query:
         ```
         id select_type table type possible_keys key rows Extra
         1 PRIMARY <derived2> system 1
         2 DERIVED source const PRIMARY PRIMARY 1
         ```
         Key points:

• The access type of `source` is `const`;
• The primary key `PRIMARY` is used;
• `rows = 1`.

1. 1. 1. Additional Analysis
         `const` usually indicates that the optimizer believes it can directly locate a single row via the primary key or a unique index. However, the condition is:
         ```sql
         c2 IS NULL
         ```
         while `c2` is a `NOT NULL PRIMARY KEY`. Normally, the optimizer should immediately determine `Impossible WHERE`, or at least should not use a primary key const lookup to match a row. Here, it treats the condition as if it could locate a row via the primary key.

The first row of data is:
```sql
(1, NULL, NULL, '0000-00-00 00:00:00', '::1')
```
where:

• `c2 = '0000-00-00 00:00:00'`, not `NULL`;
• `c0 = NULL`.

Therefore, the direct query ultimately returns one row with `c0` equal to `NULL`, displayed as:
```
direct -> NULL
```