Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

where clause bug with setting prefer_column_name_to_alias = 1 #54954

Closed
HSEhomework opened this issue Sep 24, 2023 · 0 comments · Fixed by #62457
Closed

where clause bug with setting prefer_column_name_to_alias = 1 #54954

HSEhomework opened this issue Sep 24, 2023 · 0 comments · Fixed by #62457
Labels
bug Confirmed user-visible misbehaviour in official release comp-alias-columns st-fixed

Comments

@HSEhomework
Copy link

HSEhomework commented Sep 24, 2023

You have to provide the following information whenever possible.

Wrong behavior for the filter using prefer_column_name_to_alias = 1

CREATE TABLE loans (loan_number int, security_id text) ENGINE=Memory;

INSERT INTO loans VALUES (1, 'AAA');
INSERT INTO loans VALUES (1, 'AAA');
INSERT INTO loans VALUES (1, 'AAA');
INSERT INTO loans VALUES (1, 'AAA');
INSERT INTO loans VALUES (1, 'AAA');
INSERT INTO loans VALUES (1, 'BBB');
INSERT INTO loans VALUES (1, 'BBB');
INSERT INTO loans VALUES (1, 'BBB');
INSERT INTO loans VALUES (1, 'BBB');
INSERT INTO loans VALUES (1, 'BBB');
INSERT INTO loans VALUES (1, 'BBB');


with block_0 as (
  select * from loans
),
block_1 as (
  select sum(loan_number) as loan_number from block_0 group by security_id
)
select loan_number from block_1 where loan_number > 3 settings prefer_column_name_to_alias = 1 

In this query filter loan_number > 3 for some reason will not operate on the aggregation.
By the logic original column name here should not be available, as the context after aggregation should be different and alias should be used.

https://fiddle.clickhouse.com/aa990c59-25a1-4562-9bea-17f43bd9f78f

A link to reproducer in https://fiddle.clickhouse.com/.

Does it reproduce on recent release?

yes

Expected behavior

A clear and concise description of what you expected to happen.

Expected output for the query above is
5
6

Please help with this bug.

@HSEhomework HSEhomework added the potential bug To be reviewed by developers and confirmed/rejected. label Sep 24, 2023
@tavplubix tavplubix added bug Confirmed user-visible misbehaviour in official release comp-alias-columns and removed potential bug To be reviewed by developers and confirmed/rejected. labels Sep 25, 2023
nikitamikhaylov added a commit that referenced this issue Apr 10, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Confirmed user-visible misbehaviour in official release comp-alias-columns st-fixed
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants