Hey all,

I was presented with a strange... situation if you will. I will try to
explain what is going on as much as I can, without going too deep (look
for *** to identify differences):

Query1:

Select NAbills.Acct_Ref, CustAcct.Acct_ID, NABills.BillTotal,
NABills.CustBillNo
From NABills Inner join CustAcct on NABills.Acct_Ref = CustAcct.Acct_ID
Where CustAcct.CustID='VE' and NABills.CustBillNo Is Null
Order by NABills.VendorInfo;

Result:

ACCT_REF ACCT_ID BILLTOTAL CUSTBILLNO
======== ======= ========= ==========
18271 18271 330 <null>
13778 13778 46 <null>
100396 100396 35 <null>
13842 13842 101 <null>
18272 18272 175 <null>
14383 14383 35 <null>
16135 16135 35 <null>
14996 14996 42 <null>
20254 20254 35 <null>
16338 16338 50 <null>
20721 20721 35 <null>
100398 100398 280 <null>
18272 18272 69 <null>
16138 16138 234 <null>
4741 4741 13 <null>
13752 13752 13 <null>
16107 16107 187 <null>
18270 18270 41 <null>
13753 13753 1143 <null>
100396 100396 650 <null>

Plan:

PLAN SORT (JOIN (CUSTACCT INDEX (CUSTACCT_CUSTID),NABILLS INDEX
(NACUSTBILLNOX,FK_NABILLS_ACCTREF)))

______________________________________________________________________________________________________________

Query2:

Select NAbills.Acct_Ref, CustAcct.Acct_ID, NABills.BillTotal,
NABills.CustBillNo
From NABills ***Left*** join CustAcct on NABills.Acct_Ref =
CustAcct.Acct_ID
Where CustAcct.CustID='VE' and NABills.CustBillNo Is Null
Order by NABills.VendorInfo;

Result:

ACCT_REF ACCT_ID BILLTOTAL CUSTBILLNO
======== ======= ========= ==========
18271 18271 330 <null>
13778 13778 46 <null>
100396 100396 35 <null>
13842 13842 101 <null>
18272 18272 175 <null>
14383 14383 35 <null>
16135 16135 35 <null>
14996 14996 42 <null>
20254 20254 35 <null>
16338 16338 50 <null>
20721 20721 35 <null>
100398 100398 280 <null> *********
18272 18272 69 <null>
16138 16138 234 <null>
4741 4741 13 <null>
13752 13752 13 <null>
16107 16107 187 <null>
18270 18270 41 <null>
13753 13753 1143 <null>
100396 100396 650 <null>

Plan:

PLAN SORT (JOIN (NABILLS INDEX (NACUSTBILLNOX),CUSTACCT INDEX
(RDB$PRIMARY7)))

_______________________________________________________________________________

Now notice, I simply changed the first query from a inner join to a left
join. The recordsets should be identical, but strangely enough, a NEW
record appears with a left join. The peculiar thing is, looking at the
Acct_Ref/Acct_ID fields, this record SHOULD have shown up in the inner
join as well. What could possibly be going on here?








CustAcct MetaData:

/* Domain definitions */
CREATE DOMAIN CURRENCY AS NUMERIC(15, 3)
default NULL;

/* Table: CUSTACCT, Owner: Crazypeople */

CREATE TABLE CUSTACCT
(
ACCT_ID INTEGER NOT NULL,
CUSTID VARCHAR(4),
CUSTDIV VARCHAR(4),
UNIT VARCHAR(6),
ISELIGIBLE SMALLINT,
LASTELIGIBLEUPDATE TIMESTAMP,
CAR_REF INTEGER,
CUSTREF VARCHAR(16),
ISBILLED CHAR(1),
TOBILLEDDATE TIMESTAMP,
ADMFEE CURRENCY default NULL,
EXTRAADM CURRENCY default NULL,
LEASE_REF INTEGER,
NACCT_REF INTEGER,
UC_REF INTEGER,
DP_REF INTEGER,
MC_REF INTEGER,
ISEXEMPT CHAR(1),
CITYTAX DOUBLE PRECISION,
COUNTYTAX DOUBLE PRECISION,
STATETAX DOUBLE PRECISION,
OTHERTAX DOUBLE PRECISION,
TAXINGCITY VARCHAR(20),
TAXINGCOUNTY VARCHAR(20),
TAXINGSTATE VARCHAR(20),
TAXINGOTHER VARCHAR(20),
PICKUPREF INTEGER,
SALE_ID INTEGER,
LSSTARTDATE TIMESTAMP,
TAXRATE COMPUTED BY (stateTax + CountyTax + CityTax + otherTax),
LASTUPDATE TIMESTAMP,
EXEMPTNO VARCHAR(20),
COSTCODE VARCHAR(36),
CONTACT INTEGER,
MAINTADMFEE CURRENCY default NULL,
GASCARDADMFEE CURRENCY default NULL,
MAINTADMFEESTART TIMESTAMP,
GASCARDADMFEESTART TIMESTAMP,
MAINTFEETYPE VARCHAR(5),
TITLETO INTEGER,
LIENTO INTEGER,
ID_INFO COMPUTED BY (Case When CustDiv <> '' and CustDiv Is Not Null
Then CustID || '-' || CustDiv || '-' || Unit Else CustID || '-' || Unit
End),
PRIMARY KEY (ACCT_ID)
);

