Bug #118808 Different result with range scan and table scan in sql with subquery

• Submitted: 10 Aug 11:26
• Modified: 11 Aug 6:36
• Reporter: zkong kong
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0, 8.4.3, 8.4.6
• OS: Any
• CPU Architecture: Any
• Tags: regression

[10 Aug 11:26] zkong kong

Description:
mysql> select count(*) from t2 where c < (select c from t1);
+----------+
| count(*) |
+----------+
|      972 |
+----------+
1 row in set (0.001 sec)

mysql> select count(*) from t2 ignore index(k1) where c < (select c from t1);
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.001 sec)

How to repeat:
create table t1(id int primary key, c date);

insert into t1 values(1,'1972-03-18');

create table t2(id int primary key, c int, key k1(c));

delimiter $$
create procedure load_data()
begin
declare v_max int unsigned default 1000;
declare v_counter int unsigned default 1;
  while v_counter < v_max do
    insert into t2 (id, c) values (v_counter, v_counter + 1000);
    set v_counter=v_counter+1;
  end while;
  commit;
end 
$$

delimiter ;

call load_data();

select count(*) from t2 where c < (select c from t1);

select count(*) from t2 ignore index(k1) where c < (select c from t1);

The result of the two sql is 972 and 999

[11 Aug 6:36] MySQL Verification Team

Hello zkong kong,

Thank you for the report and test case.
Verified as described.

regards,
Umesh