posthog icon indicating copy to clipboard operation
posthog copied to clipboard
verified publisher icon PostHog

CHQueryErrorAmbiguousColumnName (person_id)

Open sentry[bot] opened this issue 2 years ago • 1 comments

Sentry Issue: POSTHOG-8YY

ServerException: Code: 352.
DB::Exception: Ambiguous column 'person_id': While processing SELECT toTimeZone(toDateTime(timestamp, 'UTC'), 'UTC') AS timestamp, `--pdi.person_id` AS actor_id FROM events ..... [Check Sentry for raw sql]. timestamp, actor_id. Stack trace:

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xa3f9b5a in /usr/bin/clickhouse
1. ? @ 0x14e99975 in /usr/bin/clickhouse
2. DB::JoinToSubqueryTransformMatcher::visit(DB::ASTSelectQuery&, std::__1::shared_ptr<DB::IAST>&, DB::JoinToSubqueryTransformMatcher::Data&) @ 0x14ec7880 in /usr/bin/clickhouse
3. DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, false, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x14e73eb2 in /usr/bin/clickhouse
4. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e52b39 in /usr/bin/clickhouse
5. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
6. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
8. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
9. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
10. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e523f5 in /usr/bin/clickhouse
11. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
12. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
13. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
14. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
15. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
16. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e523f5 in /usr/bin/clickhouse
17. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
18. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
19. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
20. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
21. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
22. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_...
(2 additional frame(s) were not displayed)
...
  File "clickhouse_driver/client.py", line 453, in process_ordinary_query
    return self.receive_result(with_column_types=with_column_types,
  File "clickhouse_driver/client.py", line 117, in receive_result
    return result.get_result()
  File "clickhouse_driver/result.py", line 50, in get_result
    for packet in self.packet_generator:
  File "clickhouse_driver/client.py", line 133, in packet_generator
    packet = self.receive_packet()
  File "clickhouse_driver/client.py", line 150, in receive_packet
    raise packet.exception

CHQueryErrorAmbiguousColumnName: Code: 352.
DB::Exception: Ambiguous column 'person_id': While processing SELECT toTimeZone(toDateTime(timestamp, 'UTC'), 'UTC') AS timestamp, `--pdi.person_id` AS actor_id FROM events AS e INNER JOIN (SELECT distinct_id, argMax(person_id, version) AS person_id FROM person_distinct_id2 WHERE team_id = 12128 GROUP BY distinct_id HAVING argMax(is_deleted, version) = 0) AS pdi ON e.distinct_id = pdi.distinct_id INNER JOIN (SELECT id FROM person WHERE (team_id = 12128) AND (id IN (SELECT id FROM person WHERE (team_id = 12128) AND ((NOT (person.pmat_email ILIKE '%@kiter.app%')) AND (NOT has(['[email protected]', '[email protected]'], person.pmat_email))))) GROUP BY id HAVING (max(is_deleted) = 0) AND ((NOT (argMax(person.pmat_email, version) ILIKE '%@kiter.app%')) AND (NOT has(['[email protected]', '[email protected]'], argMax(person.pmat_email, version))))) AS person ON person.id = pdi.person_id WHERE (team_id = 12128) AND ((match(elements_chain, '(text="GET\\ STARTED")') AND (event = '$autocapture') AND (person_id IN (SELECT person_id FROM cohortpeople WHERE (team_id = 12128) AND (cohort_id = 15929) GROUP BY person_id, cohort_id, team_id, version HAVING sum(sign) > 0))) OR ((match(elements_chain, '(^|;)button(\\.|$|;|:)') AND match(elements_chain, '(text="Get\\ Started")')) AND (event = '$autocapture') AND (person_id IN (SELECT person_id FROM cohortpeople WHERE (team_id = 12128) AND (cohort_id = 15929) GROUP BY person_id, cohort_id, team_id, version HAVING sum(sign) > 0)))) AND (toDateTime(timestamp, 'UTC') >= toDateTime('2022-12-05 00:00:00', 'UTC')) AND (toDateTime(timestamp, 'UTC') <= toDateTime('2023-01-11 23:59:59', 'UTC')) AND (NOT has(['localhost:8000', 'localhost:5000', '127.0.0.1:8000', '127.0.0.1:3000', 'localhost:3000'], `mat_$host`)) GROUP BY timestamp, actor_id. Stack trace:

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xa3f9b5a in /usr/bin/clickhouse
1. ? @ 0x14e99975 in /usr/bin/clickhouse
2. DB::JoinToSubqueryTransformMatcher::visit(DB::ASTSelectQuery&, std::__1::shared_ptr<DB::IAST>&, DB::JoinToSubqueryTransformMatcher::Data&) @ 0x14ec7880 in /usr/bin/clickhouse
3. DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, false, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x14e73eb2 in /usr/bin/clickhouse
4. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e52b39 in /usr/bin/clickhouse
5. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
6. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
8. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
9. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
10. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e523f5 in /usr/bin/clickhouse
11. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
12. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
13. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
14. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
15. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
16. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x14e523f5 in /usr/bin/clickhouse
17. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14ea06cc in /usr/bin/clickhouse
18. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x14e9e3cc in /usr/bin/clickhouse
19. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context const>, bool) @ 0x14ea185b in /usr/bin/clickhouse
20. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, std::__1::shared_ptr<DB::Context const>, bool, bool) @ 0x151ad121 in /usr/bin/clickhouse
21. DB::JoinedTables::resolveTables() @ 0x14ed9901 in /usr/bin/clickhouse
22. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_...
(17 additional frame(s) were not displayed)
...
  File "posthog/queries/base.py", line 62, in handle_compare
    base_entitites = func(filter=filter, team=team, **kwargs)
  File "posthog/queries/trends/formula.py", line 76, in _run_formula_query
    result = insight_sync_execute(
  File "posthog/queries/insight.py", line 12, in insight_sync_execute
    return sync_execute(query, args=args, **kwargs)
  File "posthog/utils.py", line 1204, in inner
    return inner._impl(*args, **kwargs)  # type: ignore
  File "posthog/clickhouse/client/execute.py", line 91, in sync_execute
    raise err

sentry[bot] avatar Jan 12 '23 08:01 sentry[bot]

cc @yakkomajuri might be a good bug to pick up :)

macobo avatar Jan 12 '23 08:01 macobo