Bug Report: the join queries of cross keyspace are not longer supported in versions higher than release-16.0

[isredstar]

Overview of the Issue

Since upgrade vitess v16.0 to v18.0.2，the join queries of cross keyspace are no longer supported.
for example: sharded corder keyspace and sharded customer keyspace
v 16.0:
mysql> select co.id,co.name from corder.tb1 co join customer.tb1 cu on cu.id=co.id where cu.id=1;
+------+------+
| id | name |
+------+------+
| 1 | ddd |
+------+------+
1 row in set (0.05 sec)

v18.0.2:
mysql> select co.id,co.name from corder.tb1 co join customer.tb1 cu on cu.id=co.id where cu.id=1;
ERROR 1235 (42000): VT12001: unsupported: cross-shard correlated subquery

main branch:
mysql> select co.id,co.name from corder.tb1 co join customer.tb1 cu on cu.id=co.id where cu.id=1;
ERROR 1235 (42000): VT12001: unsupported: cross-shard correlated subquery

Reproduction Steps

Reproduce it using vitess-tester:

vitess-tester -alsologtostderr -logtostderr -vschema=SrvVSchema_Test.json cross_keyspace_test.test

cross_keyspace_test.test:

use customer;
create table if not exists customer(
  customer_id bigint not null,
  email varbinary(128),
  primary key(customer_id)
) ENGINE=InnoDB;
insert into customer.customer(customer_id, email) values(1, '[[email protected]](mailto:[email protected])');
insert into customer.customer(customer_id, email) values(2, '[[email protected]](mailto:[email protected])');
insert into customer.customer(customer_id, email) values(3, '[[email protected]](mailto:[email protected])');
insert into customer.customer(customer_id, email) values(4, '[[email protected]](mailto:[email protected])');
insert into customer.customer(customer_id, email) values(5, '[[email protected]](mailto:[email protected])');
use corder;
create table if not exists corder(
  order_id bigint not null,
  customer_id bigint,
  sku varbinary(128),
  price bigint,
  primary key(order_id)
) ENGINE=InnoDB;
insert into corder.corder(order_id, customer_id, sku, price) values(1, 1, 'SKU-1001', 100);
insert into corder.corder(order_id, customer_id, sku, price) values(2, 2, 'SKU-1002', 30);
insert into corder.corder(order_id, customer_id, sku, price) values(3, 3, 'SKU-1002', 30);
insert into corder.corder(order_id, customer_id, sku, price) values(4, 4, 'SKU-1002', 30);
insert into corder.corder(order_id, customer_id, sku, price) values(5, 5, 'SKU-1002', 30);
use customer;
--error ERROR 1235 (42000): VT12001: unsupported: cross-shard correlated subquery
select co.order_id, co.customer_id, co.price from corder.corder co left join customer.customer cu on co.customer_id=cu.customer_id where cu.customer_id=1;

SrvVSchema_Test.json:

{
  "keyspaces": {
    "customer": {
      "sharded": true,
      "vindexes": {
        "hash": {
          "type": "hash",
          "params": {},
          "owner": ""
        }
      },
      "tables": {
        "customer": {
          "type": "",
          "column_vindexes": [
            {
              "column": "customer_id",
              "name": "hash",
              "columns": []
            }
          ],
          "columns": [],
          "pinned": "",
          "column_list_authoritative": false,
          "source": ""
        }
      },
      "require_explicit_routing": false,
      "foreign_key_mode": 0,
      "multi_tenant_spec": null
    },
    "corder": {
      "sharded": true,
      "vindexes": {
        "hash": {
          "type": "hash",
          "params": {},
          "owner": ""
        }
      },
      "tables": {
        "corder": {
          "type": "",
          "column_vindexes": [
            {
              "column": "customer_id",
              "name": "hash",
              "columns": []
            }
          ],
          "columns": [],
          "pinned": "",
          "column_list_authoritative": false,
          "source": ""
        }
      },
      "require_explicit_routing": false,
      "foreign_key_mode": 0,
      "multi_tenant_spec": null
    }
  },
  "routing_rules": {
    "rules": []
  },
  "shard_routing_rules": {
    "rules": []
  },
  "keyspace_routing_rules": null,
  "mirror_rules": {
    "rules": []
  }
}

ERROR:
mysql> select co.order_id, co.customer_id, co.price from corder.corder co left join customer.customer cu on co.customer_id=cu.customer_id where cu.customer_id=1;
ERROR 1235 (42000): VT12001: unsupported: cross-shard correlated subquery

Binary Version

[vitess@dev2 vitess-main]$ vtgate --version
vtgate version Version: 21.0.0-SNAPSHOT (Git revision  branch '') built on Wed Jul 31 19:32:29 CST 2024 by vitess@dev2 using go1.22.5 linux/amd64

Operating System and Environment details

[vitess@dev2 vitess-main]$ cat /etc/os-release
NAME="CentOS Stream"
VERSION="8"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Stream 8"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://centos.org/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_SUPPORT_PRODUCT_VERSION="CentOS Stream"

[vitess@dev2 vitess-main]$ uname -sr
Linux 4.18.0-535.el8.x86_64

[vitess@dev2 vitess-main]$ uname -m
x86_64

Log Fragments

No response