Why did my WHERE clause affect my LEFT JOIN?

Question

I was writing some SQL that returned the description of a product based on a given code. I prepared my query with the assumption that codes with different capitalization could coexist. However, while filtering for results of my main table, I would like for my result to be case-sensitive. That is, searching some lowercase code would only return lowercase codes, and not the uppercase equivalents.

What I came to find, however, is that depending on the capitalization of the WHERE clause's condition, the result would change. I have looked into each table, and each has a different collation. I have tested with a RIGHT JOIN instead, and it properly joined the tables in both character cases. Furthermore, there was never a need to check for different cases: all codes should be uppercase by our system's standards and validations. So while fixing this was as simple as making sure my WHERE clause was uppercase, I was still left wondering why the query returned different results. I was taught that, during the SQL's query processing, the JOIN clause would run before the WHERE clause, ensuring that the latter would look through the joined result.

To reproduce this bug, firstly, I created a database with DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI.

Then, I created each table as such:

CREATE TABLE MAIN_TABLE (
  val VARCHAR(40) NOT NULL PRIMARY KEY,
  code VARCHAR(40) NOT NULL COLLATE UNICODE_CI
);

CREATE TABLE PRODUCTS  (
  name VARCHAR(40) NOT NULL PRIMARY KEY,
  code VARCHAR(40) NOT NULL COLLATE UNICODE
);

I then inserted the following test entries:

INSERT INTO MAIN_TABLE (val, code) VALUES ('This value is returned', 'ABC');
INSERT INTO PRODUCTS (name, code) VALUES ('My product', 'ABC');

And lastly, I performed the following query:

SELECT * FROM MAIN_TABLE
LEFT JOIN PRODUCTS 
ON MAIN_TABLE.code = PRODUCTS.code
WHERE MAIN_TABLE.code LIKE '%abc%'

Which resulted in:

MAIN_TABLE.code | MAIN_TABLE.val         | PRODUCTS.code | PRODUCTS.name
----------------+------------------------+---------------+---------------
 ABC            | This value is returned | null          | null

Notice that, although my query did find a result in MAIN_TABLE, the LEFT JOIN result came as null.

However, the exact same query, change the WHERE clause, returns a different result. So the query:

SELECT * FROM MAIN_TABLE
LEFT JOIN PRODUCTS 
ON MAIN_TABLE.code = PRODUCTS.code
WHERE MAIN_TABLE.code LIKE '%ABC%'

Ended up returning:

MAIN_TABLE.code | MAIN_TABLE.val         | PRODUCTS.code | PRODUCTS.name
----------------+------------------------+---------------+---------------
 ABC            | This value is returned | ABC           | My product

I have wondered--Was my understanding of the order of operations wrong? Does the database server read through the query, identifies that the WHERE clause's column (MAIN_TABLE.code) is the same one from the JOIN, and then changes how the JOIN is processed internally (for optimization or otherwise)? Or was this merely a bug with how Firebird interprets queries? I did expect some weird behavior, given the different collations, but I wasn't sure if it was a feature of sorts.

Why did my WHERE clause affect my LEFT JOIN?

I am not looking for ways to fix it, as I have found plenty - changing the collations, uppercasing my query, validating the code beforehand, etc.

My database runs on Firebird 3.0. I checked options for showing all messages, checked logs, and checked the query variants that did work. I saw nothing there that could give me an idea of why that happened.

user_1026 · Answer

I was taught that, during the SQL's query processing, the JOIN clause would run before the WHERE clause, ensuring that the latter would look through the joined result.

That's the correct description of the SQL semantics, so what you're seeing is most likely a bug.

The actual implementation of an RDBMS is more complex. At a high level, the SQL query is parsed into a logical query plan, which is a tree that closely follows the structure of the input SQL. The optimizer is then responsible for converting the logical plan to the actual steps (physical operators) that will run to produce the result.

The logical plan of your query will be something like:

read MAIN_TABLE        read PRODUCTS
       \                  /
      join them on MAIN_TABLE.code = PRODUCTS.code
              |
       apply filter MAIN_TABLE.code LIKE '%ABC%'

The optimizer's job is to figure out the efficient way to execute this. It can do transformations like predicate pushdown, where the filter (MAIN_TABLE.code LIKE '%ABC%') is pushed to the "read" stage, so that only relevant rows are read. Then the optimizer can decide on the physical operation it will use to read the input table (e.g. full-scan vs index-based reads).

(This is speculation on my part.) The optimizer could also notice that since you're joining on code, only the PRODUCTS that satisfy PRODUCTS.code LIKE '%ABC%' can be matched, so it could push down the predicate to the PRODUCTS scan operator as well. Depending on the collation on the input tables, if the optimizer is not very careful, the semantics of the LIKE '%ABC%' predicate could change, resulting in the behavior you're seeing.

user_3404097 · Answer

The SQL standard says that when comparing two character type expressions they must have the same collation. That's because whether two values are equal only makes sense within a given collation. Your calls to = & LIKE violate that.

It's not clear what you think you are asking for by writing that code.

Firebird documentation doesn't specify that it allow this. Unfortunately you say that you don't get any errors or warnings.

From (a draft of) the SQL standard:

Part 2: Foundation 2011-12-21

9.11 Equality operations
Syntax Rules
4) Let VS be the set of declared types of the operands of an equality operation. If VS comprises character string types, then the Syntax Rules of Subclause 9.15, "Collation determination", are applied with VS as TYPESET; let the collation to be used in the equality operation be the COLL returned from the application of those Syntax Rules.

9.15 Collation determination
Syntax Rules
4) Case:
e) Otherwise, every operand whose collation derivation is implicit shall have the same declared type collation IDTC and the collation to be used is IDTC.

You can get a case-insensitive comparison via appropriate use of UPPER or COLLATE.

Case-Insensitive Searching

For a case-insensitive search, the UPPER function could be used to convert both the search argument and the searched strings to upper-case before attempting a match

For strings in a character set that has a case-insensitive collation available, you can simply apply the collation, to compare the search argument and the searched strings directly.

See also: CONTAINING

Similarly LIKE documentation says:

Note
If you need to do a case-insensitive search for something enclosed inside a string ( LIKE '%Abc%' ), use of the CONTAINING predicate is recommended, in preference to the LIKE predicate.

@Arioch'The commented with a link to a bug report (that they added this example to). But the bug is just that no error is reported.

Why did my WHERE clause affect my LEFT JOIN?

2 Answers