The view function with settings does not work as expected after upgrading to version 24.3.3.102. about clickhouse

[den-crane]

I cannot make it work with 23.8.9.54 https://fiddle.clickhouse.com/eb6d35aa-2ac2-4695-a3d0-6c4bdb97be54

try set allow_experimental_analyzer=0; with 24.3.3.102

[den-crane]

Though, it throws exception with settings on the highest level https://fiddle.clickhouse.com/9d07b4d5-e42e-4441-91ce-ab60daeee26f , but it works as expected with the latest version too.

May be it's the same as

• #63078

[YYXXYYZZ]

I cannot make it work with 23.8.9.54 https://fiddle.clickhouse.com/eb6d35aa-2ac2-4695-a3d0-6c4bdb97be54

try set allow_experimental_analyzer=0; with 24.3.3.102

Thank you for your reply. You're right, I have not been able to reproduce the problem in version 23.8. It might be caused by a different scenario leading to the current situation. My production environment has these parameters:

SETTINGS max_memory_usage = 5000000000, max_execution_time = 9, max_bytes_before_external_group_by = 2500000000, max_bytes_before_external_sort = 2500000000, distributed_aggregation_memory_efficient = 1
The memory consumption of routine queries remains at an acceptable level. However, after updating to 24.3, memory usage spiked sharply, almost OOM

Additionally, setting allow_experimental_analyzer=0 seems to have no effect.

[YYXXYYZZ]

Though, it throws exception with settings on the highest level https://fiddle.clickhouse.com/9d07b4d5-e42e-4441-91ce-ab60daeee26f , but it works as expected with the latest version too.

May be it's the same as

• ClickHouse ignores settings from subquery #63078

the highest level SETTINGS can take effect; I am aware of this. My current situation indeed seems to resemble the issue you mentioned. Are there any temporary methods to make the settings for subqueries effective?

[YYXXYYZZ]

I cannot make it work with 23.8.9.54 https://fiddle.clickhouse.com/eb6d35aa-2ac2-4695-a3d0-6c4bdb97be54
try set allow_experimental_analyzer=0; with 24.3.3.102

Thank you for your reply. You're right, I have not been able to reproduce the problem in version 23.8. It might be caused by a different scenario leading to the current situation. My production environment has these parameters:

SETTINGS max_memory_usage = 5000000000, max_execution_time = 9, max_bytes_before_external_group_by = 2500000000, max_bytes_before_external_sort = 2500000000, distributed_aggregation_memory_efficient = 1 The memory consumption of routine queries remains at an acceptable level. However, after updating to 24.3, memory usage spiked sharply, almost OOM

Additionally, setting allow_experimental_analyzer=0 seems to have no effect.

The issue remains unresolved, but in order to make the external sorting for distinct on (a, b) * effective, I added the statement group by a, b,.... Now the memory can be limited within the specified range.