Group by datetime field fail

[mkdym]

when group by datatime field, metabase failed:

ClickHouse exception, code: 215, host: xx, port: 8123; Code: 215, e.displayText() = DB::Exception: Received from xx:9000. DB::Exception: Column `first_time` is not under aggregate function and not in GROUP BY. (version 20.3.9.70 (official build))

I find the sql generated by metabase, and execute it in clickhouse:

SELECT 
    toStartOfMonth(toDateTime(dw.ads_label_company.first_time)) AS first_time, 
    count() AS count
FROM dw.ads_label_company
GROUP BY toStartOfMonth(toDateTime(dw.ads_label_company.first_time))
ORDER BY toStartOfMonth(toDateTime(dw.ads_label_company.first_time)) ASC

Received exception from server (version 18.16.1):
Code: 215. DB::Exception: Received from xx:9000. DB::Exception: Column first_time is not under aggregate function and not in GROUP BY..

The problem is the as field, how can I solve it? Thanks.

[enqueue]

Hi @mkdym , I do not know how you created your question. When you are in the "raw" SQL mode, you can simply replace the toStartOfMonth... things in the GROUP BY and the ORDER BY with first_time. If you are using the query builder in Metabase please give me some steps to reproduce the error.

This is how I tried to reproduce your error:

1. Create Table

CREATE TABLE `ads_label_company` (first_time dateTime) Engine=Memory;

2. Fill with some data

INSERT INTO  `ads_label_company` (first_time) VALUES
('2020-02-08 13:37:00'),
('2020-02-10 01:07:00'),
('2020-03-01 00:00:01'),
('2020-03-04 00:10:10'),
('2020-03-05 12:00:12'),
('2020-05-08 07:07:07');

3. Create question

I think this is the question you are trying to ask, isn't it? If not, please let me know what you are trying to achieve. Thank you!

[mkdym]

Thanks for your reply.

I can reproduce this problem on my machine.

1. browse to the table from "浏览数据" button
2. group by
3. receive error

