[IMPALA-9922] Is this a better approach to deal with malformed input in 'to_timestamp()'

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Impala 3.4.0
• Component/s: Backend
• Labels: newbie ramp-up
• Epic Color: ghx-label-12
• Assignee: Fifteen
• Reporter: Fifteen
• Created: 07/Jul/20 08:57
• Updated: 14/Dec/20 07:12

Description

When I tried to convert `string` to `timestamp` with `to_timestamp()` function, I got some unexpected NULL values . So I am writing this issue to seek for your help. 

Currently, the built-in function `to_timestamp()` return NULL when input's length is not equal to the length of format string describes. For example, the following query returns `NULL`:

[impala-shell]default> select to_timestamp("2020-01-01 18:00:00.12","yyyy-MM-dd HH:mm:ss.SSS")
> NULL

While this query returns converted value:

[impala-shell]default> select to_timestamp("2020-01-01 18:00:00.123","yyyy-MM-dd HH:mm:ss.SSS") 
> 2020-01-01 18:00:00.123000000

 

Snippet below explains the relative logic. The file name is `cast-functions-ir.cc` 

bool ParseDateTime(const char* str, int str_len, const DateTimeFormatContext& dt_ctx,
    DateTimeParseResult* dt_result) {
  DCHECK(dt_ctx.fmt_len > 0);
  DCHECK(dt_ctx.toks.size() > 0);
  DCHECK(dt_result != NULL);
  //-------------------------------------------------------
  // if str_len < fmt_len, Parse fail and return NULL 
 //-------------------------------------------------------
  if (str_len <= 0 || str_len < dt_ctx.fmt_len || str == NULL) return false; 
  StringParser::ParseResult status;
  ...

 My proposal 

Will it be better if  function accepts the shorter input and returns converted timestamp with some padding zeros?  i.e. returns 2020-01-01 18:00:00.012000000 with the following sql

[impala-shell]default> select to_timestamp("2020-01-01 18:00:00.12","yyyy-MM-dd HH:mm:ss.SSS")