[Bug] [connector-jdbc] Catalog's SELECT_COLUMNS_SQL_TEMPLATE have some issues.

[LoverAndrew]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

OracleCatalog's sql statement of SELECT_COLUMNS_SQL_TEMPLATE have some issue when the column‘s type is NVARCHAR2 or NCHAR, the length of column should get the cols.char_col_decl_length.

so the sql statement of SELECT_COLUMNS_SQL_TEMPLATE show be

SELECT
    cols.COLUMN_NAME,
    CASE 
        WHEN cols.data_type LIKE 'INTERVAL%%' THEN 'INTERVAL'
        ELSE REGEXP_SUBSTR(cols.data_type, '^[^(]+')
    END as TYPE_NAME,
    cols.data_type || 
        CASE 
            WHEN cols.data_type IN ('VARCHAR2', 'CHAR') THEN '(' || cols.data_length || ')'
            WHEN cols.data_type IN ('NVARCHAR2','NCHAR') THEN '(' || cols.char_col_decl_length || ')' 
            WHEN cols.data_type IN ('NUMBER') AND cols.data_precision IS NOT NULL AND cols.data_scale IS NOT NULL THEN '(' || cols.data_precision || ', ' || cols.data_scale || ')'
            WHEN cols.data_type IN ('NUMBER') AND cols.data_precision IS NOT NULL AND cols.data_scale IS NULL THEN '(' || cols.data_precision || ')'
            WHEN cols.data_type IN ('RAW') THEN '(' || cols.data_length || ')'
        END AS FULL_TYPE_NAME,
    CASE 
      WHEN cols.data_type IN ('NVARCHAR2','NCHAR') THEN cols.char_col_decl_length 
      ELSE  cols.data_length
    END AS COLUMN_LENGTH,
    cols.data_precision AS COLUMN_PRECISION,
    cols.data_scale AS COLUMN_SCALE,
    com.comments AS COLUMN_COMMENT,
    cols.data_default AS DEFAULT_VALUE,
    CASE cols.nullable WHEN 'N' THEN 'NO' ELSE 'YES' END AS IS_NULLABLE
FROM
    all_tab_columns cols
LEFT JOIN 
    all_col_comments com ON cols.table_name = com.table_name AND cols.column_name = com.column_name AND cols.owner = com.owner
WHERE 
    cols.owner = '%s'
    AND cols.table_name = '%s'
ORDER BY 
    cols.column_id 

SeaTunnel Version

V2.3.3

SeaTunnel Config

none

Running Command

none

Error Exception

none

Zeta or Flink or Spark Version

No response

Java or Scala Version

No response

Screenshots

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Carl-Zhou-CN]

@LoverAndrew hi, are you willing to contribute a pr to fix it?

[LoverAndrew]

@LoverAndrew hi, are you willing to contribute a pr to fix it?

Certainly, but I don't know the process of contribute. Could you guide me the process?

[Carl-Zhou-CN]

@LoverAndrew hi, are you willing to contribute a pr to fix it?

Certainly, but I don't know the process of contribute. Could you guide me the process?

Of course, I'd be honored
Chinese document reference https://mp.weixin.qq.com/s/wOVxNjg-tSQeenUY5Sd7kQ,
English document reference https://seatunnel.incubator.apache.org/community/contribution_guide/contribute#preparing-to-contribute-code-changes
Of course, you can always consult me if you have anything unclear

[LoverAndrew]

SqlServerCatalog's SELECT_COLUMNS_SQL_TEMPLATE has the same issue.
Before sql statement is

SELECT tbl.name AS table_name,
       col.name AS column_name,
       ext.value AS comment,
       col.column_id AS column_id,
       types.name AS type,
       col.max_length AS max_length,
       col.precision AS precision,
       col.scale AS scale,
       col.is_nullable AS is_nullable,
       def.definition AS default_value
FROM sys.tables tbl
    INNER JOIN sys.columns col ON tbl.object_id = col.object_id
    LEFT JOIN sys.types types ON col.user_type_id = types.user_type_id
    LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id
    LEFT JOIN sys.default_constraints def ON col.default_object_id = def.object_id AND ext.minor_id = col.column_id AND ext.name = 'MS_Description'
WHERE schema_name(tbl.schema_id) = 'dbo' AND tbl.name = 'student'
ORDER BY tbl.name, col.column_id;

After sql statement is

