Funnel "View Users" always returns "No users found" when the funnel has a profile-property breakdown (UNKNOWN_IDENTIFIER: profile join never added)

[niajkitir]

Summary

For any funnel report broken down by a profile property (e.g. profile.properties.plan), the funnel chart renders correct step counts, but clicking View Users on any step — Completed or Dropped Off — shows "No users found". The backend call actually fails; the UI just renders the failure as an empty list.

Reproduction

1. Create a funnel report with any two events.
2. Add a breakdown on a profile property (e.g. profile.properties.plan).
3. The chart renders fine with per-value rows and counts.
4. Click the "view users" icon on any step → modal says "No users found" even when the step shows hundreds of users.

What actually happens

chart.getFunnelProfiles fails on every call with ClickHouse error 47:

Unknown expression or function identifier `profile.properties` in scope session_funnel

Generated SQL (abbreviated):

WITH session_funnel AS (
  SELECT session_id,
         windowFunnel(...)(...) AS level,
         argMax(profile_id, created_at) AS profile_id,
         profile.properties['plan'] as b_0        -- references `profile`...
  FROM events AS events                            -- ...but profiles is never joined
  WHERE project_id = ...
  GROUP BY session_id, b_0
), ...

The tRPC procedure returns 500, and apps/start/src/modals/view-chart-users.tsx does profilesQuery.data ?? [] with no error state, so the user sees "No users found" instead of an error.

Root cause

getFunnelProfiles in packages/trpc/src/routers/chart.ts duplicates the funnel SQL instead of reusing FunnelService.getFunnel, and the two copies diverge:

• The breakdown select renders getSelectPropertyKey(b.name, projectId) as b_0, which for profile.* breakdowns produces profile.properties['…'] — an expression that needs the profiles join (chart.ts:934 at b467a6ce).
• But the profiles join is only added when the event series contain profile.* filters (if (profileFilters.length > 0), chart.ts:954) — breakdowns are never checked.

Compare with FunnelService.getFunnel (packages/db/src/services/funnel.service.ts), which gates the join on anyFilterOnProfile || anyBreakdownOnProfile — that's why the chart works while the modal errors.

Cohort breakdowns hit the same class of failure in getFunnelProfiles (the breakdown expression references a cohort join alias that is never added there).

Suggested fix

Minimal: replicate the anyBreakdownOnProfile join logic from getFunnel inside getFunnelProfiles (including the properties column when the breakdown is profile.properties.*).

Better: stop duplicating — extract the funnel-CTE construction from getFunnel into a shared builder and have getFunnelProfiles use it, so the "View Users" list is always computed from the identical funnel query as the chart (same joins, same breakdown handling). We've implemented it this way in our fork and can upstream a PR if you're interested.

Independently of the fix, the modal should probably distinguish "query failed" from "0 users" instead of data ?? [].

Environment

• Reproduced at upstream HEAD b467a6ce; introduced with the report editor rework (b4214746).
• Self-hosted, ClickHouse 25.x — but the bug is version-independent (missing join in generated SQL).