提示:
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Warning Code: 1065, SQLState: 42000
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Warning Code: 1065, SQLState: 42000
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Query was empty
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Query was empty
错误编号:1065
问题分析:无效的 SQL 语句,SQL 语句为空。
既然说是无效SQL或者SQL为空,那就执行一下SQL试试。
贴出我的SQL语句:
SELECT
DATE_FORMAT( createDate, '%m-%d' ) AS createDate,
SUM( icount ) AS icount
FROM
cmscp.t_web_423
WHERE
1 = 1
GROUP BY
createDate
ORDER BY DATE_FORMAT( createDate, '%y-%m-%d' ) DESC
LIMIT 0,7;
结果执行报错Error querying database. Cause: java.sql.SQLException: java.lang.IllegalArgumentException: all columns in order by clause should be in the selected column list!DATE_FORMAT;
同样的语句在mysql上没有问题,mycat报这个错误。
从错误中发现将DATE_FORMAT加在查询字段中,即SQL改为:
SELECT
DATE_FORMAT( createDate, '%m-%d' ) AS createDate,DATE_FORMAT( createDate, '%y-%m-%d' ) ,
SUM( icount ) AS icount
FROM
cmscp.t_web_423
WHERE
1 = 1
GROUP BY
createDate
ORDER BY
DATE_FORMAT( createDate, '%y-%m-%d' ) DESC
LIMIT 0,7;
此时mycat正常执行。
总结:SQL语句在mycat库的使用问题。