BiqQuery string compare gives Query Failed Error: Unexpected. Please try again

Question

I have a simple query like this:

SELECT IF(production_center='production center 1',total_sales_less_taxes,0)
     as PC1_sales FROM ...

This fails with Query Failed Error: Unexpected.

depending on the value of the string 'production center 1'

for example if I change that string to 'NYC' which is also in the data base as a production_center value, it fails. If I change 'production center 1' to 'production center' it fails, but if I change it to 'production center xyzzy' its okay.

There is no pattern whatsoever to the failure mode.

The work around I am using is to replace production_center = ... with production_center CONTAINS '....'

which works fine.

Based on the suggestion below I tried some more simple experiments: — Mitch VanDuyn
Filed internally as a bug, investigating - thanks for reporting this! — Felipe Hoffa
This continues to happen with various queries seemingly randomly. The only common element is the use of the IF function. — Mitch VanDuyn

jacek2v jacek2v · Accepted Answer · 2014-01-02T05:18:44

If you use LIMIT then maybe this is source of problem - insert it at the end
Try other order statements.
Check what is calculated when condition (production_center='production center 1') is true. Maybe the problem is with total_sales_less_taxes.

Here is working example (public shakespeare database):

SELECT IF(corpus='comedyoferrors',corpus_date,0), corpus 
FROM [publicdata:samples.shakespeare] 
WHERE corpus IN('comedyoferrors', '1kinghenryiv')

OR This ERROR in Big Query. I figure out that if I replace corpus_date by word_count then it fails. But if I use earlier field in condition (replace corpus by word) then is ok:

SELECT word, IF(word='ACT',word_count,0), corpus 
FROM [publicdata:samples.shakespeare] 
WHERE corpus IN('comedyoferrors', '1kinghenryiv')

So, the order of the fields matters.

BiqQuery string compare gives Query Failed Error: Unexpected. Please try again

1 Answers