I came across the following problem:
In my application I have a function that checks whether some value is already in use in another record , so I should pass the id in the query of this function. And this is where the problem happens. If I do this check before a record exists, my clause would have id != NULL , which by my logic, should fetch that value from all records, since they have to , a defined ID !
Everything explained! Now in practice:
SELECT VERSION();
-- +-------------------------+
-- | VERSION() |
-- +-------------------------+
-- | 5.7.21-0ubuntu0.17.10.1 |
-- +-------------------------+
SELECT @@GLOBAL.sql_mode;
-- +------------------------------------------------------------------------------------------------------------------------+
-- | @@GLOBAL.sql_mode |
-- +------------------------------------------------------------------------------------------------------------------------+
-- | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
-- +------------------------------------------------------------------------------------------------------------------------+
CREATE DATABASE 'teste';
USE 'teste';
CREATE TABLE 'tabteste' (
'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'nome' varchar(150) COLLATE utf8_unicode_ci NOT NULL,
'cpf' varchar(14) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'cpf' ('cpf')
) ENGINE=InnoDB;
INSERT INTO 'tabteste' ('id', 'nome', 'cpf') VALUES
(1, 'FULANO', '677.215.110-03'),
(2, 'SICRANO', NULL),
(3, 'BELTRANO', '612.463.660-37'),
(4, 'FOO', NULL),
(5, 'BAR', '755.926.990-77'),
(6, 'FUBÁ', '577.862.570-71');
SELECT COUNT(*) AS 'quant' FROM 'tabteste' WHERE ('cpf' = '577.862.570-71' AND 'id' != 6);
-- RETORNA 0, POIS O ID 6 (FUBÁ), PASSADO COMO PARÂMETRO NA QUERY, USA ESSE CPF
-- +-------+
-- | quant |
-- +-------+
-- | 0 |
-- +-------+
SELECT COUNT(*) AS 'quant' FROM 'tabteste' WHERE ('cpf' = '577.862.570-71' AND 'id' != 1234);
-- RETORNA 1, POIS O ID 6 (FUBÁ), DIFERENTE DO PARÂMETRO 1234 NA QUERY, JÁ ESTÁ USANDO ESSE CPF
-- +-------+
-- | quant |
-- +-------+
-- | 1 |
-- +-------+
SELECT COUNT(*) AS 'quant' FROM 'tabteste' WHERE ('cpf' = '577.862.570-71' AND 'id' != NULL);
-- RETORNA 0 POR QUÊ? CONSIDERANDO QUE O ID 6 É DIFERENTE DE NULL!
-- +-------+
-- | quant |
-- +-------+
-- | 0 |
-- +-------+
In the last query SELECT should return 1 also, since 6 is different from NULL !
Why does this happen?
I want to resolve this without having to create a condition within the application to check if the id is NULL and write a query just because apparently > this would be a BUG of MySQL .