创建含CASE的持久化计算列时出现语法错误的技术咨询

我需要创建一个持久化计算列，返回BIGINT类型的结果（值为1或2），执行以下SQL语句时出现语法错误：

DROP TABLE measurements;

CREATE TABLE measurements (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    status AS (
        CASE id
        WHEN (
            SELECT
                COUNT(*)
            FROM
                measurement_points
            WHERE
                measurement_points.measurement_id = measurements.id
                AND measurement_points.status IN('OK', 'ERROR')) = 3 THEN
            1
        ELSE
            2
        END) PERSISTED BIGINT 
  );

错误信息：

Query 2 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
            SELECT
                COUNT(*)
            FROM
                measurement_points
            WHERE
                measurement' at line 1

使用的是SingleStore DB 8.1.1版本，单独提取查询语句执行时可正常运行：

SELECT
    id,
    (
        CASE id
        WHEN (
            SELECT
                COUNT(*)
            FROM
                measurement_points
            WHERE
                measurement_points.measurement_id = measurements.id
                AND measurement_points.status IN('OK', 'ERROR')) = 3 THEN
            1
        ELSE
            2
        END) AS status
FROM
    measurements;

执行结果：Query 1 OK

我的疑问：

1. SingleStore是否支持我想要实现的功能？
2. 导致语法错误的具体原因是什么？

1. SingleStore是否支持该功能？ #

SingleStore支持持久化计算列，但不允许计算列定义中包含关联其他表的子查询。你想要的“基于关联表计数结果返回1或2”的逻辑，无法直接通过持久化计算列实现，因为计算列只能依赖当前表的字段，不能引用其他表的数据。

2. 语法错误的具体原因 #

你在status计算列的CASE表达式中，使用了关联measurement_points表的子查询作为判断条件，这违反了SingleStore持久化计算列的语法规则。虽然这个子查询单独执行或在SELECT语句中使用合法，但计算列的定义不支持跨表关联查询，因此触发语法错误。

替代实现方案 #

如果需要实现类似逻辑，可考虑以下两种方式：

• 触发器：在measurement_points或measurements表上创建触发器，当数据变更时手动计算并更新measurements表的status字段（此时status为普通列，非计算列）。
• 物化视图：创建包含status计算逻辑的物化视图，定期刷新获取最新结果，适合实时性要求不高的场景。

内容的提问来源于stack exchange，提问作者SPQRInc