Convert left join on condition to left part directly which cause the query result miss some row

[jsxiaoshunzi]

Overview of the Issue

SQL:select * from caoguoshun_shard left join caoguoshun_shard_sec on caoguoshun_shard.id != 1;
The Qurey Plan :

We have left join with on condition which only have ralation with left table, vitess direct add the condition to left table.

But this condition only have restrict on right table, we can add the on condtion to left table only if this join is a inner join.

Reproduction Steps

1. Deploy vschema
   alter vschema on caoguoshun_shard add vindex xxhash(id);
   alter vschema on caoguoshun_shard_sec add vindex xxhash(id);

3.Create table

CREATE TABLE caoguoshun_shard ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, col varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, a int DEFAULT NULL, bar int DEFAULT NULL,
foo varchar(16) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE caoguoshun_shard_sec ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, col varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, a int DEFAULT NULL, bar int DEFAULT NULL,
foo varchar(16) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

3. Insert data
   insert into caoguoshun_shard (id,user_id,col,a,bar,foo) values(1,1,'col',1,1,'hello');
   insert into caoguoshun_shard (id,user_id,col,a,bar,foo) values(2,2,'col2',2,2,'hello');
   insert into caoguoshun_shard (id,user_id,col,a,bar,foo) values(3,3,'col',1,1,'hello');

insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(1,1,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(2,1,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(3,1,'col1',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(4,2,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(5,3,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(6,3,'col2',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(7,1,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(9,4,'col',1,1,'hello');
insert into caoguoshun_shard_sec (id,user_id,col,a,bar,foo) values(10,2,'col',1,1,'hello');

4.Execute Query And Compate results with MySQL
select * from caoguoshun_shard left join caoguoshun_shard_sec on caoguoshun_shard.id != 1;

Binary Version

vtgate version Version: 18.0.4-SNAPSHOT

Operating System and Environment details

Architecture:      arm64
Kernel:Darwin      23.1.0
Operating system:  Mac

Log Fragments

No response