BigQuery with reference ID filter does not work

JeRoen shared this problem 4 months ago
Defect Logged

Hello,

We are for a specific report connecting to a BigQuery database and have build a View on that. But when I a ClientOrg Reference ID as filter for a certain field in the view the SQL query is not executed. It is generating an error ...

Without the clientorg reference ID Filter the generated SQL is

(SELECT DISTINCT

`fieldname` AS C1

FROM `projectname`.`datasetname` `aliasname`

LIMIT 1000000)

And that query is executed correctly (the bold texts are altered).

When I activate the access filter based on ClientOrg referenceID the query is not executed correctly. With the following errormessage.

error_assist


Error retrieving results
[Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [5:4]", "reason" : "invalidQuery" } ], "message" : "Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [5:4]", "status" : "INVALID_ARGUMENT" }


The generated SQL after activating the access filter is this (altered fields are bold):
(SELECT DISTINCT

`fieldname` AS C1

FROM `projectname`.`datasetname` `aliasname`

WHERE (

`fieldname` AS C2 IN (109)

)

LIMIT 1000000)

When I remove the AS C2 in the WHERE clause it is running correctly.
How to prevent the alias in the where clause? Any idea.
Regards,
JeRoen

Comments (8)

photo
1

Hi JeRoen,

Thank you for reaching out to support with your issue.

I was able to find this behavior as a known defect in our developer platform. I will go ahead and mark your organization as affected, and attach this ticket to the task. Updates to the task will be provided here as they are available. I will in turn mark this as Defect Logged for now, feel welcome to reply here with further related inquiries.

Kind regards,

Nathan Goddard

photo
1

Hello Nathan,

Thanks for looking into it.

I hope this will be fixed rather soon because we will be making more and more use of BigQuery data sources in the near future. Thanks in advance.

Regards,

JeRoen

photo
1

Hi JeRoen,

I have increase the priority on this item and informed your account manager about this issue to make sure that it dealt with a promptly as possible.

Kind regards,

Nathan Goddard

photo
1

Hello Nathan,

A differenct scenario with the same problem. When I add an aggregation (count distinct) to a textfield YellowFin created the following SQL for BigQuery.


(SELECT DISTINCT

`field1` AS C1,

COUNT(DISTINCT(`field2` AS C2)) AS C3

FROM `project`.`dataset` `alias`

WHERE (

`field3` >= '2018-11-20 15:15:37.01'

)

GROUP BY

`field1`


This generates an error:


Error retrieving results

An error occurred retrieving the results from the database.

The error message was: "[Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [3:19]", "reason" : "invalidQuery" } ], "message" : "Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [3:19]", "status" : "INVALID_ARGUMENT" }".

Please check the SQL syntax and try again.


Simply removing the "AS C2" in the aggregation definitiion makes this query work in the bigquery editor on GCP.

This makes working with BigQuery unmanageable. Might have to revert to freehand SQL but that would be a big bummer ... :-(.

Regards,

JeRoen

photo
1

Hello JeReon,

Thank you for the additional scenario, I have written this up and added as an example to the development ticket for this issue.

If you find any other scenarios please do let me know and I will continue to update our team.

Kind regards,

Nathan Goddard

photo
1

Hello,

Any change this is getting fixed in V9? Because working with BigQuery in YellowFin is broken at the moment. Would be great if correct SQL was generated.

Thanks for any update.

Regards,

JeRoen

photo
1

Hello JeRoen,

I have checked with the development team and a fix will been included in following future releases;


7.4.13,

8.0.5,

9.0.4 (Beta)


I'm afraid I don't have a release date for these versions, so I will keep this in the status 'Defected Logged' until I can confirm their releases.


Kind regards,

Nathan Goddard

photo
1

Hello Nathan,

Thanks for looking into this. And happy to hear it is getting fixed in a next release.

We plan to move over to V9 as soon as possible after release as there a lot of improvements we need/want.

Regards,

JeRoen