Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-9922

Is this a better approach to deal with malformed input in 'to_timestamp()'

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: Impala 3.4.0
    • Fix Version/s: None
    • Component/s: Backend
    • Labels:
    • Epic Color:
      ghx-label-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")
      

       

       

        Attachments

          Activity

            People

            • Assignee:
              fifteencai Fifteen
              Reporter:
              fifteencai Fifteen
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: