Redshift int casting from varchar - how to force early evaluation order?

Context: I have a varchar column in a segment event table that contains version numbers. Most of these are formatted nicely as X.Y.Z (major.minor.patch) where X, Y, and Z are integers. However, there are a few bad values, e.g. X.Y.Z-a, X.Y.Z-testing, etc. that I want to exclude entirely.

I've confirmed that the results of the following query are what I expect (no bad version vales):

select distinct app_version 
from table_name
where app_version ~ '^[0-9.]+$'

However, when I go to use this later on, I run into the following error.

SQL Error [XX000]: ERROR: Invalid digit, Value 'f', Pos 0, Type: Integer 
  Detail: 
  -----------------------------------------------
  error:  Invalid digit, Value 'f', Pos 0, Type: Integer 
  code:      1207
  context:   false
  query:     249112189
  location:  :0
  process:   query0_279_249112189 [pid=0]
  -----------------------------------------------

For example, the following gives the example above:

with valid_versions as (
    select distinct app_version 
    from table_name
    where app_version ~ '^[0-9.]+$'
) 

select some_col

from valid_versions
inner join table_name on valid_versions.app_version = table_name.app_version

where split_part(valid_versions.app_version, '.', 1)::INT = 5

I think this is due to order of operations - that Redshift is trying to do the int conversion on values containing bad values and then erroring. I tried putting the "good" values into a temp table and everything worked fine, but I'd like to avoid temp tables if possible.

The query also works fine if I don't cast to int at all, but I need to return a numeric for my query.

Is there a way to force Redshift to evaluate the CTE first so it doesn't error out when casting the values? Alternatively, is there a way to "try cast" the value that will e.g. return a null rather than erroring?

posted about 1 year ago

CC BY-SA 4.0

Sigma‭ staff

166 reputation 5 3 26 1