apache/shardingsphere · error · UnsupportedEncryptSQLException

The SQL clause '%s=VALUES(%s)' is unsupported in encrypt fea

Error message

The SQL clause '%s=VALUES(%s)' is unsupported in encrypt feature.

What it means

EncryptInsertOnUpdateTokenGenerator throws UnsupportedEncryptSQLException('%s=VALUES(%s)') at the first mismatch site: when the INSERT ... ON DUPLICATE KEY UPDATE assignment pairs an encrypted column with a VALUES() reference whose assisted-query presence differs (one has an assisted query column configured, the other does not), so the rewrite cannot generate matching assignments.

Source

Thrown at features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/insert/EncryptInsertOnUpdateTokenGenerator.java:165

        ExpressionSegment valueColumnSegment = functionSegment.getParameters().isEmpty() ? null : functionSegment.getParameters().iterator().next();
        ShardingSpherePreconditions.checkNotNull(valueColumnSegment, () -> new IllegalArgumentException("value column segment can not be null"));
        String valueColumn = ((ColumnSegment) valueColumnSegment).getIdentifier().getValue();
        EncryptFunctionAssignmentToken result =
                new EncryptFunctionAssignmentToken(columnSegment.getStartIndex(), assignmentSegment.getStopIndex(), quoteCharacter);
        boolean isEncryptColumn = encryptTable.isEncryptColumn(column);
        boolean isEncryptValueColumn = encryptTable.isEncryptColumn(valueColumn);
        if (isEncryptColumn && isEncryptValueColumn) {
            EncryptColumn encryptColumn = encryptTable.getEncryptColumn(column);
            EncryptColumn encryptValueColumn = encryptTable.getEncryptColumn(valueColumn);
            String cipherColumn = encryptColumn.getCipher().getName();
            String cipherValueColumn = encryptValueColumn.getCipher().getName();
            result.addAssignment(cipherColumn, "VALUES(" + cipherValueColumn + ")");
            Optional<AssistedQueryColumnItem> assistedQueryColumn = encryptColumn.getAssistedQuery();
            Optional<AssistedQueryColumnItem> valueAssistedQueryColumn = encryptValueColumn.getAssistedQuery();
            if (assistedQueryColumn.isPresent() && valueAssistedQueryColumn.isPresent()) {
                result.addAssignment(assistedQueryColumn.get().getName(), "VALUES(" + valueAssistedQueryColumn.get().getName() + ")");
            } else if (assistedQueryColumn.isPresent() != valueAssistedQueryColumn.isPresent()) {
                throw new UnsupportedEncryptSQLException(String.format("%s=VALUES(%s)", column, valueColumn));
            }
            Optional<LikeQueryColumnItem> likeQueryColumn = encryptColumn.getLikeQuery();
            Optional<LikeQueryColumnItem> valueLikeQueryColumn = encryptValueColumn.getLikeQuery();
            if (likeQueryColumn.isPresent() && valueLikeQueryColumn.isPresent()) {
                result.addAssignment(likeQueryColumn.get().getName(), "VALUES(" + valueLikeQueryColumn.get().getName() + ")");
            } else if (likeQueryColumn.isPresent() != valueLikeQueryColumn.isPresent()) {
                throw new UnsupportedEncryptSQLException(String.format("%s=VALUES(%s)", column, valueColumn));
            }
        } else {
            throw new UnsupportedEncryptSQLException(String.format("%s=VALUES(%s)", column, valueColumn));
        }
        ShardingSpherePreconditions.checkState(!result.isAssignmentsEmpty(), () -> new UnsupportedEncryptSQLException(String.format("%s=VALUES(%s)", column, valueColumn)));
        return result;
    }
    
    private void addCipherAssignment(final String schemaName, final String tableName, final EncryptColumn encryptColumn,
                                     final ColumnAssignmentSegment assignmentSegment, final EncryptLiteralAssignmentToken token,
                                     final Object literalValue) {

View on GitHub (pinned to e952770a21)

Solutions

1. Make assisted-query configuration symmetric: either both the target column and the VALUES() source column have assisted query columns, or neither does
2. If only one column needs assisted query, rewrite the SQL to avoid pairing them in ON DUPLICATE KEY UPDATE
3. Review the encrypt rule YAML for the named columns after the fix and re-run the insert

Example fix

# before
columns:
  col_a: {..., assistedQueryColumn: {name: assisted_a, encryptor: ...}}
  col_b: {...}  # no assisted query
# after (align both or neither)
  col_b: {..., assistedQueryColumn: {name: assisted_b, encryptor: ...}}

void checkValuesPair(EncryptColumn target, EncryptColumn source) { if (target.getAssistedQuery().isPresent() != source.getAssistedQuery().isPresent()) throw new ConfigException("assisted-query asymmetry in col=VALUES(col)"); }

catch (UnsupportedEncryptSQLException e) { if (e.getMessage().contains("=VALUES(")) { /* align rule config; retry after reload */ } }

When it happens

Trigger: An INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(other_col) where both columns are encrypted but only one of them has queryWithAssistedColumn/assisted-query configured in the encrypt rule.

Common situations: Adding assisted-query config to one column but not its VALUES() partner; partial rule edits during key migration; column pairs that were symmetric before an assisted column was introduced.

Related errors

• Algorithm `%s` is unsupported to decrypt
• Row expression can only compare with subquery
• Can not use different encryptor for %s and %s in %s
• Failed to decrypt the ciphertext '%s' in '%s'.
• The SQL clause 'BETWEEN...AND...' is unsupported in encrypt

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/51e48027d72757ce. Report an issue: GitHub.