Category
Report Builder
Product Version
8.0
BigQuery with reference ID filter does not work
Defect Fixed
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 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
The same problem 
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Hey JeReon,
I hope you are keeping well!
Not sure if you are aware or not, as I know you had previously been doing some testing in version 9, but this Defect has been fixed in versions 7.4.13, 8.0.5 and 9 - I will go ahead and mark this ticket as 'Defect Fixed'
But also worth noting incase you were wondering - Version 9.2 has now been released and is available for download - so if you want to check it out, its had many performance improvements :)
Best Wishes,
Lesley
Hey JeReon,
I hope you are keeping well!
Not sure if you are aware or not, as I know you had previously been doing some testing in version 9, but this Defect has been fixed in versions 7.4.13, 8.0.5 and 9 - I will go ahead and mark this ticket as 'Defect Fixed'
But also worth noting incase you were wondering - Version 9.2 has now been released and is available for download - so if you want to check it out, its had many performance improvements :)
Best Wishes,
Lesley
Hello Lesley,
Thanks for your feedback. We have delayed the use of the YF 9 release but I can confirm that it is working as expected in the 8.0.5 release. So thank you.
Regards,
JeRoen
Hello Lesley,
Thanks for your feedback. We have delayed the use of the YF 9 release but I can confirm that it is working as expected in the 8.0.5 release. So thank you.
Regards,
JeRoen
Thats great to hear, JeReon :)
I hope you enjoy the rest of your week ahead!
Best Wishes,
Lesley
Thats great to hear, JeReon :)
I hope you enjoy the rest of your week ahead!
Best Wishes,
Lesley
Replies have been locked on this page!