BigQuery UNION ALL between two views blows up scanned bytes and query costs

Question

I have two views in a WITH clause like

WITH A AS (...)
, B AS (...)

that I want to UNION via:

SELECT A
UNION ALL
SELECT B

If I execute A alone BQ processes 8 GiB If I execute B alone BQ processes 1 GiB

If I do a

WITH A, B
SELECT * FROM A

it reads correctly 8 GiB, same for B only then only 1 GiB, but if I execute the query with the UNION ALL between them, the query validator tells me it has to process 1.45 TiB (!)

Am I doing something wrong here?

EDIT

Here is the full query:

with bidding AS (
  SELECT bidding_activity.date,
         bidding_activity.channel,
         bidding_activity.level4,
         bidding_activity.level3,
         bidding_activity.level2,
         bidding_activity.level1,
         bidding_activity.level0,
         bidding_activity.articleConditionDescriptionGerman,
         bidding_activity.articleConditionDescription,
         bidding_activity.articleConditionGroup,
         bidding_activity.sellingFormat,
         bidding_activity.customerNr,
         bidding_activity.customerId,
         bidding_activity.email,
         bidding_activity.nickname,
         bidding_activity.city,
         bidding_activity.zipcode,
         bidding_activity.geoId,
         bidding_activity.gender,
         bidding_activity.registrationDate,
         bidding_activity.registrationTypeBuyer AS registrationType,
         bidding_activity.languageBuyer AS language,
         bidding_activity.platformCategoryNameBidBin,
         bidding_activity.platformSubcategoryNameBidBin,
         bidding_activity.platformCategoryNameListing,
         bidding_activity.platformSubcategoryNameListing,
         bidding_activity.siteCode,
         bidding_activity.siteDescription,
         bidding_activity.sitePlatform,
         STRING(NULL) AS hasPromo,
         SUM(IFNULL(bidding_activity.automatedBids,0)) AS automatedBids,
         SUM(IFNULL(bidding_activity.bids,0)) AS bids,
         SUM(IFNULL(bidding_activity.bins,0)) AS bins,
         SUM(IFNULL(bidding_activity.manualBids,0)) AS manualBids,
         SUM(IFNULL(bidding_activity.successSaleBidBins,0)) AS successSaleBidBins
    FROM `project.DWH.bidding_activity_daily` AS bidding_activity
   WHERE date ='2018-09-01'
GROUP BY bidding_activity.date,
         bidding_activity.channel,
         bidding_activity.level4,
         bidding_activity.level3,
         bidding_activity.level2,
         bidding_activity.level1,
         bidding_activity.level0,
         bidding_activity.articleConditionDescriptionGerman,
         bidding_activity.articleConditionDescription,
         bidding_activity.articleConditionGroup,
         bidding_activity.sellingFormat,
         bidding_activity.customerNr,
         bidding_activity.customerId,
         bidding_activity.email,
         bidding_activity.nickname,
         bidding_activity.city,
         bidding_activity.zipcode,
         bidding_activity.geoId,
         bidding_activity.gender,
         bidding_activity.registrationDate,
         registrationType,
         language,
         bidding_activity.platformCategoryNameBidBin,
         bidding_activity.platformSubcategoryNameBidBin,
         bidding_activity.platformCategoryNameListing,
         bidding_activity.platformSubcategoryNameListing,
         bidding_activity.siteCode,
         bidding_activity.siteDescription,
         bidding_activity.sitePlatform)