SELECT col.TABLE_NAME AS table_name,
       col.COLUMN_NAME AS column_name,
       prop.VALUE AS comment,
	   col.ORDINAL_POSITION AS column_id,
       col.DATA_TYPE AS type,
       CASE WHEN col.DATA_TYPE in ('nchar','nvarchar','ntext') THEN col.CHARACTER_MAXIMUM_LENGTH
         ELSE col.CHARACTER_OCTET_LENGTH end  AS max_length,
       CASE WHEN col.DATA_TYPE in ('datetime','datetime2','datetimeoffset','date','time','smalldatetime' ) THEN col.DATETIME_PRECISION 
         ELSE col.NUMERIC_PRECISION end AS precision,
       col.NUMERIC_SCALE AS scale,
       col.IS_NULLABLE AS is_nullable,
       col.COLUMN_DEFAULT AS default_value
FROM INFORMATION_SCHEMA.COLUMNS col
     LEFT JOIN sys.extended_properties prop
         ON prop.major_id = OBJECT_ID(col.TABLE_SCHEMA + '.' + col.TABLE_NAME)
         AND prop.minor_id = col.ORDINAL_POSITION
         AND prop.name = 'MS_Description'
WHERE   col.TABLE_SCHEMA='dbo' AND col.TABLE_NAME = 'student'
ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;

[LoverAndrew]

PostgresCatalog's SELECT_COLUMNS_SQL_TEMPLATE has the some issue.
Before sql statement is

SELECT 
    a.attname AS column_name, 
t.typname as type_name,
    CASE 
        WHEN t.typname = 'varchar' THEN t.typname || '(' || (a.atttypmod - 4) || ')'
        WHEN t.typname = 'bpchar' THEN 'char' || '(' || (a.atttypmod - 4) || ')'
        WHEN t.typname = 'numeric' OR t.typname = 'decimal' THEN t.typname || '(' || ((a.atttypmod - 4) >> 16) || ', ' || ((a.atttypmod - 4) & 65535) || ')'
        WHEN t.typname = 'bit' OR t.typname = 'bit varying' THEN t.typname || '(' || (a.atttypmod - 4) || ')'
        ELSE t.typname
    END AS full_type_name,
    CASE
        WHEN t.typname IN ('varchar', 'bpchar', 'bit', 'bit varying') THEN a.atttypmod - 4
        WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) >> 16
        ELSE NULL
    END AS column_length,
CASE
        WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) & 65535
        ELSE NULL
    END AS column_scale,
d.description AS column_comment,
pg_get_expr(ad.adbin, ad.adrelid) AS default_value,
CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS is_nullable
FROM 
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON a.atttypid = t.oid
    LEFT JOIN pg_description d ON c.oid = d.objoid AND a.attnum = d.objsubid
    LEFT JOIN pg_attrdef ad ON a.attnum = ad.adnum AND a.attrelid = ad.adrelid
WHERE 
    n.nspname = 'public'
    AND c.relname = 'test_type_table'
    AND a.attnum > 0
ORDER BY 
    a.attnum;

After sql statement is

SELECT 
    a.attname AS column_name, 
isc.udt_name as type_name,
    CASE 
        WHEN isc.udt_name = 'varchar' THEN isc.udt_name || '(' || (isc.character_maximum_length) || ')'
        WHEN isc.udt_name = 'bpchar' THEN 'char' || '(' || (isc.character_maximum_length) || ')'
        WHEN isc.udt_name = 'numeric' OR isc.udt_name = 'decimal' THEN isc.udt_name || '(' || (isc.numeric_precision) || ', ' || (isc.numeric_scale) || ')'
        WHEN isc.udt_name = 'bit' OR isc.udt_name = 'bit varying' THEN isc.udt_name || '(' || (isc.character_maximum_length ) || ')'
        ELSE isc.udt_name
    END AS full_type_name,
    CASE
        WHEN isc.udt_name IN ('varchar', 'bpchar', 'bit', 'bit varying','text','json','') THEN isc.character_maximum_length
				WHEN isc.udt_name IN ('time','timetz','timestampt','timestamptz','date') THEN isc.datetime_precision
        ELSE isc.numeric_precision
    END AS column_length,
isc.numeric_scale AS column_scale,
d.description AS column_comment,
pg_get_expr(ad.adbin, ad.adrelid) AS default_value,
CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS is_nullable
FROM 
		information_schema.columns isc 
		LEFT JOIN pg_namespace n ON n.nspname = isc.table_schema
		LEFT JOIN pg_class c ON isc.table_name = c.relname AND n.oid= c.relnamespace
		LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = isc.column_name
    LEFT JOIN pg_description d ON c.oid = d.objoid AND a.attnum = d.objsubid
    LEFT JOIN pg_attrdef ad ON a.attnum = ad.adnum AND a.attrelid = ad.adrelid
WHERE 
    isc.table_schema = 'public'
    AND c.relname = 'test_type_table'
    AND a.attnum > 0
ORDER BY 
    a.attnum;

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

[github-actions]

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.