Some test case errors

[brown3007]

Dear all,

I faced some troubles deploying test cases:

1. Column test type "Column values missing count(1,10)":

• Pipeline: success, test: aborted for string/character datatype in both clickhouse and postgres
• Clickhouse, datatype int: Could not run test case Test_int: Orig exception: Code: 32. DB::Exception: Attempt to read after eof: while converting '' to Int32. (ATTEMPT_TO_READ_AFTER_EOF) (version 24.8.14.10459.altinitystable (altinity build))
• Postgres, datatype integer: Could not run test case test: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "" LINE 2: ...SUM(CAST(CASE WHEN (id IS NULL) THEN 1 WHEN (id = '') THEN 1... ^ [SQL: /* {"app": "OpenMetadata", "version": "1.6.5.1"} */ SELECT SUM(CAST(CASE WHEN (id IS NULL) THEN %(param_1)s WHEN (id = %(id_1)s) THEN %(param_2)s ELSE %(param_3)s END AS NUMERIC)) AS "nullCount" FROM public.testtable LIMIT %(param_4)s] [parameters: {'param_1': 1, 'id_1': '', 'param_2': 1, 'param_3': 0, 'param_4': 1}] (Background on this error at: https://sqlalche.me/e/14/9h9h)
• Clickhouse, datatype date: Could not run test case Test_date: Orig exception: Code: 38. DB::Exception: Cannot parse date: value is too short: while converting '' to Date. (CANNOT_PARSE_DATE) (version 24.8.14.10459.altinitystable (altinity build))
• Postgres, datatype timestamp: Could not run test case test: (psycopg2.errors.InvalidDatetimeFormat) invalid input syntax for type timestamp with time zone: "" LINE 2: ...EN (created_at IS NULL) THEN 1 WHEN (created_at = '') THEN 1... ^ [SQL: /* {"app": "OpenMetadata", "version": "1.6.5.1"} */ SELECT SUM(CAST(CASE WHEN (created_at IS NULL) THEN %(param_1)s WHEN (created_at = %(created_at_1)s) THEN %(param_2)s ELSE %(param_3)s END AS NUMERIC)) AS "nullCount" FROM public.testtable LIMIT %(param_4)s] [parameters: {'param_1': 1, 'created_at_1': '', 'param_2': 1, 'param_3': 0, 'param_4': 1}] (Background on this error at: https://sqlalche.me/e/14/9h9h)

2. Column test type "Column Values to Match Regex Pattern(10)":

• Clickhouse, datatype string: Could not run test case Test_String: Orig exception: Code: 46. DB::Exception: Function with name 'MATCH' does not exist. In scope SELECT count(id) AS valuesCount, SUM(accurateCastOrNull(multiIf(MATCH(id, '10'), 1, 0), 'BIGINT')) AS regexCount FROM Test_Projects.testtable LIMIT 1. Maybe you meant: ['match','path']. (UNKNOWN_FUNCTION) (version 24.8.14.10459.altinitystable (altinity build))

3. Column test type "Column Values to be Between(1,10)":

• Postgres, datatype timestamp: Could not run test case test: can't compare offset-naive and offset-aware datetimes

Am I doing something wrong or there is some advice how to use these tests?

I am using OpenMetadata 1.6.5 and Ingestion 1.6.5.1

Thank you for any help!

[TeddyCr]

@brown3007 can you upgarde to the latest stable version and let us know if you still face this issue?

[TeddyCr]

Closing as no updates in the 30+ days

[TeddyCr]

Reopening — this was closed as stale, but the bug is still present on main and on 1.13.x. It was re-reported against 1.13.0 with the same PostgreSQL INTEGER case.

Root cause is the NullMissingCount metric (ingestion/src/metadata/profiler/metrics/static/null_missing_count.py), which appends col = '' to the NULL check for every column type. The empty-string branch was added in #16017 (for #14812) and only considered string columns.

Confirmed behavior on real engines, on a table with 3 NULLs and 2 zeros:

	result
PostgreSQL integer	ERROR: invalid input syntax for type integer: "" — test aborts
PostgreSQL date / boolean / uuid / interval / inet / enum	same class of error
MySQL date	ERROR 1525: Incorrect DATE value: ''
MySQL int	5 instead of 3 — '' coerces to 0, so zero rows count as missing
MySQL boolean	4 instead of 2 — same coercion for false
MariaDB time	2 instead of 1 — '' matches '00:00:00'

So beyond the reported hard failure there is a silent wrong-result variant on MySQL/MariaDB, which is arguably worse: the test reports a number, it's just the wrong one.

This also covers the ClickHouse int and date failures from the original report — same cause.

Fix in progress.