You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "NikoJ (via GitHub)" <gi...@apache.org> on 2023/03/15 13:52:53 UTC
[GitHub] [superset] NikoJ opened a new issue, #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
NikoJ opened a new issue, #23384:
URL: https://github.com/apache/superset/issues/23384
When using `Mixed Chart` I encountered an Error when using `TIME COLUMN` along with `X-AXIS` for the same value when the source is `Clickhouse`.
The error looks like:
```
Error: :HTTPDriver for http://xx.xxx.x.84:8123 returned response code 500) Code: 215. DB::Exception: Column `dt` is not under aggregate function and not in GROUP BY. Have columns: ['count()','toStartOfDay(toDateTime(toStartOfDay(toDateTime(dt))))']: While processing toStartOfDay(toDateTime(dt)) AS dt, count() AS ...
```
I found a mistake in the query generation for Clickhouse:
```sql
SELECT toStartOfDay(toDateTime(`dt`)) AS `dt`,
COUNT(*) AS `count`
FROM
(SELECT dt,
id,
message
FROM default.test_table_v2) AS `virtual_table`
GROUP BY toStartOfDay(toDateTime(`dt`))
ORDER BY `count` DESC
LIMIT 10000;
```
The alias specified in the select `SELECT toStartOfDay(toDateTime(dt)) AS dt` is then substituted for the section with grouping `GROUP BY toStartOfDay(toDateTime(toStartOfDay(toDateTime(dt))))`.
#### How to reproduce the bug
1. Create `Database` with `Clickhouse Connect`
2. Create table
```
CREATE TABLE IF NOT EXISTS demo(
id UInt64,
message String,
dt Date
) ENGINE = MergeTree()
ORDER BY id;
```
4. Insert test data
```
INSERT INTO demo SELECT abs(toInt8(rand(2))) as id, fuzzBits('Demo',0.01) as message, '2023-02-05' as dt FROM numbers(100);
INSERT INTO demo SELECT abs(toInt8(rand(2))) as id, fuzzBits('Demo',0.01) as message, '2023-02-12' as dt FROM numbers(100);
INSERT INTO demo SELECT abs(toInt8(rand(2))) as id, fuzzBits('Demo',0.01) as message, '2023-03-01' as dt FROM numbers(100);
```
5. Create `Dataset` based on clickhouse data
6. Create `Chart` based on `Dataset` from p. 5 with chart type `Mixed Chart
* `TIME COLUMN` = `dt`
* `TIME GRAIN` = `Day`
* `X-AXIS ` = `dt`
* Query A `METRICS` = `SUM(id)`
* Query B `METRICS` = `SUM(id)`
>see Screenshots
### Expected results
In my opinion there are two ways. Or generate a query with a different alias or substitute an alias in the grouping section.
1)
```sql
SELECT toStartOfDay(toDateTime(`dt`)) AS `dt_`,
COUNT(*) AS `count`
FROM
(SELECT dt,
id,
message
FROM default.demo) AS `virtual_table`
GROUP BY toStartOfDay(toDateTime(`dt`))
ORDER BY `count` DESC
LIMIT 10000;
```
2)
```sql
SELECT toStartOfDay(toDateTime(`dt`)) AS `dt`,
COUNT(*) AS `count`
FROM
(SELECT dt,
id,
message
FROM default.demo) AS `virtual_table`
GROUP BY `dt`
ORDER BY `count` DESC
LIMIT 10000;
```
#### Screenshots
### Environment
- browser type and version: Firefox/110.0
- superset version: 2.0.1
- python version: 3.8.12
- superset-helm-chart-0.8.5
- clickhouse-connect==0.5.3
### Checklist
- [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [x] I have reproduced the issue with at least the latest released version of superset.
- [x] I have checked the issue tracker for the same issue and I haven't found one similar.
--
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@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] ktncktnc commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "ktncktnc (via GitHub)" <gi...@apache.org>.
ktncktnc commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1515964521
This annoying bug happens all the time. I hope it will be fixed soon.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-2218728367
It seems [this has been fixed on the CH side](https://github.com/apache/superset/pull/26735#issuecomment-2218461980) and this is no longer an issue. Closing, but happy to reopen if it's still an issue.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas closed issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
URL: https://github.com/apache/superset/issues/23384
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-2047699347
Thanks @kovitals - if anyone else on this thread finds this to be a sufficient answer (or even a sufficient workaround), this would be a great documentation update on the [Clickhouse docs page](https://superset.apache.org/docs/databases/clickhouse/). A PR from anyone would be helpful on that point to close this out.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] eriendeng commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "eriendeng (via GitHub)" <gi...@apache.org>.
eriendeng commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1664214193
alias after group by should not be the same with the raw column name.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] danielG1beat commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "danielG1beat (via GitHub)" <gi...@apache.org>.
danielG1beat commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1580598352
I have it too :(
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "NikoJ (via GitHub)" <gi...@apache.org>.
NikoJ commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-2056479582
@rusackas Thanks! When I have free time, I will check it and then close this issue as solved if everything 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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] nikolastojkoski commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "nikolastojkoski (via GitHub)" <gi...@apache.org>.
nikolastojkoski commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1689266666
I have the same issue on version 2.1.0 when trying to use timestamp as dimension in Aggregate Table.
> I used a trick to solve this problem but it's inconvenient
@NikoJ can you share your workaround?
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "Rydberg95 (via GitHub)" <gi...@apache.org>.
Rydberg95 commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-2056961849
> Can be fixed on ClickHouse side, with override default value for property **prefer_column_name_to_alias**
>
> via user setting for default profile
>
> ```
> <clickhouse>
> <profiles>
> <default>
> <prefer_column_name_to_alias>1</prefer_column_name_to_alias>
> ....
> </default>
> ...
> ```
Thanks, seems to work great!
As an alternative, if someone else wants to add the setting via [SQL-driven workflow](https://clickhouse.com/docs/en/operations/access-rights#access-control), the below command [sets up a new settings profile](https://clickhouse.com/docs/en/sql-reference/statements/create/settings-profile#create-settings-profile-statement) and assigns it to a user:
`CREATE SETTINGS PROFILE superset SETTINGS prefer_column_name_to_alias = 1 TO *your_clickhouse_username*`
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "chriscomeau79 (via GitHub)" <gi...@apache.org>.
chriscomeau79 commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1777985873
Still present in 3.0.1. The workaround above with setting a custom expression and alias works.
Similar situation where we're trying to use a DateTime64 column as X axis and it's grouping by day, with inconsistent use of aliases in the generated query.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse [superset]
Posted by "kovitals (via GitHub)" <gi...@apache.org>.
kovitals commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-2047447780
Can be fixed on ClickHouse side, with user setting for default profile
```
<clickhouse>
<profiles>
<default>
**<prefer_column_name_to_alias>1</prefer_column_name_to_alias>**
....
</default>
...
```
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] Rydberg95 commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "Rydberg95 (via GitHub)" <gi...@apache.org>.
Rydberg95 commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1729707338
Unfortunately still here in 3.0.0.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] Rydberg95 commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "Rydberg95 (via GitHub)" <gi...@apache.org>.
Rydberg95 commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1729723685
A workaround I found is actually to edit the Date column that is related to the error to be defined as a "custom" function, like this:
Make sure that the column has the function icon after it's edited (for me i had to input a blank space and remove it before saving in above step. When it is a function, it looks like below, with the f(x) icon at the start.
This seems to mitigate the error, and so far is not producing any side effects noticed by me. Obviously not a solution worth a PR, but could be an interim solution.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] NikoJ commented on issue #23384: Error when using "TIME COLUMN" along with "X-AXIS" for same value for Clickhouse
Posted by "NikoJ (via GitHub)" <gi...@apache.org>.
NikoJ commented on issue #23384:
URL: https://github.com/apache/superset/issues/23384#issuecomment-1664592449
I know but such query generates Superset. I used a trick to solve this problem but it's inconvenient
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org