jdbc - Apache Calcite with PostgreSQL: “DECIMAL precision 0 must be between 1 and 19” Exception - Stack Overflow

admin管理员组

文章数量:1405571

I am using Apache Calcite 1.38.0 along with the PostgreSQL JDBC driver version 42.7.2. When executing an SQL query through a JdbcSchema connection to my PostgreSQL database, I encountered an exception.

Code to initialize the connection:

BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl(MY_PG_URL);
dataSource.setUsername(MY_PG_USERNAME);
dataSource.setPassword(MY_PG_PWD);

JdbcSchema.create(
    rootSchema, 
    "pgSchema", 
    dataSource, 
    null, 
    "public"
); 
// rootSchema is obtained from CalciteConnection.getRootSchema()

PostgreSQL table DDL:

CREATE TABLE test_table (
    value NUMERIC -- A NUMERIC column without specifying precision and scale
);

Query execution:

String sql = "SELECT * FROM pgSchema.test_table";
Statement statement = calciteConnection.createStatement();
statement.executeQuery(sql);

Exception encountered:

** .apache.calcite.runtime.CalciteException: DECIMAL precision 0 must be between 1 and 19**

Investigation:

From my analysis, when PgDatabaseMetaData#getColumns retrieves column metadata, if the NUMERIC column does not explicitly specify precision and scale (i.e., its atttypmod is -1), TypeInfoCache#getPrecision and getScale return 0. This causes SqlTypeFactoryImpl#createSqlType in Calcite to fail validation for precision, leading to this exception.

Questions: 1. Is this a bug in Apache Calcite? 2. Is there any workaround to resolve this issue?

Any insights would be greatly appreciated!

本文标签： jdbcApache Calcite with PostgreSQL “DECIMAL precision 0 must be between 1 and 19” ExceptionStack Overflow