Issues when creating percentage validity checks

[vijaykiran]

This check fails:

checks for retail_orders:
   - invalid_count(discount) < 1:
       valid format:  positive percentage

With this exception:

(.venv) $soda scan -V -d aws_postgres_retail orders.yml
Soda Core 0.0.1
Reading configuration file "/Users/albin/.soda/configuration.yml"
Reading SodaCL file "orders.yml"
Scan execution starts
Query aws_postgres_retail.retail_orders.aggregation[0]:
SELECT 
  COUNT(CASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? *(\d+([\.,]\d+)?|([\.,]\d+)) *% *$') THEN 1 END) 
FROM retail_orders
Query execution error in aws_postgres_retail.retail_orders.aggregation[0]: operator does not exist: double precision ~ unknown
LINE 2: ...ASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SELECT 
  COUNT(CASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? *(\d+([\.,]\d+)?|([\.,]\d+)) *% *$') THEN 1 END) 
FROM retail_orders
  +-> operator does not exist: double precision ~ unknown
LINE 2: ...ASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Metrics invalid_count were not computed for check invalid_count(discount) < 1
Scan summary:
1/1 query ERROR
  aws_postgres_retail.retail_orders.aggregation[0] [ERROR] 0:00:00.617506
SELECT 
  COUNT(CASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? *(\d+([\.,]\d+)?|([\.,]\d+)) *% *$') THEN 1 END) 
FROM retail_orders
    operator does not exist: double precision ~ unknown
    LINE 2: ...ASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? ...
                                                                 ^
    HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

1/1 check NOT EVALUATED: 
    retail_orders in aws_postgres_retail
      invalid_count(discount) < 1 [NOT EVALUATED]
        check_value: None
1 checks not evaluated.
2 errors.
Oops! 2 errors. 0 failures. 0 warnings. 0 pass.
ERRORS:
Query execution error in aws_postgres_retail.retail_orders.aggregation[0]: operator does not exist: double precision ~ unknown
LINE 2: ...ASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SELECT 
  COUNT(CASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? *(\d+([\.,]\d+)?|([\.,]\d+)) *% *$') THEN 1 END) 
FROM retail_orders
  +-> operator does not exist: double precision ~ unknown
LINE 2: ...ASE WHEN NOT (discount IS NULL) AND NOT (discount ~ '^ *\+? ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Metrics invalid_count were not computed for check invalid_count(discount) < 1
(.venv) $

[tombaeyens]

@vijaykiran I suggest this is a won t fix. We can't really improve the error message as we don't know the schema. If a user specifies a format, then we assume it's a text column. So then the only thing we can do is run the query and that fails. Analysing the error message to deduct that this is a text mismatch seems too complex imo. Reopen if you have concrete ideas on how we could improve the error message