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

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

• has_profile translation: emits unqualified profile_id and device_id references.
• Step filter construction: passes the events qualifier to the filter builder.
• Aggregate alias: adds argMax(profile_id, created_at) AS profile_id for grouping other than profile_id.
• WHERE prefilter: places the step conditions in the same SELECT's WHERE clause.

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.