jdbc catalog - error read datetime column

[maulanaady]

I have table in mariadb server with datetime columns, when I query it using mysql client, it shows the row, but when I query it using starrocks via jdbc catalog (I used mysql client, too), it shows errors.

Steps to reproduce the behavior (Required)

1. My DDL table in mariadb:
   CREATE TABLE mydb.mytable (
   ID int(11) NOT NULL,
   INVOICE_TYPE varchar(30) NOT NULL,
   INVOICE_DATE datetime DEFAULT NULL,
   DUE_DATE datetime DEFAULT NULL,
   DATE_CREATED datetime NOT NULL DEFAULT current_timestamp(),
   DATE_MODIFIED datetime DEFAULT NULL,
   MODIFIED_BY varchar(50) DEFAULT NULL,
   PAYMENT_DATE datetime DEFAULT NULL,
   PROFORMA_DATE datetime DEFAULT NULL,
   FLAG_TERMIN varchar(5) DEFAULT NULL,
   PRIMARY KEY (ID)
   ) ENGINE=InnoDB;

2. insert into `mydb.mytable` values (291860,"1",'2023-11-05 00:00:00','2023-12-01 00:00:00', '2023-11-02 13:30:43','2023-12-04 13:25:34','dummy_name','0000-00-00 00:00:00',NULL,NULL);

3. Create jdbc catalog:
   CREATE EXTERNAL CATALOG mariadb
   PROPERTIES (
   "type"="jdbc",
   "user"="myuser",
   "password"="mypassword",
   "jdbc_uri"="jdbc:mysql://host:port",
   "driver_url"="https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar",
   "driver_class"="com.mysql.cj.jdbc.Driver"
   );

4. SELECT mariadb.mydb.mytable :

Expected behavior:

+--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+
| ID | INVOICE_TYPE | INVOICE_DATE | DUE_DATE | DATE_CREATED | DATE_MODIFIED | MODIFIED_BY | PAYMENT_DATE | PROFORMA_DATE | FLAG_TERMIN |
+--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+
| 291860 | 1 | 2023-11-05 00:00:00 | 2023-12-01 00:00:00 | 2023-11-02 13:30:43 | 2023-12-04 13:25:34 | dummy_name | 0000-00-00 00:00:00 | NULL | NULL |
+--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+

Real behavior:

SQL Error [1064] [42000]: getNextChunk failed, error: java.sql.SQLException: Zero date value prohibited[com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73), com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99), com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1285), com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java), com.starrocks.jdbcbridge.JDBCScanner.getNextChunk(JDBCScanner.java:145)]

StarRocks version

• 3.1.9

[zombee0]

could you add ?ZeroDateTimeBehavior=convertToNull to jdbc_uri and it's supported from 3.1.10

[maulanaady]

same issue already sent before my post, #24277

[zombee0]

have you upgrade to 3.1.10?