为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
在执行一些复杂的嵌套表达式时, 对于时间的计算会不准确, 有些数据会空, 有些数据本来应该是2024年的结果是2000年
建表语句 数据 查询sql在副件里面
to_timestamp_error.sql (110.3 KB)
SELECT CAST(`zfsj_ItV` AS DATETIME) AS `zfsj_ItV`,
LENGTH(DATE_FORMAT(`zfsj_ItV`, '%Y-%m')) as len,
DATE_FORMAT(`zfsj_ItV`, '%Y-%m') as form,
STR_TO_JODATIME(DATE_FORMAT(`zfsj_ItV`, '%Y-%m'), 'yyyy-MM') as to_joda,
`Y_ny_0T_PN4`,
`Y_ny_70_6oz`,
`dpmc_6Im`,
`pt_bx1`,
`ys_WE5`,
`ysdh_mhJ`,
`ddbh_kW1`
FROM (SELECT CAST(`zfsj` AS DATE) AS `zfsj_ItV`,
SUBSTRING(CAST(`zfsj` AS VARCHAR), 1, 7) AS `Y_ny_0T_PN4`,
CASE
WHEN LENGTH(DATE_FORMAT(`zfsj`, '%Y-%m')) < 4 then null
WHEN LENGTH(DATE_FORMAT(`zfsj`, '%Y-%m')) = 4
THEN STR_TO_JODATIME(DATE_FORMAT(`zfsj`, '%Y-%m'), 'yyyy')
WHEN LENGTH(DATE_FORMAT(`zfsj`, '%Y-%m')) > 7 THEN CAST(DATE_FORMAT(`zfsj`, '%Y-%m') AS DATETIME)
ELSE STR_TO_JODATIME(DATE_FORMAT(`zfsj`, '%Y-%m'), 'yyyy-MM') END AS `Y_ny_70_6oz`,
`dpmc` AS `dpmc_6Im`,
`pt` AS `pt_bx1`,
`ys` AS `ys_WE5`,
`ysdh` AS `ysdh_mhJ`,
`ddbh` AS `ddbh_kW1`
FROM (SELECT CASE
WHEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME) IS NOT NULL
THEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME)
ELSE CAST(`_persist_COLLECTION_gCsDAhhQcO_WORKING`.`zfsj_ykH` AS DATETIME) END AS `zfsj`,
CASE
WHEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`dpmc_f81` IS NOT NULL
THEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`dpmc_f81`
ELSE `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap` END AS `dpmc`,
CASE
WHEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`pt_xZw` IS NOT NULL
THEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`pt_xZw`
ELSE `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`pt_yYW` END AS `pt`,
CASE
WHEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ys_jTF` IS NOT NULL
THEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ys_jTF`
ELSE `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ys_vwR` END AS `ys`,
CASE
WHEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ysdh_5Ca` IS NOT NULL
THEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ysdh_5Ca`
ELSE `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ysdh_AEG` END AS `ysdh`,
CASE
WHEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ddbh_cWV` IS NOT NULL
THEN `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ddbh_cWV`
ELSE `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ddbh_Ph3` END AS `ddbh`
FROM (SELECT `zfsj_Rag` AS `zfsj_Rag`,
`dpmc_f81` AS `dpmc_f81`,
`pt_xZw` AS `pt_xZw`,
`ys_jTF` AS `ys_jTF`,
`ysdh_5Ca` AS `ysdh_5Ca`,
`ddbh_cWV` AS `ddbh_cWV`,
`$f6`
FROM (SELECT `zfsj_Rag`,
`dpmc_f81`,
`pt_xZw`,
`ys_jTF`,
`ysdh_5Ca`,
`ddbh_cWV`,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CASE
WHEN CAST((CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ys_jTF`), 0) AS DECIMAL(38)), 2)
ELSE (
CASE
WHEN (`ys_jTF`) < 0
THEN CEIL((`ys_jTF`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ys_jTF`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR) IS NOT NULL
THEN CAST((
CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ys_jTF`), 0) AS DECIMAL(38)), 2)
ELSE (
CASE
WHEN (`ys_jTF`) < 0
THEN CEIL((`ys_jTF`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ys_jTF`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR)
ELSE '' END,
'--'), CASE
WHEN CAST(CAST(`zfsj_Rag` AS DATETIME) AS VARCHAR) IS NOT NULL
THEN CAST(CAST(`zfsj_Rag` AS DATETIME) AS VARCHAR)
ELSE '' END),
'--'), CASE
WHEN `pt_xZw` IS NOT NULL
THEN `pt_xZw`
ELSE '' END), '--'),
CASE
WHEN CAST((CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ysdh_5Ca`), 0) AS DECIMAL(38)), 2)
ELSE (CASE
WHEN (`ysdh_5Ca`) < 0
THEN CEIL((`ysdh_5Ca`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ysdh_5Ca`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR) IS NOT NULL
THEN CAST((CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ysdh_5Ca`), 0) AS DECIMAL(38)), 2)
ELSE (CASE
WHEN (`ysdh_5Ca`) < 0
THEN CEIL((`ysdh_5Ca`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ysdh_5Ca`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR)
ELSE '' END), '--'), CASE
WHEN CAST((CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ddbh_cWV`), 0) AS DECIMAL(38)), 2)
ELSE (CASE
WHEN (`ddbh_cWV`) < 0
THEN CEIL((`ddbh_cWV`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ddbh_cWV`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR) IS NOT NULL
THEN CAST((CASE
WHEN 2 < 0
THEN ROUND(CAST(TRUNCATE((`ddbh_cWV`), 0) AS DECIMAL(38)), 2)
ELSE (
CASE
WHEN (`ddbh_cWV`) < 0
THEN CEIL((`ddbh_cWV`) * POW(10, 2) - 0.5)
ELSE FLOOR((`ddbh_cWV`) * POW(10, 2) + 0.5) END /
POW(10, 2)) END +
0e0) AS VARCHAR)
ELSE '' END), '--'),
CASE WHEN `dpmc_f81` IS NOT NULL THEN `dpmc_f81` ELSE '' END) AS `$f6`
FROM (SELECT CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATE) AS `zfsj_Rag`,
`_persist_COLLECTION_pysbE3R0v6_WORKING`.`dpmc_f81`,
`_persist_COLLECTION_pysbE3R0v6_WORKING`.`pt_xZw`,
`_persist_COLLECTION_pysbE3R0v6_WORKING`.`ys_jTF`,
`_persist_COLLECTION_pysbE3R0v6_WORKING`.`ysdh_5Ca`,
`_persist_COLLECTION_pysbE3R0v6_WORKING`.`ddbh_cWV`
FROM `_persist_COLLECTION_pysbE3R0v6_WORKING`) AS `_persist_COLLECTION_pysbE3R0v6_WORKING`) AS `_persist_COLLECTION_pysbE3R0v6_WORKING`) AS `_persist_COLLECTION_pysbE3R0v6_WORKING`
full JOIN
(SELECT `zfsj_ykH` AS `zfsj_ykH`, `dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap` AS `dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap`, `pt_yYW` AS `pt_yYW`, `ys_vwR` AS `ys_vwR`, `ysdh_AEG` AS `ysdh_AEG`, `ddbh_Ph3` AS `ddbh_Ph3`, `$f6`
FROM (SELECT `zfsj_ykH`, `dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap`, `pt_yYW`, `ys_vwR`, `ysdh_AEG`, `ddbh_Ph3`, CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CASE WHEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ys_vwR`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ys_vwR`) < 0 THEN CEIL((`ys_vwR`) * POW(10,2) - 0.5) ELSE FLOOR((`ys_vwR`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) IS NOT NULL THEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ys_vwR`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ys_vwR`) < 0 THEN CEIL((`ys_vwR`) * POW(10,2) - 0.5) ELSE FLOOR((`ys_vwR`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) ELSE '' END, '--'), CASE WHEN CAST(CAST(`zfsj_ykH` AS DATETIME) AS VARCHAR) IS NOT NULL THEN CAST(CAST(`zfsj_ykH` AS DATETIME) AS VARCHAR) ELSE '' END), '--'), CASE WHEN `pt_yYW` IS NOT NULL THEN `pt_yYW` ELSE '' END), '--'), CASE WHEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ysdh_AEG`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ysdh_AEG`) < 0 THEN CEIL((`ysdh_AEG`) * POW(10,2) - 0.5) ELSE FLOOR((`ysdh_AEG`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) IS NOT NULL THEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ysdh_AEG`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ysdh_AEG`) < 0 THEN CEIL((`ysdh_AEG`) * POW(10,2) - 0.5) ELSE FLOOR((`ysdh_AEG`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) ELSE '' END), '--'), CASE WHEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ddbh_Ph3`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ddbh_Ph3`) < 0 THEN CEIL((`ddbh_Ph3`) * POW(10,2) - 0.5) ELSE FLOOR((`ddbh_Ph3`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) IS NOT NULL THEN CAST((CASE WHEN 2 < 0 THEN ROUND(CAST(TRUNCATE((`ddbh_Ph3`), 0) AS DECIMAL(38)), 2) ELSE (CASE WHEN (`ddbh_Ph3`) < 0 THEN CEIL((`ddbh_Ph3`) * POW(10,2) - 0.5) ELSE FLOOR((`ddbh_Ph3`) * POW(10,2) + 0.5) END / POW(10, 2)) END + 0e0) AS VARCHAR) ELSE '' END), '--'), CASE WHEN `dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap` IS NOT NULL THEN `dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap` ELSE '' END) AS `$f6`
FROM (SELECT CAST(`_persist_COLLECTION_gCsDAhhQcO_WORKING`.`zfsj_ykH` AS DATE) AS `zfsj_ykH`, `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap`, `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`pt_yYW`, `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ys_vwR`, `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ysdh_AEG`, `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ddbh_Ph3`
FROM `_persist_COLLECTION_gCsDAhhQcO_WORKING`) AS `_persist_COLLECTION_gCsDAhhQcO_WORKING`) AS `_persist_COLLECTION_gCsDAhhQcO_WORKING`) AS `_persist_COLLECTION_gCsDAhhQcO_WORKING`
ON `_persist_COLLECTION_pysbE3R0v6_WORKING`.`$f6` = `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`$f6`
WHERE
(((((((`_persist_COLLECTION_pysbE3R0v6_WORKING`.`ys_jTF` IS NULL
AND `_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` IS NULL)
AND `_persist_COLLECTION_pysbE3R0v6_WORKING`.`pt_xZw` IS NULL)
AND `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ysdh_5Ca` IS NULL)
AND `_persist_COLLECTION_pysbE3R0v6_WORKING`.`ddbh_cWV` IS NULL)
AND `_persist_COLLECTION_pysbE3R0v6_WORKING`.`dpmc_f81` IS NULL)
OR (((((`_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ys_vwR` IS NULL
AND `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`zfsj_ykH` IS NULL)
AND `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`pt_yYW` IS NULL)
AND `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ysdh_AEG` IS NULL)
AND `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`ddbh_Ph3` IS NULL)
AND `_persist_COLLECTION_gCsDAhhQcO_WORKING`.`dpmc_1GK_wrap_wrap_wrap_wrap_wrap_wrap_wrap` IS NULL)))
AND TRIM(CAST(CAST(CAST(CASE WHEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME) IS NOT NULL THEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME) ELSE CAST(`_persist_COLLECTION_gCsDAhhQcO_WORKING`.`zfsj_ykH` AS DATETIME) END AS DATE) AS DATETIME) AS VARCHAR)) <> ''
AND CAST(CAST(CASE WHEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME) IS NOT NULL THEN CAST(`_persist_COLLECTION_pysbE3R0v6_WORKING`.`zfsj_Rag` AS DATETIME) ELSE CAST(`_persist_COLLECTION_gCsDAhhQcO_WORKING`.`zfsj_ykH` AS DATETIME) END AS DATE) AS DATETIME) IS NOT NULL) AS `t0`) AS `t2`
order by form nulls first;
【背景】做过哪些操作？
【业务影响】 转timestamp功能实现不了
【是否存算分离】是
【StarRocks版本】例如：3.2.6
【集群规模】例如：3fe（3 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群15-可乐鸡
【附件】
- fe.log/beINFO/相应截图
- 慢查询：
- Profile信息
- 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
- pipeline是否开启：show variables like ‘%pipeline%’;
- be节点cpu和内存使用率截图
- 查询报错：
- query_dump，怎么获取query_dump文件
- be crash
- be.out
- 外表查询报错
- be.out和fe.warn.log