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