Custom field can not be used as a major filter, but can be shown in the table visual

Hello,
Could you please support us with the problem described below?

We have HDP version 3.1
Arcadia version 5.0
Connection to Hive without arcengine
Dashboard on top of Hive managed table

We are trying to create Arcadia Dashboard on top of Hive managed table. It is possible without arcengine.
Unfortunately we have big problem with filters when we try to use custom field as a filter.
BUT the same custom field is shown fine on the table visual.
Steps to create our filter:

  1. Create dataset
  2. Go to dataset -> Fields
  3. Edit Fields
  4. Clone e.g. field contract_id
  5. Change display name to CopyContract_id
  6. Go to dasboard based on this dataset and edit it
  7. Go to Filters and click on dimension Copycontract_id to use it as a filter
  8. We have then the following error message:

Filter 5133 - Could not load data
error: (42000, 10004) Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 3:11 Invalid table alias or column reference \u0026#39;Copycontract_id\u0026#39;: (possible column names are: contract_id)

Data type of contract_id and Copycontract_id is String.

We don’t have such error message only for type timestamp - filter is working fine for timestamp type custom fields.
If we will change data type of Copycontract_id to timestamp we will be able to use it as a filter.

@Emi I would first start by checking the query/activity logs to see the full SQL that was generated. That would help pinpoint what the issue is.

The date filter and drop down filters are different in the SQL that they generate, which might be why things work with the date filter but not the multi select.

Hello @shaun

It seems that the problem is in the way how Arcadia is building SQL for filter without an arcengine.

For CopyContact_id:
“query”: “SELECT DISTINCT TA_0.contract_id\n FROM x.y TA_0\n ORDER BY Copycontract_id ASC\n LIMIT 1001”,

We have no alias Copycontract_id in select statement but it is used in ORDER BY.

When we change data type of field CopyContract_id to timestamp we don’t have ORDER BY in SQL and filter works fine:
“query”: “SELECT min(((TA_0.contract_id))) as min(((TA_0.'contract_id'))), max(((TA_0.contract_id))) as max(((TA_0.'contract_id')))\n FROM x.y TA_0\n LIMIT 5000”

@alex123 thanks this is indeed a bug and I have filed a bug report to be fixed in our next release.

Hello,
It seems that the problem is in the way how Arcadia is building SQL for filter.

For CopyContact_id:
“query”: “SELECT DISTINCT TA_0.contract_id\n FROM x.y TA_0\n ORDER BY Copycontract_id ASC\n LIMIT 1001”,

We have no alias Copycontract_id in select statement but it is used in ORDER BY.

When we change data type of field CopyContract_id to timestamp we don’t have ORDER BY in SQL and filter works fine:
“query”: “SELECT min(((TA_0.contract_id))) as min(((TA_0.'contract_id'))), max(((TA_0.contract_id))) as max(((TA_0.'contract_id')))\n FROM x.y TA_0\n LIMIT 5000”

@Emi yes it’s indeed a bug. Will have it fixed in our next release. Thanks

@shaun: Do you know which release and when that would be please? Thanks!

@waiman it should be fixed in the next release.