On 01/21/13 00:38, Kjell Rilbe wrote:
> 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.
>
Can you provide a test case?
------------------------------------------------------------------------------
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/learnnow-d2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