, listing AS (
  SELECT listing_activity.date,
         listing_activity.channel,
         listing_activity.level4,
         listing_activity.level3,
         listing_activity.level2,
         listing_activity.level1,
         listing_activity.level0,
         listing_activity.articleConditionDescriptionGerman,
         listing_activity.articleConditionDescription,
         listing_activity.articleConditionGroup,
         listing_activity.sellingFormat,
         listing_activity.customerNr,
         listing_activity.customerId,
         listing_activity.email,
         listing_activity.nickname,
         listing_activity.city,
         listing_activity.zipcode,
         listing_activity.geoId,
         listing_activity.gender,
         listing_activity.registrationDate,
         listing_activity.registrationType AS registrationType,
         listing_activity.language AS language,
         STRING(NULL) AS platformCategoryNameBidBin,
         STRING(NULL) AS platformSubcategoryNameBidBin,
         listing_activity.platformCategoryNameListing,
         listing_activity.platformSubcategoryNameListing,
         listing_activity.siteCode,
         listing_activity.siteDescription,
         listing_activity.sitePlatform,
         listing_activity.hasPromo,
         SUM(IFNULL(listing_activity.listedArticles,0)) AS listedArticles,
         SUM(IFNULL(listing_activity.listedItems,0)) AS listedItems,
         SUM(IFNULL(listing_activity.listedArticlesInDeAndFr,0)) AS listedArticlesInDeAndFr,
         SUM(IFNULL(listing_activity.postedArticles,0)) AS postedArticles,
         SUM(IFNULL(listing_activity.postedItems,0)) AS postedItems,
         SUM(IFNULL(listing_activity.repostedArticles,0)) AS repostedArticles,
         SUM(IFNULL(listing_activity.repostedItems,0)) AS repostedItems,
         SUM(IFNULL(listing_activity.articlesWithShippingCost,0)) AS articlesWithShippingCost,
         SUM(IFNULL(listing_activity.shippingFeeGross,0)) AS shippingFeeGross,
         SUM(IFNULL(listing_activity.listingPriceGross,0)) AS listingPriceGross
    FROM `project.DWH.listing_activity_daily` AS listing_activity
   WHERE date ='2018-09-01'
GROUP BY listing_activity.date,
         listing_activity.channel,
         listing_activity.level4,
         listing_activity.level3,
         listing_activity.level2,
         listing_activity.level1,
         listing_activity.level0,
         listing_activity.articleConditionDescriptionGerman,
         listing_activity.articleConditionDescription,
         listing_activity.articleConditionGroup,
         listing_activity.sellingFormat,
         listing_activity.customerNr,
         listing_activity.customerId,
         listing_activity.email,
         listing_activity.nickname,
         listing_activity.city,
         listing_activity.zipcode,
         listing_activity.geoId,
         listing_activity.gender,
         listing_activity.registrationDate,
         registrationType,
         language,
         platformCategoryNameBidBin,
         platformSubcategoryNameBidBin,
         listing_activity.platformCategoryNameListing,
         listing_activity.platformSubcategoryNameListing,
         listing_activity.siteCode,
         listing_activity.siteDescription,
         listing_activity.sitePlatform,
         listing_activity.hasPromo)
   SELECT date,
          channel,
          level4,
          level3,
          level2,
          level1,
          level0,
          articleConditionDescriptionGerman,
          articleConditionDescription,
          articleConditionGroup,
          sellingFormat,
          customerNr,
          customerId,
          email,
          nickname,
          city,
          zipcode,
          geoId,
          gender,
          registrationDate,
          registrationType,
          Language,
          PlatformCategoryNameBidBin,
          platformSubcategoryNameBidBin,
          platformCategoryNameListing,
          platformSubcategoryNameListing,
          siteCode,
          siteDescription,
          sitePlatform,
          hasPromo,
          bidding_activity.automatedBids AS  automatedBids,
          bidding_activity.bids AS bids,
          bidding_activity.bins AS bins,
          bidding_activity.manualBids AS manualBids,
          bidding_activity.successSaleBidBins AS successSaleBidBins,
          NULL AS listedArticles,
          NULL AS listedItems,
          NULL AS listedArticlesInDeAndFr,
          NULL AS postedArticles,
          NULL AS postedItems,
          NULL AS repostedArticles,
          NULL AS repostedItems,
          NULL AS closedArticles,
          NULL AS closedItems,
          NULL AS openArticles,
          NULL AS ratings,
          NULL AS gmvSoldGross,
          NULL AS gmvSoldNet,
          NULL AS gmvSoldCancelled,
          NULL AS ordersSoldGross,
          NULL AS ordersSoldCancelled,
          NULL AS itemsSoldGross,
          NULL AS itemsSoldCancelled,
          NULL AS listingFees,
          NULL AS promoFees,
          NULL AS successFees,
          NULL AS transactionRevenueExclPlp,
          NULL AS plpRevenue,
          NULL AS transactionRevenueNet,
          NULL AS incomingPayments,
          NULL AS otherCredits,
          NULL AS otherRevenues,
          NULL AS reminderFees,
          NULL AS smsFees,
          NULL AS plpPackages,
          NULL AS createdAccounts,
          NULL AS visitingAccounts,
          NULL AS articlesWithShippingCost,
          NULL AS shippingFeeGross,
          NULL AS listingPriceGross,
          NULL AS gmvBoughtGross
     FROM bidding AS bidding_activity
