maria-developers team mailing list archive

[Varun Gupta <varungupta1803@xxxxxxxxx>]

Hi Igor,
After discussing with Sergey, we came up with these conclusions as to why
we used the approach of going through all the keyuses in the KEYUSE array

Cases to consider:
we have an index on column a

1) a OP const
where OP can be (</<=/=/between/ etc on which we do range analysis) . This
case is already handled by the range optimizer. We do not create a NULL
rejecting predicate for such conditions.

2) eq_join conditions (tbl1.a = tbl2.col2)
This is the specific case we have tried to implement in MDEV-15777, we
create NULL rejecting predicates for the keyuses for each table and then
feed these predicates to range optimizer

3) a < func(b,c)
we do not handle this case, because:

a) It is harder to process. We would need to process the whole WHERE clause
to infer
non-nullability of the condition. Example:

  (t1.f2+1 < t1.f1+t1.f3) OR ... OR t1.f1 is null

here the left part of the OR condition doesn't allow f1 to be NULL, but the
right condition allows it. We would also need to take into account tables
inside outer joins. We would need to go through Item classes and add code
which say "Item_xxx() will compute to false when its argument Y is null"
(there is item->not_null_tables() currently, but not not_null_columns()).

b) the conditions in form of arbitrary functions are not as frequently used
as
ref access conditions.

to sum up a) and b) - doable but will require a lot of effort.

Do you have any specific practically relevant example in mind that we should
handle?

Regards,
Varun and Sergey