0000-00-00格式日期create select 语句报错

Viewed 46
2.1 catalog

doris版本doris-2.1.4-rc03

mysql5.7

mysql原表

CREATE TABLE `report` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `ad_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '广告的唯一ID',
  `report_utc_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '报告utc时间',
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='广告产品';

想在doris上创建的表

CREATE TABLE report
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES("replication_num"="1")
as SELECT * from mysql_erp.crm.report;

CREATE CATALOG mysql_erp PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://192.1.100.101:3306",
    "driver_url" = "mysql-connector-j-8.3.0.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

be新增allow_zero_date=true

报错内容：

mysql> CREATE TABLE report
    -> UNIQUE KEY(id)
    -> DISTRIBUTED BY HASH(id) BUCKETS 3
    -> PROPERTIES("replication_num"="1")
    -> as SELECT * from mysql_erp.crm.product_report;
ERROR 1105 (HY000): errCode = 2, detailMessage = (192.1.100.2)[INTERNAL_ERROR]UdfRuntimeException: jdbc get block address: 
CAUSED BY: SQLException: Zero date value prohibited
CAUSED BY: DataReadException: Zero date value prohibited
liu_15
2 Answers

这是 MySQL 以及 JDBC 程序本身的问题，0000-00-00 12:34:56这类数据是无法通过 JDBC 读取的，https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-datetime-types-processing.html#cj-conn-prop_zeroDateTimeBehaviorzeroDateTimeBehavior 也只能转换全 0 的时间
image.png
建议使用 where 进行过滤，JDBC catalog 层面不进行这种处理

徐振超@SelectDB6541

可以在 jdbc url 添加 zeroDateTimeBehavior=convertToNull (转为 null) 进行导入
但注意，仅支持 0000-00-00 或 0000-00-00 00:00:00 这种都是全 0 的 date 或 datatime
如果是 0000-00-00 12:34:56 这种日期全 0，时间不是全 0 的 datetime 则不支持转换

zy-kkk586