Apache Drill 1.10.0 - Issue with combination of Like operator & newline (\n) character in data

[Shankar Mane]

Hi All,

I am facing some issues while using Like operator & newline (\n) character.
Below is the in details description :

*-- Hive Queries
*

create table default.withdraw(
id string
) stored as parquet;


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


*--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*

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
-*
FYI - Drill (v1.10.0) is using above table meta store. We tested above
queries on drill too.

*Summary* - I m getting unexpected results in this version (1.10.0). *Can
someone please confirm what is the output on latest Drill version ?*

Here are the Drill queries and its outputs:

*--1) result = success*

0: jdbc:drill:> select * from withdraw;
++
|   id   |
++
| withdraw
cash  |
++
1 row selected (0.34 seconds)


*--2) **result = wrong*

0: jdbc:drill:> select * from withdraw where id like '%withdraw%';
+-+
| id  |
+-+
+-+
No rows selected (1.219 seconds)




*--3) **result = wrong*

0: jdbc:drill:> select * from withdraw where id like '%withdraw%cash';
+-+
| id  |
+-+
+-+
No rows selected (0.188 seconds)



*--4) **result = wrong*

0: jdbc:drill:> select * from withdraw where id like '%cash%';
+-+
| id  |
+-+
+-+
No rows selected (0.181 seconds)

*-- *
*-- *

Please help here in case i am missing anything.

regards,
shankar

Re: Apache Drill 1.10.0 - Issue with combination of Like operator & newline (\n) character in data

[Arina Yelchiyeva]

Hi Shankar,

To verify behavior on the latest Drill version, you can download Drill from 
here https://drill.apache.org/download/ .

Kind regards,
Arina

> On Jul 17, 2019, at 2:29 PM, Shankar Mane  wrote:
> 
> Hi All,
> 
> I am facing some issues while using Like operator & newline (\n) character.
> Below is the in details description :
> 
> *-- Hive Queries
> *
> 
> create table default.withdraw(
> id string
> ) stored as parquet;
> 
> 
> insert into default.withdraw select 'withdraw\ncash';
> 
> 
> *--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*
> 
> 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
> -*
> FYI - Drill (v1.10.0) is using above table meta store. We tested above
> queries on drill too.
> 
> *Summary* - I m getting unexpected results in this version (1.10.0). *Can
> someone please confirm what is the output on latest Drill version ?*
> 
> Here are the Drill queries and its outputs:
> 
> *--1) result = success*
> 
> 0: jdbc:drill:> select * from withdraw;
> ++
> |   id   |
> ++
> | withdraw
> cash  |
> ++
> 1 row selected (0.34 seconds)
> 
> 
> *--2) **result = wrong*
> 
> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%';
> +-+
> | id  |
> +-+
> +-+
> No rows selected (1.219 seconds)
> 
> 
> 
> 
> *--3) **result = wrong*
> 
> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%cash';
> +-+
> | id  |
> +-+
> +-+
> No rows selected (0.188 seconds)
> 
> 
> 
> *--4) **result = wrong*
> 
> 0: jdbc:drill:> select * from withdraw where id like '%cash%';
> +-+
> | id  |
> +-+
> +-+
> No rows selected (0.181 seconds)
> 
> *-- *
> *-- *
> 
> Please help here in case i am missing anything.
> 
> regards,
> shankar

Re: Apache Drill 1.10.0 - Issue with combination of Like operator & newline (\n) character in data

[Shankar Mane]

Hi,  just tested on *Drill 1.16.0 (docker) *:

*--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)





On Wed, Jul 17, 2019 at 5:03 PM Arina Yelchiyeva 
wrote:

