0

In my 3 years of running MySQL I have never encountered this issue. So I have a query

SELECT count(*) from table where  cat='green' and state = state and product = product

Result: 15000

Now when I run the same query with an additional condition that should result to true

SELECT count(*) from table where cat='green' and state = state and product = product and lga = lga

Result: 0

Pls what on earth could be wrong??? I assume that lga=lga should result to true just like 1=1 results to true

EDIT: Table schema:

id int(11) NO PRI NULL auto_increment cat varchar(128) NO NULL
state varchar(8) YES NULL
lga varchar(20) YES NULL
product varchar(150) NO NULL

To add some more context, I have a page with filters, so the only way I can ensure that the filters are flexible is to implement it the way I did.

Olamide226
  • 317
  • 5
  • 10
  • 1
    You might want to add sample data and also show us the actual query (what you pasted above won't even run). I'm not sure this is reproducible. – Tim Biegeleisen Nov 25 '20 at 15:35
  • 1
    Show the table schema and some sample data that exhibit that problem... It makes no sense as currently written. Why would you compare a field to itself? That will always be true. So clearly you are not really doing that; maybe you are instead e.g. using a subquery and not realising what precedence aliases in different tables have. – underscore_d Nov 25 '20 at 15:35
  • 4
    If there are all NULLs in lga, that would do it. Can't check nulls for equality. https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value – Martin Burch Nov 25 '20 at 15:36
  • 1
    @MartinBurch is very correct. That is why Im having such results. You cannot check nulls for equality and the lga column contains null values for what I'm running. – Olamide226 Nov 25 '20 at 15:52

1 Answers1

2

If the lga fields for all records where cat = 'green' are all NULL, this will happen because NULL = NULL will filter out all records. "A NULL value is not equal to anything, even itself."

One approach I think you could take is to wrap the condition in ISNULL()

and IFNULL(lga,TRUE) = IFNULL('user provided input here',TRUE);

Please see fiddle: http://sqlfiddle.com/#!9/83c3a4/11

Martin Burch
  • 2,486
  • 3
  • 26
  • 55
  • @Olamide226 If you can accept this answer, please do! (✔️) I'm not sure if you can because the question is closed. – Martin Burch Dec 15 '20 at 09:24