If statement not working correctly with decrypt function

[jaumecastell]

Hi, I'm trying to filter by a encrypted column depending on a condition, but if statement is not working correctly and seems that the column is decrypted with both algorithms before checking the if statement. The table is partitioned by datetime, and it's only failing when datetime filters are also applied to the query.

Here's an example:

SELECT count() FROM data_table WHERE (if(has(array, 'a'), decrypt('aes-256-gcm', encrypted_column, 'key', 'iv'), decrypt('aes-256-cfb128', encrypted_column, 'key')) = 'value1') AND (datetime >= toDateTime('2022-06-14 05:35:36')) AND (datetime < toDateTime('2022-06-14 05:45:36'))

The error is the following:

DB::Exception: Encrypted data is smaller than the size of additional data for AEAD mode, cannot decrypt.

Which ClickHouse server version to use
21.12.3.32

[jaumecastell]

Hi, just a quick update. Reviewing the documentation, I've seen that this config short_circuit_function_evaluation can be enabled to only evaluate then/else if condition is met or not. I have this setting to enable and this is working for ifs in SELECT statements, but when if conditional is on WHERE clausules, this setting has no effect.

[jaumecastell]

Hi @alexey-milovidov @Avogar , any thoughts on this issue?

[Avogar]

Seems like a bug in short circuit function evaluation. Simple case that doesn't work:

:) select number >= 0 and if(number != 0, intDiv(1, number), 1) from numbers(5)

SELECT (number >= 0) AND if(number != 0, intDiv(1, number), 1)
FROM numbers(5)

Query id: 04e9df11-8387-4b87-b100-13b27a1387b2


0 rows in set. Elapsed: 0.027 sec.

Received exception from server (version 22.6.1):
Code: 153. DB::Exception: Received from localhost:9000. DB::Exception: Division by zero: while executing 'FUNCTION and(greaterOrEquals(number, 0) :: 3, if(notEquals(number, 0), intDiv(1, number), 1) :: 0) -> and(greaterOrEquals(number, 0), if(notEquals(number, 0), intDiv(1, number), 1)) UInt8 : 2'. (ILLEGAL_DIVISION)

Although:

select if(number != 0, intDiv(1, number), 1) from numbers(5)

SELECT if(number != 0, intDiv(1, number), 1)
FROM numbers(5)

Query id: 2bc813e0-17c7-4fb3-967c-ea7f19764430

┌─if(notEquals(number, 0), intDiv(1, number), 1)─┐
│                                              1 │
│                                              1 │
│                                              0 │
│                                              0 │
│                                              0 │
└────────────────────────────────────────────────┘

Investigating.

UPD: found a bug, will fix it.

[jaumecastell]

Hi @Avogar , thanks!