Simple bug with LIKE found...

------=_NextPart_000_001E_01C3156C.3D317AD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

SELECT * FROM anytable WHERE field1+field2 LIKE '%WORD%' always yields =
0 rows.

where anytable:
`Address1` varchar(45) NOT NULL default '',
`Address2` varchar(45) NOT NULL default '',
TYPE=3DMyISAM

Easy to reproduce and exists in v4.0.8 + the current stable v4.12.

A reply would be great (with an expected fix date would be even =
better!!)

Regards,
Darren Arrowsmith
Project Development Manager

Inet Business Solutions Ltd.
T: +44 (0)121 521 5572
F: +44 (0)121 521 5574
E: darren.arrowsmith@inet-solutions.com
------=_NextPart_000_001E_01C3156C.3D317AD0--

Re: Simple bug with LIKE found...

Hi,

If you want to concat field1 and field2 you have to use :

SELECT * FROM anytable WHERE CONCAT(field1,field2) LIKE '%WORD%'

Regards,
Jocelyn

----- Original Message -----
From: "Darren Arrowsmith"
To:
Sent: Thursday, May 08, 2003 3:15 PM
Subject: Simple bug with LIKE found...


SELECT * FROM anytable WHERE field1+field2 LIKE '%WORD%' always yields 0
rows.

where anytable:
`Address1` varchar(45) NOT NULL default '',
`Address2` varchar(45) NOT NULL default '',
TYPE=MyISAM

Easy to reproduce and exists in v4.0.8 + the current stable v4.12.

A reply would be great (with an expected fix date would be even better!!)

Regards,
Darren Arrowsmith
Project Development Manager

Inet Business Solutions Ltd.
T: +44 (0)121 521 5572
F: +44 (0)121 521 5574
E: darren.arrowsmith@inet-solutions.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Simple bug with LIKE found...

Darren,

Darren Arrowsmith wrote:
> SELECT * FROM anytable WHERE field1+field2 LIKE '%WORD%' always yields 0 rows.

0 rows is correct result because in MySQL + is arithmetic operator only so in your
case it first converts fileld1 and fiedl2 to numbers and then sums them. Obviously
no number can contain string WORD. This behaviour is required by ANSI SQL.

As Jocelyn said for string concatenation you should use CONCAT(field1, field2)


However this is very inneficient way as both using CONCAT() and LIKE '%WORD%' ill
effectively prebent any index usage.

Consider using MySQL Fulltext search capabilities

http://www.mysql.com/doc/en/Fulltext_Search.html

Best regards

--
I'm MySQL certified. Are you? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org