CODEWITHSUNDEEP
phpmysqlnull
Brainfeeder
Brainfeeder

Reputation: 2632

Strange MySQL behaviour for comparison

I found a question that might explain what I am getting as result. But still it seems an odd behaviour.

We have a table filled with supplier products, it gets updated each day (new data read from an xml). A column 'updated' gets a new datetime value during the process. Products that disappear from the supplier xml file should be marked EOL in the column 'flag'

After the import from the xml file I run following SQL to try and find products which should be marked EOL.

SELECT * FROM `app_supplier_products` WHERE `flag` != 'EOL' AND `supplier_id` = 1 AND `updated` < '2014-12-29 07:15:01'

However this returned no rows. If we altered the time to 07:15:15 we did get results. It seemed as if the smaller than comparison did not return rows from another day.

After doing some other tests we managed to get it working with following SQL:

SELECT * FROM `app_supplier_products` WHERE (`flag` IS NULL OR `flag` = 'MODIFIED') AND `supplier_id` = 1 AND `updated` < '2014-12-29 07:15:01'

The flag column can, at this point, be NULL, MODIFIED or EOL. In the future we might add other possible values. This means that we'll have to update all queries when we add another value.

Why doesn't the first query return the expected rows ?

Upvotes: 0

Views: 128

Answers (1)

Barmar
Barmar

Reputation: 782653

Change the query to:

SELECT * FROM `app_supplier_products` WHERE NOT (`flag` <=> 'EOL') AND `supplier_id` = 1 AND `updated` < '2014-11-29 07:15:01'

This uses the null-safe equality operator, so it will match rows where flag = NULL.

If you can't use this operator, you can use:

SELECT * FROM `app_supplier_products` WHERE (`flag` IS NULL OR FLAG != 'EOL') AND `supplier_id` = 1 AND `updated` < '2014-11-29 07:15:01'

Upvotes: 1

Related Questions