Bug #119665 Inconsistant result after modifying optimizer_switch

• Submitted: 13 Jan 6:02
• Reporter: Jiyuan Li
• Status: Open
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 9.3.0-cluster
• OS: Any
• CPU Architecture: Any

[13 Jan 6:02] Jiyuan Li

Description:
Same query returns different results after modifying optimizer_switch

How to repeat:

create table if not exists t0(c0 decimal zerofill  unique key comment 'asdf'  null storage memory column_format default ) ;
create table if not exists t1 like t0;
create table if not exists t2(c0 double zerofill  unique column_format dynamic) ;
replace delayed into t0(c0) values(0.25412822174360883);
alter table t2 pack_keys 0, compression 'none', force;

set global bulk_insert_buffer_size = 2503926855989076838;
insert into t1(c0) values(-729406038);

update t2 set c0=0.3404819634132862 where cast(t2.c0 as signed);

create table if not exists t0_oracle(c0 decimal zerofill  unique key comment 'asdf'  null storage memory column_format default );
create table if not exists t1 like t0;
create table if not exists t1_oracle like t0_oracle;
drop table if exists t2_oracle;
create table if not exists t2_oracle(c0 double zerofill  unique column_format dynamic)  partition by key(c0) partitions 57;
REPLACE DELAYED INTO t0_oracle(c0) VALUES(0.25412822174360883);
INSERT HIGH_PRIORITY INTO t2_oracle(c0) VALUES(1981929861);

alter table t1 engine=NDBCLUSTER;

SET session optimizer_switch = 'engine_condition_pushdown=off';
SELECT /*+ NO_SKIP_SCAN(t0, c0)*/ DISTINCTROW SUM(LEAST(t2_oracle.c0, "")) AS ref0 FROM t2_oracle, t0 STRAIGHT_JOIN t1 ON (0.04634517824285045) IN (t1.c0, t1.c0) LEFT JOIN t2 ON CAST(t2.c0 AS SIGNED) LIMIT 6612091212684849383;
+------+
| ref0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

SET session optimizer_switch = 'engine_condition_pushdown=on';
SELECT /*+ NO_SKIP_SCAN(t0, c0)*/ DISTINCTROW SUM(LEAST(t2_oracle.c0, "")) AS ref0 FROM t2_oracle, t0 STRAIGHT_JOIN t1 ON (0.04634517824285045) IN (t1.c0, t1.c0) LEFT JOIN t2 ON CAST(t2.c0 AS SIGNED) LIMIT 6612091212684849383;
+------+
| ref0 |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)