Redshift Late binding views where column type has no precision or length result in Error syncing Fields for Table

[rarup1]

Describe the bug
Late binding views in redshift that contain datatype character varying or numeric without char length or decimal precision result in field-sync failure and render the table useless in Metabase UI as no fields can be found.

We find a number of scenarios cause this:

• mixes of numerical and NULL values due to a CASE WHEN result in NUMERIC (show in example)
• NULL as results in CHARACTER VARYING (shown in example)
• 'random_fixed_string' AS field_name results in CHARACTER VARYING (although not reproducible below)

Logs
metabase logs:

[b13bef4a-b6c9-4336-93b8-b4123d7ab6e7] 2022-03-24T14:14:35+00:00 WARN metabase.sync.util Error syncing Fields for Table 'Table 8,033 'models_prep.test_lbv_issues''
com.amazon.redshift.util.RedshiftException: ERROR: invalid input syntax for integer: ""
	at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2601)
	at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2269)
	at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1880)
	at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1872)

To Reproduce
Steps to reproduce the behavior:

1. Create a late binding view in Redshift:

CREATE OR REPLACE VIEW <schema>.test_lbv_issues AS
(
WITH test_data AS (
    SELECT '2021-01-01'::date AS c_date,
           'open'             AS shop_status
    UNION ALL
    SELECT '2021-01-02'::date AS c_date,
           'closed'           AS shop_status
    UNION ALL
    SELECT '2021-01-03'::date AS c_date,
           'closed'           AS shop_status
)
SELECT c_date,
       'hello' AS  naked_var,
       NULL    AS naked_null,
       CASE WHEN shop_status = 'open' THEN 11387.133 END   AS case_when_numeric_inc_nulls,
       CASE WHEN shop_status = 'open' THEN 11387.133 ELSE 12313.123 END AS case_when_numeric_no_nulls

FROM test_data
    )
WITH NO SCHEMA BINDING;

2. Sync metabase database where you have added the late binding view
3. Find table in the schame within Metabase UI and attempt to view
4. See error in logs (above) and UI:

Expected behavior
Metabase to be able to use the late binding view numeric / character varying without issue and allow for column syncing.

See the SQL query to identify all late binding view fields that are currently posing a problem:

WITH lbv_cols AS (
    SELECT lbv_cols.table_schema,
           lbv_cols.table_name,
           lbv_cols.column_name,
           REGEXP_REPLACE(REGEXP_REPLACE(lbv_cols.columntype, '\\(.*\\)'), '^_.+', 'ARRAY') AS columntype_rep,
           lbv_cols.columntype,
           lbv_cols.columnnum
    FROM pg_get_late_binding_view_cols() lbv_cols(table_schema name, TABLE_NAME name, COLUMN_NAME name, columntype text,
                                                  columnnum int))
SELECT *
FROM lbv_cols
WHERE columntype IN ('numeric', 'decimal', 'varchar', 'character varying', 'char')
  AND table_schema LIKE '%schema_name%';

Screenshots
If applicable, add screenshots to help explain your problem.

Information about your Metabase Installation:

You can get this information by going to Admin -> Troubleshooting.

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.83 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.14.1+1",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.14.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.14.1+1",
    "os.name": "Linux",
    "os.version": "5.4.156-83.273.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "redshift"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.3"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.23"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-03-23",
      "tag": "v0.42.3",
      "branch": "release-x.42.x",
      "hash": "33fb268"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Severity
It is quite problematic for anyone using DBT and Redshift with Metabase as the work around is to CAST every single field that poses this issue.

Additional context
We have the latest version of Metabase loaded.

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

[paoliniluis]

Seems that we have that hardcoded on the driver:

metabase/modules/drivers/redshift/src/metabase/driver/redshift.clj

Line 94 in 9f33ac3

(def ^:private database-type->base-type

[rarup1]

@paoliniluis is this a simple fix?

[flamber]

@rarup1 No, likely not, since this was added to fix a lot of problems with views not being synced on Redshift. #14496
But one of the developers would have to answer that, when they start reviewing it.

[flamber]

Looks like it might have been caused by a bug in the driver: aws/amazon-redshift-jdbc-driver#45
If it is caused by the driver, then it should be easily fixed by upgrading https://github.com/metabase/metabase/blob/master/modules/drivers/redshift/deps.edn#L8, but we would want to add a test.