× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

unexpected behaviour of Google BigQuery WHERE NOT list CONTAINS string

Marton
May 21, 2015
<p>Here is a sample query:</p> <pre><code>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 </code></pre> <p>this returns:</p> <pre><code>+-----+------------+---------+---+ | Row | ipc | not_ipc | | +-----+------------+---------+---+ | 1 | G01N 33/55 | G01N 34 | | | 2 | G01N 33/55 | null | | +-----+------------+---------+---+ </code></pre> <p>Here is another one:</p> <pre><code>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) </code></pre> <p>returns:</p> <pre><code>+-----+------------+---------+-----------+----------------------+---+ | 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 | | +-----+------------+---------+-----------+----------------------+---+ </code></pre> <p>This tip was originally posted on <a href="http://stackoverflow.com/questions/30355055/unexpected%20behaviour%20of%20Google%20BigQuery%20WHERE%20NOT%20list%20CONTAINS%20string/30355559">Stack Overflow</a>.</p>
comments powered by Disqus