Приветствую. Допустим есть плоская таблица со следующим набором данных: Макрорегион/УФПС/ОПС
В фильтре настраиваем иерархию:
Если сначала выбрать УФПС Брянской области, который относится к макрорегиону “ЦЕНТР”, а потом Макрорегион “СЕВЕРО-ЗАПАД”, то получаем ошибку:
Текст ошибки:
{
"message": "Service error",
"errorCode": 0,
"details": {
"detailedMessage": "Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(sum(revenue) :: 0, 'Float64' :: 2) -> CAST(sum(revenue), 'Float64') Float64 : 3'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 22.6.3.35 (official build))\n, server ClickHouseNode [uri=http://d02dc-lms-dev1d.main.russianpost.ru:8123/default, options={max_insert_block_size=100000,max_threads=8,socket_timeout=1800000}]@-1005560343 :: ReactiveQuery{traceId=luxmsbi=/Wh8FVChYvk=, dataSource=HikariDataSource (default:clickhouse), query='SELECT CAST(sum(revenue) AS Float64) / sum(check_cnt) as sum_check_cnt\nFROM (select all_check_cnt, channel_name, check_cnt, item_cnt, macroregion_name, \nconcat(CAST(ops_index as varchar) , ' ' , ops_name) as ops_index, \nops_name, revenue, ufps_name, value_date, month(cast(value_date as date)) as m_date, year(cast(value_date as date)) as y_date from dmfarma.zdorovie) AS dmfarma_zdorovie\nWHERE (1=1) AND (ufps_name = 'УФПС БРЯНСКОЙ ОБЛАСТИ') AND (macroregion_name = 'СЕВЕРО-ЗАПАД')', args=[], tx=true, thread='QueryPool-7271', fetchSize=64, queryTimeout=600}",
"errorType": "java.sql.SQLException",
"errorMessage": null,
"stackTrace": "java.sql.SQLException: Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(sum(revenue) :: 0, 'Float64' :: 2) -> CAST(sum(revenue), 'Float64') Float64 : 3'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 22.6.3.35 (official build))\n, server ClickHouseNode [uri=http://d02dc-lms-dev1d.main.russianpost.ru:8123/default, options={max_insert_block_size=100000,max_threads=8,socket_timeout=1800000}]@-1005560343 :: ReactiveQuery{traceId=luxmsbi=/Wh8FVChYvk=, dataSource=HikariDataSource (default:clickhouse), query='SELECT CAST(sum(revenue) AS Float64) / sum(check_cnt) as sum_check_cnt\nFROM (select all_check_cnt, channel_name, check_cnt, item_cnt, macroregion_name, \nconcat(CAST(ops_index as varchar) , ' ' , ops_name) as ops_index, \nops_name, revenue, ufps_name, value_date, month(cast(value_date as date)) as m_date, year(cast(value_date as date)) as y_date from dmfarma.zdorovie) AS dmfarma_zdorovie\nWHERE (1=1) AND (ufps_name = 'УФПС БРЯНСКОЙ ОБЛАСТИ') AND (macroregion_name = 'СЕВЕРО-ЗАПАД')', args=[], tx=true, thread='QueryPool-7271', fetchSize=64, queryTimeout=600}\n\tat com.luxms.bi.datagate.service.ReactiveQuery.supplyState(ReactiveQuery.java:175)\n\tSuppressed: The stacktrace has been enhanced by Reactor, refer to additional information below: \nError has been observed at the following site(s):\n\t*__checkpoint ⇢ Handler com.luxms.bi.datagate.http.DatagateController#executeQueryAsStreamJson(String, ExecuteQueryRequest) [DispatcherHandler]\n\t*__checkpoint ⇢ com.luxms.bi.http.TenantContextFilter [DefaultWebFilterChain]\n\t*__checkpoint ⇢ org.springframework.boot.actuate.metrics.web.reactive.server.MetricsWebFilter [DefaultWebFilterChain]\n\t*__checkpoint ⇢ HTTP POST \"/datagate/executeQuery\" [ExceptionHandlingWebHandler]\nOriginal Stack Trace:\n\t\tat com.luxms.bi.datagate.service.ReactiveQuery.supplyState(ReactiveQuery.java:175)\n\t\tat reactor.core.publisher.FluxGenerate.subscribe(FluxGenerate.java:80)\n\t\tat reactor.core.publisher.InternalFluxOperator.subscribe(InternalFluxOperator.java:62)\n\t\tat reactor.core.publisher.FluxSubscribeOn$SubscribeOnSubscriber.run(FluxSubscribeOn.java:194)\n\t\tat reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)\n\t\tat reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)\n\t\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)\n\t\tat java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)\n\t\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\n\t\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\n\t\tat java.base/java.lang.Thread.run(Thread.java:829)\nCaused by: java.sql.BatchUpdateException: Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(sum(revenue) :: 0, 'Float64' :: 2) -> CAST(sum(revenue), 'Float64') Float64 : 3'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 22.6.3.35 (official build))\n, server ClickHouseNode [uri=http://d02dc-lms-dev1d.main.russianpost.ru:8123/default, options={max_insert_block_size=100000,max_threads=8,socket_timeout=1800000}]@-1005560343\n\tat com.clickhouse.jdbc.SqlExceptionUtils.batchUpdateError(SqlExceptionUtils.java:107)\n\tat com.clickhouse.jdbc.internal.SqlBasedPreparedStatement.executeAny(SqlBasedPreparedStatement.java:219)\n\tat com.clickhouse.jdbc.internal.SqlBasedPreparedStatement.executeQuery(SqlBasedPreparedStatement.java:246)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat com.luxms.bi.datagate.service.ReactiveQuery.supplyState(ReactiveQuery.java:165)\n\tat reactor.core.publisher.FluxGenerate.subscribe(FluxGenerate.java:80)\n\tat reactor.core.publisher.InternalFluxOperator.subscribe(InternalFluxOperator.java:62)\n\tat reactor.core.publisher.FluxSubscribeOn$SubscribeOnSubscriber.run(FluxSubscribeOn.java:194)\n\tat reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)\n\tat reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)\n\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)\n\tat java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\n\tat java.base/java.lang.Thread.run(Thread.java:829)\n"
}
}