如何修复GridDB中使用标量子查询时的305009 SQL_PROC_MULTIPLE_TUPLE错误？

错误信息 #

305009 SQL_PROC_MULTIPLE_TUPLE ERROR SQL execution failed. Data operation may be duplicated. Check that multiple rows are not selected in places where only up to a single row is selected, as in a scalar subquery. 

问题复现代码 #

CREATE TABLE Orders (
    order_id INTEGER,
    customer_id INTEGER,
    amount DOUBLE
);

CREATE TABLE Customers (
    customer_id INTEGER,
    customer_name STRING
);

INSERT INTO Orders (order_id, customer_id, amount) VALUES (1, 101, 250.00);
INSERT INTO Orders (order_id, customer_id, amount) VALUES (2, 102, 150.00);
INSERT INTO Orders (order_id, customer_id, amount) VALUES (3, 101, 300.00);

INSERT INTO Customers (customer_id, customer_name) VALUES (101, 'Alice');
INSERT INTO Customers (customer_id, customer_name) VALUES (102, 'Bob');

-- 触发错误的查询
SELECT order_id, amount, 
    (SELECT customer_name FROM Customers WHERE Customers.customer_id = Orders.customer_id) AS customer_name
FROM Orders;

1. 如何修改查询以确保标量子查询仅返回单行？ #

有两种可靠的修改方式：

• 强制限制返回行数：在子查询末尾添加LIMIT 1，即使数据中存在重复的customer_id，也只会返回第一行结果：

  SELECT order_id, amount, 
      (SELECT customer_name FROM Customers WHERE Customers.customer_id = Orders.customer_id LIMIT 1) AS customer_name
  FROM Orders;
  

• 保证数据唯一性：给Customers表的customer_id添加主键或唯一约束，从根本上避免同一个customer_id对应多行数据：

  ALTER TABLE Customers ADD PRIMARY KEY (customer_id);
  

  添加约束后，数据库会阻止重复的customer_id插入，子查询必然只会返回单行。

2. GridDB中使用标量子查询的最佳实践 #

• 绑定唯一键/主键：子查询的过滤条件必须关联到被查询表的主键或唯一约束列，从逻辑上确保只会返回单行。
• 强制限制行数：在所有标量子查询中添加LIMIT 1，或使用MAX()/MIN()等聚合函数，即使数据出现异常（如重复数据），也不会触发错误。
• 优先用JOIN替代：对于关联查询场景，JOIN的性能和稳定性优于标量子查询，GridDB对JOIN的优化更充分。
• 单独验证子查询：嵌入主查询前，单独执行子查询，确认返回结果行数符合预期。
• 建立索引：给关联字段（如Customers.customer_id）建立索引，提升子查询执行效率，同时辅助维护数据唯一性。

3. 替代方法实现相同结果 #

最推荐的替代方案是使用JOIN，这是更高效也更符合SQL规范的写法，完全不会触发该错误：

SELECT o.order_id, o.amount, c.customer_name
FROM Orders o
LEFT JOIN Customers c ON o.customer_id = c.customer_id;

结合之前给Customers表添加的主键约束，JOIN的结果会更稳定。

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