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.

Sign up for GitHub

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

Jump to bottom

Query with array join and toYYYYMM in predicate doesn't work as expected #53152

Closed
canhld94 opened this issue Aug 8, 2023 · 2 comments · Fixed by #53440
Closed

Query with array join and toYYYYMM in predicate doesn't work as expected #53152

canhld94 opened this issue Aug 8, 2023 · 2 comments · Fixed by #53440
Labels
unexpected behaviour

Comments

@canhld94
Copy link
Contributor

canhld94 commented Aug 8, 2023

Reproduce:

select
  toYYYYMM(date) as date_,
  n
from (select 
  [toDate(now()), toDate(now())] as date,
  [1, 2] as n
) as data
array join date, n
where date_ >= 202303;

Work in 23.6: https://fiddle.clickhouse.com/cabc075a-ac8a-4a15-bd46-2bc2b1de09a6
Not work in 23.7: https://fiddle.clickhouse.com/74c0b0b6-72f2-4fb7-8a0b-bc098307da70

Suspicious PR: #52091
@canhld94 canhld94 changed the title Query with array join toYYYYMM in predicate doesn't work as expected Query with array join and toYYYYMM in predicate doesn't work as expected Aug 9, 2023
@ZhiguoZh
Copy link
Contributor

Hi @canhld94, thanks for reporting this issue! I'll start working on its fix right away!

@ZhiguoZh ZhiguoZh mentioned this issue Aug 15, 2023
Merged
ZhiguoZh added a commit to ZhiguoZh/ClickHouse that referenced this issue Aug 15, 2023
@ZhiguoZh
Fix an unexpected behavior in ClickHouse#53152
3579039 
The problematic case happens when an array column of date/datetime
is simultaneously specified in an Array Join clause without aliases
and in a time converter, such as toYYYYMM.

After applying Array Join without aliases, the column's name refers
to the flattened array items, however, its data type is recognized
as a ColumnArray still, which leads to the unexpected exception
throws when building the preimage for the time converters.

As a quick fix, we more strictly check the data types of the time
converters and quit the preimage optimization pass early.
robot-clickhouse added a commit that referenced this issue Aug 17, 2023
@robot-clickhouse
Merge pull request #53440 from ZhiguoZh/20230815-date-arrayjoin-fix
81af60e 
Fix an unexpected behavior in #53152
@ZhiguoZh
Copy link
Contributor

Hi @canhld94, the reported issue has been fixed in #53440, and the problematic query has been added in the CI test. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
unexpected behaviour
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix an unexpected behavior in #53152 ZhiguoZh/ClickHouse
2 participants
@canhld94 @ZhiguoZh