Skip to main content

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

  • January 27, 2015
  • 1 reply
  • 11 views
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?




    1 reply

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • January 27, 2015

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement