"EACH" Keyword isn't returning correct result in "GROUP EACH BY"

Question

I have had an issue over the last month with BigQuery and the use of the keyword "EACH". I have many reports that rely on some very specific and refined queries on Google Analytics Data that I have not been able to update due to this issue. I have not changed my query in any way, but one day the results started appearing as "0" rather than the actual number.

I would love to know if there was a recent production change in BigQuery that might have caused this or if I am doing something wrong. I would really love a resolution on this, it has been happening for a while and really effecting the reporting needs that we have grown to rely on. Below are some additional details of my issue:

Job ID that produces that bad result: jjldtlwOeW2zyxvi7SViTxsDJzA

Bug posted: https://code.google.com/p/google-bigquery/issues/detail?id=195

-- Query that doesn't produce the correct result

select
    date(date) as date
    ,sum(case when hits.type = "page" then 1 else 0 end) as pageviews
    ,count(distinct (case when hits.type = "page" then concat(fullvisitorid, string(visitid), hits.page.pagepath) end), 1000000) as unique_pageviews

from
    table_date_range([XXXXXXXX.ga_sessions_], timestamp('2014-10-06'), timestamp('2014-10-06'))

group each by
    date

ignore case;

-- Query that does work (But cant handle Large amount of data):

select
    date(date) as date
    ,sum(case when hits.type = "page" then 1 else 0 end) as pageviews
    ,count(distinct (case when hits.type = "page" then concat(fullvisitorid, string(visitid), hits.page.pagepath) end), 1000000) as unique_pageviews

from
    table_date_range([XXXXXXXX.ga_sessions_], timestamp('2014-10-06'), timestamp('2014-10-06'))

group by
    date

ignore case;

Danny Kitt Danny Kitt · Accepted Answer · 2014-12-17T04:27:13

I think you've found a bug in IGNORE CASE and GROUP EACH BY.

I was able to reproduce on my own set of analytics data. We'll investigate further to determine a root cause. In the meantime, a workaround is to remove IGNORE CASE from your query and make each of your equality checks case-insensitive using LOWER as follows:

select
    date
    ,sum(case when lower(hits.type) = "page" then 1 else 0 end) as pageviews
    ,count(distinct (case when lower(hits.type) = "page" then concat(fullvisitorid, string(visitid), hits.page.pagepath) end), 1000000) as unique_pageviews

from
    table_date_range([XXXXXXXX.ga_sessions_], timestamp('2014-10-06'), timestamp('2014-10-06'))

group each by
    date;

"EACH" Keyword isn't returning correct result in "GROUP EACH BY"

1 Answers