strange problems with joins and IN (3.5.2b15)

[Steven Ophalvens]

Hi Ivan,

Is there an other way I can transmit the database? It contains
proprietary info from my customer.

I set all blob fields to null and zipped the database, which is
around 883Kb zipped.

Meanwhile some more detail of the problem : 
The problem persists as well if I format my sql in this way:

select Vragen.Id, Vragen.Vraag, leerdoel,Onderdelen.naam 
from vragen 
	join leerdoelen on vragen.leerdoelId = Leerdoelen.Id 
	join onderdelen on Leerdoelen.Onderdeel= Onderdelen.Id 
where 
Vragen.Id =-994275861
or Vragen.Id = -392650463
or Vragen.Id = -555668393
or Vragen.Id = 33654552
or Vragen.Id = 261273850
or Vragen.Id = -5637208580
or Vragen.Id =-5637208581

This seems to work as long as at most 5 results are correct. If I add a 6th
correct id, I get 0 rows, If I change that 6th id to a non-existing one,
I again get 5 rows, as expected.

Greetings,

Steven Ophalvens


-----Oorspronkelijk bericht-----
Van: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Namens Ivan Smahin
Verzonden: vrijdag 8 februari 2008 15:19
Aan: Valentina Developers
Onderwerp: Re: strange problems with joins and IN (3.5.2b15)

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

_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina

__________ NOD32 2277 (20070518) Informatie __________

Dit bericht is gecontroleerd door het NOD32 Antivirus Systeem.
http://www.nod32.nl