Access left joins returns incorrect rows

[enders]

Access left joins returns incorrect rows on views (queries) with
manual created columns

Steps to reproduce

Create a table 'Table1' with the following fields
Field1 number
Field2 number
Field3 number
Value1 text

Enter the following values
1,1,1,a
1,1,2,b
1,2,1,c
1,2,2,d
2,2,2,e
3,3,3,f
(6 rows)

Create a table 'Table2' with the following fields
Field1 number
Field2 number
Field3 number
Value2 text

Enter the following values
1,1,1,x
(1 row)

Create the following query 'Query1'
SELECT
table1.*,
table2.value2
FROM
Table1 LEFT JOIN Table2 ON
(Table1.field3 = Table2.field3) AND
(Table1.field2 = Table2.field2) AND
(Table1.field1 = Table2.field1);

This will return 6 rows with an x on the row 1,1,1,a,x
(all rows from table1 combined with rows that match from table2)

Create the following query 'Query2'
SELECT
1 as field1,
Table2.field2,
Table2.field3,
Table2.value2
FROM
Table2;

This query will return only one row. The same as table2
Notice the 1 value as field1

And create the following query 'Query3'
SELECT
Table1.*,
Query2.Value2
FROM
Table1 left JOIN Query2 ON
(Table1.field1 = Query2.field1) AND
(Table1.field2 = Query2.field2) AND
(Table1.field3 = Query2.field3);

This should mean give me all rows from table1 and a value2 from query2
if possible.
And this query returns 4 rows !!!!!!!

If you dump the result into a table3 (Query4)
SELECT * into table3 FROM Query2;

And then create a the following query (Query5)
SELECT
table1.*,
table3.value2
FROM
Table1 LEFT JOIN Table3 ON
Table1.field1 = Table3.field1 AND
Table1.field2 = Table3.field2 AND
Table1.field3 = Table3.field3;

You got the 6 rows back.

I have run the same thing on SQL2000 and I get 6 rows back for query3
I have run this on Access 97 and Access 2002 and I get 4 rows

My Question : is this a bug in Access ?

With regards,

Constantijn Enders

 

[Allen Browne]

Enders, I am able to reproduce the same symptoms you describe so this does
look like a bug.

Are you able to take this up with Microsoft support (support.microsoft.com)?
If you are not able to, let us know and we will try to get a response.

To confirm the bug, I simplified your example to just two tables, each with
just one field:
- Table1, with a number field named Table1ID;
- Table2, with a number field named Table2ID.

The intermediate query adds a literal value:
SELECT 3 AS MyLiteral, Table2.Table2ID
FROM Table2;

A Left Join on the table fields works correctly:
SELECT Table1.Table1ID
FROM Table1 LEFT JOIN Query1 ON Table1.Table1ID = Query1.Table2ID;

A Left Join on the literal value does NOT work:
SELECT Table1.Table1ID
FROM Table1 LEFT JOIN Query1 ON Table1.Table1ID = Query1.MyLiteral;

The last query (with the literal value in the join) behaves like an INNER
JOIN.

 

[Allen Browne]

Enders, I have contacted Microsoft about this, and they have started a
support incident. Will let you know of any progress.

 

[enders]

Okay, Thanks.

I did some research on the Knowledge base and I found the following article

You Receive Incorrect Results from an Outer Join Query
(http://support.microsoft.com/default.aspx?scid=kb;en-us;275058)

It looks almost the same but it isn't exactly the same.

The article said that This problem was corrected in Jet 4.0 SP4.

After installing Jet 4.0 SP7 I still have the problem.

I wonder if they make a patch for the Jet 3.51 engine.

Constantijn Enders