zhaoyongjie commented on pull request #18569:
URL: https://github.com/apache/superset/pull/18569#issuecomment-1031532537
Thanks for your hard work. Before I reviewed the code, I did some
experiments.
1. Let us use this SQL(PG) to make a virtual dataset, then open it in
explore.
```
select
'2022-01-03 00:00:00'::TIMESTAMP as datetime0,
'2022-02-07 00:00:00'::TIMESTAMP as datetime1
UNION ALL
SELECT '2022-01-04 00:00:00'::TIMESTAMP,
'2022-02-08 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-05 00:00:00'::TIMESTAMP,
'2022-02-09 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-06 00:00:00'::TIMESTAMP,
'2022-02-10 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-07 00:00:00'::TIMESTAMP,
'2022-02-11 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-08 00:00:00'::TIMESTAMP,
'2022-02-12 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-09 00:00:00'::TIMESTAMP,
'2022-02-13 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-10 00:00:00'::TIMESTAMP,
'2022-02-14 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-11 00:00:00'::TIMESTAMP,
'2022-02-15 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-12 00:00:00'::TIMESTAMP,
'2022-02-16 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-13 00:00:00'::TIMESTAMP,
'2022-02-17 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-14 00:00:00'::TIMESTAMP,
'2022-02-18 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-15 00:00:00'::TIMESTAMP,
'2022-02-19 00:00:00'::TIMESTAMP
UNION ALL
SELECT '2022-01-16 00:00:00'::TIMESTAMP,
'2022-02-20 00:00:00'::TIMESTAMP
```
2. Select *table*, put datatime0 and datetime1 to **groupby control**, put
**count(*)** to metric, select **month** grain
3. viz result and query result is different.
<img width="1279" alt="image"
src="https://user-images.githubusercontent.com/2016594/152806687-5b8460a5-4cfa-4010-b84e-000ba18c22ee.png";>
This problem was not actually introduced by this PR, but it is a
long-standing one.
let us check SQL, we can see time function only apply on **main time
column**,
```
SELECT DATE_TRUNC('month', datetime0) AS datetime0,
datetime1 AS datetime1,
count(*) AS count
FROM
(select '2022-01-03 00:00:00'::TIMESTAMP as datetime0,
'2022-02-07 00:00:00'::TIMESTAMP as datetime1
UNION ALL SELECT '2022-01-04 00:00:00'::TIMESTAMP,
'2022-02-08 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-05 00:00:00'::TIMESTAMP,
'2022-02-09 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-06 00:00:00'::TIMESTAMP,
'2022-02-10 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-07 00:00:00'::TIMESTAMP,
'2022-02-11 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-08 00:00:00'::TIMESTAMP,
'2022-02-12 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-09 00:00:00'::TIMESTAMP,
'2022-02-13 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-10 00:00:00'::TIMESTAMP,
'2022-02-14 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-11 00:00:00'::TIMESTAMP,
'2022-02-15 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-12 00:00:00'::TIMESTAMP,
'2022-02-16 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-13 00:00:00'::TIMESTAMP,
'2022-02-17 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-14 00:00:00'::TIMESTAMP,
'2022-02-18 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-15 00:00:00'::TIMESTAMP,
'2022-02-19 00:00:00'::TIMESTAMP
UNION ALL SELECT '2022-01-16 00:00:00'::TIMESTAMP,
'2022-02-20 00:00:00'::TIMESTAMP) AS virtual_table
GROUP BY DATE_TRUNC('month', datetime0),
datetime1
ORDER BY count DESC
LIMIT 1000;
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/2016594/152806687-5b8460a5-4cfa-4010-b84e-000ba18c22ee.png"](https://user-images.githubusercontent.com/2016594/152806687-5b8460a5-4cfa-4010-b84e-000ba18c22ee.png"){kind=link}