DELETE fails in vtgate when using table alias / custom routing

Overview of the Issue

For migration purposes, we want to allow app/clients to access same table name with two names/alias, eg: customer <=> customer2. For such a routing rule was added. It shows that both SELECT, INSERT and UPDATE work fine, but DELETE errors out.

mysql> SELECT * FROM customer LIMIT 1;
+-------------+------------------------------------+
| customer_id | email                              |
+-------------+------------------------------------+
|           1 | 0x616C69636540646F6D61696E2E636F6D |
+-------------+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM customer2 LIMIT 1;
+-------------+------------------------------------+
| customer_id | email                              |
+-------------+------------------------------------+
|           1 | 0x616C69636540646F6D61696E2E636F6D |
+-------------+------------------------------------+
1 row in set (0.01 sec)

mysql> DELETE FROM customer2 WHERE customer_id=5;
ERROR 1105 (HY000): vtgate: http://ubuntu-bionic:15001/: target: commerce.0.master, used tablet: zone1-100 (ubuntu-bionic): vttablet: rpc error: code = InvalidArgument desc = syntax error at position 24 near 'as' (CallerID: userData1)

Reproduction Steps

Run the 101_initial_cluster.sh script in local example and insert sample data. https://vitess.io/docs/get-started/local/


vagrant@ubuntu-bionic:~/my-vitess-example$ ./101_initial_cluster.sh
add /vitess/global
add /vitess/zone1
<SNIP>
New VSchema object:
{
"tables": {
"corder": {

},
"customer": {

},
"product": {

}
}
}
If this is not what you expected, check the input data (as JSON parsing will skip unexpected fields).
Waiting for vtgate to be up...
vtgate is up!
Access vtgate at http://ubuntu-bionic:15001/debug/status

INSERT SAMPLE DATA

vagrant@ubuntu-bionic:~/my-vitess-example$ source env.sh vagrant@ubuntu-bionic:~/my-vitess-example$ mysql < ~/vitess-9.0.0-daa6085/examples/common/insert_commerce_data.sql vagrant@ubuntu-bionic:~/my-vitess-example$ mysql < ~/vitess-9.0.0-daa6085/examples/common/select_commerce_data.sql Using commerce/0 Customer customer_id email 2 bob@domain.com 3 charlie@domain.com 4 dan@domain.com Product sku description price SKU-1001 Monitor 100 SKU-1002 Keyboard 30 COrder order_id customer_id sku price 1 1 SKU-1001 100 2 2 SKU-1002 30 3 3 SKU-1002 30 4 4 SKU-1002 30 5 5 SKU-1002 30


2. Now add custom routing so that **customer2** table is an alias to original **customer** table:

```json
vagrant@ubuntu-bionic:~/my-vitess-example$ cat lmrules.json
{
  "rules": [
    {
      "from_table": "customer2",
      "to_tables": [
        "commerce.customer"
      ]
    },
    {
      "from_table": "commerce.customer2",
      "to_tables": [
        "commerce.customer"
      ]
    }
  ]
}

vagrant@ubuntu-bionic:~/my-vitess-example$ vtctl $TOPOLOGY_FLAGS ApplyRoutingRules -rules_file lmrules.json
New RoutingRules object:
{
  "rules": [
    {
      "fromTable": "customer2",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "commerce.customer2",
      "toTables": [
        "commerce.customer"
      ]
    }
  ]
}

When using the aliased table name, SELECT, INSERT and UPDATE work fine:


mysql> SELECT * FROM customer2;
+-------------+----------------------------------------+
| customer_id | email                                  |
+-------------+----------------------------------------+
|           1 | 0x616C69636540646F6D61696E2E636F6D     |
|           2 | 0x626F6240646F6D61696E2E636F6D         |
|           3 | 0x636861726C696540646F6D61696E2E636F6D |
|           4 | 0x64616E40646F6D61696E2E636F6D         |
|           5 | 0x65766540646F6D61696E2E636F6D         |
+-------------+----------------------------------------+
5 rows in set (0.00 sec)

mysql> UPDATE customer2 SET email='foo@example.org'; Query OK, 5 rows affected (0.02 sec)

mysql> INSERT INTO customer2 VALUES (NULL, 'bar@example.org'); Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM customer2; +-------------+----------------------------------+ | customer_id | email | +-------------+----------------------------------+ | 1 | 0x666F6F406578616D706C652E6F7267 | | 2 | 0x666F6F406578616D706C652E6F7267 | | 3 | 0x666F6F406578616D706C652E6F7267 | | 4 | 0x666F6F406578616D706C652E6F7267 | | 5 | 0x666F6F406578616D706C652E6F7267 | | 6 | 0x626172406578616D706C652E6F7267 | +-------------+----------------------------------+ 6 rows in set (0.00 sec)

4. However, DELETE gives an error when referencing the alias name:

mysql> DELETE FROM customer WHERE customer_id=6; Query OK, 1 row affected (0.04 sec)

mysql> DELETE FROM customer2 WHERE customer_id=5; ERROR 1105 (HY000): vtgate: http://ubuntu-bionic:15001/: target: commerce.0.master, used tablet: zone1-100 (ubuntu-bionic): vttablet: rpc error: code = InvalidArgument desc = syntax error at position 24 near 'as' (CallerID: userData1)


#### Binary version

```sh
vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: daa6085 (Git branch 'HEAD') built on Tue Jan 26 09:37:50 UTC 2021 by runner@fv-az195-520 using go1.15.7 linux/amd64

