一.场景

在对ClickHouse进行升级过程中,从22版本升级到24版本后, 通过ClickHouse映射,进行跨Mysql库表关联的sql语句查询出现错误: Compound identifier ‘xx.xx‘ cannot be resolved as table expression

二.问题

具体错误如下:

​
Code: 36. DB::Exception: Compound identifier 'account.UserInfo_00' cannot be resolved as table expression. In scope SELECT account.id AS id, account.nick_name AS nick_name, account.mobile_num AS mobile_num, userInfo.gold AS gold, multiIf(toDate(account.created_at) = today(), 1, 0) AS is_new_user, IF(agent.mobile != '', 1, 0) AS is_open_agent FROM account.Account AS account INNER JOIN (SELECT user_id AS id, gold FROM account.UserInfo_00 UNION DISTINCT SELECT user_id AS id, gold FROM account.UserInfo_01) AS userInfo ON account.id = userInfo.id LEFT JOIN (SELECT CONCAT('+62', mobile) AS mobile FROM agent.t_agent WHERE exp = 0) AS agent ON agent.mobile = account.mobile_num WHERE (LENGTH(account.Account.mobile_num) > 0) AND (userInfo.gold <= 2000000) AND (account.id NOT IN (SELECT UserId FROM default.bi_user_mall WHERE (default.bi_user_mall.StrMap['order_status']) = 'success' GROUP BY UserId ORDER BY UserId ASC)) AND (account.id IN (SELECT UserId AS id FROM default.bi_user_login WHERE ((IntMap['login_time']) <= 1746079529) AND ((IntMap['login_time']) >= 1745906729) GROUP BY UserId ORDER BY UserId ASC)) AND (account.id != 101451) AND (account.id != 101111) ORDER BY account.created_at DESC, account.id DESC. (BAD_ARGUMENTS) (version 24.9.3.128 (official build))

sql语句如下:

		select
			account.id id,
			account.nick_name nick_name,
			account.mobile mobile,
			userInfo.gold gold,
			CASE
				WHEN toDate(account.created_at) = today() THEN 1
				ELSE 0
			END as is_new_user,
			IF(agent.mobile <> '', 1, 0) AS is_open_agent
		from
			account.Account account
		INNER JOIN (
			select
				user_id as id,
				gold
			from
				account.UserInfo_00
		UNION DISTINCT
			select
				user_id as id,
				gold
			from
				account.UserInfo_01 ) userInfo ON
			account.id = userInfo.id
		LEFT JOIN (
			select
				CONCAT('+86', mobile) as mobile
			from
				agent.t_agent
			where
				`exp` = 0) agent ON
			agent.mobile = account.mobile
		where
			LENGTH(account.Account.mobile) > 0
			and userInfo.gold <= 10000
			and account.id NOT IN (
			select
				UserId
			from
				user_mall
			where
				user_mall.IntMap['order_status'] = 1
			group by
				UserId
			order by
				UserId)
			and account.id IN (
			select
				UserId as id
			from
				user_login
			where
				IntMap['login_time'] <= 1746079529
				AND IntMap['login_time'] >= 1745906729
			group by
				UserId
			order by
				UserId)
		order by
			account.created_at DESC,
			account.id DESC

三.错误原因

从上面sql语句可以看出: 这里使用了类似account.UserInfo_00这样的表名，这里的account是Mysql数据库名，而UserInfo_00是表名,在ClickHouse中，引用表名的方式需要特别注意，尤其是在涉及到多个数据库或使用联合表（UNION）的时候

接下来，需要检查用户的SQL语句:用户从account.Account表中选择数据，然后内联了两个UserInfo表，分别是UserInfo_00和UserInfo_01，通过UNION DISTINCT合并,这里在子查询中引用了account.UserInfo_00

注意:

        在ClickHouse中，如果表属于不同的数据库，可能需要使用完整的数据库名加表名，比如database.table

这里可以总结出以下原因:

• 表account.UserInfo_00不存在，或者名称拼写错误
• 数据库名或表名的大小写不匹配
• SQL语句中引用表名的方式不正确，需要调整语法
• 权限问题，当前用户没有访问这些表的权限

四.错误分析

通过上述原因排查,然后通过报错信息可得出以下分析结果:

Compound identifier 'account.UserInfo_00' cannot be resolved as table expression 表明 ClickHouse 无法正确解析表名 account.UserInfo_00:这是因为 ​​表名的引用方式不符合 ClickHouse24版本 语法规则​​，尤其在涉及 ​​多数据库​​ 或 ​​分布式表​​ 时需要特别注意

五.解决方案

1.统一表名引用方式​

​​​在 ClickHouse 中，如果表名包含特殊字符（如数字、大小写混合）或属于不同数据库，需要使用反引号（`）包裹表名,以下是修正后的 SQL:

SELECT *
FROM (
    SELECT
        account.id AS id,
        userInfo.gold AS gold,
        CASE
            WHEN toDate(account.created_at) = today() THEN 1
            ELSE 0
        END AS is_new_user,
        IF(agent.mobile <> '', 1, 0) AS is_open_agent
    FROM
        `account`.`Account` AS account  -- 使用反引号包裹数据库和表名
    INNER JOIN (
        SELECT
            user_id AS id,
            gold
        FROM
            `account`.`UserInfo_00`  -- 统一用反引号
        UNION DISTINCT
        SELECT
            user_id AS id,
            gold
        FROM
            `account`.`UserInfo_01`   -- 统一用反引号
    ) AS userInfo ON account.id = userInfo.id
    WHERE
        LENGTH(account.mobile) > 0
        AND userInfo.gold <= 100000
    ORDER BY
        account.created_at DESC,
        account.id DESC
) AS page_sql
LIMIT 10;

2.验证表是否存在​

执行以下查询确认表是否存在

-- 检查表是否存在
SELECT 
    database,
    name AS table_name
FROM system.tables 
WHERE database = 'account' 
  AND name IN ('UserInfo_00', 'UserInfo_01');

-- 预期输出：
/*
database | table_name
account  | UserInfo_00
account  | UserInfo_01
*/

3. 检查数据库上下文​

如果当前数据库不是 account，需显式指定数据库名

-- 明确指定数据库
USE account;

-- 然后执行原始查询（省略数据库名前缀）
SELECT ... FROM Account ... INNER JOIN UserInfo_00 ...

4.关键修改说明

错误写法	修正写法	说明
account.UserInfo_00	`account`.`UserInfo_00`	使用反引号包裹数据库和表名
LENGTH(account.Account.mobile)	LENGTH(account.mobile)	字段引用优化

扩展：分布式表处理​

如果 UserInfo_00 和 UserInfo_01 是分片表，建议使用 Distributed 引擎

EXPLAIN SYNTAX
SELECT ... FROM `account`.`Account`
INNER JOIN (...);