SA0038 : The comparison expression evaluates to TRUE
Introduction
Section titled “Introduction”Comparison expressions that are always true can uncover logical errors in a SQL query.
Description
Section titled “Description”This problem arises when a SQL query contains comparison logic that is flawed, resulting in conditions that will inevitably evaluate to TRUE . Such logical errors can lead to inefficient queries, returning unintended results, or excessive data processing SQL Server environments.
For example:
-- Example of a problematic querySELECT * FROM Employees WHERE 1 = 1;In the above example, the condition 1 = 1 is always true, meaning that the filter does not restrict the result set at all. This can lead to inefficient queries where unnecessary data is retrieved, potentially impacting performance and complicating debugging efforts.
-
Queries may return more data than intended, affecting performance and increasing workload on SQL Server.
-
Logical flaws can cause confusion and make query logic harder to maintain and understand.
How to fix
Section titled “How to fix”Correct flawed comparison expressions to improve the logic and efficiency of SQL queries.
Follow these steps to address the issue:
1.Identify comparison expressions within your SQL queries that may always evaluate to TRUE . These comparisons can often be found in WHERE clauses.
2.Review the logic of each identified expression. Determine if it is necessary or if it results from a logical error. For example, a condition like 1 = 1 is always TRUE and should be removed.
3.Replace or remove unnecessary comparisons. Adjust your query to appropriately filter data. Make sure the conditions accurately reflect the intended logical criteria for the result set.
4.Test the revised query to ensure it returns the intended results and operates efficiently.
For example:
-- Example of corrected querySELECT * FROM Employees WHERE EmployeeStatus = 'Active';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”3 minutes per issue.
Categories
Section titled “Categories”Design Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- Comparison will always evaluate to TRUE.-- *****************************************
SELECT * FROM sys.objects oWHERE1 = 1 AND0 != 1 AND2 > 1 AND2>=2 AND2>=1 AND2>=2.0 AND2>=1.0 ANDobject_id = [object_id] ANDo.object_id = o.[object_id] AND'ab' = N'ab' AND'ab' > N'a' AND'ab' >= N'a' AND'ab' != N'a' AND'ab' = 'ab' AND'ab' > 'a' AND'ab' >= 'a' AND'ab' != 'a' ANDname = name AND[name] = [name] ANDo.[name] = [o].[name] AND--name >= name ANDname IN (name,'aaa','bbb') AND'a' IN ('a','b','c','d') AND1 IN (2,4,3,1) AND1 NOT IN (2,4,3) AND1 NOT IN (2,4,3-2) ANDname LIKE name ANDExists (SELECT 0)-- AND dbo.myFunction(a) = dbo.myFunction(a) -- Only if deterministic function
SELECT * FROM sys.objectsWHEREobject_id = [object_id] ORsys.objects.object_id = sys.objects.[object_id]
SELECT * FROM sys.objects WHERE Exists (SELECT 0 WHERE 1 = 0)SELECT * FROM sys.objects WHERE NOT Exists (SELECT 0 WHERE 1 = 1)Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0038 : The comparison expression evaluates to TRUE. | 27 | 5 |
| 2 | SA0038 : The comparison expression evaluates to TRUE. | 28 | 4 |
| 3 | SA0038 : The comparison expression evaluates to TRUE. | 29 | 2 |
| 4 | SA0038 : The comparison expression evaluates to TRUE. | 30 | 6 |
| 5 | SA0038 : The comparison expression evaluates to TRUE. | 32 | 5 |
| 6 | SA0038 : The comparison expression evaluates to TRUE. | 33 | 0 |
| 7 | SA0038 : The comparison expression evaluates to TRUE. | 6 | 2 |
| 8 | SA0038 : The comparison expression evaluates to TRUE. | 7 | 2 |
| 9 | SA0038 : The comparison expression evaluates to TRUE. | 8 | 2 |
| 10 | SA0038 : The comparison expression evaluates to TRUE. | 9 | 1 |
| … | |||
| 28 | SA0038 : The comparison expression evaluates to TRUE. | 39 | 22 |
| 29 | SA0038 : The comparison expression evaluates to TRUE. | 42 | 61 |