`CAST('0000-00-00' AS DATE)` Has Inconsistent Semantics and Incorrectly Equals Every Non-`NULL` `SET` Value

[fyr03]

Summary

PolarDB-X assigns inconsistent SQL semantics to CAST('0000-00-00' AS DATE).

The same expression is simultaneously treated as:

• NULL when projected directly
• not NULL for <=> NULL
• equal to itself under =
• stringified as 0000-00-00 under CAST(... AS CHAR)

That internal inconsistency then causes a standalone wrong-result bug when the value is compared against a SET column: CAST('0000-00-00' AS DATE) = s returns 1 for every non-NULL SET value, including the empty set ('', mask 0) and all non-empty bitmasks (1, 3, 5, 7).

The equivalent comparisons using '0000-00-00' as a string literal or 0 as an integer do not show this behavior.

Environment

Item	Value
Deployment	Single-node Docker (SINGLE group)
sql_mode	'' (empty, set via SET SESSION sql_mode = '')
OS	Ubuntu 20.04.4 LTS
CPU Architecture	x86 / Intel(R) Xeon(R) Platinum 8358P @ 2.60GHz (64 Cores)

MySQL [replay]> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.004 sec)

MySQL [replay]> select polardb_version();
+---------+--------------+---------------------+
| TYPE    | VERSION      | RELEASE_DATE        |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN      | 2.4.0.5.4.19 | SNAPSHOT            |
| DN      | 2.4.0.8.4.19 | 20240430            |
| CDC     | 2.4.0.5.4.19 | 20240430            |
| GMS     | 2.4.0.8.4.19 | 20240430            |
+---------+--------------+---------------------+
5 rows in set (0.023 sec)

Steps to Reproduce

SET SESSION sql_mode = '';

DROP TABLE IF EXISTS repro_set;

CREATE TABLE repro_set (
    id INT NOT NULL AUTO_INCREMENT,
    s  SET('x','y','z') NULL,
    PRIMARY KEY (id)
) SINGLE;

INSERT INTO repro_set (s) VALUES
    (NULL),
    (''),
    ('x'),
    ('x,y'),
    ('x,z'),
    ('x,y,z');

SELECT
    CAST('0000-00-00' AS DATE)                         AS zero_date,
    CAST(CAST('0000-00-00' AS DATE) AS CHAR)          AS zero_date_as_char,
    (CAST('0000-00-00' AS DATE) <=> NULL)             AS zero_date_ns_null,
    (CAST('0000-00-00' AS DATE) = CAST('0000-00-00' AS DATE)) AS zero_date_self_eq;

SELECT
    id,
    s,
    s + 0 AS set_mask,
    (CAST('0000-00-00' AS DATE) = s)     AS date_eq_set,
    ('0000-00-00' = s)                   AS str_eq_set,
    (0 = s)                              AS int_eq_set,
    (CAST('0000-00-00' AS DATE) <=> s)   AS date_ns_eq_set
FROM repro_set
ORDER BY id;

SELECT
    SUM(CAST('0000-00-00' AS DATE) = s) AS scalar_matches,
    SUM('0000-00-00' = s)               AS string_matches,
    SUM(0 = s)                          AS int_matches
FROM repro_set;

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE CAST('0000-00-00' AS DATE) = s
ORDER BY id;

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE 0 = s
ORDER BY id;

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE '0000-00-00' = s
ORDER BY id;

SHOW WARNINGS;

DROP TABLE IF EXISTS repro_set;

Expected Behavior

CAST('0000-00-00' AS DATE) must have one consistent SQL meaning.

If it is treated as NULL, then:

Expression	Expected
CAST('0000-00-00' AS DATE)	NULL
CAST('0000-00-00' AS DATE) <=> NULL	1
CAST('0000-00-00' AS DATE) = CAST('0000-00-00' AS DATE)	NULL
CAST(CAST('0000-00-00' AS DATE) AS CHAR)	NULL or a representation consistent with NULL semantics

If it is treated as zero-date 0000-00-00, then:

Expression	Expected
CAST('0000-00-00' AS DATE)	0000-00-00
CAST('0000-00-00' AS DATE) <=> NULL	0
CAST('0000-00-00' AS DATE) = CAST('0000-00-00' AS DATE)	1
CAST(CAST('0000-00-00' AS DATE) AS CHAR)	0000-00-00

Either interpretation would be internally consistent. The observed behavior matches neither.

For the SET comparisons, the control rows establish the intended baseline:

• NULL should yield NULL for =
• '' has mask 0
• 'x', 'x,y', 'x,z', 'x,y,z' have masks 1, 3, 5, 7
• 0 = s should therefore match only the empty-set row

