Geek Answers Handbook

BigQuery - the same query works when sending from the user interface and reports a SQL syntax error from the package

I have an SQL query containing two joins in different fields.

When I run this query interactively in the user interface, I return the result set, no problem. When I send the same query in batch mode, I return a SQL syntax error:

The ambiguous name of the 'video' field in the JOIN. Please use the table qualifier before the field name.

But the connections are already fully qualified:

SELECT 
t1.video AS video
t1.session AS session
...
FROM
(select video, session, ...) t1
LEFT JOIN EACH
(select video, ...) t2
ON t1.video = t2.video
LEFT JOIN EACH
(select session ...) t3
ON t1.session = t3.session

Someone from Google Big Query sees this, the project is growing-ocean-426, the identifier of the batch job is job_1YPDj1wNHPg82aZcvRKjD3coykg

, ( \n ).

+4
1

, flattenResults=false . JOINs, , .

BigQuery JOINs:

BigQuery JOIN , FROM. JOIN JOIN JOIN.

, JOIN t1.video t2.video , "", .

: flattenResults=false:

SELECT t1.video
FROM (SELECT 17 as video, 18 as session) t1
JOIN (SELECT 17 as video) t2
ON t1.video = t2.video
JOIN (SELECT 18 as session) t3
ON t1.session = t3.session

, :

  • flattenResults=false. , , , , , , .

  • t1 t2. :.

    SELECT t1.video
FROM (SELECT 17 as video, 18 as session) t1
JOIN (SELECT 17 as video2) t2
ON t1.video = t2.video2
JOIN (SELECT 18 as session) t3
ON t1.session = t3.session

  • JOIN . , . :.

    SELECT t1_and_t2.video FROM
  (SELECT t1.video as video, t1.session as session
  FROM (SELECT 17 as video, 18 as session) t1
  JOIN (SELECT 17 as video) t2 
  ON t1.video = t2.video) t1_and_t2
JOIN (SELECT 18 as session) t3
ON t1_and_t2.session = t3.session;
+4

More articles:


All Articles