ASA SQL User's Guide
Query Optimization and Execution
Semantic query transformations
Types of semantic transformations
By default, Adaptive Server Anywhere databases support case-insensitive string comparisons. Occasionally the optimizer may encounter queries where the user is explicitly forcing text conversion through the use of the UPPER() or LOWER() built-in functions when such conversion is unnecessary. Adaptive Server Anywhere will automatically eliminate this unnecessary conversion when the database's collating sequence permits it. Eliminating the case translation may then permit the comparison predicate to be used for indexed retrieval of the corresponding table.
On a case insensitive database, the query
SELECT * FROM customer WHERE UPPER(lname) = 'SMITH'
is rewritten internally as
SELECT * FROM customer WHERE lname = 'SMITH'
and the optimizer can now consider using an index on customer.lname.