[Bug] PolarDB jdbc操作INTERVAL类型时驱动异常

[jiangtao69039]

Describe the bug 创建一个带有interval列类型的表,然后通过jdbc查询,会报异常.

create table test(id int,col1 interval);

通过jdbc驱动调用 ResultSetMetaData.getColumnType(i) 或者 ResultSet.getObject(i) 均会报 " function "nestedtable_in" does not exist 位置：49" 这个异常.

驱动里getPGtype能正常返回 interval

但是getSQLType报错, 报错原因是 驱动执行下面的查询语句 .

SELECT typinput='array_in'::regproc OR typinput='nestedtable_in'::regproc, typtype   FROM pg_catalog.pg_type   LEFT   JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE typname = 'interval'  ORDER BY sp.r, pg_type.oid DESC LIMIT 1

[polardb-bot[bot]]

Hi @jiangtao69039 ~ Thanks for opening this issue! 🎉

Please make sure you have provided enough information for subsequent discussion.

We will get back to you as soon as possible. ❤️

[mrdrivingduck]

@jiangtao69039 What version of JDBC are you using? You should use the JDBC version which is the same as PostgreSQL, because we are 100% compatible with PostgreSQL. nestedtable_in seems not to be a PostgreSQL proc type.

[jiangtao69039]

[jiangtao69039]

I use polardb-jdbc18.jar connect to polardb, url format is "jdbc:polardb://"

[jiangtao69039]

The exception is reported by the driver jar. It is marked on the picture.

[jiangtao69039]

[jiangtao69039]

The version detected by polardb here is 11.9, which is larger than 9.2. So 'nestedtable_in' is used here.

[mrdrivingduck]

@jiangtao69039 You should use the driver here, which is the 100% PostgreSQL driver. The PolarDB JDBC driver you are using is actually for PolarDB for PostgreSQL (with Oracle Compability), which is not the version here.

[jiangtao69039]

Yes, the statement of the query type in the pg driver will not report an error. But what I am wondering is why polardb has problems with polardb drivers

[mrdrivingduck]

@jiangtao69039 As you can see here:

The driver you have downloaded should come from the document of the 4th one, which is PolarDB for PostgreSQL (with Oracle Compability). And current repository contains the code of the 3rd one, which is PolarDB for PostgreSQL, 100% compact with PostgreSQL. They are different versions. For connecting to PolarDB-PG in this repository, the JDBC of PostgreSQL is just enough. The PolarDB driver you have downloaded is not 100% compact with PostgreSQL.

[mrdrivingduck]

/close