context filter error on Tableau 2021.2

[griguolcomerranas]

Hello!

We found a strange issue when using context filters on Tableau 2021.2. The error happens on certain dashboards, when setting a filter as context and selecting a single element in the filter. Here is the error: _HTTP status code: 404 Received error: Code: 47. DB::Exception: Unknown identifier: Country; there are columns: City, Altitude: While processing Country = 'South Korea'. (UNKNOWN_IDENTIFIER) (version 22.3.3.44 (official build)) 
Connector Class: clickhouseodbc, Version: 1.3
For support, contact Altinity Inc.
SELECT "airports"."City" AS "City", 'South Korea' AS "Country", AVG(CAST("airports"."Altitude" AS Nullable( DOUBLE))) AS "avg:Altitude:ok" FROM "airports" WHERE ("airports"."Country" = 'South Korea') GROUP BY "airports"."City"

The problem only happens when selecting a single element on the context filter. If two or more elements are selected then it works fine.

Also we have noticed that the problem does not happen on Tableau 2021.4 or later but unfortunately we are forced to use Tableau 2021.2.7 for now.

Looks like the generated query from the dashboard is not the same on Tableau 2021.2 and 2021.4, and this is probably causing the issue.

Here is an example of the generated query on Tableau 2021.2 which causes the error:

SELECT "airports"."City" AS "City",
  'South Korea' AS "Country",
  AVG(CAST("airports"."Altitude" AS Nullable(   DOUBLE))) AS "avg:Altitude:ok"
FROM "airports"
WHERE ("airports"."Country" = 'South Korea')
GROUP BY "airports"."City"

And here query generated by the same dashboard but on Tableau 2021.4, this one works fine:

SELECT "airports"."City" AS "City",
  MIN("airports"."Country") AS "Country",
  AVG(CAST("airports"."Altitude" AS Nullable(   DOUBLE))) AS "avg:Altitude:ok"
FROM "airports"
WHERE ("airports"."Country" = 'South Korea')
GROUP BY "airports"."City"

When selecting 2 or more elements in the filter the generated query is exactly the same on both Tableau 2021.2 and 2021.4 and it works fine:

SELECT "airports"."City" AS "City",
  "airports"."Country" AS "Country",
  AVG(CAST("airports"."Altitude" AS Nullable(   DOUBLE))) AS "avg:Altitude:ok"
FROM "airports"
WHERE ("airports"."Country" IN ('Belgium', 'South Korea'))
GROUP BY "airports"."City",
  "airports"."Country"

I attach a simple workbook pointing to the Altinity demo database where you can reproduce the problem (on Tableau 2021.2)

Is this something that can be fixed on the connector?

Thanks a lot for your great work!

clickhouse_altinity_demo_issue_context_filter_on_Tableau2021.2.zip

[yurifal]

Hi @griguolcomerranas

Looks like a bug in Tableau's own (SQL) code generator.

It has been introduced in v.2021.1 (and still there even in the recent point release 2021.1.14), left untouched in v.2021.2 (and still there in the recent point release 2021.2.11), and seems to be fixed in the v.2021.3 (your worksheet behaves as expected in 2021.3.10).

[griguolcomerranas]

Thanks @yurifal for the information!

However the query looks ok to me and it fails in ClickHouse. For instance, this even more simplified query fails on ClickHouse:

SELECT "airports"."City",
  'South Korea' AS "Country"
FROM "airports"
WHERE ("airports"."Country" = 'South Korea');

SQL Error [47] [07000]: Code: 47. DB::Exception: Unknown identifier: Country; there are columns: City: While processing Country = 'South Korea'.

While the same query works fine on Postgres. Perhaps is a bug on Clickhouse then? Or is it expected to fail by design?

[yurifal]

I'd rather take it as a feature (not a bug) of CH.

fyi the following works just fine: SELECT "airports"."City", 'South Korea' AS "Country_literal" FROM "airports" WHERE ("airports"."Country" = 'South Korea') ;

[griguolcomerranas]

Hi, on this topic, it seems there are already several github issues on clickhouse related to this. For instance this one looks exactly the same: https://github.com/ClickHouse/ClickHouse/issues/27068

And all the related issues are pointing to this bigger task, which is expected to solve the problem (implementation expected by 2022.Q4): https://github.com/ClickHouse/ClickHouse/issues/23194

Nevertheless, the newer Tableau versions are generating the SQL query in a different way, which also acts as workaround for the clickhouse issue.