Factors that influence string comparison
There are several factors that influence how we compare, order, and group strings.
- Character Sets or Charsets: List of characters a computer can show or use, for example letters, numbers, punctuation, symbols, and emojis. The character set used in a database or a client determines which characters can be displayed.
- Examples of Charsets: ASCII, UNICODE, KANJISJIS.
- Collation: It allows users to specify language-specific rules for string comparison, such as letter case, trailing blanks, accent marks, and numeric string ordering, among others.
- Examples of Collations: ASCII, MULTINATIONAL, EBCDIC, etc.
- Standards:
- Most relational databases (engines) follow at least the ANSI/ISO SQL-92 specification. The relevant chapter for string comparison is section 8.2 <comparison predicate>.
- The I18N internationalisation protocol is the architectural approach to designing software that supports multiple languages and regional formats without requiring code rewrites.
- It is distinct from localisation (l10n), which is the content adaptation (translation, cultural formatting) performed after the code is internationalised.
OTF, standards and string comparison
Most OTF engines are not ANSI-compliant because they built their solutions on top of the Open-Source Iceberg Java APIs. These APIs follow the Java language’s approach to string comparison, which is not ANSI-compliant. Furthermore, the Delta Tables also have the Delta Kernel, which is written in Java.
Separately, most engines do not include the I18N Internationalisation protocols in their solutions, which strongly affects string comparisons, collation, order by, and group by.
Trailing spaces: example of string comparison behaviour in OTF
The ANSI standard requires padding character strings used for comparisons so that their lengths match before comparison (add spaces to the shorter string). Moreover, the comparison of strings must consider the International Collation and Case settings associated with the user’s logon session.
ANSI- and I18N-compliant solution.
CREATE TABLE T1 (A int, B varchar(6));
insert T1 (1, ‘dkn’);
insert T1 (2, ‘dkn ‘);
insert T1 (3, ‘dkn ‘);
SELECT * FROM T1 WHERE B=’dkn’;
——————————-
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
A B
———– ——
3 dkn
1 dkn
2 dkn
For example, Teradata is ANSI- and I18N-compliant when querying tables stored in BFS, Teradata-Managed OTF, and Native Externally Managed OTF.
Non-ANSI- and non-I18N-compliant solution.
CREATE TABLE T1 (A int, B varchar(6));
insert T1 (1, ‘dkn’);
insert T1 (2, ‘dkn ’);
insert T1 (3, ‘dkn ‘);
SELECT * FROM T1 WHERE B=’dkn’;
——————————-
*** Query completed. 1 row found. 2 columns returned.
*** Total elapsed time was 1 second.
A B
———– ——
1 dkn
OTF engines such as Spark, Dremio, etc. aren’t ANSI- or I18N-compliant. Users would need to use TRIM in their queries to remove trailing spaces and obtain output similar to the one on the left.
Case-sensitivity: another example of string comparison behaviour in OTF
The Iceberg Java is case-sensitive, which is not ANSI-compliant. So when users query a table to retrieve any entry that includes ‘hello’ in one of the columns, they have to use UPPER (column) to ensure they get ‘hello’ in all possible combinations (hello, HELLO, hEllo, etc.). This is cumbersome and resource-intensive.
ANSI- and I18N-compliant solution.
CREATE TABLE T1 (A int, B varchar(5));
insert T1 (1, ‘hello’);
insert T1 (2, ‘HELLO’);
insert T1 (3, ‘hEllo’);
SELECT * FROM T1 WHERE B=’hello’;
——————————-
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
A B
———– ——
3 hello
1 HELLO
2 hEllo
Teradata is ANSI- and I18N-compliant when querying tables stored in BFS, Teradata-Managed OTF, and Native Externally Managed OTF.
Non-ANSI- and non-I18N-compliant solution.
CREATE TABLE T1 (A int, B varchar(5));
insert T1 (1, ‘hello’);
insert T1 (2, ‘HELLO’);
insert T1 (3, ‘hEllo’);
SELECT * FROM T1 WHERE B=’hello’;
——————————-
*** Query completed. 1 row found. 2 columns returned.
*** Total elapsed time was 1 second.
A B
———– ——
1 hello
OTF engines such as Spark, Dremio, etc. aren’t ANSI- or I18N-compliant. Users would need to use TRIM in their queries to remove trailing spaces and obtain output similar to the one on the left.
Leave a Reply