Union ALL + WITH: Syntax error (1.9.5 -> 2.2.0)

[ADovgalyuk]

Hi Team,
I was trying to migrate from 1.9.5 plugin version to fresh 2.2.0 and faced the issue with the final request syntax.
Original user request from grafana (last version 7.3.6):

$rateColumns(
multiIf(
  errorType=0, 'ok',
'Unknown'
) as key,
sum(count) as value
)
FROM (
  select date_time, errorType, errorState, errorCode as error, count from $table
  WHERE $timeFilter

  UNION ALL
  with (select groupArray(toInt64(Status)) from dictionary.scl_mt_cdr_error_state) as errorStateArr
  select
  (
  arrayJoin(
    arrayMap(x -> toStartOfMinute(
      toDateTime(
        ((minus($from, $to) / 100) * x)+$to
      )
    ), range(100))
  )) AS date_time,
    3 as errorType,
    arrayJoin(errorStateArr) as  errorState,
    toInt64(ErrorCode) as error,
  0 as count
  from dictionary.MAP_ErrorCodes
)

On version 1.9.5 final request to CH works without any errors:
"api/datasources/proxy/2/?query=SELECT t, arrayMap(a -> (a.1, a.2/runningDifference( t/1000 )), groupArr) FROM (SELECT t, groupArray((key, value)) AS groupArr FROM ( SELECT (intDiv(toUInt32(date_time), 3) * 3) * 1000 AS t, multiIf(errorType = 0, 'ok', 'Unknown') as key, sum(count) as value FROM ( select date_time, errorType, errorState, errorCode as error, count from statistics.SCL_send_mt_cdr_stat WHERE date_time BETWEEN toDateTime(1610723744) AND toDateTime(1610724586) AND date_time BETWEEN toDateTime(1610723744) AND toDateTime(1610724586) UNION ALL with (select groupArray(toInt64(Status)) from dictionary.scl_mt_cdr_error_state) as errorStateArr select ( arrayJoin( arrayMap(x -> toStartOfMinute( toDateTime( ((minus(1610723744, 1610724586) / 100) * x)+1610724586 ) ), range(100)) )) AS date_time, 3 as errorType, arrayJoin(errorStateArr) as errorState, toInt64(ErrorCode) as error, 0 as count from dictionary.MAP_ErrorCodes ) GROUP BY t, key ORDER BY t, key) GROUP BY t ORDER BY t) FORMAT JSON&database=statistics"

But on new 2.2.0 it's different, i can't even see "Union ALL" part:

"api/datasources/proxy/2/?query=SELECT t, arrayMap(a -> (a.1, a.2/runningDifference( t/1000 )), groupArr) FROM (SELECT t, groupArray((key, value)) AS groupArr FROM ( SELECT (intDiv(toUInt32(date_time), 180) * 180) * 1000 AS t, multiIf(errorType = 0, 'ok', 'Unknown') as key, sum(count) as value from dictionary.scl_mt_cdr_error_state) as errorStateArr select ( arrayJoin( arrayMap(x -> toStartOfMinute( toDateTime( ((minus(1610638311, 1610724651) / 100) * x)+1610724651 ) ), range(100)) )) AS date_time, 3 as errorType, arrayJoin(errorStateArr) as errorState, toInt64(ErrorCode) as error, 0 as count from dictionary.MAP_ErrorCodes ) WHERE date_time >= toDateTime(1610638311) AND date_time <= toDateTime(1610724651) GROUP BY t, key ORDER BY t, key) GROUP BY t ORDER BY t) FORMAT JSON&database=statistics"

This new syntax leads to the following error:

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 323 ('select') (line 2, col 3): select ( arrayJoin( arrayMap(x -> toStartOfMinute( toDateTime( ((minus(1610638311, 1610724651) / 100) * x)+1610724651 ) ), range. Expected one of: SAMPLE, LIMIT, WHERE, INNER, HAVING, GROUP BY, OFFSET, LEFT ARRAY JOIN, PREWHERE, JOIN, ORDER BY, array join, SETTINGS, UNION ALL, ARRAY JOIN, WITH, table, table function, subquery or list of joined tables, FINAL (version 20.12.5.14 (official build))

Is it a bug (looks like that at least)? Or should I change my request to fit new plugin version?

[Slach]

yep look like it's a bug
thanks a lot for reporting,
could you share SHOW CREATE TABLE for dictionary.MAP_ErrorCodes and $table?

[ADovgalyuk]

CREATE TABLE dictionary.scl_mt_cdr_error_state
(
    `Status` UInt64,
    `Description` String
)
ENGINE = Dictionary('scl_mt_cdr_error_state')

CREATE TABLE dictionary.MAP_ErrorCodes
(
    `ErrorCode` UInt64,
    `Description` String
)
ENGINE = Dictionary('MAP_ErrorCodes')

And $table is the following:

CREATE TABLE statistics.SCL_send_mt_cdr_stat
(
    `date_time` DateTime,
    `host` LowCardinality(String),
    `eventType` Enum8('Diameter-SC' = 1, 'Sc' = 2, 'Sc_MT' = 3, 'BRT' = 4, 'BRT-SC' = 5, 'Diameter' = 6, 'Diameter_MT' = 7, 'Diameter-SC_MT' = 8, 'SendMT' = 9, 'Unknown' = 10),
    `smppDirectionId` Int16 DEFAULT toInt16(-1),
    `status` Int8 DEFAULT toInt8(-1),
    `msc` String,
    `errorState` Int8 DEFAULT toInt8(-1),
    `errorType` Int8 DEFAULT toInt8(-1),
    `errorCode` Int8 DEFAULT toInt8(-1),
    `isCharge` Int8 DEFAULT toInt8(-1),
    `messageType` Int8 DEFAULT toInt8(-1),
    `brtConnectionId` String,
    `brtCause` String,
    `httpStatus` Int8 DEFAULT toInt8(-1),
    `diamResultCode` Int16 DEFAULT toInt16(0),
    `httpErrorCode` String,
    `count` Int32
)
ENGINE = SummingMergeTree
PARTITION BY toDate(date_time)
PRIMARY KEY (date_time, host)
ORDER BY (date_time, host, eventType, smppDirectionId, status, msc, errorState, errorType, errorCode, isCharge, messageType, brtConnectionId, brtCause, diamResultCode, httpStatus, httpErrorCode)
TTL date_time + toIntervalDay(365)
SETTINGS index_granularity = 8192

[ADovgalyuk]

Hi Guys,
Kindly reminder, any news?

[Slach]

sorry, no news right now, i will try fix it this week

[Slach]

@ADovgalyuk I found a root reason and it will fix in the next release
for a quick workaround for your query

just replace
with (select groupArray(toInt64(Status)) from dictionary.scl_mt_cdr_error_state) as errorStateArr
to
with (select groupArray(toInt64(Status)) AS g_array from dictionary.scl_mt_cdr_error_state) as errorStateArr
to avoid skip query UNION ALL

[Slach]

@ADovgalyuk could you check latest master version of plugin?