Closed
Description
In case the filtering (WHERE) clause of an SQL query contains at least two relational operators in a conjunction (AND) of which one is a comparison (<=, <, >= or >) and another is an inequality (!= or <>), the inequality can get ignored.
SELECT MIN(emp_no) FROM test_emp WHERE emp_no != 10001 AND salary > 50000
will wrongly return 10001.
This will happen despite the fact that the comparisons are done on different terms and only when the values of the comparisons are literals or foldable expressions such that the inequality's value lies outside the range of the comparison; in this case an incorrect optimisation is applied.
A workaround in this case is to replace the inequality with an NOT IN operator:
SELECT MIN(emp_no) FROM test_emp WHERE emp_no NOT IN (10001) AND salary > 50000