On Mon, 9 Jun 2008 06:41:41 -0700 (PDT), jammypodger wrote:
> On 8 Jun, 02:26, Michael Austin <maus...@firstdbasource.com> wrote:
>> Michael Austin wrote:
>> > jammiepodger wrote:
>> >> I have been using MySQL on and off for a number of years but I am
>> >> stumped by this problem.
>>
>> >> I am doing a select which requires a join to a second table using an
>> >> account key, I have a WHERE clause which ensures that I only get the
>> >> correct type of account.
>> >> Basically the type of account is 'CUSTOMER' but if I use the code
>>
>> >> ...
>> >> WHERE ACCOUNT.TYPE="CUSTOMER"
>> >> ...
>>
>> >> Then I get no matches.
>> >> However, if I use
>>
>> >> ...
>> >> WHERE STRCMP(ACCOUNT.TYPE,"CUSTOMER")=0
>> >> ...
>> >> or
>> >> ...
>> >> WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
>> >> ...
>>
>> >> Then I get matches as expected.
>>
>> >> I should add that the ACCOUNT.TYPE field has only upper case content
>> >> and is 8 characters in length.
>> >> Version of MySQL is 5.0.27 running on Fedora Core 5.
>>
>> >> Could anyone explain why I cannot get a result set if I just use the
>> >> equals operator?
>>
>> >> Please note I first posted this to comp.databases - for some reason I
>> >> missed this group originally.
>>
>> > Try using the appropriate quote marks.
>> > You have:
>> > WHERE ACCOUNT.TYPE="CUSTOMER"
>>
>> > Try:
>> > WHERE ACCOUNT.TYPE='CUSTOMER' <-- this is the correct syntax.
>>
>> I get an error message when using double-quotes:
>>
>> mysql> select * from aa where a="CUSTOMER";
>> ERROR 1054 (42S22): Unknown column 'CUSTOMER' in 'where clause'
>> mysql> select * from aa where a='CUSTOMER';
>> +----------+------+------+
>> | a | b | c |
>> +----------+------+------+
>> | CUSTOMER | 1 | 2 |
>> +----------+------+------+
>> 1 row in set (0.00 sec)
>
> I didn't realise that using single quotes was best practice, I have
> never come across an implementation where it cared if I used single or
> double, however, on your advice I will use single quotes in future.
> Unfortunately it makes no difference to this query whether I use
> single or double quotes - I get the same result.
>
> I have been trying to reproduce this problem with a much smaller data
> set. The two tables in which I can reproduce this problem at will are
> too big for a sample data set - they each contain thousands of rows.
> The original problem can be reproduced using the data from one row in
> each table, but this only works if the rows are in the original tables
> - if I export just these two rows to a new database then the problem
> cannot be reproduced on the new data set.
> This would lead me to thinking that there must be some sort of index
> problem with one or other of the original tables.
> I have used the 'CHECK TABLE' command on each of the tables to check
> for this, is there anything else I can do?

Better give us a SHOW CREATE TABLE for ACCOUNT. One thing I'm wondering
about is whether collation is case-sensitive or not. That could cause
the = comparison to fail while the STRCMP would force it to
case-insenstive instead.


--
I picked up a Magic 8-Ball the other day and it said 'Outlook not so good.'
I said 'Sure, but Microsoft still ships it.'
-- Anonymous