Tags

, ,

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

I guess that queries like the following may be found throughout every application.

select *
  from emp
 where ename like 'SCOTT';

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

Those queries are confusing, error-prone and sometimes even wrong.

Confusing

If we find a LIKE expression in a query, the expectations would be to see some wildcards in the condition. Not having any wildcards in the query leads to a certain suspicion.

Error-Prone

When using the like operator instead of an equal sign we have to make sure that every wildcard character that should not be used as a wildcard is escaped. The most common wild card is the percent sign (%) … the one that is often missed is the underscore.

So basically this query is not only searching for an object called “ALL_TABLES” but also for something starting with “ALL” having any charcater at position 4 and ending with “TABLES”.

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

To correct this query we need to escape the underscore 

select table_name, comments
  from dictionary
 where table_name like 'ALL\_TABLES' escape '\';

or even better use an equal instead of the like operator.

Same same but different

Do comparisons without wildcard always return the same result regardless of using the LIKE operator or the equal sign?

Well….no, they dont.
There are not many cases where the CHAR datatype is still used, but nevertheless it is important to know, how this datatype behaves when it comes to equal comparisons.

Blank-Padded comparison semantics (Oracle – SQL Language Reference)

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

Nonpadded comparison semantics (Oracle – SQL Language Reference)

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

Example for blank-padded comparison

As you can see it does not matter how many spaces we add to the search string Hello as the database type is a CHAR and a blank-padded comparison is used.

create table test_char (x char(10));

insert into test_char (x) values ('Hello');

select *
from test_char
where x = 'Hello';

X
----------------
Hello

select *
from test_char
where x = 'Hello ';

X
----------------
Hello

However using LIKE for the comparison changes everything. A like comparison will never use the blank-padding semantic and therefore we are not able to find a row.

select *
from test_char
where x like 'Hello';

no rows selected

What is the datatype of a string literal?

According to the documentation (ORACLE SQL Language Reference) a string (or text or character) literal has the CHAR datatype and therefore also is compared using the blank-padding semantic.

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.