Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.10.2, 11.7.2
-
Ubuntu 20.24, Docker
-
Unexpected results
-
Fix COALESCE and IFNULL functions to use - (i) argument nullness, (ii) type conversion safety of fallback values to decide nullability of result.
Description
Hello!
According to the official information
When two arguments are given, COALESCE() is the same as IFNULL():
But it does not seem to be a full information or bug.
Having view:
-- Setup
|
CREATE OR REPLACE VIEW test_coalesce_vs_ifnull AS
|
SELECT
|
-- Nullable column fallback via COALESCE()
|
COALESCE(operation_date, '1970-01-01 00:00:00') AS coalesced_date, |
-- Nullable column fallback via IFNULL()
|
IFNULL(operation_date, '1970-01-01 00:00:00') AS ifnull_date |
FROM (
|
SELECT NULL AS operation_date
|
) AS t;
|
... and making request:
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE
|
FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_NAME = 'test_coalesce_vs_ifnull'; |
I have not equal column types
+-----------------+-------------+-------------+
|
| COLUMN_NAME | IS_NULLABLE | COLUMN_TYPE |
|
+-----------------+-------------+-------------+
|
| coalesced_date | YES | varchar(19) |
|
| ifnull_date | NO | varchar(19) |
|
+-----------------+-------------+-------------+
|
What let me into this behavior: I have a view which has aggregating function MIN(operation_date) on column which type is not nullable timestamp but after MIN() it becomes nullable. So I wanted to have not nullable type some way and COALESCE does not allow it but IFNULL does. May be docs must be updated. I do not know even if it is a bug or my reading issue.
Thank you.