Bug #95967 Backwards ref scan does not perform late NULLs filtering

• Submitted: 25 Jun 2019 5:13
• Modified: 23 Jul 2019 21:37
• Reporter: Manuel Ung
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.16
• OS: Any
• CPU Architecture: Any

[25 Jun 2019 5:13] Manuel Ung

Description:
It looks like late NULL filtering is not applied consistently between the forwards/backwards RefIterators. This causes backwards scans to be in some cases much slower for no reason.

The issue is that in RefIterator<false>::Read(), we perform a check on m_ref->impossible_null_ref() whereas we don't for RefIterator<true>::Read().

How to repeat:
create table t (i int primary key auto_increment, j int, key(j));
insert into t values (null, null);
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;
insert into t select null, null from t;

# This is very fast :)
select t1.i as a, (select t2.i from t t2  where t1.j = t2.j  order by j asc, i asc limit 1) as b from t t1;

# This is very slow :(
select t1.i as a, (select t2.i from t t2  where t1.j = t2.j  order by j desc, i desc limit 1) as b from t t1;

Suggested fix:
Add m_ref->impossible_null_ref() check in RefIterator<true>::Read() 

The pre-8.0 version of these functions are called join_read_always_key/join_read_last_key and probably need something similar.

[25 Jun 2019 12:21] MySQL Verification Team

Hello Manuel,

Thank you for the report and test case.
With the provided test case not much difference noticed on 8.0.16 but if I increase rows then noticeable difference observed.

regards,
Umesh

[23 Jul 2019 21:37] Jon Stephens

Documented fix in the MySQL 8.0.18 changelog, as follows:

    Late NULL filtering, to avoid index lookups if the lookup key
    has at least one NULL, was not performed for backwards index
    scans, although it was for forward index scans.

Closed.