Single shard update with select returns: Unsupported: subqueries in sharded DML

[akenneth]

Overview of the Issue

When using an UPDATE statement with SELECT, Vitess returns unsupported: subqueries in sharded DML even if the query is routed to a single shard.

On the tutorial data, this query fails with the above error even though it targets a specific shard:

update
    corder
set
    price = (
        select
            COUNT(order_id)
        from
            corder
        where
            corder.customer_id = 1
    )
where
    customer_id = 1;

Note: this happens on recent main and on v13.0.0-rc1.

Expectation: The query can be routed to a single shard, it should run successfully without raising an error.

Background

We are trying to move our MySQL cluster into Vitess. We have a query that looks like:

update t1 set c1 = 
  (
    select COUNT(t2.id) 
    from t2 
    where t2.t1_id = 1       
      and t2.c2 = true
      and t2.c4 = 2
  ) 
where t1.id = 1
and t1.c3 = 2

Where the two tables (t1,t2) are sharded by (c3,c4) so t1.c3 = 2 and t2.c4 = 2 live in the same shard.
Running this query in our test sharded setup fails with the exception above.

Our query is a bit more complicated than the test data case I listed on top, but should behave the same. Both cases should be routed to a single shard and be handled by it.

Reproduction Steps

- Pulled `https://github.com/vitessio/vitess` 
- Check out `v13.0.0-rc1` tag (failed with `main` as well with commit `e8bf16189da1234286502ffd3a9161e34a70d3b7`).
- Follow the user guides from the [website](https://vitess.io/docs/13.0/get-started/local-docker/). (see "List of commands" below)
- Connect to mysql `mysql --protocol=tcp --port=15306`
- `use customer; update corder set price = (select COUNT(order_id) from corder where corder.customer_id = 1 ) where customer_id = 1;`
See:

ERROR 1235 (42000): unsupported: subqueries in sharded DML

List of commands

make docker_local
./docker/local/run.sh
mysql --table < ../common/insert_commerce_data.sql

for i in 200 201 202; do
 CELL=zone1 TABLET_UID=$i ./scripts/mysqlctl-up.sh
 CELL=zone1 KEYSPACE=customer TABLET_UID=$i ./scripts/vttablet-up.sh
done

vtctlclient InitShardPrimary -force customer/0 zone1-200
vtctlclient ReloadSchemaKeyspace customer

mysql --table --execute="show vitess_tablets"

vtctlclient MoveTables -source commerce -tables 'customer,corder' Create customer.commerce2customer

vtctlclient MoveTables -tablet_types=rdonly,replica SwitchTraffic customer.commerce2customer
vtctlclient MoveTables -tablet_types=primary SwitchTraffic customer.commerce2customer
vtctlclient MoveTables Complete customer.commerce2customer

vtctlclient ApplySchema -sql-file create_commerce_seq.sql commerce
vtctlclient ApplyVSchema -vschema_file vschema_commerce_seq.json commerce
vtctlclient ApplyVSchema -vschema_file vschema_customer_sharded.json customer
vtctlclient ApplySchema -sql-file create_customer_sharded.sql customer

for i in 300 301 302; do
 CELL=zone1 TABLET_UID=$i ./scripts/mysqlctl-up.sh
 SHARD=-80 CELL=zone1 KEYSPACE=customer TABLET_UID=$i ./scripts/vttablet-up.sh
done

for i in 400 401 402; do
 CELL=zone1 TABLET_UID=$i ./scripts/mysqlctl-up.sh
 SHARD=80- CELL=zone1 KEYSPACE=customer TABLET_UID=$i ./scripts/vttablet-up.sh
done

vtctlclient InitShardPrimary -force customer/-80 zone1-300
vtctlclient InitShardPrimary -force customer/80- zone1-400

vtctlclient Reshard -source_shards '0' -target_shards '-80,80-' Create customer.cust2cust

vtctlclient Reshard -tablet_types=rdonly,replica SwitchTraffic customer.cust2cust
vtctlclient Reshard -tablet_types=primary SwitchTraffic customer.cust2cust

vtctlclient Reshard Complete customer.cust2cust

for i in 200 201 202; do
 CELL=zone1 TABLET_UID=$i ./scripts/vttablet-down.sh
 CELL=zone1 TABLET_UID=$i ./scripts/mysqlctl-down.sh
done

Sample data

mysql --table < ../common/select_customer-80_data.sql

Returns:
Using customer/-80
Customer
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | alice@domain.com   |
|           2 | bob@domain.com     |
|           3 | charlie@domain.com |
|           5 | eve@domain.com     |
+-------------+--------------------+
COrder
+----------+-------------+----------+-------+
| order_id | customer_id | sku      | price |
+----------+-------------+----------+-------+
|        1 |           1 | SKU-1001 |   100 |
|        2 |           2 | SKU-1002 |    30 |
|        3 |           3 | SKU-1002 |    30 |
|        5 |           5 | SKU-1002 |    30 |
+----------+-------------+----------+-------+

Vschema

{
    "sharded": true,
    "vindexes": {
        "hash": {
            "type": "hash"
        }
    },
    "tables": {
        "customer": {
            "column_vindexes": [
                {
                    "column": "customer_id",
                    "name": "hash"
                }
            ],
            "auto_increment": {
                "column": "customer_id",
                "sequence": "customer_seq"
            }
        },
        "corder": {
            "column_vindexes": [
                {
                    "column": "customer_id",
                    "name": "hash"
                }
            ],
            "auto_increment": {
                "column": "order_id",
                "sequence": "order_seq"
            }
        }
    }
}

```

Binary Version

This happens on recent `main` and on `v13.0.0-rc1`.

Version: 13.0.0-rc1 (Git revision 3fa0fba54b branch 'HEAD') built on Sun Feb  6 12:16:43 UTC 2022 by vitess@buildkitsandbox using go1.17.6 linux/amd64

### Operating System and Environment details

```shell
cat /etc/os-release
Version: 13.0.0-rc1 (Git revision 3fa0fba54b branch 'HEAD') built on Sun Feb  6 12:16:43 UTC 2022 by vitess@buildkitsandbox using go1.17.6 linux/amd64
vitess@b5e3a7aef28b:/vt/local$ cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"


uname -sr
Linux 5.4.0-1067-azure

uname -m
x86_64

### Log Fragments

_No response_

[harshit-gangal]

Update queries do not support such complex query today, I have marked this as feature request.

[Victor-Tseng]

Meanwhile, does any one have suggestions to re-write the SQL statements to achieve what Alon wants to do?

[glortho]

@harshit-gangal do you have any ballpark sense of the timeline for this feature?

[harshit-gangal]

The the feature was merged 20 days back.

PR: #9871

This is only usable via our new Gen4 query planner.
More details: https://vitess.io/docs/13.0/reference/compatibility/query_planner/