Different behaviour of a nested query on 2 different SQL Server 2008 R2

Question

The following query returns two different results on two instances of SQL Server 2008 R2:

create table a(id int)

insert into a(id)
values(1)

insert into a(id)
values(2)

select 
   id,
   (select count(dbo.a.id) from dbo.a where dbo.a.id = "a"."id")
from a 
where a.id = 1

First machine gives

id          
----------- -----------
1           2

Second machine gives

id          
----------- -----------
1           1

We know how to fix this problem by using an explicit alias in the subquery. But because we use constructions like this a lot, it would be an immense work to do. So we would like to understand the problem.

Is there maybe an option in SQL Server to control this behaviour?

2013/07/22:

DBCC USEROPTIONS;SELECT @@VERSION; gives

Set Option                    Value
----------------------------- ----------------
textsize                      2147483647
language                      Deutsch
dateformat                    dmy
datefirst                     1
lock_timeout                  -1
quoted_identifier             SET
arithabort                    SET
ansi_null_dflt_on             SET
ansi_warnings                 SET
ansi_padding                  SET
ansi_nulls                    SET
concat_null_yields_null       SET
isolation level               read committed
------------------------------------
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
        Jun 17 2011 00:57:23
        Copyright (c) Microsoft Corporation

        Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

and

Set Option                    Value
----------------------------- ----------------
textsize                      2147483647
language                      Deutsch
dateformat                    dmy
datefirst                     1
lock_timeout                  -1
quoted_identifier             SET
arithabort                    SET
ansi_null_dflt_on             SET
ansi_warnings                 SET
ansi_padding                  SET
ansi_nulls                    SET
concat_null_yields_null       SET
isolation level               read committed
------------------------------------
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   
        Apr  2 2010 15:48:46   
        Copyright (c) Microsoft Corporation  
        Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

For the first server the query work how we want it.

2013/07/24

It seems not to depend on the server but on the databases.

Server:

Set Option                   Value
---------------------------- ----------------------------------------------
textsize                     2147483647
language                     Deutsch
dateformat                   dmy
datefirst                    1
lock_timeout                 -1
quoted_identifier            SET
arithabort                   SET
ansi_null_dflt_on            SET
ansi_warnings                SET
ansi_padding                 SET
ansi_nulls                   SET
concat_null_yields_null      SET
isolation level              read committed

(13 Zeile(n) betroffen)

Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
Jun 17 2011 00:57:23 
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)


(1 Zeile(n) betroffen)

Following query:

create table a(id int)
insert into a(id)
values(1)
insert into a(id)
values(2)
select * from a
select 
   id,
   (select count(dbo.a.id) from dbo.a where dbo.a.id = "a"."id")
from a 
where a.id = 1
drop table a
SELECT USER_NAME() AS CurrentUser;
SELECT SCHEMA_NAME() AS CurrentSchema;

First database gives:

id
-----------
1
2

(2 Zeile(n) betroffen)

id          
----------- -----------
1           2

(1 Zeile(n) betroffen)

CurrentUser
--------------------------
dbo

(1 Zeile(n) betroffen)

CurrentSchema
--------------------------
dbo

(1 Zeile(n) betroffen)

Second database gives:

id
-----------
1
2

(2 Zeile(n) betroffen)

id          
----------- -----------
1           1

(1 Zeile(n) betroffen)

CurrentUser
-----------------------
dbo

(1 Zeile(n) betroffen)

CurrentSchema
-----------------------
dbo

(1 Zeile(n) betroffen)

Answer 1

The simplest explanation could be that those a tables have diff. rows.

Otherwise, I can't reproduce the problem. A query that references objects without a explicit schema (ex. SELECT * FROM A) can give you diff. results on the same server if

1) That query is executed by diff. users.

2) The users are mapped to diff. schemas (ex. dbo and dbo2).

3) There are two(or more) tables/objects with the same name in diff. schemas (ex: dbo.A and dbo2.A).

Run this script in SSMS with SQLCMD mode selected (menu: Query > SQLCMD mode):

SET NOCOUNT ON;
CREATE DATABASE Test;
GO
USE Test;
GO

CREATE TABLE dbo.A(ID INT);
INSERT INTO dbo.A(ID) VALUES (1);
GO

CREATE SCHEMA dbo2;
GO
CREATE TABLE dbo2.A(ID INT);
INSERT INTO dbo2.A(ID) VALUES (1),(2);
GO
-- Test database has two tables [A] with diff. schema (dbo, dbo2)

USE master;
GO
CREATE LOGIN login_test_1 WITH PASSWORD='pas$w0Rd', DEFAULT_DATABASE=Test;
GO
CREATE LOGIN login_test_2 WITH PASSWORD='pas$w0Rd', DEFAULT_DATABASE=Test;
GO
USE Test;
GO
-- This user is mapped to schema [dbo]
CREATE USER user_test_1 FOR LOGIN login_test_1 WITH DEFAULT_SCHEMA=dbo;  
GO
GRANT SELECT ON dbo.A TO user_test_1;
GO
GRANT SELECT ON dbo2.A TO user_test_1;
GO
-- This user is mapped to schema [dbo2] (!)
CREATE USER user_test_2 FOR LOGIN login_test_2 WITH DEFAULT_SCHEMA=dbo2;
GO
GRANT SELECT ON dbo.A TO user_test_2;
GO
GRANT SELECT ON dbo2.A TO user_test_2;
GO

-- Test login_test_1
:CONNECT (local)\SQL2008R2 -U login_test_1 -P pas$w0Rd
SELECT USER_NAME() AS CurrentUser;
SELECT SCHEMA_NAME() AS CurrentSchema;
SELECT  *
FROM    A;
GO

-- Test login_test_2
:CONNECT (local)\SQL2008R2 -U login_test_2 -P pas$w0Rd
SELECT USER_NAME() AS CurrentUser;
SELECT SCHEMA_NAME() AS CurrentSchema;
SELECT  *
FROM    A;
GO
SET NOCOUNT OFF;

Results:

Connecting to (local)\SQL2008R2 as login_test_1...
CurrentUser
--------------------------------------------------------------------------------------------------------------------------------
user_test_1
CurrentSchema
--------------------------------------------------------------------------------------------------------------------------------
dbo
ID
-----------
1
Disconnecting connection from (local)\SQL2008R2 as login_test_1...

Connecting to (local)\SQL2008R2 as login_test_2...
CurrentUser
--------------------------------------------------------------------------------------------------------------------------------
user_test_2
CurrentSchema
--------------------------------------------------------------------------------------------------------------------------------
dbo2
ID
-----------
1
2
Disconnecting connection from (local)\SQL2008R2 as login_test_2...

In this case, login_test_1 (dbo) gets 1 row but login_test_2 (dbo2) gets 2 rows.

Cleanup script:

/*
DROP DATABASE Test;
DROP LOGIN login_test_1;
DROP LOGIN login_test_2;
GO
*/