intermittent error: Invalid value at 'start_index' (TYPE_UINT64), "1e+05" [invalid]

[HarlanH]

A report that I've been running daily for months started failing in the last day or two with an intermittent error. I can usually re-run and get it to work, but it's puzzling. It appears to be an error pulling the data back.

In bq_check_response, result$content has a 400 error code and the error Error: Invalid value at 'start_index' (TYPE_UINT64), "1e+05" [invalid] . start_index isn't in my code.

Happy to help debug this, but not sure where to start (or how to generate a reprex).

bigrquery is version 1.3.1; R version 3.6.1; x86_64-apple-darwin15.6.0

[drewmcdonald]

Thanks for posting @HarlanH. I have also seen this pop up in the last few days.

[NicolaDonelli]

Hi @HarlanH I am experiencing the same problem and I was about to open an issue myself right now!

We experienced today a strange error that was not present yesterday (same query same system configuration)

The code is:

query=MY_QUERY
path=MY_KEYPATH
projectID=MY_PROJECT


bigrquery::bq_auth(path = path)
tb <- bigrquery::bq_project_query(x = projectID, query = query, 
                                  destination_table = NULL, 
                                  use_legacy_sql=FALSE) %>% 
  bigrquery::bq_table_download() 

Today it returns:
Error: Invalid value at 'start_index' (TYPE_UINT64), "1e+05" [invalid]

We suspect that something might have changed con BQ API side since if we set the parameter "start_index" of bq_table_download function to 1 it works (but it actually downloads one less row wrt yesterday).

This is the "working" solution:

query=MY_QUERY
path=MY_KEYPATH
projectID=MY_PROJECT


bigrquery::bq_auth(path = path)
tb <- bigrquery::bq_project_query(x = projectID, query = query, 
                                  destination_table = NULL, 
                                  use_legacy_sql=FALSE) %>% 
  bigrquery::bq_table_download(start_index=1) 

[hadley]

Random idea — try options(scipen = 20) and see if that fixes the problem.

[NicolaDonelli]

options(scipen = 20)

Thanks @hadley, your proposed solution actually works

[drewmcdonald]

@hadley is your intuition that something is rendering the integer 100000 as a string "1e5"?

[hadley]

@drewmcdonald yes, exactly — obviously it's just a hack until someone figures out where the conversion to string is happening (and if possible, why it's only started breaking recently). I do notice that there was a jsonlite release on July 26 but nothing there's nothing related in the news.

[mlampros]

The following is a reproducible example which throws an Error on Ubuntu 18.04 (credentials and project have to be adjusted),

bq_auth_list = list(email = "YOURNAME@email.com",
                    path = "YOUR_CREDS_FILE.json")

verbose = TRUE
max_results = Inf
project = "YOUR_PROJECT"
DATASET = "open_images"

query = glue::glue("select * from `bigquery-public-data.{DATASET}.labels` LIMIT 2000000")

do.call(bigrquery::bq_auth, bq_auth_list)
init_query = bigrquery::bq_dataset_query(x = bigrquery::bq_dataset(project,
                                                                   DATASET),
                                         query = query,
                                         billing = project,
                                         quiet = ifelse(verbose, FALSE, TRUE))

dat = bigrquery::bq_table_download(init_query, max_results = max_results,
                                   quiet = ifelse(verbose, FALSE, TRUE))


# rlang::last_error()
# rlang::last_trace()

Based on the 'Backtrace' ,

> rlang::last_error()
<error/bigrquery_invalid>
Invalid value at 'start_index' (TYPE_UINT64), "1e+05" [invalid] 
Backtrace:
 1. bigrquery::bq_table_download(...)
 5. bigrquery:::bq_check_response(...)
 6. bigrquery:::signal_reason(json$error$errors[[1L]]$reason, json$error$message)
Run `rlang::last_trace()` to see the full context.

> rlang::last_trace()
<error/bigrquery_invalid>
Invalid value at 'start_index' (TYPE_UINT64), "1e+05" [invalid] 
Backtrace:
    █
 1. ├─bigrquery::bq_table_download(...)
 2. │ └─bigrquery:::bq_download_pages(...)
 3. │   └─curl::multi_run(pool = pool)
 4. └─(function (result) ...
 5.   └─bigrquery:::bq_check_response(...)
 6.     └─bigrquery:::signal_reason(json$error$errors[[1L]]$reason, json$error$message)

the error comes from the bigrquery:::bq_download_pages() function, which takes the page_info object as input. The page_info object uses the bq_download_page_info() function which returns the following list,

  list(
    n_rows = max_results,
    n_pages = n_pages,

    begin = page_begin,
    end = page_end
  )

all 4 output sublists (n_rows, n_pages, begin, end) are of type 'numeric'. I was able to fix the error for my use case by converting to integer which I guess - currently - the function expects,

  list(
    n_rows = as.integer(max_results),
    n_pages = as.integer(n_pages),

    begin = as.integer(page_begin),
    end = as.integer(page_end)
  )

[hadley]

@mlampros thanks for the investigation! That suggests that a minimal reprex of:

httr::modify_url("http://google.com", query = list(startIndex = 1e5))
#> [1] "http://google.com/?startIndex=1e%2B05"

^{Created on 2020-07-31 by the reprex package (v0.3.0)}

I'm not sure why this ever worked. Perhaps google previously was more permissive in passing url parameters and now they're rolling out a stricter parser? That would be a possible explanation for why the failures were intermittent — its likely the roll out of a new API version was gradual, so it was luck or not whether you got the old version (that worked) or the new version (that didn't).

It's not 100% clear to me whether this should be fixed in bigrquery (I'd probably do the coercion to integer in bq_download_page_handle() so it's closer to where its used) or in httr.

[gjuggler]

This is affecting users of our project as well – see r-lib/httr#670 for a potential httr-side fix. If that's deemed too low-level or risky, I'm happy to change tack and pursue a bigrquery PR instead.

[hadley]

I think that's a reasonable approach, but I'd need to think about it as it'll have widespread impact. I think a PR to bigrquery would also be useful (i.e. leave the httr one and I'll get to it eventually; I'll prioritise reviewing a bigrquery PR)