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

[YYXXYYZZ]

Suppose I execute the following SQL query:

SELECT number, COUNT(*) FROM numbers(100000) GROUP BY number LIMIT 10 SETTINGS max_memory_usage = 5
I receive the following error:

Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 5.50 MiB (attempt to allocate chunk of 5763328 bytes), maximum: 5.00 B.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)
However, when I use the view function like this:

SELECT * FROM cluster(xxx, view(SELECT number, COUNT(*) FROM numbers(100000) GROUP BY number LIMIT 10 SETTINGS max_memory_usage = 5))
everything works fine. But, that's not what I want. I need the view subquery to limit the query's memory usage. When I used version 23.8.9.54, it worked perfectly. After upgrading, these settings seem to be ignored by the server.

The current situation is that I have to use the method of querying like cluster(xxx, view(xxx)). How can I achieve the same effect as before the upgrade?

[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

• ClickHouse ignores settings from subquery #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.