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.

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.

Please post an export of the CREATE TABLE statement and one of the relevant
rows as an INSERT INTO export.

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.

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)

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?

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

Peter H. Coffin wrote:
> On Mon, 9 Jun 2008 06:41:41 -0700 (PDT), jammypodger wrote:
>> 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.
I asked for that 2 days ago!

On Mon, 9 Jun 2008 18:07:59 +0100, Paul Lautman wrote:
> Peter H. Coffin wrote:
>> On Mon, 9 Jun 2008 06:41:41 -0700 (PDT), jammypodger wrote:
>>> 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.
> I asked for that 2 days ago!

Then perhaps this would have been only a reminder.

--
30. All bumbling conjurers, clumsy squires, no-talent bards, and cowardly
thieves in the land will be preemptively put to death. My foes will surely
give up and abandon their quest if they have no source of comic relief.
--Peter Anspach's list of things to do as an Evil Overlord

On 9 Jun, 20:07, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Mon, 9 Jun 2008 18:07:59 +0100, Paul Lautman wrote:
> > Peter H. Coffin wrote:
> >> On Mon, 9 Jun 2008 06:41:41 -0700 (PDT), jammypodger wrote:
> >>> 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.
> > I asked for that 2 days ago!
>
> Then perhaps this would have been only a reminder.
>
> --
> 30. All bumbling conjurers, clumsy squires, no-talent bards, and cowardly
> * * thieves in the land will be preemptively put to death. My foes will surely
> * * give up and abandon their quest if they have no source of comic relief.
> * * * * * * * * --Peter Anspach's list of things to do as an Evil Overlord

Ok, create table for the two relevant tables;-

