Meget mystisk! Select returnerer for mange rækker nogle gange.

Dette statement returnerer rækker som ikke hører til companyid 4580.
Subselecten TBB1 returnerer ingen id'er, hvilket er korrekt.
Hvis unionen testes separat så returnerer den de korrekte id'er.
Hvis en del af kriterierne fjernes, f.eks. bare "AND (TB_BuildingElements_published = 3)" så returneres de korrekte rækker.

Hvad er det jeg overser?
Er der fejl i MS SQL 2000 optimizeren? (Ja, jeg ved godt jeg burde skifte til MS SQL 2005, men jeg vil gerne vide om det vil løse problemet før jeg gør det)
Kan MS SQL have rod i sine indexer? I givet fald, kan de ryddes op?


SELECT DISTINCT TOP 1001 TB_BuildingElements.TB_BuildingElements_id id, TB_BuildingElements.TB_BuildingElements_name name

FROM TB_BuildingElements WITH (NOLOCK) WHERE (upper(TB_BuildingElements_VVSno) LIKE upper('4050%')) AND (TB_BuildingElements_published = 3) AND

(TB_BuildingElements_projectId = 2) AND (TB_BuildingElements_recyclebin = 0 OR TB_BuildingElements_recyclebin IS NULL) AND TB_BuildingElements_id IN

((SELECT DISTINCT TBB1.TB_BuildingElements_id

FROM TB_BuildingElements TBB1 WITH (NOLOCK) JOIN

TB_DefaultPermission WITH (NOLOCK) ON (TB_DefaultPermission_entityId = TBB1.TB_BuildingElements_id AND TB_DefaultPermission_entityType = 111 AND TB_DefaultPermission_readPermission = 1) JOIN TB_PermissionGroup WITH (NOLOCK) ON (TB_DefaultPermission_permissionGroupId = TB_PermissionGroup_id AND TB_PermissionGroup_projectId = 2) JOIN TB_UserMemberOf WITH (NOLOCK) ON (TB_UserMemberOf_permissionGroupId = TB_PermissionGroup_id) JOIN TB_ProjectUser WITH (NOLOCK) ON (TB_ProjectUser_id = TB_UserMemberOf_ProjectUserId AND TB_ProjectUser_userId = 646)

WHERE (TBB1.TB_BuildingElements_projectId = 2))

UNION (SELECT DISTINCT TBB2.TB_BuildingElements_id

FROM TB_BuildingElements TBB2 WITH (NOLOCK) JOIN

TB_DefaultPermission WITH (NOLOCK) ON (TB_DefaultPermission_entityId = TBB2.TB_BuildingElements_id AND TB_DefaultPermission_entityType = 111 AND TB_DefaultPermission_readPermission = 1) JOIN TB_Supplier WITH (NOLOCK) ON (TB_Supplier_buildingElementId = TBB2.TB_BuildingElements_id AND (TB_Supplier_contactPersonId = 646 OR TB_Supplier_companyId = 4580) AND TB_Supplier_recyclebin = 0) JOIN TB_Role WITH (NOLOCK) ON (TB_Role_permissionGroupId = TB_DefaultPermission_permissionGroupId AND TB_Role_systemRoleType = 3) JOIN TB_PermissionGroup WITH (NOLOCK) ON (TB_Role_permissionGroupId = TB_PermissionGroup_id AND TB_PermissionGroup_projectId = 2)

WHERE (TBB2.TB_BuildingElements_projectId = 2)))