Fix `getColumns` for Late binding views

ssheikin commented 1 year ago

... where column is a CASE numeric with null

If we have a late binding view defined as example view test_view, the JDBC driver will fail to obtain its schema through the standard JDBC method DatabaseMetaData.getColumns, with the following error: ERROR: invalid input syntax for integer: ""

Testcase:

CREATE OR REPLACE VIEW test_view AS
WITH test_data AS (SELECT 'value1' AS value UNION ALL SELECT 'value2' AS value)
SELECT CASE WHEN value = 'value1' THEN 123.45 END AS numeric_with_null
FROM test_data WITH NO SCHEMA BINDING;

SELECT * FROM test_view;

Initial issue is described in https://github.com/aws/amazon-redshift-jdbc-driver/issues/45 Some specific field types in late-binding views cause the Redshift JDBC driver to throw an error on the getColumns call when fetching field metadata, but

2.1.0.5 Fix Github issue 45 4c24b47c5de633e8fd9bb1a2c1d414f740eb9909 seems fix some of these cases, but not all. Specifically, a case statement that produces a mix of numeric and null values still results in an error.

maybe issue was reintroduced with 2.1.0.8 Fix numeric scale issue with Numeric data type of an external table e080dd6591e0ebca4e62c8c2767ac1de29b4fef2

Description

Do not parse not existing precision (as i other parts of existing code)

Motivation and Context

https://github.com/aws/amazon-redshift-jdbc-driver/issues/45 was not fixed completely.

Testing

CREATE OR REPLACE VIEW test_view AS
WITH test_data AS (SELECT 'value1' AS value UNION ALL SELECT 'value2' AS value)
SELECT CASE WHEN value = 'value1' THEN 123.45 END AS numeric_with_null
FROM test_data WITH NO SCHEMA BINDING;

SELECT * FROM test_view;

Screenshots (if appropriate)

Types of changes

[x] Bug fix (non-breaking change which fixes an issue)
[ ] New feature (non-breaking change which adds functionality)

Checklist

[ ] Local run of mvn install succeeds
[ ] My code follows the code style of this project
[ ] My change requires a change to the Javadoc documentation
[ ] I have updated the Javadoc documentation accordingly
[ ] I have read the README document
[ ] I have added tests to cover my changes
[ ] All new and existing tests passed
[ ] A short description of the change has been added to the CHANGELOG

This section is not appropriate for this project.

License

By submitting this pull request, I confirm that you can use, modify, copy, and redistribute this contribution, under the terms of your choice.

ssheikin commented 1 year ago

@bhvkshah @Brooke-white @iggarish @hyandell Could you please take a look on this PR?

bhvkshah commented 1 year ago

@ssheikin Thanks for submitting this PR, we will review this and get back to you as soon as we have an update.

ssheikin commented 1 year ago

@bhvkshah @Brooke-white Have you got a chance to take a look on this pr?

kokosing commented 1 year ago

Thank you!

aws / amazon-redshift-jdbc-driver