Ignoring duplicate NULL field when inserting data

Need Help
1

I have this code in SendersTable.php :

public function validationDefault(Validator $validator): Validator
{
    ....
    $validator
        ->scalar('public_key')
        ->maxLength('public_key', 1024)
        ->allowEmptyString('public_key')
        ->add('public_key', 'unique', ['rule' => 'validateUnique', 'provider' => 'table']);
    ....
}

Then, I have added a behavior to the table which successfully replaces '' to NULL .

When I try to add a new row into the table it fails because validateUnique is running this command:

SELECT 1 AS existing FROM senders Senders WHERE (Senders.public_key) IS NULL LIMIT 1

And this is the problem: There are already rows where public_key is NULL and there is a unique index for public_key . Because MariaDB supports multiple NULL in a unique index the index is OK.

How can I override the functionality of validateUnique so it doesn’t run the SELECT command when the condition is NULL ?

I am running CakePHP 4.1

Note:
When I don’t add the behavior to the table then I see this SELECT statement checking for duplicate entry:

SELECT 1 AS existing FROM senders Senders WHERE Senders.public_key = '' LIMIT 1

You see, now it’s checked with public_key = '' - so the behavior works.

2

Have you considered writing your own validation rule?

Take a look at this documentation section.

3

Yes, but, honestly, I consider it as an annoying bug when CakePHP is checking something which doesn’t have to be checked.

MariaDB and other databases accept multiple NULL entries in a unique index, why is CakePHP baking here something total weird which doesn’t follow the standard?

4

The idea of having a ‘standard’ that a framework can accommodate seems a little optimistic. If there were such standards, frameworks would be much less valuable.

To me, their value is in getting my simple cases up and running quickly while providing flexible adjustments (like the validation rules) to support my systems special needs.

But if you really believe this behavior is out of step with the most common usage, post an issue on the project. You might find widespread support if a lot of people are experiencing the same. Or you might find it’s a know but tolerated variation as the developers try to balance the needs of new adopters and the needs of legacy developer who maintain long standing cake-based systems.

5

Thank you for your time and your thoughts.

To make a long story short:
The underlying databases support NULL values in a unique index. There is in my eyes no reason that a framework is baking its own rule which violates everything. NULL is NULL and must be excluded from this check.

I can refer to this SO answer which explicitly explains that multiple NULL values are allowed in a unique index. We can now discuss if the SO answer is a hoax, but at least I provide a reference and you have nice words.

And there is also this wiki entry which would rather attest my point of view than the current CakePHP implementation. I repeat myself: I consider it as a bug.

Yes, I will post it as an issue in the project; we will see how the core will react, but, honestly, I am not really very optimistic…

6

If you use Application Rules instead of validation it will pass, the isUnique allows null values.

You can open an issue on the validation of unique to search for null, i suggest to open in on github

7

Thank you, in my eyes only wasted time.

8

sorry, but you, like me, just waste your time.
My conclusion is that nobody want to accept and solve, the bugs that we reported.

9

I am impressed, apparently somebody is reading the forum. It has been now considered in CakePHP 4.2 with the option allowMultipleNulls:
https://book.cakephp.org/4/en/orm/validation.html#creating-unique-field-rules

10

This someone seems to be Mr. othercorey according to this PR :grinning_face_with_smiling_eyes: