Hex VARBINARY values not getting normalized by query planner

[Anders-PlanetScale]

Overview of the Issue

An INSERT of a hex 0x or X' notated value into a VARBINARY column is not normalized by the vtgate query planner.

This means cached query plans for these types of INSERT queries are unique and won't be usable by similar subsequent INSERT statements.

Reproduction Steps

Steps to reproduce this issue, example:

Create a Vitess cluster with a 2 or more shard keyspace in it. For ease of repro I used the vitess/examples/local schema/vschema files. I created a keyspace1, which is unsharded and used for sequences. And a keyspace2, which has 2 shards and contains the schema for the table we will be running inserts on.

Apply schemas and vschemas to the cluster.

vtctlclient -server=localhost:15999 ApplySchema -sql-file ~/vitess/examples/local/create_commerce_seq.sql keyspace1; vtctlclient -server=localhost:15999 ApplySchema -sql-file ~/vitess/examples/local/create_commerce_schema.sql keyspace2; vtctlclient -server=localhost:15999 ApplyvSchema -vschema_file ~/vitess/examples/local/vschema_commerce_seq.json keyspace1; vtctlclient -server=localhost:15999 ApplyvSchema -vschema_file ~/vitess/examples/local/vschema_customer_sharded.json keyspace2

Once the schemas and vschemas are applied run the following test insert.

cat repro.sql
INSERT INTO corder(order_id, customer_id, sku, price) VALUES (100, 290, 0x676F, 1000) ,
(101, 400, 0x7265, 2000) ,
(102, 598, 0x6D65, 500);

mysql -h 127.0.0.1 -P 15306 -u vitess -p < repro.sql

browse to the vtgate web UI and look at the query_plans. On my setup this is located at: http://localhost:15001/debug/query_plans
Notice the query plan for our repro.sql INSERT shows normalized, bind var, values for all but the hex values;

  "Key": "insert into corder(order_id, customer_id, sku, price) values (:vtg1, :vtg2, 0x676F, :vtg3), (:vtg4, :vtg5, 0x7265, :vtg6), (:vtg7, :vtg8, 0x6D65, :vtg9)",
  "Value": {
   "QueryType": "INSERT",
   "Original": "insert into corder(order_id, customer_id, sku, price) values (:vtg1, :vtg2, 0x676F, :vtg3), (:vtg4, :vtg5, 0x7265, :vtg6), (:vtg7, :vtg8, 0x6D65, :vtg9)",

I tried the same thing but with X' notated hex values and it produced the same results, the hex values remain un-normalized in the query plan.

I also edited the vschema_customer_sharded.json to define the column type for the sku column to be VARBINARY, and BINARY in another test.
Example vschema with column type defined;

{
    "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"
                }
            ],
            "columns": [
              {
                "name": "sku",
                "type": "BINARY"
              }  
            ],
            "auto_increment": {
                "column": "order_id",
                "sequence": "order_seq"
            }
        }
    }
}

Binary version

This was tested and reproduced on Vitess v10.0.2, v11.0.0, and v12.0.0

go version go1.17.1 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): Pop!_OS 20.10
• Kernel version (output of uname -sr): Linux 5.11.0-7620-generic
• Architecture (output of uname -m): x86_64

[Anders-PlanetScale]

Fixed by #9118