Description:
When a `DATE`-type column from a derived table (e.g., `ttt.b` in `COALESCE(ttt.b, t.a)`) is merged via derived merge optimization, the nullability check may fail due to `Item_view_ref` lacking an overridden `get_date()` method. This causes the execution to fall back to `item_ref::get_date()`, which incorrectly evaluates nullability for `DATE` types, unlike other data types (e.g., strings, integers) that have proper overrides.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(id INT PRIMARY KEY ,a DATE);
INSERT INTO t (id,a)VALUES (1,'2025-01-01'),(2,'2025-01-02');
SELECT ttt.b,t.a,COALESCE(NULL,t.a),COALESCE(ttt.b,t.a),COALESCE(ttt.b,t.id)
FROM t
LEFT JOIN
(SELECT STR_TO_DATE('2024-01-03','%Y-%m-%d') AS b
FROM (SELECT 1)tt
WHERE 1=0 )ttt ON 1=1;
# result
+------+------------+--------------------+---------------------+----------------------+
| b | a | COALESCE(NULL,t.a) | COALESCE(ttt.b,t.a) | COALESCE(ttt.b,t.id) |
+------+------------+--------------------+---------------------+----------------------+
| NULL | 2025-01-01 | 2025-01-01 | 2024-01-03 | 1 |
| NULL | 2025-01-02 | 2025-01-02 | 2024-01-03 | 2 |
+------+------------+--------------------+---------------------+----------------------+
# COALESCE(ttt.b, t.a) should be equal to COALESCE(NULL, t.a) because ttt.b is null.
set optimizer_switch="derived_merge=off";
SELECT ttt.b,t.a,COALESCE(NULL,t.a),COALESCE(ttt.b,t.a),COALESCE(ttt.b,t.id)
FROM t
LEFT JOIN
(SELECT STR_TO_DATE('2024-01-03','%Y-%m-%d') AS b
FROM (SELECT 1)tt
WHERE 1=0 )ttt ON 1=1;
# Correct result
+------+------------+--------------------+---------------------+----------------------+
| b | a | COALESCE(NULL,t.a) | COALESCE(ttt.b,t.a) | COALESCE(ttt.b,t.id) |
+------+------------+--------------------+---------------------+----------------------+
| NULL | 2025-01-01 | 2025-01-01 | 2025-01-01 | 1 |
| NULL | 2025-01-02 | 2025-01-02 | 2025-01-02 | 2 |
+------+------------+--------------------+---------------------+----------------------+
Suggested fix:
Implement `Item_view_ref::get_date()` to properly handle null detection for `DATE` types.
diff --git a/sql/item.cc b/sql/item.cc
index 9c19fcc344d..9b750d7a468 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8892,6 +8892,14 @@ bool Item_view_ref::val_json(Json_wrapper *wr) {
return super::val_json(wr);
}
+bool Item_view_ref::get_date(MYSQL_TIME *ltime, my_time_flags_t fuzzydate) {
+ if (has_null_row()) {
+ null_value = true;
+ return true;
+ }
+ return super::get_date(ltime, fuzzydate);
+}
+
bool Item_view_ref::is_null() {
if (has_null_row()) return true;
diff --git a/sql/item.h b/sql/item.h
index 2d276107258..7d6cb31fcdc 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -6243,6 +6243,7 @@ class Item_view_ref final : public Item_ref {
String *val_str(String *str) override;
bool val_bool() override;
bool val_json(Json_wrapper *wr) override;
+ bool get_date(MYSQL_TIME *ltime, my_time_flags_t fuzzydate) override;
bool is_null() override;
bool send(Protocol *prot, String *tmp) override;
bool collect_item_field_or_view_ref_processor(uchar *arg) override;
Description: When a `DATE`-type column from a derived table (e.g., `ttt.b` in `COALESCE(ttt.b, t.a)`) is merged via derived merge optimization, the nullability check may fail due to `Item_view_ref` lacking an overridden `get_date()` method. This causes the execution to fall back to `item_ref::get_date()`, which incorrectly evaluates nullability for `DATE` types, unlike other data types (e.g., strings, integers) that have proper overrides. How to repeat: DROP TABLE IF EXISTS t; CREATE TABLE t(id INT PRIMARY KEY ,a DATE); INSERT INTO t (id,a)VALUES (1,'2025-01-01'),(2,'2025-01-02'); SELECT ttt.b,t.a,COALESCE(NULL,t.a),COALESCE(ttt.b,t.a),COALESCE(ttt.b,t.id) FROM t LEFT JOIN (SELECT STR_TO_DATE('2024-01-03','%Y-%m-%d') AS b FROM (SELECT 1)tt WHERE 1=0 )ttt ON 1=1; # result +------+------------+--------------------+---------------------+----------------------+ | b | a | COALESCE(NULL,t.a) | COALESCE(ttt.b,t.a) | COALESCE(ttt.b,t.id) | +------+------------+--------------------+---------------------+----------------------+ | NULL | 2025-01-01 | 2025-01-01 | 2024-01-03 | 1 | | NULL | 2025-01-02 | 2025-01-02 | 2024-01-03 | 2 | +------+------------+--------------------+---------------------+----------------------+ # COALESCE(ttt.b, t.a) should be equal to COALESCE(NULL, t.a) because ttt.b is null. set optimizer_switch="derived_merge=off"; SELECT ttt.b,t.a,COALESCE(NULL,t.a),COALESCE(ttt.b,t.a),COALESCE(ttt.b,t.id) FROM t LEFT JOIN (SELECT STR_TO_DATE('2024-01-03','%Y-%m-%d') AS b FROM (SELECT 1)tt WHERE 1=0 )ttt ON 1=1; # Correct result +------+------------+--------------------+---------------------+----------------------+ | b | a | COALESCE(NULL,t.a) | COALESCE(ttt.b,t.a) | COALESCE(ttt.b,t.id) | +------+------------+--------------------+---------------------+----------------------+ | NULL | 2025-01-01 | 2025-01-01 | 2025-01-01 | 1 | | NULL | 2025-01-02 | 2025-01-02 | 2025-01-02 | 2 | +------+------------+--------------------+---------------------+----------------------+ Suggested fix: Implement `Item_view_ref::get_date()` to properly handle null detection for `DATE` types. diff --git a/sql/item.cc b/sql/item.cc index 9c19fcc344d..9b750d7a468 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8892,6 +8892,14 @@ bool Item_view_ref::val_json(Json_wrapper *wr) { return super::val_json(wr); } +bool Item_view_ref::get_date(MYSQL_TIME *ltime, my_time_flags_t fuzzydate) { + if (has_null_row()) { + null_value = true; + return true; + } + return super::get_date(ltime, fuzzydate); +} + bool Item_view_ref::is_null() { if (has_null_row()) return true; diff --git a/sql/item.h b/sql/item.h index 2d276107258..7d6cb31fcdc 100644 --- a/sql/item.h +++ b/sql/item.h @@ -6243,6 +6243,7 @@ class Item_view_ref final : public Item_ref { String *val_str(String *str) override; bool val_bool() override; bool val_json(Json_wrapper *wr) override; + bool get_date(MYSQL_TIME *ltime, my_time_flags_t fuzzydate) override; bool is_null() override; bool send(Protocol *prot, String *tmp) override; bool collect_item_field_or_view_ref_processor(uchar *arg) override;