2009-02-06

Microsoft Access SQL bug

There are many bugs in Microsoft Access. Here's one that I found today.

LEFT JOIN a table to a query where the query has a literal in the SELECT clause will replicate the literal for non joined records.
Table1
KeyMyText
1A
2B
3C
4D
Table2
KeymyNum
110
320
Query1 Introduce a literal.
SELECT Key, "XYZ" AS myLit, myNum
FROM Table2;
KeymyLitmyNum
1XYZ10
3XYZ20
Query2 The bug.
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query1 ON Table1.Key=Query1.Key;
KeyMyTextmyNummyLit
1A10XYZ
2BXYZ
3C20XYZ
4DXYZ
Query3 The fix.
SELECT DISTINCT *
FROM Query1
WHERE myLit="XYZ";
KeymyLitmyNum
1XYZ10
3XYZ20
Query4 Bug Free!
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query3 ON Table1.Key=Query3.Key;
KeyMyTextmyNummyLit
1A10XYZ
2B
3C20XYZ
4D