Operating system and Environment details

OS, Architecture, and any other information you can provide about the environment.

Operating system (output of cat /etc/os-release):

vagrant@ubuntu-bionic:~/my-vitess-example$ cat /etc/os-release
NAME="Ubuntu"
VERSION="18.04.5 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.5 LTS"
VERSION_ID="18.04"
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"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

Kernel version (output of uname -sr):

vagrant@ubuntu-bionic:~/my-vitess-example$ uname -sr
Linux 4.15.0-135-generic

Architecture (output of uname -m):

vagrant@ubuntu-bionic:~/my-vitess-example$ uname -m
x86_64

Log Fragments


vagrant@ubuntu-bionic:~/my-vitess-example$ tail -n3 vtdataroot/tmp/vtgate*
==> vtdataroot/tmp/vtgate.INFO <==
I0312 10:18:49.527257    2704 unix_socket.go:36] Not listening on socket file
I0312 10:18:49.571739    2704 vschema_manager.go:74] Received vschema update
I0312 10:20:05.958276    2704 vschema_manager.go:74] Received vschema update

==> vtdataroot/tmp/vtgate.out <==

==> vtdataroot/tmp/vtgate.pid <== 2704

==> vtdataroot/tmp/vtgate_querylog.txt <== Execute 127.0.0.1:44858 vagrant 'userData1' 'vagrant' 2021-03-12 10:21:09.509521 2021-03-12 10:21:09.547033 0.037512 0.000276 0.037229 0.000003 DELETE "delete from customer where customer_id = :vtg1" map[vtg1:type:INT64 value:"6" ] 1 1 "" "commerce" "" "MASTER" Execute 127.0.0.1:44858 vagrant 'userData1' 'vagrant' 2021-03-12 10:21:14.862116 2021-03-12 10:21:14.863715 0.001599 0.000108 0.001486 0.000000 DELETE "delete from customer2 where customer_id = :vtg1" map[vtg1:type:INT64 value:"5" ] 1 0 "target: commerce.0.master, used tablet: zone1-100 (ubuntu-bionic): vttablet: rpc error: code = InvalidArgument desc = syntax error at position 24 near 'as' (CallerID: userData1)" "commerce" "" "MASTER" Execute 127.0.0.1:44858 vagrant 'userData1' 'vagrant' 2021-03-12 10:21:50.556275 2021-03-12 10:21:50.558057 0.001782 0.000065 0.001713 0.000000 SELECT "select * from customer2" map[] 1 5 "" "commerce" "customer" "MASTER"

==> vtdataroot/tmp/vtgate.ubuntu-bionic.vagrant.log.INFO.20210312-101849.2704 <== I0312 10:18:49.527257 2704 unix_socket.go:36] Not listening on socket file I0312 10:18:49.571739 2704 vschema_manager.go:74] Received vschema update I0312 10:20:05.958276 2704 vschema_manager.go:74] Received vschema update

vitessio / vitess