Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

Question

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single space is inserted.

Using this table if I do this:

SELECT text_num::INTEGER AS digit_num
FROM relevant_table
WHERE TRIM(text_num) != ''

Then the query executes without issue. However, if I use this as a subquery with an outer where clause:

SELECT * FROM (
SELECT text_num::INTEGER AS digit_num
FROM relevant_table
WHERE TRIM(text_num) != '') AS subquery
WHERE digit_num BETWEEN 2 AND 5;

It produces an error associated with attempting to cast a space to an integer value.

Looking into the explain plan the source of the error feels obvious. The optimizer has constructed a filter operating off of "relevant_table" which is attempting to directly filter the column like this

text_num::INTEGER >= 3 AND text_num::INTEGER <= 5 AND TRIM(text_num) != ''

My gut says this is not correct. SQL operates, usually, in a sort of set based manner, and the actions of the optimizer aren't forcing any order of operations on the application of the filters. I would think that the use of a subquery should make it explicit that the conditions inside that subquery have to be satisfied first even in the case where the optimizer is flattening out the query.

But I don't see an objective standard for this. Is it an error in the optimizer or is this just expected functionality or simply a quirk of the optimizer to be worked around?

Answer 1

My gut says this is not correct. SQL operates, usually, in a sort of set based manner, and the actions of the optimizer aren't forcing any order of operations on the application of the filters.

The logical clauses of a query are not guaranteed to be executed in the same exact order of operations when the query plan is physically generated and executed. The SQL Engine is free to re-order logical operators, in the way it feels is most physically performant, as long as in doing so would provide the same logical results in the end. This is a fact of how query planners work, across most modern database systems, and is not unique to Redshift.

It is the responsibility of the database developer to architect the database and design their queries in a way that is not error prone to this. That typically includes storing numbers that are ultimately queried as numbers, in a numerical data type.

In short, this is expected, and specifically because of the physical order of operations of the query plan that was selected. Sometimes such an occurrence is nondeterministic and a different query plan for the same exact query would not result in such an error. But again, it's up to the developer to design and code vigilantly to prevent this from the get-go.

Answer 2

Many optimizers do indeed work this way (both PostgreSQL and SQL Server). They do not expect a runtime error in the code, so they reorder clauses and simplify subqueries in any way they see fit. A similar issue is apparent in divide-by-zero. This is entirely by design, as it hugely simplifies compilation.

The only guaranteed method of ensuring that a conversion or calculation short-circuits correctly and does not error, is to use a construct that guarantees it. The only ones in Postgres are CASE and NULLIF. In SQL Server you can also use TRY_CAST TRY_CONVERT and IIF.

Furthermore, even CASE and NULLIF don't work when the short-circuited expression is a constant or an aggregation, as that is normally transformed out of the enclosing CASE. This is noted in the docs.