/* Index definitions for CUSTACCT */

CREATE INDEX ACCARX ON CUSTACCT(CAR_REF);
CREATE INDEX ACDP ON CUSTACCT(DP_REF);
CREATE INDEX ACLEASE ON CUSTACCT(LEASE_REF);
CREATE INDEX ACMC ON CUSTACCT(MC_REF);
CREATE INDEX ACNAREFX ON CUSTACCT(NACCT_REF);
CREATE INDEX ACPICKUPS ON CUSTACCT(PICKUPREF);
CREATE INDEX ACSALES ON CUSTACCT(SALE_ID);
CREATE INDEX CUSTACCT_CAR_REF_INDEX ON CUSTACCT(CAR_REF);
CREATE INDEX CUSTACCT_CUSTID ON CUSTACCT(CUSTID);
CREATE INDEX CUSTBILLED ON CUSTACCT(CUSTID, ISBILLED);
CREATE INDEX CUSTBILLED2 ON CUSTACCT(CUSTID, ISBILLED, ACCT_ID);
CREATE INDEX IDUNIT ON CUSTACCT(CUSTID, UNIT);
CREATE INDEX INFOX ON CUSTACCT(CUSTID, CUSTDIV, UNIT);

___________________________________________________________________________________________

NABills Metadata:

/* Domain definitions */
CREATE DOMAIN CURRENCY AS NUMERIC(15, 3)
default NULL;

/* Table: NABILLS, Owner: EDMALLOY */

CREATE TABLE NABILLS
(
NABILL_ID INTEGER NOT NULL,
ACCT_REF INTEGER,
CAR_REF INTEGER,
NA_REF INTEGER,
BILLDATE TIMESTAMP,
BILLSUBTTL DECIMAL(15,2),
BILLTAX DECIMAL(9,2),
INVOICEDDATE TIMESTAMP,
CUSTBILLDATE TIMESTAMP,
AMTPAID DECIMAL(15,2),
CUSTBILLNO INTEGER,
BILLTOTAL COMPUTED BY (BillSubTTL + BillTax),
TRANSCITY VARCHAR(30),
TRANSST VARCHAR(2),
MILEAGE INTEGER,
PLATE_NO VARCHAR(10),
CONINVNO INTEGER,
PAGE_1 INTEGER,
PAGE_2 INTEGER,
PAGE_3 INTEGER,
VENDOR VARCHAR(30) NOT NULL,
VENDORINFO VARCHAR(36) NOT NULL,
BILLMONTH COMPUTED BY (extract(month from billdate)),
BILLYEAR COMPUTED BY (extract(year from billdate)),
VENDORUPPER COMPUTED BY (upper(vendor)),
PAIDBATCHNO INTEGER,
LAST_PMT_DATE TIMESTAMP,
AMT_DUE CURRENCY default NULL,
CUSTPAIDAMT CURRENCY default NULL,
CUSTPAIDDATE TIMESTAMP,
PDCHECKNO VARCHAR(12),
REBATE CURRENCY default NULL,
PRIMARY KEY (NABILL_ID)
);

/* Index definitions for NABILLS */

CREATE INDEX ACCTMILEAGE ON NABILLS(ACCT_REF, MILEAGE);
CREATE INDEX NAACCT_REF ON NABILLS(ACCT_REF);
CREATE INDEX NABILLPAGE1X ON NABILLS(NABILL_ID, PAGE_1);
CREATE INDEX NABILLPAGE2X ON NABILLS(NABILL_ID, PAGE_2);
CREATE INDEX NABILLPAGE3X ON NABILLS(NABILL_ID, PAGE_3);
CREATE INDEX NABILLS_BILLDATE ON NABILLS(BILLDATE);
CREATE INDEX NABILLS_BILLTOTAL ON NABILLS(BILLSUBTTL, BILLTAX);
CREATE INDEX NABILLS_MILEAGE ON NABILLS(MILEAGE);
CREATE INDEX NABILLS_PAGE_1 ON NABILLS(PAGE_1);
CREATE INDEX NABILLS_X1 ON NABILLS(ACCT_REF, BILLDATE, MILEAGE,
BILLSUBTTL, BILLTAX, PAGE_1);
CREATE INDEX NACAR_REFX ON NABILLS(CAR_REF);
CREATE INDEX NACUSTBILLNOX ON NABILLS(CUSTBILLNO);
CREATE INDEX NANA_REFX ON NABILLS(NA_REF);
CREATE INDEX NAVENINFOX ON NABILLS(VENDOR, VENDORINFO);
CREATE INDEX PDBATCHX ON NABILLS(PAIDBATCHNO);
CREATE INDEX VENDORX ON NABILLS(VENDOR);
CREATE INDEX VENODRX ON NABILLS(VENDOR);
ALTER TABLE NABILLS ADD CONSTRAINT FK_NABILLS_ACCTREF FOREIGN KEY
(ACCT_REF) REFERENCES CUSTACCT (ACCT_ID);
ALTER TABLE NABILLS ADD CONSTRAINT FK_NABILLS_CARREF FOREIGN KEY
(CAR_REF) REFERENCES CARS (CAR_ID);