Bug Report: fully qualified keyspace name in subquery demanded to be correct, but not used

[wiebeytec]

Overview of the Issue

When you fully qualify a keyspace name on a table in sub query, you get Unknown database 'example_unittest_sites' (errno 1049) (sqlstate 42000) .

Example query:

select * from example_unittest_sites.fake_lld as lld 
where not exists 
(select 1 from example_unittest_sites.fake_alarmLog as al where al.idSite = lld.idSite);

Referring to example_unittest_sites.fake_alarmLog has the following conceptual conflict:

• It demands the keyspace exists, otherwise you get ERROR 1105 (HY000): VT05003: unknown database 'example_unittest_sites_wrong' in vschema
• But it is not parsed, so it's simply passed as database name to the shard, resulting in Unknown database 'example_unittest_sites'

This is the explain query:

mysql> vexplain select * from example_unittest_sites.fake_lld as lld where not exists (select 1 from example_unittest_sites.fake_alarmLog as al where al.idSite = lld.idSite) \G
*************************** 1. row ***************************
JSON: {
        "OperatorType": "Route",
        "Variant": "Scatter",
        "Keyspace": {
                "Name": "example_unittest_sites",
                "Sharded": true
        },
        "FieldQuery": "select lld.idSite, lld.valueFloat from fake_lld as lld where 1 != 1",
        "Query": "select lld.idSite, lld.valueFloat from fake_lld as lld where not exists (select 1 from example_unittest_sites.fake_alarmLog as al where al.idSite = lld.idSite)",
        "Table": "fake_lld"
}
1 row in set (0,01 sec)

In the explain output above, the query select 1 from example_unittest_sites.alarmLog as al where al.idSite = lld.idSite is just taken as is, and can't be executed because MySQL doesn't know the DB.

I can work around it by not fully-qualifying and just saying alarmLog.

When I try to subquery on a table in a different shard, I get ERROR 1105 (HY000): VT12001: unsupported: correlated subquery is only supported for EXISTS. It also says that when I invert the condition in the subquery (where al.idSite != lld.idSite).

I don't know what the fix would be. Because it does seem to detect the case of trying to find data that is not in the shard, perhaps the only problem is that it doesn't remove the database name from table name.

Reproduction Steps

Deploy SQL:

CREATE TABLE `fake_lld` (
  `idSite` int unsigned NOT NULL,
  `valueFloat` float DEFAULT NULL,
  
  PRIMARY KEY (`idSite`)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;



CREATE TABLE `fake_alarmLog` (
  `idAlarm` int unsigned NOT NULL AUTO_INCREMENT,
  `idSite` int unsigned NOT NULL,
  PRIMARY KEY (`idAlarm`)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

VSchema:

{
  "sharded": true,
  "vindexes": {
    "a_standard_hash": {
      "type": "hash"
    }
  },
  "tables": {
    "fake_lld": {
      "column_vindexes": [
        {
          "column": "idSite",
          "name": "a_standard_hash"
        }
      ]
    },
    "fake_alarmLog": {
      "column_vindexes": [
        {
          "column": "idSite",
          "name": "a_standard_hash"
        }
      ]
    }
  }
}

Binary Version

vtgate --version
vtgate version Version: 18.0.0 (Git revision 9a6f5262f7707ff80ce85c111d2ff686d85d29cc branch 'HEAD') built on Mon Nov  6 12:16:43 UTC 2023 by runner@fv-az422-64 using go1.21.3 linux/amd64

Operating System and Environment details

PRETTY_NAME="Ubuntu 22.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.3 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy

### Log Fragments

_No response_