Then, I found the error logs from metabase:

 {:type "query",
  :query {:source-table 267, :aggregation [["count"]], :breakout [["datetime-field" ["field-id" 2335] "day"]]},
  :database 2,
  :parameters [],
  :middleware {:add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT toDate(`dw`.`ads_label_company`.`first_time`) AS `first_time`, count() AS `count` FROM `dw`.`ads_label_company` GROUP BY toDate(`dw`.`ads_label_company`.`first_time`) ORDER BY toDate(`dw`.`ads_label_company`.`first_time`) ASC",
  :params nil},
 :status :failed,
 :class java.lang.Throwable,
 :stacktrace
 ["ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)"
  "ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:875)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:616)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:117)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:100)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:95)"
  "ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:90)"
  "ru.yandex.clickhouse.ClickHousePreparedStatementImpl.executeQuery(ClickHousePreparedStatementImpl.java:132)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
  "--> driver.sql_jdbc.execute$fn__70879.invokeStatic(execute.clj:267)"
  "driver.sql_jdbc.execute$fn__70879.invoke(execute.clj:265)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)"
  "driver.sql_jdbc$fn__73052.invokeStatic(sql_jdbc.clj:49)"
  "driver.sql_jdbc$fn__73052.invoke(sql_jdbc.clj:47)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:69)"
  "query_processor.context.default$default_runf.invoke(default.clj:67)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
  "query_processor.reducible$pivot.invoke(reducible.clj:31)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__43149.invoke(mbql_to_native.clj:26)"
  "query_processor.middleware.check_features$check_features$fn__42463.invoke(check_features.clj:42)"
  "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__43314.invoke(optimize_datetime_filters.clj:133)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__47282.invoke(wrap_value_literals.clj:137)"
  "query_processor.middleware.annotate$add_column_info$fn__41084.invoke(annotate.clj:577)"
  "query_processor.middleware.permissions$check_query_permissions$fn__42338.invoke(permissions.clj:64)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__43813.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__42536.invoke(cumulative_aggregations.clj:61)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__44345.invoke(resolve_joins.clj:183)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__39271.invoke(add_implicit_joins.clj:245)"
  "query_processor.middleware.limit$limit$fn__43135.invoke(limit.clj:38)"
  "query_processor.middleware.format_rows$format_rows$fn__43116.invoke(format_rows.clj:81)"
  "query_processor.middleware.desugar$desugar$fn__42602.invoke(desugar.clj:22)"
  "query_processor.middleware.binning$update_binning_strategy$fn__41628.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__42144.invoke(resolve_fields.clj:24)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__38807.invoke(add_dimension_projections.clj:272)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__39027.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__39420.invoke(add_source_metadata.clj:105)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__44010.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__41269.invoke(auto_bucket_datetimes.clj:125)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__42191.invoke(resolve_source_table.clj:46)"
  "query_processor.middleware.parameters$substitute_parameters$fn__43795.invoke(parameters.clj:97)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__42243.invoke(resolve_referenced.clj:80)"
  "query_processor.middleware.expand_macros$expand_macros$fn__42858.invoke(expand_macros.clj:158)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__39451.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__47166.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021$fn__44025.invoke(resolve_database_and_driver.clj:33)"
  "driver$do_with_driver.invokeStatic(driver.clj:61)"
  "driver$do_with_driver.invoke(driver.clj:57)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__44021.invoke(resolve_database_and_driver.clj:27)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__43064.invoke(fetch_source_query.clj:243)"
  "query_processor.middleware.store$initialize_store$fn__47175$fn__47176.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
  "query_processor.store$do_with_store.invoke(store.clj:40)"
  "query_processor.middleware.store$initialize_store$fn__47175.invoke(store.clj:10)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__42120.invoke(cache.clj:209)"
  "query_processor.middleware.validate$validate_query$fn__47184.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__43162.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39289.invoke(add_rows_truncated.clj:36)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__47151.invoke(results_metadata.clj:128)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__42479.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__43884.invoke(process_userland_query.clj:136)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__42422.invoke(catch_exceptions.clj:174)"
  "query_processor.reducible$async_qp$qp_STAR___38090$thunk__38091.invoke(reducible.clj:101)"
  "query_processor.reducible$async_qp$qp_STAR___38090.invoke(reducible.clj:107)"
  "query_processor.reducible$sync_qp$qp_STAR___38099$fn__38102.invoke(reducible.clj:133)"
  "query_processor.reducible$sync_qp$qp_STAR___38099.invoke(reducible.clj:132)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:218)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:214)"
  "query_processor$fn__47350$process_query_and_save_execution_BANG___47359$fn__47362.invoke(query_processor.clj:233)"
  "query_processor$fn__47350$process_query_and_save_execution_BANG___47359.invoke(query_processor.clj:225)"
  "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403$fn__47406.invoke(query_processor.clj:245)"
  "query_processor$fn__47394$process_query_and_save_with_max_results_constraints_BANG___47403.invoke(query_processor.clj:238)"
  "api.dataset$fn__50692$fn__50695.invoke(dataset.clj:52)"
  "query_processor.streaming$streaming_response_STAR_$fn__34128$fn__34129.invoke(streaming.clj:73)"
  "query_processor.streaming$streaming_response_STAR_$fn__34128.invoke(streaming.clj:72)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)"
  "async.streaming_response$do_f_async$fn__23118.invoke(streaming_response.clj:85)"],
 :context :ad-hoc,
 :error
 "ClickHouse exception, code: 215, host: xxxx, port: 8123; Code: 215, e.displayText() = DB::Exception: Received from xxxx:9000. DB::Exception: Column `first_time` is not under aggregate function and not in GROUP BY. (version 20.3.9.70 (official build))\n",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:type :query,
  :query
  {:source-table 267, :aggregation [[:aggregation-options [:count] {:name "count"}]], :breakout [[:datetime-field [:field-id 2335] :day]], :order-by [[:asc [:datetime-field [:field-id 2335] :day]]]},
  :database 2,
  :middleware {:add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :nested? false,
   :query-hash [-80, -36, -116, 61, 84, 29, -88, -92, -42, -55, 105, 49, 34, 110, -108, 102, -33, 80, 92, 74, 71, 90, 41, 7, -54, -29, 45, 100, 69, 89, 100, -115]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :data {:rows [], :cols []}}

and execute the sql statement by clickhouse-client, also receive an error:

The statement can success if removed ASfirst_time``.

Table created sql:

So, I think the generated sql statement is something wrong. Or my version or some setting is wrong?

[clebeg]

I meet the same question! the column type is Date，the local query toDate(first_time) group by toDate(first_time) can not work!

[clebeg]

this sql statement syntax is wrong!

[enqueue]

Hi @clebeg and @mkdym ,

which version of ClickHouse server are you running? It could be that the query processor has become a little bit smarter during the last couple of releases. Here is what I did to try and reproduce your issue on my local machine (albeit running a very fresh version of ClickHouse 20.4.5.36):

Create table (I do not have replication, but should not really matter):

CREATE TABLE ads_local_label_company (
  `build_date` Date,
  `company_id` String,
  `label` String,
  `first_time` DateTime,
  `last_time` DateTime
) ENGINE=MergeTree
  PARTITION BY tuple()
  ORDER BY (build_date, company_id, label, first_time, last_time);

Insert five rows of sample data

INSERT INTO ads_local_label_company (
  build_date,
  company_id,
  label,
  first_time,
  last_time
)
VALUES
('2020-02-08', 'Acme Inc.', 'green', date_add(now(), interval -1 day), now()),
('2020-02-10', 'Acme Inc.', 'blue', date_add(now(), interval -2 day), now()),
('2020-02-10', 'Foo Corp.', 'green', date_add(now(), interval -3 day), now()),
('2020-06-30', 'Foo Corp.', 'blue', date_add(now(), interval -4 day), now()),
('2020-06-10', 'Bar Ltd.', 'blue', date_add(now(), interval -5 day), now());

Copy and paste your query:

SELECT 
    toDate(dw.ads_local_label_company.first_time) AS first_time, 
    count() AS count
FROM dw.ads_local_label_company
GROUP BY toDate(dw.ads_local_label_company.first_time)
ORDER BY toDate(dw.ads_local_label_company.first_time) ASC

Result:

┌─first_time─┬─count─┐
│ 2020-06-27 │     1 │
│ 2020-06-28 │     1 │
│ 2020-06-29 │     1 │
│ 2020-06-30 │     1 │
│ 2020-07-01 │     1 │
└────────────┴───────┘

5 rows in set. Elapsed: 0.117 sec.

[mkdym]

Thanks for reply. My version is 20.3.9.70, clebeg's version is 19.14.7 (can see in the screenshots). I will try 20.4 later and back to report result.

[clebeg]

Thanks for replay, I Try to Upgrade My ClickHouse Cluster version, but this work may be cost so much time! then i will back report again!

[mkdym]

I found this problem is related to clickhouse distributed table. Reproduce steps:

1. create a clickhouse-cluster(20.3.7.90). use https://github.com/tetafro/clickhouse-cluster

2. create metabase(0.41.5) with clickhouse driver(0.8.1). and add clickhouse to metabase.

docker run -d -p 3000:3000 -v `pwd`/plugins:/plugins --name metabase metabase/metabase:v0.41.5

3. create clickhouse local and distributed table.

CREATE TABLE ads_local_label_company on cluster ch_cluster (`build_date` Date, `company_id` String, `label` String, `first_time` DateTime, `last_time` DateTime) ENGINE = ReplicatedMergeTree('/clickhouse/{shard}/default/ads_local_label_company', '{replica}') PARTITION BY toYYYYMMDD(build_date) ORDER BY (build_date, company_id, label, first_time, last_time) SETTINGS index_granularity = 8192;

CREATE TABLE ads_label_company on cluster ch_cluster (`build_date` Date, `company_id` String, `label` String, `first_time` DateTime, `last_time` DateTime) ENGINE = Distributed(ch_cluster, default, ads_local_label_company, rand());

insert into ads_local_label_company values ('2021-11-16', '123', 'aaa', '2021-11-16 00:00:00', '2021-11-16 00:00:00');

4. query on metabse.

5. the error sql in error log is

SELECT toDate(`default`.`ads_label_company`.`build_date`) AS `build_date`, count(*) AS `count` FROM `default`.`ads_label_company` GROUP BY toDate(`default`.`ads_label_company`.`build_date`) ORDER BY toDate(`default`.`ads_label_company`.`build_date`) ASC

and these below sqls execute correctly:

SELECT
    toDate(default.ads_label_company.build_date),
    count(*) AS count
FROM default.ads_label_company
GROUP BY toDate(default.ads_label_company.build_date)
ORDER BY toDate(default.ads_label_company.build_date) ASC;

SELECT
    toDate(default.ads_local_label_company.build_date) AS build_date,
    count(*) AS count
FROM default.ads_local_label_company
GROUP BY toDate(default.ads_local_label_company.build_date)
ORDER BY toDate(default.ads_local_label_company.build_date) ASC

So I think maybe it is a problem of (clickhouse distributed table or metabase).

[mkdym]

Tried clickhouse latest 21.12 and 20.4, it's ok. It seems a problem on clickhouse version 20.3.9 and before.

[slvrtrn]

Seems to not be reproducible on recent ClickHouse versions.