Multiple INSERT/UPDATE statements returns undefined method `dataset_id' for nil:NilClass

[bfeigin]

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Please run down the following list and make sure you've tried the usual "quick fixes":

• Search the issues already opened: https://github.com/googleapis/google-cloud-ruby/issues ✅
• Search StackOverflow: http://stackoverflow.com/questions/tagged/google-cloud-platform+ruby ✅

If you are still having issues, please be sure to include as much information as possible:

Environment details

• OS: OSX 11.2.1
• Ruby version: ruby 2.6.3p62
• Gem name and version: google-cloud-bigquery (1.16.0)

Steps to reproduce

1. establish connection to bigquery
2. Run query with multiple SELECT, INSERT or a single CREATE [OR REPLACE] PROCEDURE
3. see error undefined method `dataset_id' for nil:NilClass

Code example

credentials = { ... }

multi_insert_statement = "INSERT into `sql-app-00000.ds1.test_set`(foo) values ('foo');
INSERT into `sql-app-00000.ds1.test_set`(foo) values ('foo');"
bigquery_client = Google::Cloud::Bigquery.new(credentials: credentials)
bigquery_client.query(multi_insert_statement)
> undefined method `dataset_id

Full backtrace

irb(main):030:0> client = Google::Cloud::Bigquery.new(credentials: credentials)
=> #<Google::Cloud::Bigquery::Project:0x0000... @service=Google::Cloud::Bigquery::Service(sql-app-XXXXX)>
irb(main):031:0> client.query statement
Traceback (most recent call last):
        1: from (irb):31
NoMethodError (undefined method `dataset_id' for nil:NilClass)

rescued error.backtrace:

/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/service.rb:500:in `rescue in execute'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/service.rb:493:in `execute'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/service.rb:354:in `job_query_results'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/query_job.rb:538:in `block in wait_until_done!'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/query_job.rb:537:in `loop'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/query_job.rb:537:in `wait_until_done!'
/usr/local/bundle/ruby/2.6.0/gems/google-cloud-bigquery-1.16.0/lib/google/cloud/bigquery/project.rb:769:in `query'

Traced with breakpoints

Tracing with breakpoints leads me to the source at

google-cloud-ruby/google-cloud-bigquery/lib/google/cloud/bigquery/query_job.rb

Line 1691 in 9d6e55a

@gapi.configuration.query.destination_table.dataset_id

Called in

google-cloud-ruby/google-cloud-bigquery/lib/google/cloud/bigquery/query_job.rb

Line 686 in 9d6e55a

def data token: nil, max: nil, start: nil

Making sure to follow these steps will guarantee the quickest resolution possible.

Thanks!

[quartzmo]

@bfeigin Thank you for reporting this issue. I will try to reproduce it soon.

[quartzmo]

I have reproduced this bug.

As noted above, at query_job.rb#L1691, the destination_table is empty when the query includes multiple INSERT statements. Because the QueryJob#data currently sends the destination_table value to tabledata.list in order to return the query results, the error occurs.

Presumably BigQuery does not populate destination_table when there are multiple INSERT statements because the target tables in the statements could be different. This client faces the same uncertainty, so I am unsure what table reference to send to tabledata.list in this case.

[quartzmo]

@bfeigin As a workaround, you may use #query_job instead of #query to avoid the tabledata.list RPC and therefore the error.

[tswast]

In Python, we avoid calling tabledata.list if the query has no results: https://github.com/googleapis/python-bigquery/blob/93d15e2e5405c2cc6d158c4e5737361344193dbc/google/cloud/bigquery/job/query.py#L1316-L1321 to address this issue.

[ZimbiX]

As a workaround, we found that it worked to suffix ; select 1 to our query.

(Discovered while batching alter table statements in sequel-bigquery)

[quartzmo]

@ZimbiX That seems like a great tip, thank you! I'm going to take a look at what you're doing this week to see if we can make progress on this issue.