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

Issue with combination of Like operator & newline (\n) character in data

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: Server
    • Labels:
      None

      Description

      Below is the in details description of issue and reproduce steps as well :
       
       
      -------------------------------------------------- Hive Queries ------------------------------------------------------------
       
       
      create table default.withdraw(
      id string
      ) stored as parquet;
       
       

      insert into default.withdraw select 'withdraw\ncash';

      -- note here, we have added "\n" character
       
       
       

      --1)  result = success

      hive> select * from default.withdraw where id like '%withdraw%';
      OK
      withdraw
      cash
      Time taken: 0.078 seconds, Fetched: 1 row(s)

       
       

      --2) *result = wrong ( I also saw that hive 2.3.4 also have issue here, But same query is working in Presto, MySQL, redshift)*

      hive> select * from default.withdraw where id like '%withdraw%cash';

      OK
      Time taken: 0.066 seconds

       
       

      --3) result = success

      hive> select * from default.withdraw where id like '%cash%';
      OK
      withdraw
      cash
      Time taken: 0.086 seconds, Fetched: 1 row(s)

       
       
       
      -------------------------------------------------- Drill Queries -----------------------------------------------------
       
      Tested on Drill version - 1.16.0 (Docker). I used hive plugin and above mentioned hive table.

      Here are the Drill queries and its outputs: 
       
       
       
      --1) result = success
      apache drill> select * from hive.default.withdraw;
      ---------------

           id      

      ---------------

      withdraw
      cash

      ---------------
      1 row selected (0.189 seconds)
       
       
       
      --2) result = success
      apache drill> select * from hive.default.withdraw where id  like '%withdraw%';
      ---------------

           id      

      ---------------

      withdraw
      cash

      ---------------
      1 row selected (0.168 seconds)
       
       
       
      --3) result = wrong
      apache drill> select * from hive.default.withdraw where id  like '%withdraw%cash';
      ----

      id

      ----
      ----
      No rows selected (0.214 seconds)
       
       
       
      --4) result = success
      apache drill> select * from hive.default.withdraw where id  like '%cash%';
      ---------------

           id      

      ---------------

      withdraw
      cash

      ---------------
      1 row selected (0.181 seconds)
       
      ------------------------------------------------- ------------------------------------------------- 
      Please help here in case i am missing anything.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              ssmane3 Shankar
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: