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

Aperta
  • Qualcosa non va?
#663 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
76/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
clickhouse, typescript
Ambito
analytics, backend, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
TypeScript
Stelle
7.3k
Fork
539
Merge medio
1g 20h
PR unite (30g)
80

Preparare l'ambiente

  • Include un Dockerfile o un file Docker Compose
  • Nessun modello di pull request
  • Nessuna guida per i contributori

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di Openpanel-dev/openpanel

Tutte le issue di Openpanel-dev/openpanel

Issue simili

Altre issue su TypeScript

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.