谷歌BigQuery的意外行为WHERE不列出CONTAINS字符串 [英] unexpected behaviour of Google BigQuery WHERE NOT list CONTAINS string

问题描述

我有一个小例子表 temp.ty ：

从这张表中，我只想提取 ipc 不存在于 not_ipc （并且其中 exclude 不存在于 other_classes 中），这似乎相当直接。然而，以下查询返回零结果：

  SELECT * FROM temp.ty其中不包含not_ipc 
  

如果我将 not_ipc 与例如'G01N 33'如果我用'G01N'替换 not_ipc 它工作得很好。

我尝试了通配符和LIKE操作以及STRING（）函数的变体，但到目前为止我还无法管理。
我有一种强烈的感觉，我错过了一些东西，但我无法弄清楚。

查询：

  select * from 
（选择'G01N 33/55'as ipc，'G01N 34'as not_ipc ），
（选择'G01N 33/55'为ipc，'G01N 33'为not_ipc），
（选择'G01N 33/55'为ipc，字符串（null）为not_ipc）
其中ipc不包含not_ipc或not_ipc为空
  

返回：

  + ----- + ------------ + --------- + --- + 
 |行| ipc | not_ipc | | 
 + ----- + ------------ + --------- + --- + 
 | 1 | G01N 33/55 | G01N 34 | | 
 | 2 | G01N 33/55 | null | | 
 + ----- + ------------ + --------- + --- + 
  

以下是另一个：

  select * from 
（选择'G01N 33/55'为ipc，'G01N 33'为not_ipc，'C08K 3/08'为排除，'C08K 3/08，C08K 77/02'为other_classes），
选择'G01N 33/55'为ipc，'G01N 34'为not_ipc，字符串（空）为排除，'C08K 3/08，C08K 77/02'为other_classes），
（选择'G01N 33/55' 'as ipc，'G01N 33'as not_ipc，string（null）as exclude，string（null）as other_classes），
（选择'G01N 33/55'as ipc，'G01N 36'as not_ipc，string（ null）as exclude，string（null）as other_classes），
（选择'G01N 33/55'as ipc，string（null）as not_ipc，'C08K 3/08'as exclude，string（null）as other_classes ）
其中（非ipc包含not_ipc或not_ipc为空）和（not other_classes包含exclude或exclude为null或other_classes为null）
  

返回：

  + ----- + ------- - ---- + --------- + ----------- + ---------------------- + --- + 
 |行| ipc | not_ipc |排除| other_classes | | 
 + ----- + ------------ + --------- + ----------- + ---- ------------------ + --- + 
 | 1 | G01N 33/55 | G01N 34 | null | C08K 3/08，C08K 77/02 | | 
 | 2 | G01N 33/55 | G01N 36 | null | null | | 
 | 3 | G01N 33/55 | null | C08K 3/08 | null | | 
 + ----- + ------------ + --------- + ----------- + ---- ------------------ + --- + 
  

I have a small example table temp.ty: From this table I want to only extract the rows where ipc is not present in not_ipc (and where exclude is not present in other_classes), which seems rather straigtforward. Yet the following query returns zero results:

SELECT * FROM temp.ty where not ipc contains not_ipc

If I repclace not_ipc with for example 'G01N 33' it doesn't work either yet if I replace not_ipc with 'G01N' it works just fine.

I have tried variations with wildcards and LIKE operations as well as the STRING() function, but so far I have not been able to manage. I have a strong feeling I am missing something, but I cant figure it out.

解决方案

Here is a sample query:

select * from
(select 'G01N 33/55' as ipc, 'G01N 34' as not_ipc),
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc),
(select 'G01N 33/55' as ipc, string(null) as not_ipc)
where not ipc contains not_ipc or not_ipc is null

this returns:

+-----+------------+---------+---+
| Row |    ipc     | not_ipc |   |
+-----+------------+---------+---+
|   1 | G01N 33/55 | G01N 34 |   |
|   2 | G01N 33/55 | null    |   |
+-----+------------+---------+---+

Here is another one:

select * from
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc, 'C08K 3/08' as exclude, 'C08K 3/08,C08K 77/02' as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 34' as not_ipc, string(null) as exclude, 'C08K 3/08,C08K 77/02' as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc, string(null) as exclude, string(null) as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 36' as not_ipc, string(null) as exclude, string(null) as other_classes),
(select 'G01N 33/55' as ipc, string(null) as not_ipc, 'C08K 3/08' as exclude, string(null) as other_classes)
where (not ipc contains not_ipc or not_ipc is null) and (not other_classes contains exclude or exclude is null or other_classes is null)

returns:

+-----+------------+---------+-----------+----------------------+---+
| Row |    ipc     | not_ipc |  exclude  |    other_classes     |   |
+-----+------------+---------+-----------+----------------------+---+
|   1 | G01N 33/55 | G01N 34 | null      | C08K 3/08,C08K 77/02 |   |
|   2 | G01N 33/55 | G01N 36 | null      | null                 |   |
|   3 | G01N 33/55 | null    | C08K 3/08 | null                 |   |
+-----+------------+---------+-----------+----------------------+---+

这篇关于谷歌BigQuery的意外行为WHERE不列出CONTAINS字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！