Issue 1:
this is standart superset time series bar chart:

query:

SELECT DATE(order_date, -strftime('%d', order_date) || ' days', '+1 day') AS __timestamp,
deal_size AS deal_size,
sum(sales) AS "(Sales)"
FROM "Vehicle Sales"
JOIN
(SELECT deal_size AS deal_size__,
sum(sales) AS mme_inner__
FROM "Vehicle Sales"
WHERE order_date >= '2003-01-01 00:00:00.000000'
AND order_date < '2005-06-01 00:00:00.000000'
GROUP BY deal_size
ORDER BY mme_inner__ DESC
LIMIT 100
OFFSET 0) AS anon_1 ON deal_size = deal_size__
WHERE order_date >= '2003-01-01 00:00:00.000000'
AND order_date < '2005-06-01 00:00:00.000000'
GROUP BY deal_size,
DATE(order_date, -strftime('%d', order_date) || ' days', '+1 day')
ORDER BY "(Sales)" DESC
LIMIT 50000
OFFSET 0;

request that should have been:

SELECT DATE(order_date, -strftime('%d', order_date) || ' days', '+1 day') AS __timestamp,
deal_size AS deal_size,
sum(sales) AS "(Sales)"
FROM "Vehicle Sales"
WHERE order_date >= '2003-01-01 00:00:00.000000'
AND order_date < '2005-06-01 00:00:00.000000'
GROUP BY deal_size,
DATE(order_date, -strftime('%d', order_date) || ' days', '+1 day')
ORDER BY "(Sales)" DESC
LIMIT 50000
OFFSET 0;

why does superset add this request?

JOIN
(SELECT deal_size AS deal_size__,
sum(sales) AS mme_inner__
FROM "Vehicle Sales"
WHERE order_date >= '2003-01-01 00:00:00.000000'
AND order_date < '2005-06-01 00:00:00.000000'
GROUP BY deal_size
ORDER BY mme_inner__ DESC
LIMIT 100
OFFSET 0) AS anon_1 ON deal_size = deal_size__

This JOIN increases the data loading time

Old superset works fine

Issue 2:
First of all I have setup Superset with the current docker image (https://superset.apache.org/docs/installation/installing-superset-using-docker-compose) on an AWS EC2 instance. I've also installed superset on my local laptop (https://hub.docker.com/r/apache/superset) but had the same thing.

After registering the AWS Athena driver and an dataset, I have created a simple time series diagram with a "group by". The query is working and data is visualized. Please see screenshot.

When I look at the sql query executed by Athena you can see this: --> ### Actual results

There is a subquery included in the query which seems to be useless. The columns of the subquery are not used in the outer query. The join is useless.

The disadvantage is, that the query takes much more time with this subquery - x*100% in comparision to the expected query.

How to reproduce the bug

create a timeseries chart and group by an attribute.

Expected results

SELECT date_trunc('day', CAST(date_parse(p_date, '%Y-%m-%d') AS TIMESTAMP)) AS "__timestamp",

"c_jobid" AS "c_jobid",

COUNT(*) AS "count"

FROM "x"."y"

WHERE "p_date" >= '2021-10-01'

GROUP BY "c_jobid",

date_trunc('day', CAST(date_parse(p_date, '%Y-%m-%d') AS TIMESTAMP))

ORDER BY "count" DESC

LIMIT 1000

Actual results

SELECT date_trunc('day', CAST(date_parse(p_date, '%Y-%m-%d') AS TIMESTAMP)) AS "timestamp",

"c_jobid" AS "c_jobid",

COUNT(*) AS "count"

FROM "x"."y"

JOIN

(SELECT "c_jobid" AS "c_jobid",

COUNT(*) AS "mme_inner__"

FROM "vehicle_cdc2_cca_qual_pre"."te_mgu"

WHERE "p_date" >= '2021-10-01'

GROUP BY "c_jobid"

ORDER BY "mme_inner__" DESC

LIMIT 100) AS "anon_1" ON "c_jobid" = "c_jobid__"

WHERE "p_date" >= '2021-10-01'

GROUP BY "c_jobid",

date_trunc('day', CAST(date_parse(p_date, '%Y-%m-%d') AS TIMESTAMP))

ORDER BY "count" DESC

LIMIT 1000

Screenshots

Environment

(please complete the following information):

browser type and version: Firefox 88.0

superset version: Superset 0.0.0dev

python version: Python 3.8.12

node.js version: ? (docker exec -it superset node -v --> node not on path)

any feature flags active: no

Checklist

Make sure to follow these steps before submitting your issue - thank you!

I have checked the superset logs for python stacktraces and included it here as text if there are any.

I have reproduced the issue with at least the latest released version of superset.

I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context