strange problems with joins and IN (3.5.2b15)

[Ivan Smahin]

Hello Steven,

Friday, February 8, 2008, 4:11:18 PM, you wrote:

> Hello all,

> I have the following query :

> SELECT Vragen.Id, Vragen.Vraag, leerdoel,Onderdelen.naam 
> FROM vragen 
>         JOIN leerdoelen ON leerdoelId = Leerdoelen.Id 
>         JOIN onderdelen ON Onderdeel= Onderdelen.Id 
> WHERE Vragen.Id IN 
>         ( -32029668, 224357312, 860901721, -939585291, -1003271300)

> This works correctly UP TO 5 items in the IN clause (5 rows are returned),
> but as soon as a 6th item gets added, instead of 6 rows, I get 0 rows
> returned.


> If I drop the where clause completely, I get 0 rows returned (I expected
> 3075).

> If I change that to :

> select Vragen.Id, Vragen.Vraag, leerdoel, Onderdelen.naam
> from vragen, leerdoelen,onderdelen
> where
>         leerdoelId = Leerdoelen.Id 
> and
>         Onderdeel= Onderdelen.Id

> I mostly get 0 rows, but if I repeatedly execute it in vStudio, I
> sometimes get the correct result (3075 rows). I get the correct result
> maybe about 1 in 10 times. 
> I never get the correct result with the following sql:

> select Vragen.Id, Vragen.Vraag, leerdoel, Onderdelen.naam
> from vragen 
>         join leerdoelen on leerdoelId = Leerdoelen.Id 
>         join onderdelen on Onderdeel= Onderdelen.Id


> This is in a local database, running on 3.5.2b15 on Vista.

Please, report it to Mantis and attach some database which
shows up the bug.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com