Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2328

Concat operator returns wrong result when one of the operands is NULL

    XMLWordPrintableJSON

    Details

      Description

      Queries below should return NULL:

      0: jdbc:drill:schema=dfs> select cast(null as varchar(10)) || '--' from t1;
      +------------+
      |   EXPR$0   |
      +------------+
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      | --         |
      +------------+
      10 rows selected (0.09 seconds)
      0: jdbc:drill:schema=dfs> select a1 || '--' from t1 where a1 is null;
      +------------+
      |   EXPR$0   |
      +------------+
      | --         |
      +------------+
      1 row selected (0.105 seconds)
      

      Looks harmless at first, but a very common pattern in many customer queries will be broken: grouping by using '||' as following:

      select
              cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10)),
              sum(c_integer)  as sum1
      from
              alltypes_with_nulls
      group by
              cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10))
      order by
              cast(extract(day from c_timestamp) as varchar(10)) || '-' || cast(extract(month from c_timestamp) as varchar(10)) || '-' || cast(extract(year from c_timestamp) as varchar(10))
      ;
      

        Attachments

        1. DRILL-2328.1.patch
          6 kB
          Sean Hsuan-Yi Chu

          Activity

            People

            • Assignee:
              seanhychu Sean Hsuan-Yi Chu
              Reporter:
              vicky Victoria Markman
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: