BigQuery 3 tables join + group by issue

Question

The following query is returning a result:

  SELECT id_a, 
         id_b, 
         id_c
    FROM (
         SELECT id_a, 
                id_b, 
                id_c, 
                Dep_Travail_b
           FROM (
                SELECT A.ID id_a, 
                       B.ID id_b, 
                       B.Dep_Travail Dep_Travail_b
                  FROM [regal-cider-369:insee_recensement_1968_2010.Dep_Naissance] A 
                       INNER JOIN [regal-cider-369:insee_recnsement_1968_2010.Dep_Travail] B 
                                  ON A.ID = B.ID
                ) as JOIN1
                           INNER JOIN (
                                       SELECT ID id_c 
                                         FROM [regal-cider-369:insee_recnsement_1968_2010.Dep_Residence] C
                                      ) as C
                                      ON id_a = id_c
         )
ORDER BY id_a, 
         id_b, 
         id_c

but if the ORDER BY clause is replaced by GROUP BY, the following error is returned:

Field 'id_c' not found; did you mean 'id_a'?

A simplified version the same query leads to the same results (work with ORDER BY at the end of the query, fails with GROUP BY).

  SELECT id_a, 
         id_b, 
         id_c
    FROM (
          SELECT A.ID id_a ,
                 B.ID id_b
            FROM [regal-cider-369:insee_recensement_1968_2010.Dep_Naissance] A 
                 INNER JOIN [regal-cider-369:insee_recensement_1968_2010.Dep_Travail] B 
                            ON A.ID = B.ID
         ) as JOIN1
                   INNER JOIN (
                               SELECT ID id_c 
                                 FROM [regal-cider-369:insee_recensement_1968_2010.Dep_Residence] C
                               ) as C
                              ON id_a = id_c
ORDER BY id_a, 
         id_b, 
         id_c

Is there an issue with GROUP BY and joins, or did I miss something?

Answer 1

It seems that it is working when I add aliases (fieldA, fieldB, fieldC) for the selected fields, A simple version that works with GROUP BY is:

    SELECT id_a AS fieldA, 
           id_b as fieldB, 
           id_c as fieldC
    FROM (
          SELECT A.ID id_a ,
                 B.ID id_b
            FROM [regal-cider-369:insee_recensement_1968_2010.Dep_Naissance] A 
                 INNER JOIN [regal-cider-369:insee_recensement_1968_2010.Dep_Travail] B 
                            ON A.ID = B.ID
         ) as JOIN1
                   INNER JOIN (
                               SELECT ID id_c 
                                 FROM [regal-cider-369:insee_recensement_1968_2010.Dep_Residence] C
                               ) as C
                              ON id_a = id_c
GROUP BY fieldA, 
         fieldB, 
         fieldC