When running an SQL or FEDSQL procedure query that includes the INTERSECT of two queries that reference a Google BigQuery table, you might encounter a performance issue and error similar to the following:

This issue occurs when the query does not include the DISTINCT keyword. The Google BigQuery database requires the DISTINCT keyword to be included with the INTERSECT operator. When the INTERSECT keyword is not present in the SQL or FEDSQL query, the query that is passed to the database does not include the DISTINCT keyword. 

As a result of the error, each of the two sub-queries is submitted separately. Then, the results of each is pulled into SAS^® software, which performs the INTERSECT into a single result set. 

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Google BigQuery	Linux for x64	9.4		9.4 TS1M6

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.42 is available at:

https://tshf.sas.com/techsup/download/hotfix/HF2/K2L.html#69503

---

In this scenario, the following error might occur: "Error 400: Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [1:139], invalidQuery."

Type:	Problem Note
Priority:	high

Date Modified:	2022-08-29 11:07:15
Date Created:	2022-08-25 10:46:22