Problem filtering with a "like" expression

Problem filtering with a "like" expression

am 21.03.2011 11:51:27 von Johan De Taeye

--000e0cd35376dd512b049efbe85c
Content-Type: text/plain; charset=ISO-8859-1

I ran into this case where a "like" expression is not evaluated correctly if
the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A' ||
'%'*
Is this a known limitation? Or a bug?


create table lookup (
name varchar(60)
);

insert into lookup (name) values ('AAA');

select * from lookup where name like 'A%';
=> 1 record returned. OK

select * from lookup where name like 'A' || '%';
=> returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
=> same as previous and returns nothing. INCORRECT!


I reproduced this problem on win32 using versions 5.1 and 5.5.10


Best regards,

Johan

--000e0cd35376dd512b049efbe85c--

Re: Problem filtering with a "like" expression

am 21.03.2011 12:10:01 von petya

Hi,

|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.

Peter Boros

On 03/21/2011 11:51 AM, Johan De Taeye wrote:
> I ran into this case where a "like" expression is not evaluated correctly if
> the pattern is an expression.
> The example below shows a case where *AAA* is not considered *like 'A' ||
> '%'*
> Is this a known limitation? Or a bug?
>
>
> create table lookup (
> name varchar(60)
> );
>
> insert into lookup (name) values ('AAA');
>
> select * from lookup where name like 'A%';
> => 1 record returned. OK
>
> select * from lookup where name like 'A' || '%';
> => returns nothing. INCORRECT!
> select * from lookup where name like ('A' || '%');
> => same as previous and returns nothing. INCORRECT!
>
>
> I reproduced this problem on win32 using versions 5.1 and 5.5.10
>
>
> Best regards,
>
> Johan
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Problem filtering with a "like" expression

am 21.03.2011 12:24:18 von Simcha

On Mon, 21 Mar 2011 11:51:27 +0100
Johan De Taeye wrote:

> insert into lookup (name) values ('AAA');
>
> select * from lookup where name like 'A%';
> => 1 record returned. OK
>
> select * from lookup where name like 'A' || '%';
> => returns nothing. INCORRECT!

The query is incorrect. The OR switch does not act as an ellipsis, and does not apply both values to the LIKE. You need to write LIKE X OR LIKE Y, as

select * from lookup where name like 'A' || or name like '%';

> select * from lookup where name like ('A' || '%');
> => same as previous and returns nothing. INCORRECT!

Again correct, you tried to match `name` against boolean TRUE (the evaluation of you expression).

>
> Best regards,
>
> Johan


--
Simcha Younger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Problem filtering with a "like" expression

am 21.03.2011 12:26:32 von Johan De Taeye

After updating the SQL_MODE, it works as I expect.

Thanks for your prompt replies!

Johan

-----Original Message-----
From: petya [mailto:petya@petya.org.hu]
Sent: Monday, March 21, 2011 12:10 PM
To: Johan De Taeye
Cc: mysql@lists.mysql.com
Subject: Re: Problem filtering with a "like" expression

Hi,

|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.

Peter Boros

On 03/21/2011 11:51 AM, Johan De Taeye wrote:
> I ran into this case where a "like" expression is not evaluated
> correctly if the pattern is an expression.
> The example below shows a case where *AAA* is not considered *like 'A'
> ||
> '%'*
> Is this a known limitation? Or a bug?
>
>
> create table lookup (
> name varchar(60)
> );
>
> insert into lookup (name) values ('AAA');
>
> select * from lookup where name like 'A%';
> => 1 record returned. OK
>
> select * from lookup where name like 'A' || '%';
> => returns nothing. INCORRECT!
> select * from lookup where name like ('A' || '%');
> => same as previous and returns nothing. INCORRECT!
>
>
> I reproduced this problem on win32 using versions 5.1 and 5.5.10
>
>
> Best regards,
>
> Johan
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org