Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: SQLException异常:java.sql.SQLException: Can’t find column Column#371 in schema Column:
|
username: Hacker_KspmEJqW
[TiDB Usage Environment] Production Environment
[TiDB Version] 5.7.25-TiDB-v5.2.3
[Reproduction Path] Executing SQL directly through workbench is normal, but executing through the program results in this error.
The connector used is mysql-connector-java 8.0.30
The query code is as follows:
Connection pst = conn.prepareStatement(dataSql);
resultSet = pst.executeQuery();
rsd = resultSet.getMetaData();
[Encountered Problem: Problem Phenomenon and Impact]
java.sql.SQLException: Can’t find column Column#371 in schema Column:
Unique key: [[test.atdpersonpaycode.personid, test.atdpersonpaycode.timecarddate, test.atdpersonpaycode.startdtm]]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]
The SQL content is as follows:
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
|
username: 
|
username: