[BigQuery]Cannot GROUP BY field references from SELECT list-CodePudding

When running query statement with group by in Google BigQuery, it was failed, and show

Cannot GROUP BY field references from SELECT list alias xxx

I tried many times to obtain its rules, but failed either. My investigation is below:

a> Create tables and insert values

Create table FFNR_A, FFNR_B CREATE TABLE FFNR_A (A1 INT NOT NULL, A2 INT NOT NULL, A3 INT NOT NULL);

CREATE TABLE FFNR_B (B1 INT NOT NULL, B2 INT NOT NULL, B3 INT NOT NULL,B4 INT NOT NULL);

INSERT INTO FFNR_A VALUES (0, 3, 1); INSERT INTO FFNR_A VALUES (1, 0, 2); INSERT INTO FFNR_A VALUES (2, 1, 1); INSERT INTO FFNR_A VALUES (3, 2, 2); INSERT INTO FFNR_A VALUES (5, 3, 0); INSERT INTO FFNR_A VALUES (6, 3, 2); INSERT INTO FFNR_A VALUES (7, 4, 1); INSERT INTO FFNR_A VALUES (8, 4, 3);

INSERT INTO FFNR_B VALUES (1, 1, 2, 0); INSERT INTO FFNR_B VALUES (2, 2, 3, 0); INSERT INTO FFNR_B VALUES (3, 2, 4, 0); INSERT INTO FFNR_B VALUES (4, 1, 5, 0); INSERT INTO FFNR_B VALUES (5, 7, 0, 0); INSERT INTO FFNR_B VALUES (6, 8, 2, 0); INSERT INTO FFNR_B VALUES (7, 7, 1, 0); INSERT INTO FFNR_B VALUES (8, 8, 3, 0); INSERT INTO FFNR_B VALUES (0, 1, 3, 0);

b> Run Query
-- Cannot GROUP BY field references from SELECT list alias B1 at [3:60]
SELECT   A0.`A1`, B1.`B1`,
  FROM `xxx`.`FFNR_B` B1, `xxx`.`FFNR_A` A0
WHERE (A0.`A2` = B1.`B1`) AND (A0.`A2` = B1.`B1`) GROUP BY B1.`B1`,
 A0.`A1`  limit 2;

-- Works
SELECT   A0.`A1`, B1.`B2`,
  FROM `xxx`.`FFNR_B` B1, `xxx`.`FFNR_A` A0
WHERE (A0.`A2` = B1.`B1`) AND (A0.`A2` = B1.`B1`) GROUP BY B1.`B2`,
 A0.`A1`  limit 2;

-- Replace B1->A1 and column A1->A2
-- If use B1(tab), failed either
 SELECT   A0.`A2`, A1.`B1`,
  FROM `xxx`.`FFNR_B` A1, `xxx`.`FFNR_A` A0
WHERE (A0.`A1` = A1.`B1`) AND (A0.`A1` = A1.`B1`) GROUP BY A1.`B1`,
 A0.`A2`  limit 2;

I didn't get any doc in BigQuery docs. Can you give me any suggestions about the rules of group by? Or Is it a bug in BigQuery?

Thanks

CodePudding user response：

Concluding the discussion from the comments:

-- Cannot GROUP BY field references from SELECT list alias B1 at [3:60] 
SELECT A0.`A1`, B1.`B1`, FROM `xxx`.`FFNR_B` B1, `xxx`.`FFNR_A` A0 
WHERE (A0.`A2` = B1.`B1`) AND (A0.`A2` = B1.`B1`) GROUP BY B1.`B1`, A0.`A1` limit 2;

As @Mikhail Berlyant said, in your query, the GROUP BY is getting confused by B1 being table alias and column name in output. The table/alias name should not be the same with column name in GROUP BY. To avoid this issue, use table aliases different from column names or simply use GROUP BY B1, A1 or GROUP BY 1, 2. It is a limitation in BigQuery and not a Bug. Refer to this doc for more information about Groupable data types.