FIX: Incorrect result when you run a query that contains a conjunction and a disjunction in SQL Server Compact 3.5

Article translations Article translations
Article ID: 2494715
Expand all | Collapse all

SYMPTOMS

When you run a query against a Microsoft SQL Server Compact 3.5 database, an incorrect result is returned. This issue occurs when the query contains a conjunction and a disjunction. If we assume that the operators are AND and OR, the query must meet the following specific conditions:
  • One of the operands of the AND operator is an expression that contains the OR operator.
  • The operands of the OR operator contain expressions that reference the same column. Therefore, an index of the column can be used to match the expressions. 
  • The other operand of the AND operator contains a reference to a column that uses the same index as the column that is specified in the previous bullet point in this list. Therefore, the index can be also used on this expression.
For example, assume that you create the following tables that use indexes in a SQL Server Compact 3.5 database.
CREATE TABLE T1(
 C1 int,
 C2 int
)
GO
CREATE INDEX IND_T1 ON T1 (C1, C2)
GO
CREATE TABLE T2(
 C1 int,
 C2 int
)
GO
CREATE INDEX IND_T2 ON T2 (C1, C2)
GO
INSERT INTO T1 VALUES (1, 0)
GO
INSERT INTO T1 VALUES (2, 0)
GO
INSERT INTO T2 VALUES (1, -1)
GO
INSERT INTO T2 VALUES (2, 0)
GO
Then, you run the following query:
SELECT T1.C1 [T1.C1], T1.C2 [T1.C2], T2.C1 [T2.C1], T2.C2 [T2.C2]
FROM T1 INNER JOIN T2 ON
T1.C1 = T2.C1
AND (T1.C2 = T2.C2 OR T2.C2 = -1)
GO
After you run the query, you receive the following incorrect result:
Collapse this tableExpand this table
T1.C1T1.C2T2.C1T2.C2
2020
However, you expect the following result:
Collapse this tableExpand this table
T1.C1T1.C2T2.C1T2.C2
101-1
2020
Note This issue also occurs when you use the WHERE clause to perform a logical conjunction on two expressions. For example, this issue also occurs when you run the following query: 
SELECT T1.C1 [T1.C1], T1.C2 [T1.C2], T2.C1 [T2.C1], T2.C2 [T2.C2]
FROM T1 INNER JOIN T2 ON
T1.C1 = T2.C1
WHERE T1.C2 = T2.C2 OR T2.C2 = -1
GO

RESOLUTION

The hotfix that resolves this issue is included in cumulative update package 4 for SQL Server Compact 3.5 Service Pack 2.

For more information about how to obtain cumulative update package 4 for SQL Server Compact 3.5 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
2516828 Cumulative Update 4 for SQL Server Compact 3.5 Service Pack 2

WORKAROUND

To work around this issue, rewrite the query by using one of the following methods: 

Method 1

Use table hints in the query to disable the indexes. For example, run the following query to disable the indexes:
SELECT T1.C1 [T1.C1], T1.C2 [T1.C2], T2.C1 [T2.C1], T2.C2 [T2.C2]
FROM T1 INNER JOIN T2 WITH (INDEX(0)) ON
T1.C1 = T2.C1
AND (T1.C2 = T2.C2 OR T2.C2 = -1)
GO 

Method 2

Re-write the query to avoid meeting the conditions that are mentioned in the "Symptoms" section:
SELECT T1.C1 [T1.C1], T1.C2 [T1.C2], T2.C1 [T2.C1], T2.C2 [T2.C2]
FROM T1 INNER JOIN T2 ON
(T1.C1 = T2.C1 AND T2.C2 = -1) OR (T1.C1 = T2.C1 AND T1.C2 = T2.C2)

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2494715 - Last Review: April 27, 2011 - Revision: 2.0
Keywords: 
kbfix kbqfe kbexpertiseadvanced kbsurveynew KB2494715

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com