Cannot find objects on unique index with nullable column

[thecodingmachine]

Cannot find objects on unique index with nullable column

[moufmouf]

Ok, this is not a bug (it's a feature!)

When you have an index on multiple columns, the index can be used by the database if it is used in the correct order.

If your index is defined on columns [A, B, C], then you can use the index to filter upon:

• column A,
• column A and B
• column A, B and C.

If you perform a query on column B alone, the database cannot use the index (because the data is first ordered on column A). For more details: https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

So TDBM behaviour is correct. For the index to be used, the left most column of the index MUST be used.
The other columns are optional.

[moufmouf]

Question 2 : If the first FK is nullable, I should be able to query the same function by passing null for the nullable column :

Ok, this issue is legit. We should put a '!' on the field.

[moufmouf]

... but now we have a problem.

findOneByAandBandC(A $a = null, ?B $b = null, ?C $c = null)

Let's imagine all 3 foreign keys are nullable.

When I call findOneByAandBandC(null, null), do I want a query that does "WHERE A_ID IS NULL" or do I want a query that does: "WHERE A_ID IS NULL AND B_ID IS NULL" ???

According to what we said, if I write findOneByAandBandC(null, null), we should do "WHERE A_ID IS NULL" but if I write findOneByAandBandC(null, null, $c), then the presence of the last parameter makes the second parameter behave as a "IS NULL" and we should go for "WHERE A_ID IS NULL AND B_ID IS NULL AND C_ID = :cid"

The problem is that the "null" meaning is ambiguous. In case of the first parameter, it is easy to assess we want a "IS NULL" (otherwise, we would call the "findAll" method, but for subsequent parameters, we cannot really know.

We could of course call "function_get_params" to know the exact number of parameters passed but I'm a bit reluctant to have 2 different behaviours between findOneByAandBandC($a) and findOneByAandBandC($a, null).

[moufmouf]

The meaning of the "null" parameter must not be different based on the position of the parameter (first or not first parameter) in the method, otherwise, this will be error prone for users.
The behaviour of null must stay the same: no filter. If one wants a filter on "IS NULL", it is always possible to override the method.