【 TiDB 使用环境】生产环境
【 TiDB 版本】 5.7.25-TiDB-v5.2.3
【复现路径】 直接通过workbench 执行sql 正常, 通过程序 执行出现这个错误
使用的连接器为 mysql-connector-java 8.0.30
查询代码如下
Connection pst = conn.prepareStatement(dataSql);
resultSet = pst.executeQuery();
rsd = resultSet.getMetaData();
【遇到的问题：问题现象及影响】
java.sql.SQLException: Can’t find column Column#371 in schema Column:
Unique key: [[test.atdpersonpaycode.personid,test.atdpersonpaycode.timecarddate,test.atdpersonpaycode.startdtm]]
【资源配置】
【附件：截图/日志/监控】
sql 内容如下:
SELECT psn.personid,
SUM(ifnull(D11 , 0)) + SUM(ifnull(D12 , 0)) + SUM(ifnull(D13 , 0)) as hours
FROM
test.psnaccount psn
LEFT JOIN (
SELECT
t.TIMECARDDATE, kuanggong, SUM(IF( t.kuanggong = 1 || t.D11et <= t.cst || t.D11st >= t.cet,
t.D11,D11 - IF(t.D11st < t.cet AND t.D11et > cst,TIMESTAMPDIFF(
MINUTE,
IF(t.D11st > t.cst, t.D11st, t.cst),
IF(t.D11et < t.cet, t.D11et, t.cet)
) / 60.0,
t.D11
)
)) DIV 0.5 * 0.5 D11,
SUM(IF(
t.kuanggong = 1 || t.D12et <= t.cst || t.D12st >= t.cet,
t.D12,
D12 - IF(
t.D12st < t.cet
AND t.D12et > cst,
TIMESTAMPDIFF(
MINUTE,
IF(t.D12st > t.cst, t.D12st, t.cst),
IF(t.D12et < t.cet, t.D12et, t.cet)
) / 60.0,
t.D12
)
)) DIV 0.5 * 0.5 D12,
sum(IF(
t.kuanggong = 1 || t.D13et <= t.cst || t.D13st >= t.cet,
t.D13,
D13 - IF(
t.D13st < t.cet
AND t.D13et > cst,
TIMESTAMPDIFF(
MINUTE,
IF(t.D13st > t.cst, t.D13st, t.cst),
IF(t.D13et < t.cet, t.D13et, t.cet)
) / 60.0,
t.D13
)
)) DIV 0.5 * 0.5 D13,
t.PERSONID
FROM
(
SELECT
a.PERSONID,
a.TIMECARDDATE,
IF(kuanggong.ClassCode = ‘H02’, 1, 0) kuanggong,
IF(aac.ClassCode = ‘D11’, a.PAYHOURS, 0) ‘D11’,
IF(aac.ClassCode = ‘D12’, a.PAYHOURS, 0) ‘D12’,
IF(aac.ClassCode = ‘D13’, a.PAYHOURS, 0) ‘D13’,
IF(aac.ClassCode = ‘D11’, a.startdtm, ‘9999-01-01’) ‘D11st’,
IF(aac.ClassCode = ‘D11’, a.enddtm, ‘9999-01-01’) ‘D11et’,
IF(aac.ClassCode = ‘D12’, a.startdtm, ‘9999-01-01’) ‘D12st’,
IF(aac.ClassCode = ‘D12’, a.enddtm, ‘9999-01-01’) ‘D12et’,
IF(aac.ClassCode = ‘D13’, a.startdtm, ‘9999-01-01’) ‘D13st’,
IF(aac.ClassCode = ‘D13’, a.enddtm, ‘9999-01-01’) ‘D13et’,
CONCAT(a1.CALENDARDATE, ’ ‘, a1.TIMEFROM, ‘:00’) ‘cst’,
CONCAT(
IF(
a1.TIMEFROM > a1.TIMETO,
DATE_ADD(a1.CALENDARDATE, INTERVAL 1 DAY),
a1.CALENDARDATE
),
’ ‘,
a1.TIMETO,
‘:00’
) ‘cet’
FROM
test.atdpersonpaycode a
INNER JOIN test.atd_attendance_class aac ON a.PAYCODE = aac.Id
left JOIN test.atdemployeecalendar a1 ON a.PERSONID = a1.PERSONID
AND a1.CALENDARDATE = a.TIMECARDDATE
left join (
select
a.PERSONID,
TIMECARDDATE,
ClassCode
from
test.atdpersonpaycode a
INNER JOIN test.atd_attendance_class aac ON a.PAYCODE = aac.Id
and ClassCode = ‘H02’
) kuanggong on kuanggong.PERSONID = a.PERSONID
and kuanggong.TIMECARDDATE = a.TIMECARDDATE
WHERE
aac.ClassCode in (‘D12’, ‘D11’, ‘D13’)and a1.CALENDARTYPE!=‘3’ AND a.PAYHOURS!=‘0’
and a.TIMECARDDATE >= CONCAT(DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM “2022-10-01”),1) + INTERVAL QUARTER(“2022-10-01”)*3-3 MONTH),’%Y-%m-’),‘01’)
AND a.TIMECARDDATE <=LAST_DAY(CONCAT(DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM “2022-10-01”),1) + INTERVAL QUARTER(“2022-10-01”)*3-1 MONTH),‘%Y-%m-’),‘01’))
GROUP BY
a.PERSONID,
a.TIMECARDDATE
,A.STARTDTM
) t
GROUP BY
t.PERSONID,
t.TIMECARDDATE
) a ON a.personid = psn.personid
GROUP BY psn.personid