> Hi Shankar,
>
> To verify behavior on the latest Drill version, you can download Drill
> from here https://drill.apache.org/download/ <
> https://drill.apache.org/download/>.
>
> Kind regards,
> Arina
>
> > On Jul 17, 2019, at 2:29 PM, Shankar Mane 
> wrote:
> >
> > Hi All,
> >
> > I am facing some issues while using Like operator & newline (\n)
> character.
> > Below is the in details description :
> >
> > *-- Hive Queries
> > *
> >
> > create table default.withdraw(
> > id string
> > ) stored as parquet;
> >
> >
> > insert into default.withdraw select 'withdraw\ncash';
> >
> >
> > *--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*
> >
> > 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
> > -*
> > FYI - Drill (v1.10.0) is using above table meta store. We tested above
> > queries on drill too.
> >
> > *Summary* - I m getting unexpected results in this version (1.10.0). *Can
> > someone please confirm what is the output on latest Drill version ?*
> >
> > Here are the Drill queries and its outputs:
> >
> > *--1) result = success*
> >
> > 0: jdbc:drill:> select * from withdraw;
> > ++
> > |   id   |
> > ++
> > | withdraw
> > cash  |
> > ++
> > 1 row selected (0.34 seconds)
> >
> >
> > *--2) **result = wrong*
> >
> > 0: jdbc:drill:> select * from withdraw where id like '%withdraw%';
> > +-+
> > | id  |
> > +-+
> > +-+
> > No rows selected (1.219 seconds)
> >
> >
> >
> >
> > *--3) **result = wrong*
> >
> > 0: jdbc:drill:> select * from withdraw where id like '%withdraw%cash';
> > +-+
> > | id  |
> > +-+
> > +-+
> > No rows selected (0.188 seconds)
> >
> >
> >
> > *--4) **result = wrong*
> >
> > 0: jdbc:drill:> select * from withdraw where id like '%cash%';
> > +-+
> > | id  |
> > +-+
> > +-+
> > No rows selected (0.181 seconds)
> >
> > *-- *
> > *-- *
> >
> > Please help here in case i am missing anything.
> >
> > regards,
> > shankar
>
>

Re: Apache Drill 1.10.0 - Issue with combination of Like operator & newline (\n) character in data

[Arina Yelchiyeva]

Well, looks like a bug, you can fill in Drill Jira (please include reproduce 
steps).

Contributions are always welcome!

Kind regards,
Arina

> On Jul 17, 2019, at 4:13 PM, Shankar Mane  wrote:
> 
> Hi,  just tested on *Drill 1.16.0 (docker) *:
> 
> *--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)
> 
> 
> 
> 
> 
> On Wed, Jul 17, 2019 at 5:03 PM Arina Yelchiyeva 
> wrote:
> 
>> Hi Shankar,
>> 
>> To verify behavior on the latest Drill version, you can download Drill
>> from here https://drill.apache.org/download/ <
>> https://drill.apache.org/download/>.
>> 
>> Kind regards,
>> Arina
>> 
>>> On Jul 17, 2019, at 2:29 PM, Shankar Mane 
>> wrote:
>>> 
>>> Hi All,
>>> 
>>> I am facing some issues while using Like operator & newline (\n)
>> character.
>>> Below is the in details description :
>>> 
>>> *-- Hive Queries
>>> *
>>> 
>>> create table default.withdraw(
>>> id string
>>> ) stored as parquet;
>>> 
>>> 
>>> insert into default.withdraw select 'withdraw\ncash';
>>> 
>>> 
>>> *--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*
>>> 
>>> 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
>>> -*
>>> FYI - Drill (v1.10.0) is using above table meta store. We tested above
>>> queries on drill too.
>>> 
>>> *Summary* - I m getting unexpected results in this version (1.10.0). *Can
>>> someone please confirm what is the output on latest Drill version ?*
>>> 
>>> Here are the Drill queries and its outputs:
>>> 
>>> *--1) result = success*
>>> 
>>> 0: jdbc:drill:> select * from withdraw;
>>> ++
>>> |   id   |
>>> ++
>>> | withdraw
>>> cash  |
>>> ++
>>> 1 row selected (0.34 seconds)
>>> 
>>> 
>>> *--2) **result = wrong*
>>> 
>>> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%';
>>> +-+
>>> | id  |
>>> +-+
>>> +-+
>>> No rows selected (1.219 seconds)
>>> 
>>> 
>>> 
>>> 
>>> *--3) **result = wrong*
>>> 
>>> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%cash';
>>> +-+
>>> | id  |
>>> +-+
>>> +-+
>>> No rows selected (0.188 seconds)
>>> 
>>> 
>>> 
>>> *--4) **result = wrong*
>>> 
>>> 0: jdbc:drill:> select * from withdraw where id like '%cash%';
>>> +-+
>>> | id  |
>>> +-+
>>> +-+
>>> No rows selected (0.181 seconds)
>>> 
>>> *-- *
>>> *-- *
>>> 
>>> Please help here in case i am missing anything.
>>> 
>>> regards,
>>> shankar
>> 
>> 