CREATE TABLE `LGdoca` (
`_AUTO_` int(11) default NULL,
`BATID` int(11) NOT NULL,
`BATCH` char(8) character set latin1 collate latin1_bin NOT NULL,
`SRCLT` char(2) character set latin1 collate latin1_bin NOT NULL,
`SRCLGR` char(2) character set latin1 collate latin1_bin NOT NULL,
`DOCSEQ` int(11) NOT NULL,
`LINESEQ` int(11) NOT NULL,
`RECTYPE` tinyint(3) unsigned NOT NULL,
`UNIQID` int(11) NOT NULL auto_increment,
`FMTNAME` char(6) character set latin1 collate latin1_bin NOT NULL,
`PANELNME` char(8) character set latin1 collate latin1_bin NOT NULL,
`LGRTYPE` tinyint(3) unsigned NOT NULL,
`LGRNAME` char(8) character set latin1 collate latin1_bin NOT NULL,
`LGRNUM` char(2) character set latin1 collate latin1_bin NOT NULL,
`BALID` int(11) NOT NULL,
`DOCTYPD` char(3) character set latin1 collate latin1_bin NOT NULL,
`PERIOD` tinyint(3) unsigned NOT NULL,
`DATE1` int(11) NOT NULL,
`REF1` char(10) character set latin1 collate latin1_bin NOT NULL,
`REF2` char(10) character set latin1 collate latin1_bin NOT NULL,
`REF3` char(10) character set latin1 collate latin1_bin NOT NULL,
`ACCOUNT` char(20) character set latin1 collate latin1_bin NOT NULL,
`ACCID` int(11) NOT NULL,
`AMT1` bigint(20) NOT NULL,
`AMT2` bigint(20) NOT NULL,
`AMT3` bigint(20) NOT NULL,
`AMT1DP` tinyint(3) unsigned NOT NULL,
`AMT2DP` tinyint(3) unsigned NOT NULL,
`AMT3DP` tinyint(3) unsigned NOT NULL,
`DEBIT` bigint(20) NOT NULL,
`CREDIT` bigint(20) NOT NULL,
`DRCR` tinyint(3) unsigned NOT NULL,
`STATUS` tinyint(3) unsigned NOT NULL,
`PRN` int(11) NOT NULL,
`POSTDATE` int(11) NOT NULL,
`ORIGLGR` char(2) character set latin1 collate latin1_bin NOT NULL,
`ERROR` tinyint(3) unsigned NOT NULL,
`RECON` char(1) character set latin1 collate latin1_bin NOT NULL,
`STMT` int(11) NOT NULL,
`MTCHAMT` bigint(20) NOT NULL,
`MTCHDATE` int(11) NOT NULL,
`MTCHREF` char(10) character set latin1 collate latin1_bin NOT NULL,
`LINKB` tinyint(3) unsigned NOT NULL,
`LINKC` tinyint(3) unsigned NOT NULL,
`LINKD` tinyint(3) unsigned NOT NULL,
`LINKE` tinyint(3) unsigned NOT NULL,
`LINKF` tinyint(3) unsigned NOT NULL,
`IMMPRINT` tinyint(3) unsigned NOT NULL,
`PRINTDOC` tinyint(3) unsigned NOT NULL,
`LINKU` tinyint(3) unsigned NOT NULL,
`DOCSTAT` char(1) character set latin1 collate latin1_bin NOT NULL,
`DOCTYPE` tinyint(3) unsigned NOT NULL,
`LETLEV` tinyint(3) unsigned NOT NULL,
`PRTCOPY` tinyint(3) unsigned NOT NULL,
`CURRCODE` char(2) character set latin1 collate latin1_bin NOT NULL,
`CURRGRP` char(2) character set latin1 collate latin1_bin NOT NULL,
`ADDR` int(11) NOT NULL,
`DATE2` int(11) NOT NULL,
`DATE3` int(11) NOT NULL,
`DATE4` int(11) NOT NULL,
`LINEDESC` char(20) character set latin1 collate latin1_bin NOT
NULL,
`LABELS` smallint(5) unsigned NOT NULL,
`LABTYP` tinyint(3) unsigned NOT NULL,
`GRPYEAR` tinyint(3) unsigned NOT NULL,
`LGRYEAR` tinyint(3) unsigned NOT NULL,
`CSHFLAG` tinyint(3) unsigned NOT NULL,
`NOPOSTUP` tinyint(3) unsigned NOT NULL,
`REF4` char(10) character set latin1 collate latin1_bin NOT NULL,
`CNTRYDSP` char(3) character set latin1 collate latin1_bin NOT NULL,
`CNTRYORG` char(3) character set latin1 collate latin1_bin NOT NULL,
`COMMCODE` char(8) character set latin1 collate latin1_bin NOT NULL,
`MODTRANS` char(1) character set latin1 collate latin1_bin NOT NULL,
`TERMSDEL` char(3) character set latin1 collate latin1_bin NOT NULL,
`TECCODE` char(1) character set latin1 collate latin1_bin NOT NULL,
`NOTCCODE` char(2) character set latin1 collate latin1_bin NOT NULL,
`NETMASS` bigint(20) NOT NULL,
`SSDCODE` int(11) NOT NULL,
`EECTXN` tinyint(3) unsigned NOT NULL,
`ADDR2` int(11) NOT NULL,
`NOPRINT` tinyint(3) unsigned NOT NULL,
`BRECNO` int(11) NOT NULL,
`CRECNO` int(11) NOT NULL,
`DRECNO` int(11) NOT NULL,
`ERECNO` int(11) NOT NULL,
`FRECNO` int(11) NOT NULL,
`URECNO` int(11) NOT NULL,
`PRTPANEL` char(8) character set latin1 collate latin1_bin NOT NULL,
`COMPLETE` tinyint(3) unsigned NOT NULL,
`TOTLSEQ000` smallint(5) unsigned NOT NULL,
`TOTLSEQ001` smallint(5) unsigned NOT NULL,
`TOTLSEQ002` smallint(5) unsigned NOT NULL,
`TOTLSEQ003` smallint(5) unsigned NOT NULL,
`CRETIME` int(11) NOT NULL,
`CREDATE` int(11) NOT NULL,
`CREUSER` char(8) character set latin1 collate latin1_bin NOT NULL,
UNIQUE KEY `INDEX0` (`UNIQID`),
UNIQUE KEY `INDEX1` (`BATID`,`DOCSEQ`,`LINESEQ`,`RECTYPE`)
) ENGINE=MyISAM AUTO_INCREMENT=72180 DEFAULT CHARSET=latin1 ;

