Система не верно формирует запрос к источнику (БД Clickhouse)

amikhailovsky · 11.Январь.2023 07:41:05

Коллеги, добрый день.
В Luxms версии 8.10.23 не работает запрос

в БД есть поле с типом Date

это запрос к БД из куба
select default.“v_Request54”.AFFECTED_ITEM as AFFECTED_ITEM,
default.“v_Request54”.CATEGORY as CATEGORY,
default.“v_Request54”.CLOSE_TIME as CLOSE_TIME,
default.“v_Request54”.“CLOSE_TIME_month” as “CLOSE_TIME_month”,
default.“v_Request54”.“CLOSE_TIME_year” as “CLOSE_TIME_year”,
default.“v_Request54”.DURATION_REACTION as DURATION_REACTION,
default.“v_Request54”.DURATION_REGISTRATION as DURATION_REGISTRATION,
default.“v_Request54”.DURATION_SOLUTION as DURATION_SOLUTION,
default.“v_Request54”.HPC_ARRIVE_TYPE as HPC_ARRIVE_TYPE,
default.“v_Request54”.HPC_CLOSE_MARK as HPC_CLOSE_MARK,
default.“v_Request54”.HPC_DIRECTION as HPC_DIRECTION,
default.“v_Request54”.HPC_GROUPSERVICE as HPC_GROUPSERVICE,
default.“v_Request54”.HPC_RETURN_TO_WORK as HPC_RETURN_TO_WORK,
default.“v_Request54”.HPC_SERVICE as HPC_SERVICE,
default.“v_Request54”.HPC_TIME_ACTUAL_END as HPC_TIME_ACTUAL_END,
default.“v_Request54”.“HPC_TIME_ACTUAL_END_month” as “HPC_TIME_ACTUAL_END_month”,
default.“v_Request54”.“HPC_TIME_ACTUAL_END_year” as “HPC_TIME_ACTUAL_END_year”,
default.“v_Request54”.HPC_TIME_ACTUAL_START as HPC_TIME_ACTUAL_START,
default.“v_Request54”.“HPC_TIME_ACTUAL_START_month” as “HPC_TIME_ACTUAL_START_month”,
default.“v_Request54”.“HPC_TIME_ACTUAL_START_year” as “HPC_TIME_ACTUAL_START_year”,
default.“v_Request54”.HPC_TIME_REGISTRED as HPC_TIME_REGISTRED,
default.“v_Request54”.“HPC_TIME_REGISTRED_month” as “HPC_TIME_REGISTRED_month”,
default.“v_Request54”.“HPC_TIME_REGISTRED_year” as “HPC_TIME_REGISTRED_year”,
default.“v_Request54”.HPC_TYPE_FEEDBACK as HPC_TYPE_FEEDBACK,
default.“v_Request54”.INCIDENT_ID as INCIDENT_ID,
default.“v_Request54”.OPEN_TIME as OPEN_TIME,
default.“v_Request54”.“OPEN_TIME_month” as “OPEN_TIME_month”,
default.“v_Request54”.“OPEN_TIME_year” as “OPEN_TIME_year”,
default.“v_Request54”.PRIORITY_CODE as PRIORITY_CODE,
default.“v_Request54”.SLA_DURATION_REACTION as SLA_DURATION_REACTION,
default.“v_Request54”.SLA_DURATION_REGISTRATION as SLA_DURATION_REGISTRATION,
default.“v_Request54”.SLA_DURATION_SOLUTION as SLA_DURATION_SOLUTION,
default.“v_Request54”.SLA_REACTION_BREACH as SLA_REACTION_BREACH,
default.“v_Request54”.SLA_REGISTRATION_BREACH as SLA_REGISTRATION_BREACH,
default.“v_Request54”.SLA_SOLUTION_BREACH as SLA_SOLUTION_BREACH,
default.“v_Request54”.HPC_CLOSE_MARK_INTEGER as HPC_CLOSE_MARK_INTEGER,
default.“v_Request54”.RESOLUTION_CODE as RESOLUTION_CODE
from default.“v_Request54”

в кубе это поле с типом PERIOD

на диаграмме включили фильтр по полю “Дата поступления” (OPEN_TIME)
и сразу при смене значений фильтра выходит ошибка

sergey_pismennyy · 12.Январь.2023 10:48:37

