Query with strange behaviour
We are using Informix-OnLine Dynamic Server 7.10.UD1 and are faced with a
query which shows a very strange behaviour.
Description of the query :
--------------------------
Give a list of all the clients with their clientno and name, and the orderno
of their most recent order.
Tables used : CLIENT, ORDER
-----------
CLIENT :
------
Definition :
----------
CREATE TABLE client
( cno SERIAL NOT NULL,
name VARCHAR(25) NOT NULL,
fname VARCHAR(25) NOT NULL,
address VARCHAR(25) NOT NULL,
city VARCHAR(25) NOT NULL,
PRIMARY KEY (cno) CONSTRAINT unique_cno
);
Contents :
--------
1|VAN BELLE|WERNER|HUNDELGEMSE STEENWEG, 105|GENT|
2|HOLLANDS|MICHEL|LEENWEG, 15|WACHTEBEKE|
3|DE GRAEVE|LIEVEN|GUIDO GEZELLELAAN, 95|DEINZE|
4|MAENHOUT|FREDDY|KESSELAARSTRAAT, 5|KAPRIJKE|
5|DE LANGHE|JO|VAN AKENSTRAAT, 13|GENT|
6|LAROY|JAN|GANZENDRIES, 231|GENT|
7|LAMBERT|MAURICE|ZWIJNAARDSESTEENWEG, 133|GENT|
8|GOOSENS|CARLOS|BALGERHOEKE, 154|EEKLO|
9|GOVAERT|BERT|DONKLAAN, 17|BERLARE|
10|VERHELLEN|STEFAAN|BREDERODESTRAAT, 15|MELLE|
11|DEWULF|JOZEF|KERKSTRAAT, 66|OLSENE|
12|DE VEIRMAN|MIA|HAMSTRAAT, 15|WETTEREN|
13|DE ROO|GRETA|WESTSTRAAT, 54|SLEIDINGE|
14|DE COSTER|PATRICK|VANDENBROECKSTRAAT, 40|OOSTAKKER|
15|DE BOEVER|MARIA|BAAIGEMSTRAAT, 387|GAVERE|
16|COSYNS|LUC|TWEEKERKENSTRAAT, 132|ZOTTEGEM|
17|BOONE|ERIK|WALPOORTSTRAAT, 4|GENT|
18|WILLEMS|BETTY|STOEPESTRAAT, 41|ERTVELDE|
19|VERMAERCKE|KAREL|STATIONSTRAAT, 130|EKE|
20|VERMEIREN|JACOB|WEVERBOSLAAN, 44|GENTBRUGGE|
21|VAN WIJNSBERGHE|MICHEL|OOSTHOEK, 22B|LEMBERGE|
22|DE TAEYE|TANJA|ZWIJNAARDSESTWG, 100B|GENT|
23|VANHAUTE|ERIK|NAZARETHSESTEENWEG, 49|DEINZE|
24|MAES|FERDINAND|MECHELSESTRAAT, 24|LONDERZEEL|
25|LEFEVRE|JEANNE|WELVAARTSTRAAT, 48|AALST|
26|DE LEENHEER|JOHN|OMMEGANGSTRAAT, 35|ZELE|
27|EVERAERT|CLARA|MOLENSTRAAT, 62|MELDERT|
28|DE TAYE|WALTER|BOTERMELKSTRAAT, 3|ONKERZELE|
29|DE TROYER|AN|BOSVELD, 18|DENDERWINDEKE|
30|DERIEMAEKER|EDOUARD|ZONNESTRAAT, 17|RONSE|
31|DE SADELEER|BENJAMIN|BOSKOUTERLAAN, 21|ERPE|
32|DE LANGHE|JO|STATIONSTRAAT, 11|OUDENAARDE|
33|DE BILDE|ANTOON|HEIRSTRAAT, 118|DENDERMONDE|
34|BORGHMANS|HERMAN|HOPMARKT, 30|AALST|
35|VERLEYEN|MARK|DORPSTRAAT, 6|AALTER|
36|VAN WESEMAEL|KURT|STEENSTRAAT, 72|LEDE|
37|VAN WAEYENBERGHE|FREDERICK|GENTSTRAAT, 51|GERAARDSBERGEN|
38|VAN WIJNSBERGHE|ACHIEL|BRUSSELSESTEENWEG, 120|LEBEKE|
39|VAN HAVERBEKE|HARRY|BRUSSELSESTEENWEG, 147|AALTER|
40|VAN HAUWERMEIREN|JAN|ASTRIDPLEIN, 8|LEBEKE|
41|VAN DER PERRE|WIM|KOUTERSTRAAT, 9|AALST|
42|VANDERSNICK|ANN|PIETER HEIRMANSHOEK, 6|BERLARE|
43|VAN DEN BOSSCHE|WALTER|VOORUITZICHTSTRAAT, 83|AALST|
44|DE CORTE|JO|TER PLATEN, 5|GENT|
45|VERMAERKE|JOHAN|PONTSTRAAT, 72|DEINZE|
46|VANDEKERKHOVE|MARC|SMIDSESTRAAT, 58|GENT|
47|GIJSSENS|STEVEN|MULLEMSTRAAT, 6|TERNAT|
48|OPSTAELE|HANS|ZELLICKLAAN, 24|OOSTENDE|
49|DEVOS|WOUTER|STATIONSSTRAAT, 17|OOSTROZEBEKE|
50|VAN DE VELDE|GEERT|VEERSTRAAT, 84A|UITBERGEN|
ORDER :
-----
Definition :
----------
CREATE TABLE order
( ono SERIAL NOT NULL,
cno INTEGER NOT NULL,
odate DATE NOT NULL,
PRIMARY KEY (ono) CONSTRAINT unique_ono,
FOREIGN KEY (cno) REFERENCES client CONSTRAINT known_cno
);
Contents :
--------
1|1|01/09/95|
2|2|01/09/95|
3|20|04/09/95|
4|10|04/09/95|
5|7|06/09/95|
6|1|07/09/95|
7|2|08/09/95|
8|24|08/09/95|
9|15|11/09/95|
10|10|11/09/95|
11|1|13/09/95|
12|5|13/09/95|
13|21|14/09/95|
14|10|15/09/95|
15|7|18/09/95|
16|1|20/09/95|
17|2|21/09/95|
18|21|22/09/95|
19|16|26/09/95|
20|16|28/09/95|
21|11|02/10/95|
22|12|04/10/95|
23|30|06/10/95|
24|31|06/10/95|
25|27|06/10/95|
26|21|11/10/95|
27|26|13/10/95|
28|42|23/10/95|
29|45|27/10/95|
30|50|27/10/95|
Below 2 variants of the query are shown (only difference: order of the tables
in the FROM-clause!) :
QUERY_1 :
-------
SELECT klant.knr, naam, bnr
FROM bestelling, klant --
WHERE klant.knr = bestelling.knr
AND bnr = (SELECT MAX (bnr)
FROM bestelling
WHERE knr = klant.knr)
QUERY_2 :
-------
SELECT klant.knr, naam, bnr
FROM klant, bestelling --
WHERE klant.knr = bestelling.knr
AND bnr = (SELECT MAX (bnr)
FROM bestelling
WHERE knr = klant.knr)
1) On a first try (after some period of activity), QUERY_1 yielded the correct
result, namely :
knr naam bnr
1 VAN BELLE 16 <--
2 HOLLANDS 17 <--
5 DE LANGHE 12
7 LAMBERT 15
10 VERHELLEN 14 <--
11 DEWULF 21
12 DE VEIRMAN 22
15 DE BOEVER 9
16 COSYNS 20
20 VERMEIREN 3
21 VAN WIJNSBERGHE 26 <--
24 MAES 8
26 DE LEENHEER 27
27 EVERAERT 25
30 DERIEMAEKER 23
31 DE SADELEER 24
42 VANDERSNICK 28
45 VERMAERKE 29
50 VAN DE VELDE 30
However, QUERY_2, yielded an *incorrect* result : the 4 rows marked with '<--'
above were missing (apparently the clients with 3 or more orders).
2) We issued the command 'UPDATE STATISTICS' and tried again.
Now *both* queries yielded the same, *incorrect* result.
3) We exported/dropped/imported the database and tried again.
Now *both* queries yielded the correct result.
4) We again issued the command 'UPDATE STATISTICS' and retried.
Now *both* queries again yielded the *incorrect* result.
We also ran the queries on tables with less data, but could not reproduce the
strange behaviour described above (we always got the correct result).
Did anyone else experience problems with similar queries ?
Is this perhaps a known bug ?
Regards,
Ria.
-----------------------------------------------------------
Ria Vandenberghe Ria.Vandenberghe@rug.ac.be
UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory
Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM
tel: +32/9/264.55.10 fax: +32/9/264.58.42