fix(time grain): time grain clickhouse db error

[hxxdraised]

SUMMARY

DB error on chart load when time granularity is set when the source is Clickhouse. Error example: Error: Orig exception: Code: 215. DB::Exception: Column `Column2` is not under aggregate function and not in GROUP BY: While processing toStartOfDay(toDateTime(Column2)) AS Column2, count() AS count.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

before: after:

TESTING INSTRUCTIONS

1. Create ClickHouse database
2. Make table with temporal column
3. Install the Clickhouse driver via adding clickhouse-sqlalchemy==0.2.5 line in docker/requirements-local.txt
4. Connect ClickHouse DB to superset
5. Create line chart from table datasource and select temporal column in X-Axis option

ADDITIONAL INFORMATION

• [x] Has associated issue: Fixes #23384
• [ ] Required feature flags:
• [ ] Changes UI
• [ ] Includes DB Migration (follow approval process in SIP-59)
  • [ ] Migration is atomic, supports rollback & is backwards-compatible
  • [ ] Confirm DB migration upgrade and downgrade tested
  • [ ] Runtime estimates and downtime expectations provided
• [ ] Introduces new feature or API
• [ ] Removes existing feature or API

[codecov[bot]]

Codecov Report

Attention: 1 lines in your changes are missing coverage. Please review.

Comparison is base (6443001) 69.07% compared to head (b4da5ce) 69.13%. Report is 51 commits behind head on master.

Files	Patch %	Lines
superset/models/helpers.py	50.00%	1 Missing :warning:

Additional details and impacted files

@@            Coverage Diff             @@
##           master   #26735      +/-   ##
==========================================
+ Coverage   69.07%   69.13%   +0.06%     
==========================================
  Files        1930     1930              
  Lines       75279    75010     -269     
  Branches     8429     8429              
==========================================
- Hits        51999    51859     -140     
+ Misses      21133    21004     -129     
  Partials     2147     2147              

Flag	Coverage Δ
hive	53.85% <75.00%> (+0.28%)	:arrow_up:
mysql	78.02% <75.00%> (+0.19%)	:arrow_up:
postgres	78.12% <75.00%> (+0.19%)	:arrow_up:
presto	53.80% <75.00%> (+0.27%)	:arrow_up:
python	83.01% <75.00%> (+0.23%)	:arrow_up:
sqlite	77.71% <75.00%> (+0.19%)	:arrow_up:
unit	56.38% <75.00%> (+0.36%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[zhaoyongjie]

@hxxdraised please post the original SQL and fixed SQL in the PR description for the reviewing. I guess that this issue is an known issue in CH SQL parser side, but there is a workaround to fix it in Superset side.

To create a verbose name in the Dataset level in Superset, and then the SQL snippet will be toStartOfDay(toDateTime(Column2)) AS THE_NEW_VERBOSE_NAME

[TechAuditBI]

@john-bodley @betodealmeida Should not we somehow connect this PR with SIPs 115 and 117?

[john-bodley]

@TechAuditBI this change seems unrelated to SIP-115 and SIP-117. Personally—if we adopt the "shift left" mentatility—th necessary change should be made in the clickhouse-connect SQLAlchemy dialect.

[rusackas]

Hi all! Just wondering if there's anyone sees a way to move forward on this, and close out the issue (which is getting pretty stale).

I'm also wondering if this is even still an issue, since clickhouse-connect was 0.5.3 at the time of reporting and is now on 0.7.7 - maybe we'll get lucky :D