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?