Bug: Funnel has_profile filter conflicts with the profile_id aggregate alias (ClickHouse Code 184)

Đang mở
  • Có gì không ổn?
#663 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
3/5
Thời gian dự kiến
1-2 ngày
Mức phù hợp với người mới
76/100
Loại issue
Lỗi
Độ rõ ràng
Đặc tả rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
clickhouse, typescript
Lĩnh vực
analytics, backend, databases

Hướng nghiên cứu

Read packages/core/src/modules/chart/src/filter-where.ts around the has_profile translation, then trace how funnel.sql.ts passes the events qualifier and builds the session-grouped aggregate and WHERE prefilter. Start by checking existing funnel tests for both has_profile values and profile-grouped behavior. Done means the session-grouped filters execute successfully and return the expected identified and anonymous sessions; add database-backed coverage with prefer_column_name_to_alias = 0.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

Summary

Session-grouped funnel queries use argMax(profile_id, created_at) AS profile_id while the built-in has_profile filter generates an unqualified profile_id != device_id or profile_id = device_id predicate. The funnel builder places these predicates in the same SELECT's WHERE clause.

ClickHouse can resolve profile_id in the predicate to the aggregate alias instead of the input column, causing Code 184 (ILLEGAL_AGGREGATION). This alias-resolution failure was reproduced locally with the reduced SQL below.

All dates and identifiers below are synthetic. No application data is required for the SQL example.

Version checked

  • Repository: Openpanel-dev/openpanel
  • Branch: main
  • Commit: a5a6eb7d62c9579620ec5e3fbfd4ce760f3d8073
  • ClickHouse local: 26.2.4.23, with prefer_column_name_to_alias returning false and no override supplied.
  • Verification: source inspection and execution of reduced SQL against ClickHouse local. A full Dashboard/API/database test was not run for this commit.

Steps to reproduce

The application steps below follow the inspected code path; they have not been executed end to end for this report.

  1. Use a valid funnel request in a local development environment and set funnelGroup to session_id.
  2. Add a step filter with name: "has_profile" and value: ["true"], keeping the remaining request fields valid.
  3. Inspect the generated SQL and execute the query against ClickHouse.
  4. Repeat with value: ["false"].

The relevant SQL fragments are equivalent to:

-- SELECT: aggregate output reuses the input column name
argMax(profile_id, created_at) AS profile_id

-- WHERE: has_profile predicate uses the unqualified name
profile_id != device_id -- true
profile_id = device_id  -- false

The filter builder receives the events qualifier, but the has_profile branch does not apply it. Step conditions are used both in windowFunnel and in the WHERE prefilter.

Minimal SQL example

This isolates the SELECT/WHERE alias conflict without accessing application tables. It omits windowFunnel, joins, and other request conditions.

Run with clickhouse-local --query "<SQL below>":

SELECT
    session_id,
    argMax(profile_id, created_at) AS profile_id
FROM VALUES(
    'session_id String, profile_id String, device_id String, created_at DateTime',
    ('s1', 'p1', 'd1', '2026-01-01 00:00:00'),
    ('s2', 'd2', 'd2', '2026-01-01 00:00:00')
) AS events
WHERE profile_id != device_id
GROUP BY session_id
FORMAT JSONEachRow

Expected behavior

The filter should compare input event columns before aggregation, and a supported has_profile filter should not introduce an aggregate into WHERE through alias resolution.

For the synthetic fixture, identified filtering should return session s1; anonymous filtering should return session s2.

Actual behavior

The minimal SQL example exited with code 184:

Code: 184. DB::Exception: Aggregate function argMax(profile_id, created_at) AS profile_id is found in WHERE in query. (ILLEGAL_AGGREGATION)

Replacing != with = produced the same error. Both predicates generated by has_profile therefore fail in this reduced SQL example.

Two independent changes to the identified-filter example were also executed locally:

  • Qualify the predicate as events.profile_id != events.device_id: exit code 0, output {"session_id":"s1","profile_id":"p1"}.
  • Keep the original predicate but rename the aggregate output to actor_profile_id: exit code 0, output {"session_id":"s1","actor_profile_id":"p1"}.

These results confirm the alias collision in the reduced SQL. Source inspection confirms the conflicting fragments in the funnel builder. They do not establish the exact error location or HTTP response for a complete application query, or validate a complete application fix.

Relevant source

Suggested fix and regression coverage

Apply the provided events qualifier to the input columns in the has_profile branch, or use a distinct aggregate output alias and update its references consistently.

Add regression coverage for session-grouped funnels with both has_profile values, including a database-backed execution test with prefer_column_name_to_alias = 0. Verify the funnel results as well as successful SQL execution, and preserve existing profile-grouped funnel behavior.

Ngôn ngữ chính
TypeScript
Star
7.3k
Fork
539
Merge trung bình
1 ngày 20 giờ
Pull request đã merge (30 ngày)
80

Chuẩn bị môi trường

  • Có Dockerfile hoặc tệp Docker Compose
  • Không có mẫu pull request
  • Không có hướng dẫn đóng góp

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của Openpanel-dev/openpanel

Tất cả issue của Openpanel-dev/openpanel

Issue tương tự

Thêm issue về TypeScript

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.