0000662: IN may return too many records

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0000662

[NexusDB] SQL Engine

minor

sometimes

2010-07-08 16:06

2010-09-03 13:31

Reporter

Eivind

View Status

public

Assigned To

hdanzl

Priority

normal

Resolution

fixed

Status

closed

Product Version

V3.0401 Release

Summary

0000662: IN may return too many records

Description

eivinds comment: it doesnt matter if the NOT IN clause is used in the join or made into a where clause; it returns more records than the other variant which to me looks like it should be identical. Looks like the NOT IN somehow includes records that should be excluded.

#L+

SELECT Taa, Tab, Tac, Tad, Tba, Tbb, Tbc, Tbd, Tca, Tcb, Tcc, Tcd,
       Tda, Tdb, Tdc, DD.Tno as Tdd,
       Eead, Eebd, Eecd, DD.E as EedD, Esda, Esdb, Esdc, DD.S as Escd
       INTO C2r3B16a FROM C2r3B15
       JOIN ZInn as DD on DD.W=Eedc and DD."N"=Escd
            and DD.Tno NOT IN (Tbb, Tbc, Tbd, Tcb, Tcc, Tcd, Tdb, Tdc);
// and DD.Tno<>Tbb and DD.Tno<>Tbc and DD.Tno<>Tbd
// and DD.Tno<>Tcb and DD.Tno<>Tcc and DD.Tno<>Tcd
// and DD.Tno<>Tdb and DD.Tno<>Tdc;

//SELECT * FROM C2r3B16 WHERE Tdc=132 and Tdd=114;
//SELECT * FROM C2r3B16a WHERE Tdc IN (132, 145);
SELECT * FROM C2r3B16a WHERE Tdd IN (Tbb, Tbc, Tbd, Tcb, Tcc, Tcd, Tdb, Tdc);
SELECT * INTO C2r3B16 FROM C2r3B16a WHERE Tdd NOT IN (Tbb, Tbc, Tbd, Tcb, Tcc, Tcd, Tdb, Tdc);

What is the difference between these two terms, since thy give different outcomes?

A)
            and DD.Tno NOT IN (Tbb, Tbc, Tbd, Tcb, Tcc, Tcd, Tdb, Tdc);

versus:

B)
            and DD.Tno<>Tbb and DD.Tno<>Tbc and DD.Tno<>Tbd
            and DD.Tno<>Tcb and DD.Tno<>Tcc and DD.Tno<>Tcd
            and DD.Tno<>Tdb and DD.Tno<>Tdc;

I wonder if DELPHI may have problems controlling some cache, or similar?

When I run the query in the attached file, the first term results in 8933 records too many into the C2r3B16a table.
This can be seen by comparing the results after running the same filter again.

Regards,

Ole Knudsen

Additional Information

Attached Files

Geoff-Problem.zip (54,430 bytes) 2010-07-08 16:06

Relationships

There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2010-07-08 16:06	Eivind	New Issue
2010-07-08 16:06	Eivind	Status	new => assigned
2010-07-08 16:06	Eivind	Assigned To	=> hdanzl
2010-07-08 16:06	Eivind	File Added: Geoff-Problem.zip
2010-07-29 18:34	hdanzl	Status	assigned => QA Testing
2010-07-29 18:34	hdanzl	Resolution	open => fixed
2010-07-29 18:34	hdanzl	Fixed in Version	=> V3.05 Release
2010-07-29 18:34	hdanzl	Target Version	=> V3.05 Release
2010-09-03 13:31	hdanzl	Status	QA Testing => closed

Mantis 1.1.0a3[^]

35 total queries executed.
31 unique queries executed.