Добрый день!
Коллеги, для полного анализа пришлите ddl самой таблицы и вьюхи. В ошибке база говорит о разнице типов и причин возникновения на самом деле несколько. Точнее сможем сказать, когда смоделируем у себя.

amikhailovsky · 12.Январь.2023 11:11:56

Добрый.
Приложил. Так как портал не дает выложить в виде файла. то прямо в тексте.
====view===
– default.v_Request54 source

CREATE VIEW default.v_Request54
(

`INCIDENT_ID` String,

`AFFECTED_ITEM` String,

`CATEGORY` String,

`CLOSE_TIME` Nullable(Date),

`OPEN_TIME` Nullable(Date),

`PRIORITY_CODE` Nullable(Int32),

`HPC_ARRIVE_TYPE` String,

`HPC_CLOSE_MARK` String,

`HPC_CLOSE_MARK_INTEGER` Nullable(Int32),

`HPC_DIRECTION` String,

`HPC_GROUPSERVICE` String,

`HPC_RETURN_TO_WORK` Int32,

`HPC_SERVICE` String,

`HPC_TIME_ACTUAL_END` Nullable(Date),

`HPC_TIME_ACTUAL_START` Nullable(Date),

`HPC_TIME_REGISTRED` Nullable(Date),

`HPC_TYPE_FEEDBACK` String,

`RESOLUTION_CODE` String,

`DURATION_REGISTRATION` Nullable(Int32),

`DURATION_REACTION` Nullable(Int32),

`DURATION_SOLUTION` Nullable(Int32),

`SLA_DURATION_REACTION` Nullable(Int32),

`SLA_DURATION_REGISTRATION` Nullable(Int32),

`SLA_DURATION_SOLUTION` Nullable(Int32),

`CLOSE_TIME_month` Nullable(UInt8),

`OPEN_TIME_month` Nullable(UInt8),

`HPC_TIME_ACTUAL_END_month` Nullable(UInt8),

`HPC_TIME_ACTUAL_START_month` Nullable(UInt8),

`HPC_TIME_REGISTRED_month` Nullable(UInt8),

`CLOSE_TIME_year` Nullable(UInt16),

`OPEN_TIME_year` Nullable(UInt16),

`HPC_TIME_ACTUAL_END_year` Nullable(UInt16),

`HPC_TIME_ACTUAL_START_year` Nullable(UInt16),

`HPC_TIME_REGISTRED_year` Nullable(UInt16),

`SLA_REGISTRATION_BREACH` UInt8,

`SLA_REACTION_BREACH` UInt8,

`SLA_SOLUTION_BREACH` UInt8

) AS
SELECT
rez.INCIDENT_ID,

rez.AFFECTED_ITEM,

rez.CATEGORY,

toDateOrNull(rez.CLOSE_TIME) AS CLOSE_TIME,

toDateOrNull(rez.OPEN_TIME) AS OPEN_TIME,

toInt32OrNull(rez.PRIORITY_CODE) AS PRIORITY_CODE,

rez.HPC_ARRIVE_TYPE,

rez.HPC_CLOSE_MARK,

toInt32OrNull(rez.HPC_CLOSE_MARK) AS HPC_CLOSE_MARK_INTEGER,

rez.HPC_DIRECTION,

rez.HPC_GROUPSERVICE,

toInt32(rez.HPC_RETURN_TO_WORK) AS HPC_RETURN_TO_WORK,

rez.HPC_SERVICE,

toDateOrNull(rez.HPC_TIME_ACTUAL_END) AS HPC_TIME_ACTUAL_END,

toDateOrNull(rez.HPC_TIME_ACTUAL_START) AS HPC_TIME_ACTUAL_START,

toDateOrNull(rez.HPC_TIME_REGISTRED) AS HPC_TIME_REGISTRED,

rez.HPC_TYPE_FEEDBACK,

rez.RESOLUTION_CODE,

toInt32OrNull(rez.DURATION_REGISTRATION) AS DURATION_REGISTRATION,

toInt32OrNull(rez.DURATION_REACTION) AS DURATION_REACTION,

toInt32OrNull(rez.DURATION_SOLUTION) AS DURATION_SOLUTION,

toInt32OrNull(rez.SLA_DURATION_REACTION) AS SLA_DURATION_REACTION,

toInt32OrNull(rez.SLA_DURATION_REGISTRATION) AS SLA_DURATION_REGISTRATION,