CAST('0000-00-00' AS DATE) = s should not match all non-NULL SET rows unless PolarDB-X is treating one value as equal to 0, 1, 3, 5, and 7 at the same time, which is impossible under a consistent equality semantics.

Actual Behavior

1. CAST('0000-00-00' AS DATE) Is Already Internally Inconsistent

SELECT
    CAST('0000-00-00' AS DATE)                         AS zero_date,
    CAST(CAST('0000-00-00' AS DATE) AS CHAR)          AS zero_date_as_char,
    (CAST('0000-00-00' AS DATE) <=> NULL)             AS zero_date_ns_null,
    (CAST('0000-00-00' AS DATE) = CAST('0000-00-00' AS DATE)) AS zero_date_self_eq;
+-----------+-------------------+-------------------+-------------------+
| zero_date | zero_date_as_char | zero_date_ns_null | zero_date_self_eq |
+-----------+-------------------+-------------------+-------------------+
| NULL      | 0000-00-00        | 0                 | 1                 |
+-----------+-------------------+-------------------+-------------------+

So the same value is treated as:

• NULL for projection
• not NULL for <=> NULL
• equal to itself under =
• 0000-00-00 when cast to CHAR

2. DATE = SET Comparison Then Produces Wrong Results

SELECT
    id,
    s,
    s + 0 AS set_mask,
    (CAST('0000-00-00' AS DATE) = s)     AS date_eq_set,
    ('0000-00-00' = s)                   AS str_eq_set,
    (0 = s)                              AS int_eq_set,
    (CAST('0000-00-00' AS DATE) <=> s)   AS date_ns_eq_set
FROM repro_set
ORDER BY id;
+----+-------+----------+-------------+------------+------------+----------------+
| id | s     | set_mask | date_eq_set | str_eq_set | int_eq_set | date_ns_eq_set |
+----+-------+----------+-------------+------------+------------+----------------+
| 1  | NULL  | NULL     | NULL        | NULL       | NULL       | 0              |
| 2  |       | 0.0      | 1           | 0          | 1          | 1              |
| 3  | x     | 1.0      | 1           | 0          | 0          | 1              |
| 4  | x,y   | 3.0      | 1           | 0          | 0          | 1              |
| 5  | x,z   | 5.0      | 1           | 0          | 0          | 1              |
| 6  | x,y,z | 7.0      | 1           | 0          | 0          | 1              |
+----+-------+----------+-------------+------------+------------+----------------+

The control paths are sane:

• 0 = s matches only the empty-set row (set_mask = 0)
• '0000-00-00' = s matches no rows

But CAST('0000-00-00' AS DATE) = s returns 1 for every non-NULL row, including masks 0, 1, 3, 5, and 7.

3. The Wrong Result Appears in WHERE, Not Just in a Projected Expression

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE CAST('0000-00-00' AS DATE) = s
ORDER BY id;
+----+-------+----------+
| id | s     | set_mask |
+----+-------+----------+
| 2  |       | 0.0      |
| 3  | x     | 1.0      |
| 4  | x,y   | 3.0      |
| 5  | x,z   | 5.0      |
| 6  | x,y,z | 7.0      |
+----+-------+----------+

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE 0 = s
ORDER BY id;
+----+---+----------+
| id | s | set_mask |
+----+---+----------+
| 2  |   | 0.0      |
+----+---+----------+

SELECT id, s, s + 0 AS set_mask
FROM repro_set
WHERE '0000-00-00' = s
ORDER BY id;
(empty)

SHOW WARNINGS is empty.

Strongest Contradictions

Contradiction A: Zero-Date DATE Value Has No Stable Meaning

CAST('0000-00-00' AS DATE)                     -- projected as NULL
CAST(CAST('0000-00-00' AS DATE) AS CHAR)      -- 0000-00-00
CAST('0000-00-00' AS DATE) <=> NULL           -- 0
CAST('0000-00-00' AS DATE) =
CAST('0000-00-00' AS DATE)                    -- 1

These cannot all be correct under one consistent SQL interpretation.

Contradiction B: One Value Equals Every Non-NULL SET Mask

The same DATE value compares equal to:

• empty set, mask 0
• 'x', mask 1
• 'x,y', mask 3
• 'x,z', mask 5
• 'x,y,z', mask 7

No consistent equality semantics can make a single scalar value equal to all of 0, 1, 3, 5, and 7.

Analysis

This is a real wrong-result bug. The replay proves two layers of failure:

1. CAST('0000-00-00' AS DATE) itself has context-dependent semantics.
2. The DATE = SET comparison path then amplifies that inconsistency into obviously incorrect equality results.

The precise internal root cause is still a hypothesis. The most likely explanation is that PolarDB-X uses multiple incompatible internal representations for zero-date DATE values and different executor/coercion paths consult different representations. But the wrong-result behavior does not depend on that explanation being correct.