Unable to map SAP HANA columns with all-lowercase names using jdbc_fdw

[ajcolman]

Unable to map SAP HANA columns with all-lowercase names using jdbc_fdw

Summary

When creating foreign tables from SAP HANA to PostgreSQL using jdbc_fdw, columns with all-lowercase names (e.g., submitted) fail to map correctly, resulting in an error: ERROR: remote server returned an error. Columns with mixed-case names (e.g., isCrin, selfInv, draftKey) work perfectly.

Environment

• PostgreSQL Version: 16.x
• jdbc_fdw Version: 1.2
• SAP HANA Version: 2.0
• JDBC Driver: SAP HANA JDBC Driver (ngdbc.jar)
• Operating System: Linux

Steps to Reproduce

1. SAP HANA Table Structure

The SAP HANA table OINV contains columns with different naming patterns:

-- Query in SAP HANA
SELECT COLUMN_NAME, DATA_TYPE_NAME, POSITION
FROM SYS.TABLE_COLUMNS 
WHERE SCHEMA_NAME = 'EXAMPLE' 
  AND TABLE_NAME = 'OINV'
  AND COLUMN_NAME IN ('isCrin', 'selfInv', 'draftKey', 'submitted')
ORDER BY POSITION;

Result:

COLUMN_NAME | DATA_TYPE_NAME | POSITION
------------|----------------|----------
isCrin      | NVARCHAR       | 75
selfInv     | NVARCHAR       | 78
draftKey    | INTEGER        | 84
submitted   | NVARCHAR       | 114

2. Foreign Table Definition in PostgreSQL

-- Create foreign table
CREATE FOREIGN TABLE test_columns (
    "DocEntry"   INTEGER    OPTIONS (column_name 'DocEntry'),
    "isCrin"     VARCHAR(1) OPTIONS (column_name 'isCrin'),
    "selfInv"    VARCHAR(1) OPTIONS (column_name 'selfInv'),
    "draftKey"   INTEGER    OPTIONS (column_name 'draftKey'),
    "submitted"  VARCHAR(1) OPTIONS (column_name 'submitted')
)
SERVER hana_fdw
OPTIONS (
    schema_name 'EXAMPLE',
    table_name  'OINV'
);

3. Execute Query

-- This query WORKS (without 'submitted')
SELECT "DocEntry", "isCrin", "selfInv", "draftKey" 
FROM test_columns 
LIMIT 1;
-- ✅ Success

-- This query FAILS (with 'submitted')
SELECT "DocEntry", "isCrin", "selfInv", "draftKey", "submitted" 
FROM test_columns 
LIMIT 1;
-- ❌ ERROR: remote server returned an error [XX000]

Expected Behavior

All columns should be accessible regardless of their naming convention (all-lowercase, CamelCase, etc.), as long as the column_name in OPTIONS matches the exact column name in SAP HANA.

Actual Behavior

Columns with all-lowercase names cannot be queried and produce the error:

ERROR: remote server returned an error [XX000]

Pattern Analysis

Column Name	Case Pattern	Works?
DocEntry	CamelCase	✅ Yes
CANCELED	All UPPERCASE	✅ Yes
isCrin	lowercase + CamelCase	✅ Yes
selfInv	lowercase + CamelCase	✅ Yes
draftKey	lowercase + CamelCase	✅ Yes
submitted	All lowercase	❌ No

Workaround

Currently, the workaround is to create a view in SAP HANA that renames the problematic column:

-- In SAP HANA
CREATE VIEW "EXAMPLE"."V_OINV" AS
SELECT 
    "DocEntry",
    "isCrin",
    "selfInv",
    "draftKey",
    "submitted" AS "SubmittedFlag"  -- Rename to mixed-case
FROM "EXAMPLE"."OINV";

Then map to the view in PostgreSQL:

-- In PostgreSQL
CREATE FOREIGN TABLE "EXAMPLE"."OINV" (
    "DocEntry"      INTEGER    OPTIONS (column_name 'DocEntry'),
    "isCrin"        VARCHAR(1) OPTIONS (column_name 'isCrin'),
    "selfInv"       VARCHAR(1) OPTIONS (column_name 'selfInv'),
    "draftKey"      INTEGER    OPTIONS (column_name 'draftKey'),
    "SubmittedFlag" VARCHAR(1) OPTIONS (column_name 'SubmittedFlag')  -- Use renamed column
)
SERVER hana_fdw
OPTIONS (
    schema_name 'EXAMPLE',
    table_name  'V_OINV'  -- Point to view
);

Additional Information

Tested Scenarios

✅ Working:

• Columns starting with lowercase but containing uppercase letters (isCrin, selfInv, draftKey)
• Columns in all uppercase (CANCELED, DOCTYPE)
• Columns in CamelCase (DocEntry, CardCode)

❌ Not Working:

• Columns in all lowercase (submitted)

Question

Is this a known limitation of jdbc_fdw when handling case-sensitive identifiers in SAP HANA? The SAP HANA JDBC driver should handle case-sensitive column names correctly when they are quoted.

Possible Root Cause

It appears that jdbc_fdw might not be properly quoting all-lowercase identifiers when sending queries to SAP HANA via JDBC, or there might be a conflict with how the JDBC driver interprets these identifiers.

Related Information

• SAP HANA is case-sensitive and requires quoted identifiers for exact case matching
• The issue is specifically with columns that are entirely lowercase with no uppercase characters
• Other FDW implementations might not have this issue

Request

Could you please investigate why all-lowercase column names fail to map correctly? This issue requires creating views in the remote database as a workaround, which adds unnecessary complexity to the setup.

Thank you for maintaining this excellent extension! 🙏