> On 1/18/2013 2:48 PM, Kjell Rilbe wrote:
>> Den 2013-01-18 13:30 skrev Gary Benade såhär:
>>> On 1/18/2013 2:02 PM, Kjell Rilbe wrote:
>>>> Den 2013-01-18 12:56 skrev Kjell Rilbe såhär:
>>>>> Hi,
>>>>>
>>>>> 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 a line to the WHERE:
>>>>> 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?
>>>>>
>>>> Note: I tried inserting the results of the first query into a new table.
>>>> Then I did
>>>>
>>>> select *
>>>> from newtable
>>>> where "Kod" in ('17111', '17112', '17113', '21111', '21112',
>>>> '21113')
>>>>
>>>> It did not return any records. Still the second select does return
>>>> six records.
>>>>
>>>> Kjell
>>> I have a feeling it my be related to nulls in ECO_ID, what do you
>>> get when you run the following query?
>>>
>>> select B.*
>>> from "Branschkod" B
>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>> where coalesce(B."ECO_ID",'') <> coalesce(B2."ECO_ID",'')
>> No nulls in ECO_ID - it's a pk. Your query returns the same 2533
>> records as the first SQL above.
> What does this return?
>
> select * from
> {
> select B.*
> from "Branschkod" B
> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
> where B."ECO_ID" <> B2."ECO_ID"
> ) bb
> where bb."Kod" in ('17111', '17112', '17113', '21111', '21112',
> '21113')
>
>That query returns the same six rows as the one with the "in list"
>directly in the inner query. I had already tried that approach, actually.

Rows that are returned when there's a WHERE clause, but not if there's no WHERE clause doesn't make sense at all. What you could try, is to locate the error. If you do a backup & restore, do you observe the same behaviour on the restored copy? If not, that indicates that there is an error in your database file. If the error occurs on the restored copy as well, can you reproduce it using two separate tools, e.g. FlameRobin and isql? If so, it looks as if Firebird is to blame, though it's still uncertain whether it is a general error or something related to a particular character set or collation. What's the DDL of Namn, ECO_ID and Kod (I don't know enough to be of any more help, but find it more understandable if things like this occur if e.g. Namn is defined as a BLOB or a huge (VAR)CHAR or if you use a character set/collation that you've defined yourself)?

Set