toInt32OrNull(rez.SLA_DURATION_SOLUTION) AS SLA_DURATION_SOLUTION,

toMonth(toDateTimeOrNull(rez.CLOSE_TIME)) AS CLOSE_TIME_month,

toMonth(toDateTimeOrNull(rez.OPEN_TIME)) AS OPEN_TIME_month,

toMonth(toDateTimeOrNull(rez.HPC_TIME_ACTUAL_END)) AS HPC_TIME_ACTUAL_END_month,

toMonth(toDateTimeOrNull(rez.HPC_TIME_ACTUAL_START)) AS HPC_TIME_ACTUAL_START_month,

toMonth(toDateTimeOrNull(rez.HPC_TIME_REGISTRED)) AS HPC_TIME_REGISTRED_month,

toYear(toDateTimeOrNull(rez.CLOSE_TIME)) AS CLOSE_TIME_year,

toYear(toDateTimeOrNull(rez.OPEN_TIME)) AS OPEN_TIME_year,

toYear(toDateTimeOrNull(rez.HPC_TIME_ACTUAL_END)) AS HPC_TIME_ACTUAL_END_year,

toYear(toDateTimeOrNull(rez.HPC_TIME_ACTUAL_START)) AS HPC_TIME_ACTUAL_START_year,

toYear(toDateTimeOrNull(rez.HPC_TIME_REGISTRED)) AS HPC_TIME_REGISTRED_year,

multiIf(DURATION_REGISTRATION > SLA_DURATION_REGISTRATION,

1,
DURATION_REGISTRATION < SLA_DURATION_REGISTRATION,
0,
0) AS SLA_REGISTRATION_BREACH,

multiIf(DURATION_REACTION > SLA_DURATION_REACTION,

1,
DURATION_REACTION < SLA_DURATION_REACTION,
0,
0) AS SLA_REACTION_BREACH,

multiIf(DURATION_SOLUTION > SLA_DURATION_SOLUTION,

1,
DURATION_SOLUTION < SLA_DURATION_SOLUTION,
0,
0) AS SLA_SOLUTION_BREACH
FROM default._Request1 AS rez
WHERE rez.INCIDENT_ID != ‘AM1000’;

==== table ===
– default._Request1 definition

CREATE TABLE default._Request1
(

`INCIDENT_ID` String,

`AFFECTED_ITEM` String,

`CATEGORY` String,

`CLOSE_TIME` String,

`HPC_STATUS` String,

`OPEN_TIME` String,

`PRIORITY_CODE` String,

`RESOLUTION_CODE` String,

`SYSMODTIME` String,

`HPC_ARRIVE_TYPE` String,

`HPC_CLOSE_MARK` String,

`HPC_CONTACT_CENTER` String,

`HPC_DIRECTION` String,

`HPC_GROUPSERVICE` String,

`HPC_KC_FIRST` String,

`HPC_KC_SECOND` String,

`HPC_RETURN_TO_WORK` String,

`HPC_SERVICE` String,

`HPC_TIME_ACTUAL_END` String,

`HPC_TIME_ACTUAL_START` String,

`HPC_TIME_REGISTRED` String,

`HPC_TYPE_FEEDBACK` String,

`HPC_ADDITIONAL_FIELD_1` String,

`HPC_ADDITIONAL_FIELD_2` String,

`HPC_RETURN_TO_WORK_FROM_CONTACT` String,

`DIT_SLA_CONDITION_ID` String,

`DIT_ESCALATION_COUNT` String,

`DURATION_REGISTRATION` String,

`DURATION_REACTION` String,

`DURATION_SOLUTION` String,

`SLA_DURATION_REACTION` String,

`SLA_DURATION_REGISTRATION` String,

`SLA_DURATION_SOLUTION` String

)
ENGINE = MergeTree
PRIMARY KEY INCIDENT_ID
ORDER BY INCIDENT_ID
SETTINGS index_granularity = 8192;

sergey_pismennyy · 19.Январь.2023 15:36:02

Коллеги, добрый вечер!
Мы попытались воссоздать ситуацию на своих серверах, но пока не получили вашу ошибку. Еще разбираемся.

amikhailovsky · 20.Январь.2023 07:18:27

Коллеги, если что - вопрос не срочный.
Здесь думаю, нужно в целом понять причину ошибки, чтобы ее устранить.