The following query returns "Table too large for JOIN. Consider using JOIN EACH. For more details, https://developers.google.com/bigquery/docs/query-reference#joins"
It worked fine until about 2 days ago.
I did find a work around for other queries that I was unnecessarily using the "Allow Large Results" option on. I disabled that option and the other queries processed fine. Unfortunately when I do that on the query below it returns the error "Response too large to return.".
SELECT di.d.uid AS D_Table_UID,
di.i.uid AS I_Table_UID,
di.i.CustomerID as Customer_ID,
di.i.Route as Route,
di.i.Store as SPOT_Database,
di.d.zero AS Invoice_Line_Number,
di.i.CurrentStatus AS Invoice_Status,
di.i.Invoice_ID AS Invoice_ID,
CONCAT(di.i.CustomerID,'-',DATE( SEC_TO_TIMESTAMP(TIMESTAMP_TO_SEC(di.i.Dropoffdate)-21600))) AS Visit_ID,
IF(di.d.Category CONTAINS 'Upcharge','Upcharge','Item') AS Type,
di.d.Category AS Category,
IF(di.d.Category CONTAINS 'Upcharge', t.Item, di.d.Item) AS Item,
di.d.Item AS Item_W_UC,
IF(di.d.Category CONTAINS 'Alteration', 'Alteration', di.d.Department) AS Department,
di.i.DepartmentGroup as TEMP_Department_Group,
di.d.ItemNum AS Item_Number,
IF(di.d.Category CONTAINS 'Alteration', t.Item_Base_Piece_Count, di.d.PieceCount) AS Pieces,
di.d.Quantity AS Quantity,
IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity))) AS Price_Base,
di.i.CouponTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Coupon,
di.i.AdjustmentTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Adjustment,
di.i.DiscountTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Discount,
di.i.EnviroTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Enviro,
di.i.TaxTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Tax,
di.i.VoidTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/di.i.VoidTotal) AS Paid_Void,
di.i.WriteoffTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Writeoff,
di.i.PaidCash*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Cash,
di.i.PaidCheck*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Check,
di.i.PaidCreditCard*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Credit_Card,
di.i.PaidAccount*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Account,
di.i.PaidOther*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Other,
di.i.TotalPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Total,
di.i.TaxPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration',(di.d.Price*t.Item_Base_Quantity),(di.d.Price*di.d.Quantity)))/(di.i.Subtotal-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Paid_Tax,
di.i.CouponTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal+di.i.CouponTotal))) AS Price_Coupon1,
di.i.AdjustmentTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Price_Adjustment1,
di.i.DiscountTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Price_Discount1,
di.i.EnviroPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Price_Enviro1,
di.i.TaxPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Price_Tax1,
di.i.VoidTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/di.i.VoidTotal) AS Paid_Void1,
di.i.WriteoffTotal*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Writeoff1,
di.i.PaidCash*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Cash1,
di.i.PaidCheck*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Check1,
di.i.PaidCreditCard*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Credit_Card1,
di.i.PaidAccount*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Account1,
di.i.PaidOther*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Other1,
di.i.TotalPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration', (di.d.Price*t.Item_Base_Quantity), (di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Total1,
di.i.TaxPaid*(IF(di.d.Category CONTAINS 'Upcharge', di.d.Price,IF(di.d.Category CONTAINS 'Alteration',(di.d.Price*t.Item_Base_Quantity),(di.d.Price*di.d.Quantity)))/((di.i.TotalPaid-di.i.EnviroPaid-di.i.TaxPaid)-(di.i.DiscountTotal+di.i.AdjustmentTotal))) AS Paid_Tax1,
di.i.Modifieddate AS Date_Modified,
di.i.Dropoffdate AS Date_Dropoff,
di.i.Pickupdate AS Date_Pickup,
di.i.CompletionDate AS Date_Ready,
di.i.Paiddate AS Date_Paid,
di.i.PriceModifieddate AS Date_Price_Modified,
FROM (SELECT * FROM (SELECT * FROM SPOT.i ) AS i JOIN EACH SPOT.d AS d ON i.Invoice_ID = d.Invoice_ID ) AS di JOIN EACH SPOT.temp2 AS t ON di.i.Invoice_ID = t.Invoice_ID AND di.d.ItemNum = t.Item_Number;
See the response here: 'Response too large to return' error when using a destination table and 'Allow Large Results' option This is a bug preparing the query when you have "Allow Large Results" selected, we're working on a fix.