View Incident Report

Serious

Reported By: Danny Humphress
Reported On: 2/8/2004
For: Version 4.02 Build 1

# 1563 Using LIKE Operator With a Constant Single Character and a Wildcard Causes Incorrect Results

In some cases, it appears that LIKE ‘xx%’ (trailing wildcard) is not being optimized and it could be. I have a table that contains a CHAR(30) field named Call (a library call number). It is indexed as follows. I want to have a query return all that begin with the digit ‘0’.

Index
=====

CREATE NOCASE INDEX Call on InvCtrl (Call)

Queries
=======

The following query is optimized and returns 1,452 records in 2.3 
seconds:
 
SELECT * FROM InvCtrl
WHERE UPPER(Call)>=UPPER('0') AND UPPER(Call)<UPPER('1')

However, the following query is not being optimized and takes 10.1 
seconds to return the same 1,452 records:

SELECT * FROM InvCtrl
WHERE UPPER(Call) LIKE UPPER('0%')

Resolution

Fixed Problem on 2/10/2004 in version 4.03 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap