There is some strange behaviour with one of my tables in MySQL. When I
perform a query with an exact match, the result set comes back empty.
When I run it with a LIKE clause, I get some results.

Does Not Work:

mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE
ORDER_REF = '20061108/RAPTEST/RDL611004643' ORDER BY LOG_TIMESTAMP"

Works:

mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE
ORDER_REF LIKE '20061108/RAPTEST/RDL611004643%' ORDER BY LOG_TIMESTAMP"

There is certainly 1 record in MSG where MSG.ORDER_REF =
'20061108/RAPTEST/RDL611004643'

Why should MySQL not return it for the first query?

In article <1163105789.314191.279110@h54g2000cwb.googlegroups .com>,
yankeerivera@yahoo.com writes:

> There is certainly 1 record in MSG where MSG.ORDER_REF =
> '20061108/RAPTEST/RDL611004643'

> Why should MySQL not return it for the first query?

Probably because your order_ref ends with some whitespace.

Harald Fuchs wrote:
> In article <1163105789.314191.279110@h54g2000cwb.googlegroups .com>,
> yankeerivera@yahoo.com writes:
>
> > There is certainly 1 record in MSG where MSG.ORDER_REF =
> > '20061108/RAPTEST/RDL611004643'
>
> > Why should MySQL not return it for the first query?
>
> Probably because your order_ref ends with some whitespace.

Yes, I thought so, but that is not the case. I dumped the whole table
to a file and I checked. The value _is_ present and it does _not_
contain any leading or trailing whitespaces.

>> > There is certainly 1 record in MSG where MSG.ORDER_REF =
>> > '20061108/RAPTEST/RDL611004643'
>>
>> > Why should MySQL not return it for the first query?
>>
>> Probably because your order_ref ends with some whitespace.
>
> Yes, I thought so, but that is not the case. I dumped the whole table
> to a file and I checked. The value _is_ present and it does _not_
> contain any leading or trailing whitespaces.

.... or may be some non printable character(s),
check with:

select length(ORDER_REF) from MSG;

or

use cat -v on your dump file.


Regards
Dimitre