Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!rutgers!att!codas!ablnc!fang!jhc
From: jhc@fang.ATT.COM (John H. Carter)
Newsgroups: comp.databases
Subject: Informix/SQL 2.10.03C Bug?
Keywords: SQL
Message-ID: <316@fang.ATT.COM>
Date: 24 May 89 18:52:27 GMT
Distribution: usa
Organization: AT&T, Maitland, FL
Lines: 75

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 & Hobbes