BigQuery使用AEAD函数加解密列时DECRYPT_BYTES解密失败排查

你提供的SQL代码报错的核心原因是非确定性函数重复执行导致加密、解密使用的密钥集不匹配，具体拆解如下：

• KEYS.NEW_KEYSET 是BigQuery的非确定性函数，每次调用都会生成全新的、独立的AEAD密钥集，无法通过相同入参复现之前生成的结果。
• 你的SQL中两次引用了CustomerData这个CTE：第一次是生成EncryptedCustomerData时做加密，此时调用KEYS.NEW_KEYSET生成了第一组密钥集；第二次是解密时的子查询再次查询CustomerData，此时会重新执行CustomerData的逻辑，KEYS.NEW_KEYSET会生成第二组完全不同的密钥集，用和加密时不匹配的密钥解密自然会触发报错。

如果你是按照你描述的步骤提前创建了持久化的CustomerData表存储生成好的keyset，而不是用CTE临时生成的话，不会出现这个问题，只有在你给出的示例SQL中用CTE动态生成keyset才会触发该错误。

你可以参考以下修改后的示例代码，避免重复调用生成密钥的逻辑即可正常解密：

WITH CustomerData AS (
SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset, b'jaguar' AS favorite_animal UNION ALL
SELECT 2 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset, b'zebra' AS favorite_animal UNION ALL 
SELECT 3 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset, b'nautilus' AS favorite_animal
),
EncryptedWithKey AS (
SELECT 
  customer_id, 
  keyset,
  AEAD.ENCRYPT(keyset, favorite_animal, CAST(CAST(customer_id AS STRING) AS BYTES)) AS encrypted_animal
FROM CustomerData
)
SELECT
  customer_id,
  AEAD.DECRYPT_BYTES(
    keyset,
    encrypted_animal,
    CAST(CAST(customer_id AS STRING) AS BYTES)
  ) AS favorite_animal
FROM EncryptedWithKey;

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