You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/06/23 01:07:56 UTC
[GitHub] [shardingsphere] xiaoqunhu commented on issue #18139: [5.1.1] missing some fields when using MySQL aggregate function with join
xiaoqunhu commented on issue #18139:
URL: https://github.com/apache/shardingsphere/issues/18139#issuecomment-1163814731
This is My Log:
1. this one is mising cnt
2022-06-22 16:45:35.880 INFO 28204 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: **select * from (**SELECT d.id,count(d.id) as cnt
FROM one_vacant_visit_detail d
left join one_visit_relate_order r on d.id = r.visit_id
WHERE 1 = 1
AND DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s') >= concat('2022-05-24 00:00:00', ' 00:00:00')
AND concat('2022-05-31 23:59:59', ' 23:59:59') >= DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s')
group by d.id order by d.created_time desc**) t**
2022-06-22 16:45:35.880 INFO 28204 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-06-22 16:45:35.880 INFO 28204 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL(simple): [slave] ::: 1
<== Columns: id
<== Row: 1461516732026003479
<== Total: 1
2.this is OK
2022-06-22 16:47:19.671 INFO 21000 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT d.id,count(d.id) as cnt
FROM one_vacant_visit_detail d
left join one_visit_relate_order r on d.id = r.visit_id
WHERE 1 = 1
AND DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s') >= concat('2022-05-24 00:00:00', ' 00:00:00')
AND concat('2022-05-31 23:59:59', ' 23:59:59') >= DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s')
group by d.id order by d.created_time desc
2022-06-22 16:47:19.671 INFO 21000 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-06-22 16:47:19.671 INFO 21000 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL(simple): [slave] ::: 1
<== Columns: id, cnt
<== Row: 1461516732026003479, 1
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7646ec15]
sum up:
1.
sql <
select * from (SELECT d.id,count(d.id) as cnt
FROM one_vacant_visit_detail d
left join one_visit_relate_order r on d.id = r.visit_id
WHERE 1 = 1
AND DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s') >= concat('2022-05-24 00:00:00', ' 00:00:00')
AND concat('2022-05-31 23:59:59', ' 23:59:59') >= DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s')
group by d.id order by d.created_time desc) t
>
is not ok,
but
<
SELECT d.id,count(d.id) as cnt
FROM one_vacant_visit_detail d
left join one_visit_relate_order r on d.id = r.visit_id
WHERE 1 = 1
AND DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s') >= concat('2022-05-24 00:00:00', ' 00:00:00')
AND concat('2022-05-31 23:59:59', ' 23:59:59') >= DATE_FORMAT(d.created_time, '%Y-%m-%d %H:%i%s')
group by d.id order by d.created_time desc
>
is ok
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org