Clickhouse: toDate(nullif('', '')), toDateTime(nullif('', '')) throws Parsing error

[dm-logv]

Describe the bug or unexpected behaviour

When casting nullif('', '') to Date/DateTime types using toDate()/toDateTime() ClickHouse returns exceptions:

SQL Error [38]: ClickHouse exception, code: 38, host: *, port: *; Code: 38, e.displayText() = DB::Exception: Cannot parse date: value is too short: Cannot parse Date from String (version 19.15.3.6)

SQL Error [41]: ClickHouse exception, code: 41, host: *, port: *; Code: 41, e.displayText() = DB::Exception: Cannot parse datetime: Cannot parse DateTime from String (version 19.15.3.6)

How to reproduce

Some expected results:

select
    toDateTime(NULL)


toDateTime(NULL)
----------------
[NULL]          

select
    toDateTime('')


SQL Error [41]: ClickHouse exception, code: 41, host: *, port: *; Code: 41, e.displayText() = DB::Exception: Cannot parse datetime: Cannot parse DateTime from String (version 19.15.3.6)

select
    nullif('', '')


nullif('', '')
--------------
[NULL]        

select
    toDateTime(nullif(1, 1))


toDateTime(nullif(1, 1))
------------------------
                  [NULL]

select
    toDateTimeOrNull(nullif('', ''))


toDateTimeOrNull(nullif('', ''))
--------------------------------
                          [NULL]

Unexpected behavior:

select
    toDateTime(nullif('', ''))


SQL Error [41]: ClickHouse exception, code: 41, host: *, port: *; Code: 41, e.displayText() = DB::Exception: Cannot parse datetime: Cannot parse DateTime from String (version 19.15.3.6)

select
    toDate(nullif('', ''))


SQL Error [38]: ClickHouse exception, code: 38, host: *, port: *; Code: 38, e.displayText() = DB::Exception: Cannot parse date: value is too short: Cannot parse Date from String (version 19.15.3.6)

Expected behavior

select
    toDateTime(nullif('', ''))


toDateTime(nullif('', ''))
--------------------------------
                          [NULL]

[den-crane]

Clickhouse calculates both branches (if and else) for PERFORMANCE.

SELECT parseDateTimeBestEffortOrNull(nullIf('', ''))

┌─parseDateTimeBestEffortOrNull(nullIf('', ''))─┐
│                                          ᴺᵁᴸᴸ │
└───────────────────────────────────────────────┘

[filimonov]

@den-crane it's not the case here.
It looks like it just tries to take String from Nullable(String), ignoring the Nullable.

SELECT toDateTime(CAST(NULL, 'Nullable(String)'))

Received exception from server (version 19.16.1):
Code: 41. DB::Exception: Received from localhost:9000. DB::Exception: Cannot parse datetime: Cannot parse DateTime from String. 

laptop5591.local :) select toDateTime(CAST(Null, 'Nullable(Nothing)'));

SELECT toDateTime(CAST(NULL, 'Nullable(Nothing)'))

┌─toDateTime(CAST(NULL, 'Nullable(Nothing)'))─┐
│ ᴺᵁᴸᴸ                                        │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec. 

[filimonov]

The issue is still there:

SELECT toDateTime(CAST(NULL, 'Nullable(String)'))


Received exception from server (version 20.6.1):
Code: 41. DB::Exception: Received from localhost:9000. DB::Exception: Cannot parse datetime: Cannot parse DateTime from String. 

0 rows in set. Elapsed: 0.012 sec. 

[alexey-milovidov]

Workaround: toDateTimeOrZero.

[alexey-milovidov]

Depends on #12587.