Skip to content

Iterating BigQuery query results produces incorrect number of rows #1612

Closed

Description

@polleyg

This is in relation to post on Stack Overflow post here.

As per the sample code here, a user should be be able to paginate the results of a BigQuery query using the following code structure:

while (result != null) {
      Iterator<List<FieldValue>> iter = result.iterateAll();
      while (iter.hasNext()) {
        List<FieldValue> row = iter.next();
        //do something with row/data
        System.out.println(row);
      }
      result = result.getNextPage();
    }
  }

However, when the result set is large (in my tests >31,000 rows), more rows are returned/iterated even though calling result.getTotalRows() returns the correct/expected number of rows.

For example, I have a table with 85,250 rows (9.45 MB). When I query it via the Java API, and use the code from the example above, it actually iterates 160,296 times.

Even if I limit the result set in the query using limit 5000, and set setPageSize(1000L), then it iterates 15,000 times e.g:

QueryRequest queryRequest = QueryRequest
                .newBuilder("SELECT * FROM [<my-project-id>:<dataset>.<table_with_85250_rows>] limit 5000")
                .setUseLegacySql(true)
                .setPageSize(1000L)
                .build();
        QueryResponse response = bigQuery.query(queryRequest);
        QueryResult result = response.getResult();
        System.out.println("Total rows: " + result.getTotalRows());
        Integer rowNumber = 1;
        while(result != null){
            Iterator<List<FieldValue>> iter = result.iterateAll();
            while(iter.hasNext()){
                List<FieldValue> row = iter.next();
                System.out.println("Row: " + rowNumber + ", with number of columns: " + row.size());
                rowNumber++;
            }
            result = result.getNextPage();
        }

Output:

Total rows: 5000
Row: 1, with number of columns: 11
Row: 2, with number of columns: 11
Row: 3, with number of columns: 11
Row: 4, with number of columns: 11
Row: 5, with number of columns: 11
Row: 6, with number of columns: 11
Row: 7, with number of columns: 11
Row: 8, with number of columns: 11
Row: 9, with number of columns: 11
Row: 10, with number of columns: 11
Row: 11, with number of columns: 11
[.....]
Row: 14997, with number of columns: 11
Row: 14998, with number of columns: 11
Row: 14999, with number of columns: 11
Row: 15000, with number of columns: 11

Activity

  1. kamakay commented on Feb 13, 2017

    @kamakay

    Any news? This is a kind of blocking situazion when using API.

  2. polleyg commented on Mar 7, 2017

    @polleyg
    Author

    This is not a bug, but rather confusing documentation/examples. See updated SO question - http://stackoverflow.com/questions/42099842/bigquery-how-to-iterate-over-results-using-java-api

  3. added
    priority: p2Moderately-important priority. Fix may not be included in next release.
    on May 8, 2017
  4. added this to the BigQuery GA milestone on May 8, 2017
  5. garrettjonesgoogle commented on Aug 10, 2017

    @garrettjonesgoogle
    Contributor

    Closing out - see the Stack Overflow question.

  6. added a commit that references this issue on Dec 22, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

api: bigqueryIssues related to the BigQuery API.priority: p2Moderately-important priority. Fix may not be included in next release.

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions