CODEWITHSUNDEEP
mysqlsql
thiago marini
thiago marini

Reputation: 544

MySQL apparently can't tell difference between strings

I bumped into this quite weird case:

select count(*) from shipments where user_id = 1 and pickup_id = 2 returns 2

select count(*) from shipments where user_id = 1 and pickup_id = 2 and order_id = 'AAA' returns 1

select count(*) from shipments where user_id = 1 and pickup_id = 2 and order_id <> 'AAA' returns 0

What on earth is going on? I'm using MySQL 5.7.17

Upvotes: 0

Views: 37

Answers (2)

Anandhu Nadesh
Anandhu Nadesh

Reputation: 672

Possibly there is a null value in order_id. <> will not account for that.

<> is not not NULL-tolerant. However 'NOT IN' is. So instead of <> use NOT IN

select count(*) from shipments where user_id = 1 and pickup_id = 2 and order_id NOT IN ('AAA')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Apparently, order_id is NULL in one row.

Instead of <>, use:

select count(*)
from shipments
where user_id = 1 and pickup_id = 2 and
      (not order_id <=> 'AAA')

The <=> is the NULL-safe equality operator (see here). MySQL doesn't have a single NULL-safe inequality, so just use NOT or:

(order_id <> 'AAA' or order_id is null)

Upvotes: 2

Related Questions