json_contains语法异常问题

Viewed 26

版本:2.0.5
场景:本来是打算通过跨 mysql catalog 关联查询数据,但是发现 mysql 的 catalog 建了以后,过几天就会出现查询异常,表现就是一直查看不出来一直卡着,最后客户端超时断开链接,详见:查询Mysql Catalog异常

然后把我 mysql 的表同步到 doris,发现更奇怪的事情,一样的语句在跨 catalog 的时候可以正常,表迁过来就不行了

建表语句

CREATE TABLE `xxxx` (
  `id` int(11) NULL,
  `config` json NULL COMMENT '配置json'
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

查询语句

select config
from xxx
where is_deleted = 0
  and json_contains(config, 'TC', '$.s1')
  and json_contains(config, 1, '$.c1')
  and json_contains(config, 56104, '$.c2')
limit 1;

demo数据

{
	"tips": "<p>测试</p><p><strong style=\"color: rgb(255, 0, 0);\">xxx:</strong></p><p>yyyy<strong style=\"color: rgb(255, 0, 0);\">zzzz。</p>",
	"c1": [
		1,
		2
	],
	"c2": [
		64398
	],
	"s1": [
		"GSTC",
		"QSCFW"
	]
}

补充:mysql 的类型也是 json

3 Answers

用户已经用其他的函数绕过了,问题先close了,后面有问题可以加微信Faith_xzc

报错内容:

errCode = 2, detailMessage = ()[CANCELLED][INTERNAL_ERROR]failed to initialize storage reader. tablet=15574604.439917221.3348a16c27a8d3a4-66e58982970d76ab, res=[RUNTIME_ERROR]Illegal column should be ColumnString 0# doris::vectorized::FunctionJsonContains::execute_impl(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long) at /home/zcp/repo_center/doris_release/doris/be/src/vec/common/cow.h:132 1# doris::vectorized::DefaultExecutable::execute_impl(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function.h:521 2# doris::vectorized::PreparedFunctionImpl::execute_without_low_cardinality_columns(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsign ...

方便的话,可以分别在两个sql(通过catalog查和把数据导入到本地查)前面加一下explain然后执行贴一下结果