CODEWITHSUNDEEP
sqlpresto
slysid
slysid

Reputation: 5498

Why SQL query returns values that should be filtered out by condition in Presto?

I am not able to understand the output of my SQL queries and trying to get some sense of it. My data set is as follows

table name: my_table

assignee
--------
Mat_Bar
dan.eml
mon.gal

My first simple query

WITH rows as
(
  SELECT 
  distinct(assignee) 
  FROM 
  my_table
  WHERE 
  assignee NOT LIKE '%_%'
)
SELECT * FROM rows WHERE assignee = 'Mat_Bar'

Above query returned zero records as expected

Now I am adding another OR condition to above query as follows

WITH rows as
(
  SELECT 
  distinct(assignee) 
  FROM 
  my_table
  WHERE 
  assignee NOT LIKE '%_%' OR assignee NOT LIKE '%.%'
)
SELECT * FROM rows WHERE assignee = 'Mat_Bar'

Above query returns one row "Mat_Bar"

I am expecting my second query should return zero records too as the first one but it doesn't. Any suggestions?

PS: I am running the query on AWS Athena (Presto)

Thanks

Upvotes: 1

Views: 525

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This does not do what you think:

assignee NOT LIKE '%_%'

Because _ is a wildcard that matches one character, this just checks that assignee is not a single character.

I suspect that you are confusing LIKE with regexp_like(). LIKE is much more limited and only supports two wildcards: % and _.

Upvotes: 1

Nick
Nick

Reputation: 147166

Negated conditions need to be ANDed together, not ORed, otherwise the result will always be true. Consider

a != 0 OR a != 1

Regardless of the value of a, including 0 and 1, the condition will always be true. So change your condition to 

assignee NOT LIKE '%\_%' AND assignee NOT LIKE '%.%'

Note that _ is a wildcard matching a single character so to match a literal _ you need to escape it as I have done above.

Upvotes: 0

Related Questions