1

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?

3
  • If the base column type of a number column is text, it's possible that in the attempt to convert the column to integer, there is a text value that cannot be converted to INTEGER, thus creating your error. It's the conversion to digit_num that would not work.
    – Caleb Carl
    Nov 16, 2023 at 19:53
  • Your code shows blank text as '', not a single space as ' '. Is that intended?
    – Caleb Carl
    Nov 16, 2023 at 20:28
  • @CalebCarl Yes, I'm trimming the field down to 0 characters or a null string, kerning on that font feels odd. And yes, cast can't convert the space to an integer, that's expected. The issue is that the subquery executes without issue when run independently, but the moment it is used as a subquery the way the optimizer processes the filters causes the cast operation to precede the operation filtering out the rows with a single space, so then I get the error. My suggestion is that the optimizer is incorrect in how it processes the filter.
    – MattB
    Nov 16, 2023 at 21:32

2 Answers 2

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.

2
  • If this is the case I'm still bothered by the implementation of subqueries. What purpose do they serve beyond providing an alternative syntax?
    – MattB
    Dec 4, 2023 at 17:52
  • @MattB That's a bit of a theoretical question, because one could ask the same question for most things in a SQL language. They exist to make it easier to logically code and solve problems with. But a fundamental law of most database engines is that the physical execution is left for the engine to decide on, for utmost performance capabilities. A subquery's purpose is to provide a way to inline a temporary query (akin to a view) that is localized and can only be referenced by an outer-query within scope. In short, it's like a localized view without having to create another database object.
    – J.D.
    Dec 4, 2023 at 20:36
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.