Re[2]: Line Breaks Problem (\r\n) in Query

[Andrew]

Hello Brent,

   Thanks for reply.
   
   But I would like to know if we have desired behavior in latest
   versions of MySQL or it is a bug.

-- 
Best regards,
 Andrewmailto:[EMAIL PROTECTED]

Tuesday, April 5, 2005, 11:46:57 AM, you wrote:

BB I'm not sure what may have changed, but what you are searching on is
BB really a partial value of a field. You wouldn't normally think of \r\n
BB as characters, but they are are, just like a or b.
BB Whenever you are searching  on a partial value, you should use LIKE and
BB %.
BB SELECT * FROM table_name WHERE column_name LIKE 'value%';

BB You won't have to change any of your indexes or table structures, your
BB indexes will still be used for a fast search.

BB On Apr 5, 2005, at 9:18 AM, [EMAIL PROTECTED] wrote:

 Hello,
   I recall this problem again. No answer still. If I need to talk to
   other list(probably internals) just let me know.

   -
   I would like to describe the following problem and get an opinion
   from list members.

   I have simple table like
   describe table_name;

 +-+--+--+-+- 
 ++
   | Field   | Type | Null | Key | Default |
 Extra  |

 +-+--+--+-+- 
 ++
   | table_name_id   | int(10) unsigned |  | PRI | NULL  |
 auto_increment |
   | column_name | varchar(30)  |  | |   |
 |

 +-+--+--+-+- 
 ++

   My table values contains line breaks ('\r\n').
   For example I have 1 row with 'value\r\n' in 'column_name'.

   SELECT HEX(column_name)FROM table_name;
   will return
   76616C75650D0A

   I'm running the following query (I want to get records and DO NOT
   use '\r\n' im my WHERE clause)
   SELECT * FROM table_name WHERE column_name='value';

   On MySQL Ver 11.18 Distrib 3.23.55 that query returns 1 row,
   on MySQL Ver 14.7 Distrib 4.1.9 empty set is return.

   I did not found anything in change log files about that difference.

   So, I wonder if behavior of newer MySQL version is feature or bug or
   bug fix. What is correct?

   Thanks!

 -- 
 Andrew Bidochko


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: 
 http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re[2]: Line Breaks Problem (\r\n) in Query

[Joerg Bruehe]

Andrew,

you can tell the answer from Brent's info (not obvious in your mail, as
you put your new text above the quote):


Am Di, den 05.04.2005 schrieb Andrew um 18:05:
 Hello Brent,
 
Thanks for reply.

But I would like to know if we have desired behavior in latest
versions of MySQL or it is a bug.
 
 -- 
 Best regards,
  Andrewmailto:[EMAIL PROTECTED]
 
 Tuesday, April 5, 2005, 11:46:57 AM, you wrote:
 
 BB I'm not sure what may have changed, but what you are searching on is
 BB really a partial value of a field. You wouldn't normally think of \r\n
 BB as characters, but they are are, just like a or b.
 BB Whenever you are searching  on a partial value, you should use LIKE and
 BB %.
 BB SELECT * FROM table_name WHERE column_name LIKE 'value%';

As '\r\n' are characters, and you yourself wrote you store them in your
data, it is correct that MySQL does not return data for your equals
query:
   'value' = 'value\r\n'  MUST return false.

To get that row returned,
1) either use LIKE (as Brent proposed), or
2) include the '\r\n' in the string you search for, or
3) modify your data so that trailing control characters are not stored.

IMO, 3) might be the best way, depending on your application's neds.


HTH,
Joerg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]