UNION ALL
   SELECT date,
          channel,
          level4,
          level3,
          level2,
          level1,
          level0,
          articleConditionDescriptionGerman,
          articleConditionDescription,
          articleConditionGroup,
          sellingFormat,
          customerNr,
          customerId,
          email,
          nickname,
          city,
          zipcode,
          geoId,
          gender,
          registrationDate,
          registrationType,
          Language,
          PlatformCategoryNameBidBin,
          platformSubcategoryNameBidBin,
          platformCategoryNameListing,
          platformSubcategoryNameListing,
          siteCode,
          siteDescription,
          sitePlatform,
          hasPromo,
          NULL AS automatedBids,
          NULL AS bids,
          NULL AS bins,
          NULL AS manualBids,
          NULL AS successSaleBidBins,
          selling_activity.listedArticles AS listedArticles,
          selling_activity.listedItems AS listedItems,
          selling_activity.listedArticlesInDeAndFr AS listedArticlesInDeAndFr,
          selling_activity.postedArticles AS postedArticles,
          selling_activity.postedItems AS postedItems,
          selling_activity.repostedArticles AS repostedArticles,
          selling_activity.repostedItems AS repostedItems,
          NULL AS closedArticles,
          NULL AS closedItems,
          NULL AS openArticles,
          NULL AS ratings,
          NULL AS gmvSoldGross,
          NULL AS gmvSoldNet,
          NULL AS gmvSoldCancelled,
          NULL AS ordersSoldGross,
          NULL AS ordersSoldCancelled,
          NULL AS itemsSoldGross,
          NULL AS itemsSoldCancelled,
          NULL AS listingFees,
          NULL AS promoFees,
          NULL AS successFees,
          NULL AS transactionRevenueExclPlp,
          NULL AS plpRevenue,
          NULL AS transactionRevenueNet,
          NULL AS incomingPayments,
          NULL AS otherCredits,
          NULL AS otherRevenues,
          NULL AS reminderFees,
          NULL AS smsFees,
          NULL AS plpPackages,
          NULL AS createdAccounts,
          NULL AS visitingAccounts,
          selling_activity.articlesWithShippingCost AS articlesWithShippingCost,
          selling_activity.shippingFeeGross AS shippingFeeGross,
          selling_activity.listingPriceGross AS listingPriceGross,
          NULL AS gmvBoughtGross
     FROM listing AS selling_activity

Your question is not clear; maybe including the actual query that you are trying to run would help. — Elliott Brossard, Sep 21 '18 at 17:50
I'm not seeing anything obvious. Maybe you could [file a bug](https://issuetracker.google.com/issues/new?component=187149&template=0). — Elliott Brossard, Sep 24 '18 at 13:50
@Danieboy if I use the old syntax I get: `Error: Duplicate column names in the result are not supported. Found duplicate(s):` even though this should not happen with the query above, so I think it's a bug. — Tobi, Sep 25 '18 at 14:49

BigQuery UNION ALL between two views blows up scanned bytes and query costs

0 Answers0