ES dataset will auto add default datetime column to sql though chart already has another datetime column as time column

[winglight]

A clear and concise description of what the bug is.

How to reproduce the bug

1. create a dataset from any ES index with temporal columns
2. create a chart based on the dataset previous created and select TABLE mode
3. check the default datetime column(let's say, default_date) of the dataset, and choose another temporal column(let's say, second_date) as time column
4. choose any TIME RANGE filter, such as previous calendar week and update chart
5. click the right top button "..." and click the button "View query"
6. in SQL, you'll see:

WHERE default_date>= '2022-11-14 00:00:00.000000'
  AND default_date< '2022-11-21 00:00:00.000000'
  AND second_date>= '2022-11-14 00:00:00.000000'
  AND second_date< '2022-11-21 00:00:00.000000'

As contrast, if the dataset comes from mysql, it works as expected. So, I certainly confirm that's a bug.

Expected results

The SQL should be:

WHERE 
  AND second_date>= '2022-11-14 00:00:00.000000'
  AND second_date< '2022-11-21 00:00:00.000000'

Actual results

WHERE default_date>= '2022-11-14 00:00:00.000000'
  AND default_date< '2022-11-21 00:00:00.000000'
  AND second_date>= '2022-11-14 00:00:00.000000'
  AND second_date< '2022-11-21 00:00:00.000000'

Screenshots

If applicable, add screenshots to help explain your problem.

Environment

(please complete the following information):

• browser type and version: chrome 107
• superset version: k8s helm chart version: 0.7.7 app version: 1.0
• python version: 3.8.13
• node.js version: N/A
• any feature flags active:

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

Add any other context about the problem here.

[rusackas]

CC @lauderbaugh holler if you're willing to help repro/triage this one!

[volhahedranovich]

I'm experiencing the same issue.

'day' is default datetime column, but if I select another time column, shouldn't the query be filtered only by the selected one?

[maunke]

I am also experiencing the bug with clickhouse.

Superset: apache/superset:2.0.1
ClickHouse: yandex/clickhouse-server:21.11.2.2
clickhouse-sqlalchemy: 0.1.10

See where section on generated sql query

WHERE created_at >= toDateTime('2022-12-01 00:00:00')
  AND created_at < toDateTime('2023-01-01 00:00:00')
  AND delivered_at >= '2022-12-01 00:00:00'
  AND delivered_at < '2023-01-01 00:00:00'

superset dataset
created_at: is temporal and default datetime
delivered_at: is temporal and nullable datetime

[mohamed-artal]

I'm experiencing the same issue.
with version 2.0.1.

[eloymc98]

I'm experiencing the same issue with Superset version 2.0.0 and Clickhouse

[Always-prog]

Same issue with Superset 2.1.1 and Clickhouse database

[Always-prog]

As I see - this is the time secondary column feature - https://github.com/apache/superset/blob/master/superset/db_engine_specs/README.md#time_secondary_columns--false.
And it's enabled on clickhouse: https://github.com/apache/superset/blob/master/superset/db_engine_specs/clickhouse.py#L55.

But, it can be disabled without changing the source code?

[Always-prog]

Hello @rusackas @lauderbaugh!
How about adding a checkbox for feature time secondary column to the dataset? i think it would be better than hardcoding it for all database connections (Clickhouse, elasticsearch, Kusto). It would also allow the ability to add a second time column to other databases.

I can create PR for it