CREATE TABLE `LGchartd` (
`_AUTO_` int(11) default NULL,
`COANAME` char(8) character set latin1 collate latin1_bin NOT NULL
default '',
`ACCOUNT` char(20) character set latin1 collate latin1_bin default
NULL,
`UNIQID` int(11) NOT NULL auto_increment,
`SHORTD` char(20) character set latin1 collate latin1_bin NOT NULL
default '',
`DESC1` char(40) character set latin1 collate latin1_bin default
NULL,
`DESC2` char(40) character set latin1 collate latin1_bin NOT NULL
default '',
`TYPE` tinyint(3) unsigned NOT NULL default '0',
`CLASS` tinyint(3) unsigned NOT NULL default '0',
`NATURE` tinyint(3) unsigned NOT NULL default '0',
`TB_KSECT` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`POST` tinyint(3) unsigned NOT NULL default '0',
`SUBPOST` tinyint(3) unsigned NOT NULL default '0',
`USERKEY000` char(20) character set latin1 collate latin1_bin NOT
NULL default '',
`USERKEY001` char(20) character set latin1 collate latin1_bin NOT
NULL default '',
`USERKEY002` char(20) character set latin1 collate latin1_bin NOT
NULL default '',
`TB_USER1` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_USER2` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_USER3` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_USER4` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_USER5` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_USER6` char(6) character set latin1 collate latin1_bin NOT NULL
default '',
`TB_BCOM` char(8) character set latin1 collate latin1_bin NOT NULL
default '',
`TAXTYP` tinyint(3) unsigned NOT NULL default '0',
`EMUCONV` tinyint(3) unsigned NOT NULL default '0',
`OLDCURR` char(2) character set latin1 collate latin1_bin NOT NULL
default '',
`BANKACC` int(11) NOT NULL default '0',
UNIQUE KEY `INDEX1` (`UNIQID`),
UNIQUE KEY `INDEX0` (`COANAME`,`ACCOUNT`),
KEY `INDEX2` (`COANAME`,`USERKEY000`),
KEY `INDEX3` (`COANAME`,`USERKEY001`),
KEY `INDEX4` (`COANAME`,`USERKEY002`)
) ENGINE=MyISAM AUTO_INCREMENT=75888 DEFAULT CHARSET=latin1;

I have moved on in my exploration of this problem, three separate
queries explain the problem in its entirety...

Q1:
select count(*) from LGdoca,LGchartd where
LGchartd.ACCOUNT=LGdoca.REF4 and LGchartd.COANAME='CUSTOMER' and
LGdoca.UNIQID=207 ;

This correctly results in 1 row found.

Q2:
select count(*) from LGdoca,LGchartd where
LGchartd.ACCOUNT=LGdoca.REF4 and LGchartd.COANAME='CUSTOMER' and
LGdoca.UNIQID>206 and LGdoca.UNIQID<208 ;

This incorrectly results in 0 rows found.

Q3:
select count(*) from LGdoca,LGchartd where
LGchartd.ACCOUNT=LGdoca.REF4 and left(LGchartd.COANAME,8)='CUSTOMER'
and LGdoca.UNIQID>206 and LGdoca.UNIQID<208 ;

This correctly results in 1 row found.


The query which fails (Q2) differs from the other two working queries
in ways which I would consider irrelevant.
The actual UNIQID that I picked at random for this example (207) is
not relevant. Every UNIQID I have tested has exhibited the same
behaviour.
Also, I understand that the queries could be rewritten / optimised
etc. but I am trying to find a reason for them not working as I expect
- I have already written the required query differently to side step
the problem on the production site.

As I stated earlier, this problem only occurs in the data set in its
current state. If I try to cut down either of the tables then the
problem no longer occurs.

Apologies for the long post, thanks to anyone who made it this far.

jammy

jammypodger wrote:
> On 9 Jun, 20:07, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>> On Mon, 9 Jun 2008 18:07:59 +0100, Paul Lautman wrote:
>>> Peter H. Coffin wrote:
>>>> On Mon, 9 Jun 2008 06:41:41 -0700 (PDT), jammypodger wrote:
>>>>> 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.
>>> I asked for that 2 days ago!
>> Then perhaps this would have been only a reminder.
>>
>> --
>> 30. All bumbling conjurers, clumsy squires, no-talent bards, and cowardly
>> thieves in the land will be preemptively put to death. My foes will surely
>> give up and abandon their quest if they have no source of comic relief.
>> --Peter Anspach's list of things to do as an Evil Overlord
>
> Ok, create table for the two relevant tables;-
>
> CREATE TABLE `LGdoca` (
> `_AUTO_` int(11) default NULL,
> `BATID` int(11) NOT NULL,
> `BATCH` char(8) character set latin1 collate latin1_bin NOT NULL,
> `SRCLT` char(2) character set latin1 collate latin1_bin NOT NULL,
> `SRCLGR` char(2) character set latin1 collate latin1_bin NOT NULL,
> `DOCSEQ` int(11) NOT NULL,
> `LINESEQ` int(11) NOT NULL,
> `RECTYPE` tinyint(3) unsigned NOT NULL,
> `UNIQID` int(11) NOT NULL auto_increment,
> `FMTNAME` char(6) character set latin1 collate latin1_bin NOT NULL,
> `PANELNME` char(8) character set latin1 collate latin1_bin NOT NULL,
> `LGRTYPE` tinyint(3) unsigned NOT NULL,
> `LGRNAME` char(8) character set latin1 collate latin1_bin NOT NULL,
> `LGRNUM` char(2) character set latin1 collate latin1_bin NOT NULL,
> `BALID` int(11) NOT NULL,
> `DOCTYPD` char(3) character set latin1 collate latin1_bin NOT NULL,
> `PERIOD` tinyint(3) unsigned NOT NULL,
> `DATE1` int(11) NOT NULL,
> `REF1` char(10) character set latin1 collate latin1_bin NOT NULL,
> `REF2` char(10) character set latin1 collate latin1_bin NOT NULL,
> `REF3` char(10) character set latin1 collate latin1_bin NOT NULL,
> `ACCOUNT` char(20) character set latin1 collate latin1_bin NOT NULL,
> `ACCID` int(11) NOT NULL,
> `AMT1` bigint(20) NOT NULL,
> `AMT2` bigint(20) NOT NULL,
> `AMT3` bigint(20) NOT NULL,
> `AMT1DP` tinyint(3) unsigned NOT NULL,
> `AMT2DP` tinyint(3) unsigned NOT NULL,
> `AMT3DP` tinyint(3) unsigned NOT NULL,
> `DEBIT` bigint(20) NOT NULL,
> `CREDIT` bigint(20) NOT NULL,
> `DRCR` tinyint(3) unsigned NOT NULL,
> `STATUS` tinyint(3) unsigned NOT NULL,
> `PRN` int(11) NOT NULL,
> `POSTDATE` int(11) NOT NULL,
> `ORIGLGR` char(2) character set latin1 collate latin1_bin NOT NULL,
> `ERROR` tinyint(3) unsigned NOT NULL,
> `RECON` char(1) character set latin1 collate latin1_bin NOT NULL,
> `STMT` int(11) NOT NULL,
> `MTCHAMT` bigint(20) NOT NULL,
> `MTCHDATE` int(11) NOT NULL,
> `MTCHREF` char(10) character set latin1 collate latin1_bin NOT NULL,
> `LINKB` tinyint(3) unsigned NOT NULL,
> `LINKC` tinyint(3) unsigned NOT NULL,
> `LINKD` tinyint(3) unsigned NOT NULL,
> `LINKE` tinyint(3) unsigned NOT NULL,
> `LINKF` tinyint(3) unsigned NOT NULL,
> `IMMPRINT` tinyint(3) unsigned NOT NULL,
> `PRINTDOC` tinyint(3) unsigned NOT NULL,
> `LINKU` tinyint(3) unsigned NOT NULL,
> `DOCSTAT` char(1) character set latin1 collate latin1_bin NOT NULL,
> `DOCTYPE` tinyint(3) unsigned NOT NULL,
> `LETLEV` tinyint(3) unsigned NOT NULL,
> `PRTCOPY` tinyint(3) unsigned NOT NULL,
> `CURRCODE` char(2) character set latin1 collate latin1_bin NOT NULL,
> `CURRGRP` char(2) character set latin1 collate latin1_bin NOT NULL,
> `ADDR` int(11) NOT NULL,
> `DATE2` int(11) NOT NULL,
> `DATE3` int(11) NOT NULL,
> `DATE4` int(11) NOT NULL,
> `LINEDESC` char(20) character set latin1 collate latin1_bin NOT
> NULL,
> `LABELS` smallint(5) unsigned NOT NULL,
> `LABTYP` tinyint(3) unsigned NOT NULL,
> `GRPYEAR` tinyint(3) unsigned NOT NULL,
> `LGRYEAR` tinyint(3) unsigned NOT NULL,
> `CSHFLAG` tinyint(3) unsigned NOT NULL,
> `NOPOSTUP` tinyint(3) unsigned NOT NULL,
> `REF4` char(10) character set latin1 collate latin1_bin NOT NULL,
> `CNTRYDSP` char(3) character set latin1 collate latin1_bin NOT NULL,
> `CNTRYORG` char(3) character set latin1 collate latin1_bin NOT NULL,
> `COMMCODE` char(8) character set latin1 collate latin1_bin NOT NULL,
> `MODTRANS` char(1) character set latin1 collate latin1_bin NOT NULL,
> `TERMSDEL` char(3) character set latin1 collate latin1_bin NOT NULL,
> `TECCODE` char(1) character set latin1 collate latin1_bin NOT NULL,
> `NOTCCODE` char(2) character set latin1 collate latin1_bin NOT NULL,
> `NETMASS` bigint(20) NOT NULL,
> `SSDCODE` int(11) NOT NULL,
> `EECTXN` tinyint(3) unsigned NOT NULL,
> `ADDR2` int(11) NOT NULL,
> `NOPRINT` tinyint(3) unsigned NOT NULL,
> `BRECNO` int(11) NOT NULL,
> `CRECNO` int(11) NOT NULL,
> `DRECNO` int(11) NOT NULL,
> `ERECNO` int(11) NOT NULL,
> `FRECNO` int(11) NOT NULL,
> `URECNO` int(11) NOT NULL,
> `PRTPANEL` char(8) character set latin1 collate latin1_bin NOT NULL,
> `COMPLETE` tinyint(3) unsigned NOT NULL,
> `TOTLSEQ000` smallint(5) unsigned NOT NULL,
> `TOTLSEQ001` smallint(5) unsigned NOT NULL,
> `TOTLSEQ002` smallint(5) unsigned NOT NULL,
> `TOTLSEQ003` smallint(5) unsigned NOT NULL,
> `CRETIME` int(11) NOT NULL,
> `CREDATE` int(11) NOT NULL,
> `CREUSER` char(8) character set latin1 collate latin1_bin NOT NULL,
> UNIQUE KEY `INDEX0` (`UNIQID`),
> UNIQUE KEY `INDEX1` (`BATID`,`DOCSEQ`,`LINESEQ`,`RECTYPE`)
> ) ENGINE=MyISAM AUTO_INCREMENT=72180 DEFAULT CHARSET=latin1 ;
>
> CREATE TABLE `LGchartd` (
> `_AUTO_` int(11) default NULL,
> `COANAME` char(8) character set latin1 collate latin1_bin NOT NULL
> default '',
> `ACCOUNT` char(20) character set latin1 collate latin1_bin default
> NULL,
> `UNIQID` int(11) NOT NULL auto_increment,
> `SHORTD` char(20) character set latin1 collate latin1_bin NOT NULL
> default '',
> `DESC1` char(40) character set latin1 collate latin1_bin default
> NULL,
> `DESC2` char(40) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TYPE` tinyint(3) unsigned NOT NULL default '0',
> `CLASS` tinyint(3) unsigned NOT NULL default '0',
> `NATURE` tinyint(3) unsigned NOT NULL default '0',
> `TB_KSECT` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `POST` tinyint(3) unsigned NOT NULL default '0',
> `SUBPOST` tinyint(3) unsigned NOT NULL default '0',
> `USERKEY000` char(20) character set latin1 collate latin1_bin NOT
> NULL default '',
> `USERKEY001` char(20) character set latin1 collate latin1_bin NOT
> NULL default '',
> `USERKEY002` char(20) character set latin1 collate latin1_bin NOT
> NULL default '',
> `TB_USER1` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_USER2` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_USER3` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_USER4` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_USER5` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_USER6` char(6) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TB_BCOM` char(8) character set latin1 collate latin1_bin NOT NULL
> default '',
> `TAXTYP` tinyint(3) unsigned NOT NULL default '0',
> `EMUCONV` tinyint(3) unsigned NOT NULL default '0',
> `OLDCURR` char(2) character set latin1 collate latin1_bin NOT NULL
> default '',
> `BANKACC` int(11) NOT NULL default '0',
> UNIQUE KEY `INDEX1` (`UNIQID`),
> UNIQUE KEY `INDEX0` (`COANAME`,`ACCOUNT`),
> KEY `INDEX2` (`COANAME`,`USERKEY000`),
> KEY `INDEX3` (`COANAME`,`USERKEY001`),
> KEY `INDEX4` (`COANAME`,`USERKEY002`)
> ) ENGINE=MyISAM AUTO_INCREMENT=75888 DEFAULT CHARSET=latin1;
>
> I have moved on in my exploration of this problem, three separate
> queries explain the problem in its entirety...
>
> Q1:
> select count(*) from LGdoca,LGchartd where
> LGchartd.ACCOUNT=LGdoca.REF4 and LGchartd.COANAME='CUSTOMER' and
> LGdoca.UNIQID=207 ;
>
> This correctly results in 1 row found.
>
> Q2:
> select count(*) from LGdoca,LGchartd where
> LGchartd.ACCOUNT=LGdoca.REF4 and LGchartd.COANAME='CUSTOMER' and
> LGdoca.UNIQID>206 and LGdoca.UNIQID<208 ;
>
> This incorrectly results in 0 rows found.
>
> Q3:
> select count(*) from LGdoca,LGchartd where
> LGchartd.ACCOUNT=LGdoca.REF4 and left(LGchartd.COANAME,8)='CUSTOMER'
> and LGdoca.UNIQID>206 and LGdoca.UNIQID<208 ;
>
> This correctly results in 1 row found.
>
>
> The query which fails (Q2) differs from the other two working queries
> in ways which I would consider irrelevant.
> The actual UNIQID that I picked at random for this example (207) is
> not relevant. Every UNIQID I have tested has exhibited the same
> behaviour.
> Also, I understand that the queries could be rewritten / optimised
> etc. but I am trying to find a reason for them not working as I expect
> - I have already written the required query differently to side step
> the problem on the production site.
>
> As I stated earlier, this problem only occurs in the data set in its
> current state. If I try to cut down either of the tables then the
> problem no longer occurs.
>
> Apologies for the long post, thanks to anyone who made it this far.
>
> jammy


Apparently you are using a CHAR field that may have been space padded.

Try this:

select '|'||LGchartd.COANAME||'|' from LGchartd where LGchartd.COANAME
like 'CUSTOMER%';

This should produce something like

|CUSTOMER|

or more than likely because you are using latin1_bin
|CUSTOMER | (Customer with up to 12 spaces)

You should investigate the difference between CHAR and VARCHAR - they
will behave differently depending on the collating sequence and some
other factors. Although MySQL did do some quite silly things with
respect to data types. (ie not completely ANSI/Industry standard.)