8

Prerequisites: In MS Access 2010 create the following tables:

CREATE TABLE ATBL(ID INT);
INSERT INTO ATBL(ID) VALUES (1);
INSERT INTO ATBL(ID) VALUES (2);
INSERT INTO ATBL(ID) VALUES (3);

CREATE TABLE BTBL(ID INT);
INSERT INTO BTBL(ID) VALUES (1);
INSERT INTO BTBL(ID) VALUES (2);

Also create a view called BVIEW which uses the following SELECT statement:

SELECT A.ID FROM ATBL AS A WHERE A.ID = 1 OR A.ID = 2

Now BVIEW should have the same contents as BTBL. Nevertheless the following two queries will return different results:

SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM  BTBL AS B WHERE B.ID=A.ID)
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM BVIEW AS B WHERE B.ID=A.ID)

The first query returns two records (1 and 2), but the second query returns all records from ATBL. What's wrong here? Am I missing something?

Gangnus
  • 24,044
  • 16
  • 90
  • 149
Alexander Tobias Bockstaller
  • 4,657
  • 4
  • 36
  • 58

1 Answers1

6

A view is really a saved SQL SELECT statement. At least, this is what a saved view in MS Access is. And you use the same inner variables A and B. IMHO, they are getting mixed. The last line really looks as 

SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM (SELECT A.ID FROM ATBL AS A WHERE A.ID = 1 OR A.ID = 2) AS B WHERE B.ID=A.ID)

Try to change some inner names, for example:

SELECT AA.ID FROM ATBL AS AA WHERE AA.ID = 1 OR AA.ID = 2

So, the last line will look as

SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM (SELECT AA.ID FROM ATBL AS AA WHERE AA.ID = 1 OR AA.ID = 2) AS B WHERE B.ID=A.ID)

So, as we see here, MS Access even doesn't know how to isolate aliases!

Gangnus
  • 24,044
  • 16
  • 90
  • 149
  • Ok, I got you wrong the first time, now that I see your SQL I understand what you meant. It's working now. Thanks for clarifying this behaviour! – Alexander Tobias Bockstaller Jan 31 '12 at 13:48
  • 2
    It is really terrible, isn't it? MS Access even doesn't know to isolate inner variables! – Gangnus Jan 31 '12 at 14:14
  • +1 Just to be clear, this must be considered a bug with the Access database engine, one with negligible prospects of ever being fixed and for the usual excuse i.e. that a customer may rely on the (broken) behaviour (and it is important to remember that the most important customer of the Access database engine is the Windows team!) – onedaywhen Jan 31 '12 at 14:42
  • @onedaywhen And MS Access is much less buggy than Excel and Excel is much less buggy than Word. I programmed a year in Word - it was almost impossible! – Gangnus Jan 31 '12 at 15:02