Re: Apache Drill 1.10.0 - Issue with combination of Like operator & newline (\n) character in data

[Shankar Mane]

Jira has been created @ https://issues.apache.org/jira/browse/DRILL-7328

On Wed, Jul 17, 2019 at 6:55 PM Arina Yelchiyeva 
wrote:

> Well, looks like a bug, you can fill in Drill Jira (please include
> reproduce steps).
>
> Contributions are always welcome!
>
> Kind regards,
> Arina
>
> > On Jul 17, 2019, at 4:13 PM, Shankar Mane 
> wrote:
> >
> > Hi,  just tested on *Drill 1.16.0 (docker) *:
> >
> > *--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)
> >
> >
> >
> >
> >
> > On Wed, Jul 17, 2019 at 5:03 PM Arina Yelchiyeva <
> arina.yelchiy...@gmail.com>
> > wrote:
> >
> >> Hi Shankar,
> >>
> >> To verify behavior on the latest Drill version, you can download Drill
> >> from here https://drill.apache.org/download/ <
> >> https://drill.apache.org/download/>.
> >>
> >> Kind regards,
> >> Arina
> >>
> >>> On Jul 17, 2019, at 2:29 PM, Shankar Mane 
> >> wrote:
> >>>
> >>> Hi All,
> >>>
> >>> I am facing some issues while using Like operator & newline (\n)
> >> character.
> >>> Below is the in details description :
> >>>
> >>> *-- Hive Queries
> >>> *
> >>>
> >>> create table default.withdraw(
> >>> id string
> >>> ) stored as parquet;
> >>>
> >>>
> >>> insert into default.withdraw select 'withdraw\ncash';
> >>>
> >>>
> >>> *--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*
> >>>
> >>> 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
> >>> -*
> >>> FYI - Drill (v1.10.0) is using above table meta store. We tested above
> >>> queries on drill too.
> >>>
> >>> *Summary* - I m getting unexpected results in this version (1.10.0).
> *Can
> >>> someone please confirm what is the output on latest Drill version ?*
> >>>
> >>> Here are the Drill queries and its outputs:
> >>>
> >>> *--1) result = success*
> >>>
> >>> 0: jdbc:drill:> select * from withdraw;
> >>> ++
> >>> |   id   |
> >>> ++
> >>> | withdraw
> >>> cash  |
> >>> ++
> >>> 1 row selected (0.34 seconds)
> >>>
> >>>
> >>> *--2) **result = wrong*
> >>>
> >>> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%';
> >>> +-+
> >>> | id  |
> >>> +-+
> >>> +-+
> >>> No rows selected (1.219 seconds)
> >>>
> >>>
> >>>
> >>>
> >>> *--3) **result = wrong*
> >>>
> >>> 0: jdbc:drill:> select * from withdraw where id like '%withdraw%cash';
> >>> +-+
> >>> | id  |
> >>> +-+
> >>> +-+
> >>> No rows selected (0.188 seconds)
> >>>
> >>>
> >>>
> >>> *--4) **result = wrong*
> >>>
> >>> 0: jdbc:drill:> select * from withdraw where id like '%cash%';
> >>> +-+
> >>> | id  |
> >>> +-+
> >>> +-+
> >>> No rows selected (0.181 seconds)
> >>>
> >>> *-- *
> >>> *-- *
> >>>
> >>> Please help here in case i am missing anything.
> >>>
> >>> regards,
> >>> shankar
> >>
> >>
>
>