Hi,

I've asked this on the support list, but got no solutions or 
explanations. I was able to get the data I needed with an exists query, 
but the results described here remain unexplained, and since it appears 
to be a bug as far as I can see, I'm asking here.

This SQL returns 2533 records, none of which contain B."Kod" starting 
with 1711 or 2111 (checked thoroughly):

select B.*
from "Branschkod" B
inner join "Branschkod" B2 on B2."Namn" = B."Namn"
where B."ECO_ID" <> B2."ECO_ID"

Now, I add an "and" line to the WHERE clause:

select B.*
from "Branschkod" B
inner join "Branschkod" B2 on B2."Namn" = B."Namn"
where B."ECO_ID" <> B2."ECO_ID"
   and B."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')

This select DOES return six records, containing B."Kod" starting with 
1711 or 2111 (one for each code in the list).

How can this happen?

Both queries have this plan:
PLAN SORT (MERGE (SORT (B2 NATURAL), SORT (B NATURAL)))

Firebird 2.1, WI-V2.1.4.18393 on Windows Server 2008 64 bit.

Notes from replies on the support list:
- ECO_ID is a primary key and not nullable.
- The plan show no indices are used so I assume index corruption could 
not be the issue.

Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_123012
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to