查询语句报 Out of range value for column

Question

各位好，遇到一个问题需要向大家请教，我的环境是MySql 驱动包是mysql-connector-java-5.1.30.jar(试过很多版本都是一样)，查询一条语句报 Out of range value for column，百度谷歌只发现插入更新报这个错误的资料，所以向大家请教，希望不吝赐教。
语句如下：
SELECT
CONCAT(
date_format(dcm.DPRODUCTIONDATE, '%Y'),
'年度'
) AS productionDate,
date_format(dcm.DPRODUCTIONDATE, '%Y') AS shortDate,
sum(COALESCE(dcm.NCOSTTIME, 0)) AS nCostTime,
sum(
COALESCE (dcm.NSTANDARDTIME, 0)
) AS standardTime2,
CASE
WHEN (1) = '0' THEN
COALESCE (
sum(dcm.NNORMALSHUTDOWNTIME),
0
)
ELSE
COALESCE (
sum(dcm.nNSDirectTimeQty),
0
)
END normalShutTime,
CASE
WHEN (1) = '0' THEN
COALESCE (
sum(dcm.NABNORMALSHUTDOWNTIME),
0
)
ELSE
COALESCE (
sum(dcm.nASDirectTimeQty),
0
)
END abnormalShutTime,
CASE
WHEN (1) = '0' THEN
COALESCE (sum(dcm.NCOSTTIME), 0)
ELSE
COALESCE (sum(dcm.nStandardTime), 0)
END standardTime,
CASE
WHEN (1) = '0' THEN
COALESCE (sum(dcm.NATTENDANCETIME), 0)
ELSE
sum(
COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE (
dcm.nDirectSupportTimeQty,
0
) + COALESCE (
dcm.nOTDirectSupportTimeQty,
0
)
)
END attendanceTime,
CASE
WHEN (1) = '0' THEN
fncalcefficiency (
sum(
COALESCE (dcm.nAttendanceTime, 0)
),
sum(
COALESCE (dcm.NSTANDARDTIME, 0)
),
0,
0
)
ELSE
fncalcefficiency (
sum(
COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (
dcm.nDirectSupportTimeQty,
0
) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE (
dcm.nOTDirectSupportTimeQty,
0
)
),
sum(
COALESCE (dcm.NSTANDARDTIME, 0)
),
0,
0
)
END generalRate,
CASE
WHEN (1) = '0' THEN
fncalcefficiency (
sum(
COALESCE (dcm.nAttendanceTime, 0)
),
sum(COALESCE(dcm.NCOSTTIME, 0)),
sum(
COALESCE (dcm.NNORMALSHUTDOWNTIME, 0)
),
0
)
ELSE
fncalcefficiency (
sum(
COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (
dcm.nDirectSupportTimeQty,
0
) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE (
dcm.nOTDirectSupportTimeQty,
0
)
),
sum(COALESCE(dcm.NCOSTTIME, 0)),
sum(
COALESCE (dcm.nNSDirectTimeQty, 0)
),
0
)
END processRate,
CASE
WHEN (1) = '0' THEN
fncalcefficiency (
sum(
COALESCE (dcm.nAttendanceTime, 0)
),
sum(COALESCE(dcm.NCOSTTIME, 0)),
sum(
COALESCE (dcm.NNORMALSHUTDOWNTIME, 0)
),
sum(
COALESCE (
dcm.NABNORMALSHUTDOWNTIME,
0
)
)
)
ELSE
fncalcefficiency (
sum(
COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (
dcm.nDirectSupportTimeQty,
0
) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE (
dcm.nOTDirectSupportTimeQty,
0
)
),
sum(COALESCE(dcm.NCOSTTIME, 0)),
sum(
COALESCE (dcm.nNSDirectTimeQty, 0)
),
sum(
COALESCE (dcm.nASDirectTimeQty, 0)
)
)
END workRate
FROM
ec_XLOrganCostAndOutput dcm
INNER JOIN ec_SysOrganization org ON (
org.id = 'efcb6a64-ac7e-4da8-a959-92904e9b3318'
AND dcm.SLINKSERIALNOX LIKE CONCAT(org.SLINKSERIALNO, '%')
)
WHERE
org.istatus = '0'
AND dcm.DPRODUCTIONDATE >= str_to_date('2016-03-01', '%Y-%m-%d')
AND dcm.DPRODUCTIONDATE <= str_to_date('2016-03-31', '%Y-%m-%d')
GROUP BY
date_format(dcm.DPRODUCTIONDATE, '%Y')
ORDER BY
date_format(dcm.DPRODUCTIONDATE, '%Y')
错误信息如下：
Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'AttendanceTime' at row 695324
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4230)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2188)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3538)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
at test.ConnectService.main(ConnectService.java:232)

根据错误信息，说超出了，但是很奇怪的是，我把这条语句精剪，只查询2个字段又没错误：
SELECT
CONCAT(
    date_format(dcm.DPRODUCTIONDATE, '%Y'),
    '年度'
) AS productionDate,
CASE

WHEN (1) = '0' THEN
COALESCE (sum(dcm.NATTENDANCETIME), 0)
ELSE
sum(
COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE (
dcm.nDirectSupportTimeQty,
0
) + COALESCE (
dcm.nOTDirectSupportTimeQty,
0
)
)
END attendanceTime
FROM
ec_XLOrganCostAndOutput dcm
INNER JOIN ec_SysOrganization org ON (
org.id = 'efcb6a64-ac7e-4da8-a959-92904e9b3318'
AND dcm.SLINKSERIALNOX LIKE CONCAT(org.SLINKSERIALNO, '%')
)
WHERE
org.istatus = '0'
AND dcm.DPRODUCTIONDATE >= str_to_date('2016-03-01', '%Y-%m-%d')
AND dcm.DPRODUCTIONDATE <= str_to_date('2016-03-31', '%Y-%m-%d')
GROUP BY
date_format(dcm.DPRODUCTIONDATE, '%Y')
ORDER BY
date_format(dcm.DPRODUCTIONDATE, '%Y')

所以很是迷惑，不知道错误到底是什么？

Answer 1

用JDBC执行的
Connection conn = c.getConn();
PreparedStatement ps = conn.prepareStatement(countSql) ;

    ResultSet rs = ps.executeQuery();