CODEWITHSUNDEEP
google-bigquery
Danny Allen
Danny Allen

Reputation: 949

BigQuery query failed with error: "Unexpected. Please try again."

The following query is failing with the error message: "Unexpected. Please try again."

SELECT sapId as SAP_ID, accessionId as ACCESSION_ID, diagnosticSetId as DIAGNOSTIC_SET_ID
, if( pimsSoftwareCode is null, 
    if((feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')) OR (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'))
        , 'MOBILE', 'ONLINE'
    )
,'PIMS') as MODE
, if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
    'iOS'
    ,if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')
        , 'Android', null)) as MOBILE_OS
, date(usec_to_timestamp(createAuditDate)) as DATE
, time(usec_to_timestamp(createAuditDate)) as TIME
FROM [20141104Android_backup.EventLog]
where month(USEC_TO_TIMESTAMP(createAuditDate)) = 10
and year(USEC_TO_TIMESTAMP(createAuditDate)) = 2014
and xUserAgent != 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)'
and ( (feature = 'Result_View' and appUrl contains '/app/viewCumulative') or (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')) or (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS')) );

When I modify the following line:

if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
    'iOS'
    ,if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')
        , 'Android', null))

to be:

if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
'iOS'
,if(feature = 'Result_View' and REGEXP_MATCH(xUserAgent, 'Android')
    , 'Android', null))

the query works just fine. I can provide the failing job id if needed. I'm at a total loss to understand why the first variant fails and the second works just fine. Thank you for any help you can provide.

Upvotes: 3

Views: 282

Answers (1)

Danny Kitt
Danny Kitt

Reputation: 3251

The internal error for your job was Field 'MOBILE_OS' is incompatible with the table schema: Type mismatch: actual 'TYPE_BOOL' vs. expected 'TYPE_STRING'. We should surface this error better instead of giving the "Unexpected. Please try again." message.

I've seen this previously when using a literal null, since the query engine usually interprets it as type boolean. Try using STRING(null) instead of null in your IF statement.

Upvotes: 3

Related Questions