sql - splitByChar 可空

Question

我有下一个查询：

SELECT DISTINCT col_name, toTypeName(col_name)
  FROM remote('host_name', 'db.table', 'user', 'password')

结果是 6 条记录 ( WITHOUT NULL)。例子：

some_prefix-1, Nullable(String)
...
some_prefix-6, Nullable(String)

现在我尝试splitByChar，但我得到：

代码：43，e.displayText() = DB::Exception: 嵌套类型Array(String) 不能在Nullable 类型内（版本20.1.2.4（官方构建））

我尝试使用not null condition和转换类型，但问题仍然存在。像那样：

SELECT DISTINCT toString(col_name) AS col_name_str,
                splitByChar('-', col_name_str)
  FROM remote('host_name', 'db.table', 'user', 'password')
 WHERE col_name IS NOT NULL

这是预期的行为吗？如何解决这个问题？

标签： sqlclickhouse

Answer 1

splitByChar 缺乏 Nullable 支持（https://github.com/ClickHouse/ClickHouse/issues/6517）

你使用了错误的演员表toString

SELECT DISTINCT
    cast(col_name, 'String') AS col_name_str,
    splitByChar('-', col_name_str)
FROM
(
    SELECT cast('aaaaa-vvvv', 'Nullable(String)') AS col_name
)
WHERE isNotNull(col_name)

┌─col_name_str─┬─splitByChar('-', cast(col_name, 'String'))─┐
│ aaaaa-vvvv   │ ['aaaaa','vvvv']                           │
└──────────────┴────────────────────────────────────────────┘

或者assumeNotNull

SELECT DISTINCT
    assumeNotNull(col_name) AS col_name_str,
    splitByChar('-', col_name_str)
FROM
(
    SELECT cast('aaaaa-vvvv', 'Nullable(String)') AS col_name
)
WHERE isNotNull(col_name)

┌─col_name_str─┬─splitByChar('-', assumeNotNull(col_name))─┐
│ aaaaa-vvvv   │ ['aaaaa','vvvv']                          │
└──────────────┴───────────────────────────────────────────┘