google-bigquery - BigQuery 查询失败，错误为 : "Unexpected. Please try again."

Question

以下查询失败并显示错误消息:“意外。请重试。”

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')) );

当我修改以下行时:

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))

成为:

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))

查询工作得很好。如果需要，我可以提供失败的作业 ID。我完全无法理解为什么第一个变体失败而第二个变体工作得很好。感谢您提供的任何帮助。

Answer 1

您的作业的内部错误是字段“MOBILE_OS”与表架构不兼容:类型不匹配:实际“TYPE_BOOL”与预期“TYPE_STRING”。我们应该更好地解决这个错误，而不是给出“意外。请重试。”消息。

我之前在使用字面值 null 时看到过这种情况，因为查询引擎通常将其解释为 boolean 类型。尝试在您的 IF 语句中使用 STRING(null) 而不是 null。