a very strange vertica regular expression error, different return values from different server

John_Y

we have two vertica clusters in our company, one for production and one for standby, we have an ETL job which calles the following regex sql script

select REGEXP_SUBSTR(TRIM(BOTH ' ' FROM '2323107'), '^\d{1,18}$', 1, 1, 'b')::INT AS  finalvalue

a few days ago, this script started to return null for each value we passed in, but still returns the expected value from our standby server, we have verified that the version for both clusters are the same

cluster 1

select REGEXP_SUBSTR(TRIM(BOTH ' ' FROM '2323107'), '^\d{1,18}$', 1, 1, 'b')::INT AS finalvalue

return 2323107

cluster 2
select REGEXP_SUBSTR(TRIM(BOTH ' ' FROM '2323107'), '^\d{1,18}$', 1, 1, 'b')::INT AS finalvalue

return null

Anyone can shred some light on how could this happen?

Sharon_Cutter

Differences in the "standard conforming strings" settings?  

 

dbadmin=> SET STANDARD_CONFORMING_STRINGS TO ON;

SET

dbadmin=> select REGEXP_SUBSTR(TRIM(BOTH ' ' FROM '2323107'), '^\d{1,18}$', 1, 1, 'b')::INT AS  finalvalue;

 finalvalue 

------------

    2323107

(1 row)

 

dbadmin=> SET STANDARD_CONFORMING_STRINGS TO OFF;

SET

dbadmin=> select REGEXP_SUBSTR(TRIM(BOTH ' ' FROM '2323107'), '^\d{1,18}$', 1, 1, 'b')::INT AS  finalvalue;

WARNING 4168:  Nonstandard use of escape in a string literal at or near "'^\d" at character 53

HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'

 finalvalue 

------------

           

(1 row)

See also the StandardConformingStrings configuration parameter.