You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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;
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
The text was updated successfully, but these errors were encountered:
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
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;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
Operating System and Environment details
Log Fragments
No response
The text was updated successfully, but these errors were encountered: