jhc@fang.ATT.COM (John H. Carter) (05/25/89)
I have just uncovered a particularly nasty bug in Informix/SQL version
2.10.03C. A query that performs a join on two tables and is further
restricted by references to columns in BOTH tables gives erroneous
results. That is if 'where columnA = X' is specified, rows may be
returned which have a columnA of Y.
This problem seems to be linked to the ORDER BY clause in that if
that clause if omitted, everything works just fine.
I have tried this on an earlier version (2.10.00B) and it doesn't seem
to have the problem.
Has anyone out there seen this problem? Anybody got an explanation?
Does anyone have a later version that doesn't have this problem?
The following SQL commands will duplicate the problem:
create table customer (
firstname char(10),
lastname char(10),
custnumber integer
);
create table invoice (
invcnumber integer,
invcdate date,
custnumber integer
);
insert into customer (firstname,lastname,custnumber)
values ('john', 'smith', 1);
insert into customer (firstname,lastname,custnumber)
values ('bill', 'smith', 2);
insert into customer (firstname,lastname,custnumber)
values ('fred', 'jones', 3);
insert into invoice (invcnumber,invcdate,custnumber)
values (101, '1/1/89', 1);
insert into invoice (invcnumber,invcdate,custnumber)
values (102, '1/1/89', 2);
insert into invoice (invcnumber,invcdate,custnumber)
values (103, '1/1/89', 3);
insert into invoice (invcnumber,invcdate,custnumber)
values (104, '2/1/89', 1);
insert into invoice (invcnumber,invcdate,custnumber)
values (105, '2/1/89', 2);
insert into invoice (invcnumber,invcdate,custnumber)
values (106, '2/1/89', 3);
select
customer.firstname,
customer.lastname,
invoice.invcnumber,
invoice.invcdate
from
customer, invoice
where
customer.lastname = 'smith' and
customer.custnumber = invoice.custnumber and
invoice.invcdate = '1/1/89'
order by customer.lastname;
This query will return 3 rows on 2.10.03C:
firstname lastname invcnumber invcdate
fred jones 103 01/01/1989
john smith 101 01/01/1989
bill smith 102 01/01/1989
If this has already been discussed in this newsgroup, my apologies.
Please respond via e-mail if possible.
--
John Carter - Orlando, Fl. | Must have been a barge
UUCP att!codas!fang!jhc | coming through!
VOICE (407) 660-3377 | -- Calvin & Hobbesaland@infmx.UUCP (Dr. Scump) (05/29/89)
In article <316@fang.ATT.COM> jhc@fang.ATT.COM (John H. Carter) writes: >I have just uncovered a particularly nasty bug in Informix/SQL version >2.10.03C. A query that performs a join on two tables and is further >restricted by references to columns in BOTH tables gives erroneous >results. That is if 'where columnA = X' is specified, rows may be >returned which have a columnA of Y. > >This problem seems to be linked to the ORDER BY clause in that if >that clause if omitted, everything works just fine. > >I have tried this on an earlier version (2.10.00B) and it doesn't seem >to have the problem. > >Has anyone out there seen this problem? Anybody got an explanation? >Does anyone have a later version that doesn't have this problem? >... >If this has already been discussed in this newsgroup, my apologies. >Please respond via e-mail if possible. >-- >John Carter - Orlando, Fl. At the risk of pestering non-Informix users, I think that this one is serious enough to post. Unaffected members of the audience can hit 'n' now; you will *not* be quizzed on this at the end of your news session... :-] This is a known bug (Bug #3981, for reference). It affects engine versions 2.10.03B through .03G (SE and Turbo engines) in UNIX ports only. It is FIXED in engine versions 2.10.03J and greater. (The current version being ported is .03K). The symptoms of the problem are as you described. If you have a join between two tables and have filters on columns in *both* tables, *and* you have an ORDER BY, you can get surplus rows returned. Ouch. The problem has a couple of workarounds: 1) if your filter for one of the tables is limited to the join column, changing the filter to use the corresponding column in the other table (and therefore, the same table as the other filter) avoids the problem. [This does not apply to your example, as both filters are on non-join columns] 2) instead of using ORDER BY in the same SELECT statement, run the primary SELECT statement into a temporary table using the INTO TEMP clause. Then, select from that temporary table and use ORDER BY on this SELECT. In your example, do the following: select customer.firstname, customer.lastname, invoice.invcnumber, invoice.invcdate from customer, invoice where customer.lastname = 'smith' and customer.custnumber = invoice.custnumber and invoice.invcdate = '1/1/89' into temp wrkarnd; select * from wrkarnd order by lastname; this one works every time. As stated earlier, the problem is fixed effective with the .03J versions. Assuming that you are on an AT&T machine, the following machines have the version with this fix currently shipping: Machine O/S Current Version AT&T 3B2/300, 310, 400 UNIX Sys V Rel 3.1* .03K AT&T 3B2/500 UNIX Sys V Rel 3.1* .03K AT&T 3B2/600 UNIX Sys V Rel 3.1* .03K AT&T 3B20 UNIX Sys V Rel 2.1.0V3 .03J AT&T 6386 UNIX Sys V/386 V 3.2 .03J The shipping versions for the 3B2/700, 3B4000, and 3B5 do not yet have the fix. Hope this helps. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega